商用運用ができる実践的
PostgreSQL技術者の育成
2021年11月12日
PostgreSQL Conference Japan 2021 (A3)
PostgreSQLエンタープライズ・コンソーシアム
富士通Ｊａｐａｎ株式会社 多田 明弘
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

自己紹介◼ 多田明弘 （ただ あきひろ）◼ a_tada@fujitsu.com◼ 富士通Ｊａｐａｎ 民需ソリューション開発本部所属◼ データベース歴はOracle7から始めて25年以上開発者、アーキティクト、開発PM、運用など一通り経験。現在
は共通技術部門として本部内の標準化等施策を主務◼ PostgreSQLは5年程度◼ PostgreSQL エンタープライズコンソーシアム（PGECons）の活動には2017年度より参画

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

2

PGECons について
◼ PGEConsの発足と目的
 2012年4月11日発足
 ミッションクリティカル性の高いエンタープライズ領域へのPostgreSQLの普及を推進するため、各種ツールやPostgreSQL本体に関する利用技術情報の収集と提供および、その整備などの活動を企業ベースで展開する
活動項目

概要

情報発信

会員の導入実績を基に、PostgreSQLおよび周辺ツールに関する情報を集約し、情報発信サイトやセミナ等を通じて提供する

共同検証

エンタープライズ領域への適用に向けて必要となる情報を、実証を通じて充実を図る

開発コミュニティへのフィードバック

よりミッションクリティカル性の高い領域への適用に向けた技術的な課題を集約し、開発コミュニティに要望を発信する

開発プロジェクト支援

会員間での機能拡張に関する連携開発や、必要な周辺ツールの開発プロジェクト支援を進める
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

3

2021年度 体制・会員構成◼ 会員は法人およびそれに準ずる団体で構成
2021年度体制
理事長
運営委員長
技術部会長
CR部会長
広報・発信部会長
広報・発信副部会長
事務局長
監事

総会
監事

理事会
事務局
運営委員会

技術部会

WG
種別
正会員

広報・発信部会

WG：日本電信電話株式会社：日本電気株式会社：富士通株式会社：SRA OSS, Inc. 日本支社
：株式会社 日立製作所：株式会社アシスト：SRA OSS, Inc. 日本支社
：オープンソース活用研究所

CR部会

WG
概要

理事

理事会に参加、理事長および運営委員長は理事のうちから就任する

運営委員

運営委員会に参加、部会長およびWG長は運営委員から就任する

総会議決権ありワーキンググループ（WG）に参加し、活動に貢献

一般会員

メーリングリストやWebなどから、活動情報を取得することが可能
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

なし
4ワーキンググループ活動について◼

現在3つのワーキンググループにて活動中
 新技術検証WG （WG1）◼ 新バージョンの性能や新技術の検証を通じて有用性を明確化◼ スケールアップ検証、新機能における性能特性調査等

 移行WG （WG2）◼ 異種DBMSからの移行をテーマに活動◼ 商用DBMSからの移行プロセスに伴う技術調査や検証を実施

 課題検討WG （WG3）◼ データベース管理者やアプリケーション開発者が抱える現場の課題や困り事に対するテーマを設定◼ 可用性・運用性・保守性・セキュリティ・接続性が主な課題領域

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

5

PostgreSQL自習書のねらい
Q7:PostgreSQLをよりミッションクリティカル性の高いエンター
プライズ領域で採用するための課題は何だとお考えですか？

PGECons 2019年度 成果発表会アンケートより
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

6

PostgreSQL自習書のねらい
Q２１：PGEConsに何を期待していますか？またどんな情報を発信してほしいですか？（複数回答可）

PGECons 2019年度 成果発表会アンケートより
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

7

PostgreSQL自習書のねらい

エンジニア不足

設計・運用の情報

既存のシステムで設計・開発・運用をしてきた

PostgreSQLの技術者に移行しよう

移行元のDB技術者を

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

8

PostgreSQL自習書のねらい「PostgreSQL自習書」の内容とダウンロード先 「PostgreSQL自習書」の内容◼

◼

◼

DBAの立場として把握しておきたい物理的、論理的な構造と全体俯瞰
商用運用上の基本的なポイント
RDBMSやSQLの基本は割愛されています→ データベースを初めて学ぶ方には向きません ダウンロード先◼

https://pgecons-sec-tech.github.io/techreport/pdf/wg2_PostgresSelfStudyBook.pdf

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

9商用での安全な運用の勘所◼ お話の前提 Oracleの経験はあるけどPostgreSQＬは初めての人◼ 商用運用でデータベースに望むこと 「PostgreSQL自習書」の運用への応用として以下2点の勘所を説明します

SQLの応答時間が一定であること
SQLが遅くなったとき原因が特定できること
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

10商用での安全な運用の勘所◼ SQLの応答時間が一定であること絶対避けたいこと運用中に遅くなる
一部運用の変化に伴う場合は許容します
運用の変化

SQLの応答劣化

特徴

データ量の増加予測できる＝対策できる

同時接続数の増加許容できる＝遅くなることを理解できる

上記以外許容できない❓❓❓❓

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

11商用での安全な運用の勘所◼ SQLの応答時間が一定であること運用で起こる現象
だんだん遅くなる

急に遅くなる

原因

対策

表のブロック密度劣化による物理的なアクセス量の増加• 適切なFILLFACTORでブ
ロック密度を一定に保つ

索引の物理的な状態が悪化• 適切なFILLFACTORでHOTを有効にする• 索引の再構築

実行計画が変化した••••

開発中の性能テストはク テスト時、データの格納状態リアしたが運用では遅い が物理的に最良の状態だっただけ

物理的な状態を一定に保つ
複雑なSQLを避ける
ヒント句
統計情報を固定• 運用状態を見越したテスト（一定期間運用テストした後に性能テストを実施する）

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

12商用での安全な運用の勘所◼ SQLの応答時間が一定であること運用で起こる現象
だんだん遅くなる

急に遅くなる

原因

対策

表のブロック密度劣化による物理的なアクセス量の増加• 適切なFILLFACTORでブ
ロック密度を一定に保つ

索引の物理的な状態が悪化• 適切なFILLFACTORでHOTを有効にする• 索引の再構築

実行計画が変化した••••

PostgreSQL自習書で解説される部分

開発中の性能テストはク テスト時、データの格納状態リアしたが運用では遅い が物理的に最良の状態だっただけ

物理的な状態を一定に保つ
複雑なSQLを避ける
ヒント句
統計情報を固定• 運用状態を見越したテスト（一定期間運用テストした後に性能テストを実施する）

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

13商用での安全な運用の勘所◼ SQLの応答時間が一定であること
 表のブロック密度が劣化◼ PostgreSQLの読み取り一貫性は「追記型」を採用

1行分のデータ1バイト更新する

COMMIT前を参照可能にする

1行丸ごと追加
ブロックイメージ（実際の格納は上記の通りではありません）
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

14商用での安全な運用の勘所◼ SQLの応答時間が一定であること
 表のブロック密度が劣化◼ 全件更新したら表のサイズはどうなるか

運用では処理フラグ（１バイト）を全件更新など
表 更新前

表のサイズが2倍

表 更新後

VACUUM後極端な例ですが、ブロックの利用率を定義するFILLFACTOR＝100%（デフォルト値）の場合、最良のサイズの２倍になります。全件読み込み時のアクセス量、キャッシュ使用量も２倍になります。
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

15商用での安全な運用の勘所◼ SQLの応答時間が一定であること
 索引のブロック密度が劣化◼ FILLFACTOR=100%（デフォルト）で表を更新すると更新カラムが索引カラムでなくても索引も更新される

表 更新前

行の格納ブロックの位置が変わる

表 更新後

更新前の索引# select tree_level,index_size,avg_leaf_density from pgstatindex('pgbench_accounts_pkey');
tree_level | index_size | avg_leaf_density------------+------------+-----------------2 |11255808 |90.03

更新後の索引# select tree_level,index_size,avg_leaf_density from pgstatindex('pgbench_accounts_pkey');
tree_level | index_size | avg_leaf_density------------+------------+-----------------索引まで更新される
2 |22487040 |45.18
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

16商用での安全な運用の勘所◼ SQLの応答時間が一定であること
 FILLFACTORを適切に指定する。
◼ 全件更新があると仮定し、FILLFACTOR=50%で指定

表 更新前
表のサイズは変わらない（100%のときの2倍ではあるが）
表 更新後
更新行は同一ブロック内に書き込まれる

更新前の索引# select tree_level,index_size,avg_leaf_density from pgstatindex('pgbench_accounts_pkey');
tree_level | index_size | avg_leaf_density------------+------------+-----------------2 |11255808 |90.03

更新後の索引# select tree_level,index_size,avg_leaf_density from pgstatindex('pgbench_accounts_pkey');
tree_level | index_size | avg_leaf_density------------+------------+-----------------索引はそのまま
2 |11255808 |90.03
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

17商用での安全な運用の勘所◼ SQLの応答時間が一定であること

運用の勘所

効果

表毎にFILLFACTORを適切に指定しましょう• SQL応答のバラツキが減少する• 索引の更新が減少する• 実行計画の変化が抑えられる

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

18商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 正常時と何が違うか
正常時
レポート

データベース

PostgreSQL自習書では運用上の基本部分まで解説ログファイル、リポジトリデータなど比較
レポート

遅いとき

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

19商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの標準機能から取得できる情報

情報

手段

現時点の情報または現時点までの累積情報

過去の時系列的な情報•••システムカタログ
統計情報コレクタ
pg_stat_statementsなどの追加モジュールサーバログ（何を出力するかはパラメータファイルで調整）

過去の情報はサーバログのみ
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

20商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの標準機能の情報からできる対応◼ バッチ処理で遅いSQLを知りたい

サーバログで確認する。
log_min_duration_statementで出力閾値を指定→遅いSQLはわかるが、なぜ遅いかは不明◼ バッチ処理で遅いSQLの実行計画を知りたい

サーバログで確認する。
auto_explainで出力閾値を指定→実行計画から“遅そうな部分”を想定
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

21

商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの標準機能の情報からできる対応◼ オンライン処理で遅いSQLを知りたい

調査時点でも遅い場合
・explain analyzeでSQLのどの実行部分が遅いのか確認
・DMLの場合は本番データに影響を与えないように工夫➢ 調査用ダミーデータを事前に用意しておく➢ explain analyzeをトランザクションで実行し確認後にrollback

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

22商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの標準機能の情報からできる対応◼ バッチ、オンライン共通➢ pgstattupleで表のデータ格納状態が劣化していないか➢ pgstatindexで索引の状態が劣化していないか➢ pg_stat_all_tablesで想定外にseq処理が多くないか➢ pg_stat_all_indexで想定した索引が使われているかなど、全般的な状況から原因を絞り込む
欠点：統計情報コレクタはリセットしてからの累積のため、過去の問題のあった“時間帯”の発生量はわからない
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

23商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの標準機能の情報からできる対応◼ バッチ、オンライン共通(前スライド欠点の解決案）

統計情報コレクタを累積して調査したい時間帯を終了時間ー開始時間の発生の差分から求める元の定義に日時を追加したワーク
pg_stat_XXXX
pg_stsat_statements他

w_pg_stat_XXXX
w_pg_stsat_statements
他
1回/Hで全件追加

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

1回/日で古い日時を削除
24

商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの標準機能の情報からできる対応◼ バッチ、オンライン共通(解決案の実施例）

pg_stat_statementsを累積し任意時間の差分から分析
start_time

end_time

ユーザ名 クエリID

クエリ

実行回数 実行時間(ms) 平均実行時間(ms) 影響受けた行数

2019-09-10
18:00:00+09

2019-09-10
19:00:00+09

postgres831879657

UPDATE pgbench_tellers SET tbalance = tbalance + $1
WHERE tid = $2

5,000

145,593.28

29.12

5,000

2019-09-10
18:00:00+09

2019-09-10
19:00:00+09

fsepuser

831879657

UPDATE pgbench_tellers SET tbalance = tbalance + $1
WHERE tid = $2

5,000

141,064.75

28.21

5,000

2019-09-10
18:00:00+09

2019-09-10
19:00:00+09postgres 3872086279

UPDATE pgbench_branches SET bbalance = bbalance + $1
WHERE bid = $2

5,000

138,750.83

27.75

5,000

2019-09-10
18:00:00+09

2019-09-10
19:00:00+09

fsepuser 3872086279

UPDATE pgbench_branches SET bbalance = bbalance + $1
WHERE bid = $2

5,000

115,700.57

23.14

5,000

2019-09-10
18:00:00+09

2019-09-10
19:00:00+09

postgres 3371683456

UPDATE pgbench_accounts SET abalance = abalance + $1
WHERE aid = $2

5,000

1,243.99

0.25

5,000

2019-09-10
18:00:00+09

2019-09-10
19:00:00+09

fsepuser 3371683456

UPDATE pgbench_accounts SET abalance = abalance + $1
WHERE aid = $2

5,000

879.24

0.18

5,000

開始-終了時刻間での発生量
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

25商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの周辺ソフトウェアから取得できる情報

情報

手段（例）

現時点までの累積情報

pg_store_plans(SQL,実行計画毎に実行統計が累積される）
pg_statsinfo（＋pg_store_plans）

過去の時系列的な情報

上記はLinux版のみ利用可能。
Windows版,クラウドのデータベースサービス上では使用できません。
→ クラウドサービスが提供するツールかP24のようなスライドの対策で対応
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

26商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの周辺ソフトウェアから情報からできる対応

pg_statsinfo（＋pg_store_plans）で運用している場合◼ バッチ処理、オンライン処理共通➢ 正常な時間帯と遅い時間帯でレポートを出力し比較

pg_statsinfoのレポートには実行計画は出力されないのでレポート内の遅いSQLのQuery ID、Plan IDでpg_stat_statementsとpg_store_plansビューからSQL文と実行計画も別途取得（→ 次スライドに調査イメージ）するかpg_stats_reporterを追加で導入しコマンドライン機能でレポート出力するとよい。
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

27商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 PostgreSQLの周辺ソフトウェアから情報からできる対応
test01=# select pss.query,psp.* from pg_store_plans as psp,pg_stat_statements as pss
where psp.queryid_stat_statements = pss.queryid
and psp.queryid_stat_statements = -1228162415474808213;-[ RECORD 1 ]-----------+--------------------------------------------------------------------------------------------------query| UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
userid| 10dbid| 26809
queryid| 1445217560
planid| 43793328
queryid_stat_statements | -1228162415474808213
plan| Update on pgbench_tellers (cost=0.14..8.16 rows=1 width=358)+|-> Index Scan using pgbench_tellers_pkey on pgbench_tellers (cost=0.14..8.16 rows=1 width=358)+|
Index Cond: (tid = 2)
calls
| 810800
total_time| 32290370.74642882
min_time| 0.015441
max_time| 1444.273831
mean_time| 39.82532159155185
stddev_time| 62.29389686886388
rows| 810800
shared_blks_hit| 5926214
shared_blks_read| 1
shared_blks_dirtied| 205
shared_blks_written| 17
local_blks_hit| 0local_blks_read| 0local_blks_dirtied| 0
local_blks_written| 0
temp_blks_read| 0
temp_blks_written| 0blk_read_time| 0
blk_write_time| 0
first_call| 2021-09-29 10:44:04.066434+09
last_call| 2021-09-29 13:57:27.097104+09

調査イメージ

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

28商用での安全な運用の勘所◼ SQLが遅くなったときに原因が特定できること
 運用の勘所
正常時

データベース
どこまで取得するか・情報量の増加＝負荷増加（ＣＰＵ，ディスク）→対応するためコストは増加
・分析できないと問題解決長期化のリスク→トラブル対応コストの増加

レポート

ログファイル、リポジトリデータなど

比較
レポート

遅いとき

☞システム要件に応じて決定すること
Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

29

PGEConsウェブサイト◼ https://www.pgecons.org/

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

30

会員募集
◼ 正会員・一般会員を広く募集いたします WG・CR部会で一緒に活動を行っていただける団体様⇒ 正会員 PostgreSQLのエンタープライズ領域に興味を持っている団体様 ⇒ 一般会員

お問い合わせ先：PostgreSQLエンタープライズ・コンソーシアム事務局
メール
Web： jimukyoku@pgecons.org： http://www.pgecons.org/

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.

31

Copyright © PostgreSQL Enterprise Consortium, All Rights Reserved.