PostgreSQLに限らず多くのRDBMSはどのようにデータベースを探索するのかが高速なのかの「実行計画」を立てます。クエリを発行する都度、データベースの大きさ等を計測するのは非現実的な計算量になるので、一定周期で計測する「統計情報」に基づいて「実行計画」を立てます。
このページでは「統計情報」について考察します。
- PostgreSQL インストール
- PostgreSQL データベースの作成
- PostgreSQL contribモジュールの使い方
- PostgreSQL contribモジュールの使い方
- PostgreSQL パラメタの設定方法
- PostgreSQL ベンチマークツールの紹介
- PostgreSQL WAL(Write Ahead Log)の基本説明
- PostgreSQL バックアップとリストア
- PostgreSQL PITR(Point In Time Recovery)基本概念の説明
- PostgreSQL PITR(Point In Time Recovery)操作方法の説明
- PostgreSQL PITR(Point In Time Recovery)タイムライン操作
- PostgreSQL ストリーミングレプリケーションの最小構成
- PostgreSQL ストリーミングレプリケーションのパラメタ説明
- PostgreSQL ストリーミングレプリケーションの障害復旧
- PostgreSQL ストリーミングレプリケーションの多段構成
- PostgreSQL ストリーミングレプリケーションのDR向け設定
- PostgreSQL ストリーミングレプリケーションのコンフリクト
- PostgreSQL 自動バキューム(AUTO VACUUM)
- PostgreSQL HOT(Heap Only Tuple)
- PostgreSQL インデックスのメンテナンス
- PostgreSQL 統計情報の更新 (いまここ)
- PostgreSQL 実行計画
- PostgreSQL スロークエリの調査方法
統計情報の挙動確認
初期設定
統計情報は定期的に自動更新され、多くの場合は統計情報による問題は発生しません。まず、統計情報が自動更新されないと、どのような不都合が発生するかを観察してみましょう。
動作確認のため、統計情報の自動更新が発生しづらいパラメタを投入します。
これは仕様理解を目的とした実験的な設定です。実務では使用しないよう注意ください。
# vi /var/lib/pgsql/13/data/postgresql.conf <omitted> autovacuum_analyze_threshold = 2000
再起動で設定を反映させます。
# systemctl restart postgresql-13.service
動作確認用に200件のレコードを挿入します。
dropdb test
createdb test
psql test << EOF
CREATE TABLE staff(
id serial PRIMARY KEY,
number integer default 0,
f_name varchar(30) default '',
l_name varchar(30) default ''
);
EOF
for i in {1..200}
do
[ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done
統計情報の手動更新
多くの場合、統計情報は自動更新されるため大きな問題には発展しません。自動更新で不都合がある場合は手動の更新も可能です。
このシナリオの場合、自動更新が発生しづらくなっています。統計情報が更新されたか否かはpg_stat_all_tablesテーブルより確認が可能です。analyze_countが統計情報を手動更新した回数で、autoanalyze_countが統計情報を自動更新した回数です。
[postgres@centos10 ~]$ psql test << EOF > \x > SELECT * FROM pg_stat_all_tables where relname ='staff'; > EOF Expanded display is on. -[ RECORD 1 ]-------+------- relid | 17154 schemaname | public relname | staff seq_scan | 1 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 200 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 200 n_dead_tup | 0 n_mod_since_analyze | 200 n_ins_since_vacuum | 200 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0
それでは現在の統計情報を見てみましょう。pg_classテーブルを見ると、現在300件のレコード(tuples)が挿入されているのにも関わらず、0件と表示されている事がわかります。これは統計情報が1度も更新されていないためです。
test=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'staff';
relpages | reltuples
----------+-----------
0 | 0
(1 row)
統計情報を手動で更新するには、ANALYZEコマンドを使用します。
test=# ANALYZE staff ; ANALYZE test=#
統計情報が更新されreltuplesが300に変わった事が確認できます。
test=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'staff';
relpages | reltuples
----------+-----------
2 | 200
(1 row)
実行計画の観察
さらに100件のレコードを追加し、計300件の状態にします。
for i in {201..300}
do
[ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done
この状態で1件のレコードを取得するSQLの実行計画を見てみます。Seq Scanと呼ばれる探索方法でcostが4.50かかるとの計算結果です。
test=# EXPLAIN SELECT * FROM staff WHERE id = 50;
QUERY PLAN
------------------------------------------------------
Seq Scan on staff (cost=0.00..4.50 rows=1 width=10)
Filter: (id = 50)
(2 rows)
実行計画はデータベースをどのように探索するのが最短かを計画立てる機能で、Seq Scan, Index Scanなどの様々な方法があります。この様々な方法をcost比較し、最も最短となる方法を探します。
データベースを全走で探索するSeq Scanは以下のようにコストを算出します。
(ディスクページ読み取り x seq_page_cost) + (スキャンした行 x cpu_tuple_cost)
全走探索で読み取りを実施した後、「WHERE id = 50」でidが一致するかどうかの演算処理が発生します。演算に伴うコストは以下の通り算出します。
(演算回数 x cpu_operator_cost)
デフォルト設定ではseq_page_cost=1.0, cpu_tuple_cost=0.01, cpu_operator_cost=0.0025です。統計情報は更新されてないので、ペース数=2, カラム数=200です。
これら値を元に計算すると、コストは以下のようになります。
(2 * 1.0) + (200 * 0.01) + (200 * 0.0025) = 4.50
さて、ここで統計情報を更新し、もう１回、実行計画を見てみましょう。costが4.50から5.75に増えている事が分かります。
test=# ANALYZE staff;
ANALYZE
test=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'staff';
relpages | reltuples
----------+-----------
2 | 300
(1 row)
test=# EXPLAIN SELECT * FROM staff WHERE id = 50;
QUERY PLAN
------------------------------------------------------
Seq Scan on staff (cost=0.00..5.75 rows=1 width=10)
Filter: (id = 50)
(2 rows)
cost5.75の算出根拠は以下の通りです。
(2 * 1.0) + (300 * 0.01) + (300 * 0.0025) = 5.75
統計情報更新頻度のチューニング
マスタデータならばバッチ処理によるデータ更新の都度、手動VACUUMを実行し統計情報を更新すると良いでしょう。一方、トランザクションデータならば、自動の統計情報更新に任せれば多くの場合はうまくいきます。以下、統計情報の自動更新に関するパラメタをまとめます。
統計情報の自動更新に関するパラメタ
下記の「ANALYZE閾値」を超えた時に、自動バキュームデーモンがANALYZEを開始します。「ANALYZE閾値」は以下の数式によって与えられます。
ANALYZE閾値 = ANALYZE基礎閾値 + ANALYZE規模係数 * タプル数
「ANALYZE基礎閾値」「ANALYZE規模係数」は、それぞれpostgresql.confのautovacuum_analyze_thresholdとautovacuum_analyze_scale_factorでデフォルト設定は50と0.1になります。
[postgres@centos10 ~]$ psql -c "show all " | grep analyze autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze.
例えば、10,000行のテーブルの場合ならば、
50 + 10,000 * 0.1 = 1,050
の計算で1,050を超えるレコードを更新すればANALYZEが発生します。充分大きなテーブルならば、約10%が更新されれればANALYZEされる事になります。
統計情報の自動更新の初回実行
さきほど実験目的で投入したautovacuum_analyze_thresholdをコメントアウトしデフォルト設定に戻します。さらにANALYZEを観察しやすいようlog_min_messagesをDEBUG2に変更します。
vi /var/lib/pgsql/13/data/postgresql.conf <omitted> #autovacuum_analyze_threshold = 2000 log_min_messages = 'DEBUG2'
設定を反映させます。
systemctl restart postgresql-13.service
autovacuum_analyze_thresholdのデフォルト設定は50ですので、新規作成されたテーブルに50を超えるレコードが作成されると、統計情報が更新されます。統計情報の初回更新の様子を観察するために51件のテーブルを作成します。
dropdb test
createdb test
psql test << EOF
CREATE TABLE staff(
id serial PRIMARY KEY,
number integer default 0,
f_name varchar(30) default '',
l_name varchar(30) default ''
);
EOF
for i in {1..51}
do
[ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done
統計情報が更新されるまで待ちます。下記統計情報を表示するSQL文を実行し、レコード件数が51件と表示されれば統計情報が更新された事を意味します。
[postgres@centos10 ~]$ psql test -c "SELECT relpages, reltuples FROM pg_class WHERE relname = 'staff';"
relpages | reltuples
----------+-----------
1 | 51
(1 row)
pg_stat_all_tablesテーブルのlast_autoanalyzeやautoanalyze_countからも統計情報が更新された事を読み取れます。
[postgres@centos10 ~]$ psql test << EOF > \x > SELECT * FROM pg_stat_all_tables where relname ='staff'; > EOF Expanded display is on. -[ RECORD 1 ]-------+---------------------------- relid | 17250 schemaname | public relname | staff seq_scan | 1 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 51 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 51 n_dead_tup | 0 n_mod_since_analyze | 0 n_ins_since_vacuum | 51 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | 2021-01-23 11:52:05.1378+09 vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 1
ログレベルをDEBUG2以上にすれば、ログにも統計情報を更新された旨の出力が表示されます。
$ tail -f /var/lib/pgsql/13/data/log/postgresql-Sat.log <omitted> 2021-01-23 11:52:05.133 JST [27038] DEBUG: "public.staff"を解析しています 2021-01-23 11:52:05.137 JST [27038] DEBUG: "staff": 1ページの内1をスキャン。51の有効な行と0の不要な行が存在。51行をサンプリング。推定総行数は51
充分大きいテーブルの統計情報の自動更新
9,949件のレコードを追加し計10,000レコードのテーブルを作成します。また、作成後に手動VACUUMを実行します。
for i in {52..10000}
do
[ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done
psql test -c "VACUUM staff;"
10,000レコードのテーブルを作成した状態のpg_stat_all_tablesをメモに残します。後続のシナリオでautoanalyze_countが増えた事から、統計情報の更新がなされた事を確認します。
[postgres@centos10 ~]$ psql test << EOF > \x > SELECT * FROM pg_stat_all_tables where relname ='staff'; > EOF Expanded display is on. -[ RECORD 1 ]-------+------------------------------ relid | 17250 schemaname | public relname | staff seq_scan | 1 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 10000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 9834 n_dead_tup | 0 n_mod_since_analyze | 179 n_ins_since_vacuum | 0 last_vacuum | 2021-01-23 12:24:51.796116+09 last_autovacuum | 2021-01-23 12:23:05.741684+09 last_analyze | last_autoanalyze | 2021-01-23 12:23:05.75281+09 vacuum_count | 1 autovacuum_count | 2 analyze_count | 0 autoanalyze_count | 3
この10,000行のテーブルは、以下計算式の通り、1,050行を超えるレコードが更新された時に統計情報が自動更新されます。
50 + 10,000 * 0.1 = 1,050
1,051行のレコードを追加します。
for i in {10001..11051}
do
[ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done
pg_stat_all_tablesを見ると、autoanalyze_countが増えている事が分かります。確かに統計情報が更新された事が分かります。
[postgres@centos10 ~]$ psql test << EOF > \x > SELECT * FROM pg_stat_all_tables where relname ='staff'; > EOF Expanded display is on. -[ RECORD 1 ]-------+------------------------------ relid | 17250 schemaname | public relname | staff seq_scan | 1 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 11051 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 11051 n_dead_tup | 0 n_mod_since_analyze | 0 n_ins_since_vacuum | 1051 last_vacuum | 2021-01-23 12:24:51.796116+09 last_autovacuum | 2021-01-23 12:23:05.741684+09 last_analyze | last_autoanalyze | 2021-01-23 12:27:05.784366+09 vacuum_count | 1 autovacuum_count | 2 analyze_count | 0 autoanalyze_count | 4
ログにも統計情報を更新された旨の出力が表示されます。
$ tail -f /var/lib/pgsql/13/data/log/postgresql-Sat.log 2021-01-23 12:27:05.773 JST [49298] DEBUG: "public.staff"を解析しています 2021-01-23 12:27:05.778 JST [49298] DEBUG: "staff": 60ページの内60をスキャン。11051の有効な行と0の不要な行が存在。11051行をサンプリング。推定総行数は11051 2021-01-23 12:27:05.786 JST [26919] DEBUG: サーバプロセス (PID 49298)は終了コード0で終了しました
