Aurora PostgreSQLは、クラスタにリードレプリカを追加することにより、容易に読み取り処理をスケールアウトすることができます。また、プライマリインスタンスに障害が発生した場合でも、リードレプリカがあればフェイルオーバーが実施され、ダウンタイムを常に短くすることができます。非常に便利なサービスではありますが、統計情報についての仕様に個人的に意外性を感じましたので、Tipsとしてまとめてみました。
まとめ
Aurora PostgreSQLの統計情報については、下記のような特徴があります。特にpg_stat_all_tablesの特徴を把握していなかったことで、プライマリとリードレプリカでpg_stat_all_tablesから取得できる差異が発生していることより、リードレプリカにおいては実行計画が適切に選ばれないのではないかと勘違いしていました。
- 実行計画に用いられる統計情報はpg_statisticに記録されている
- pg_statisticの情報はプライマリとリードレプリカで同期されている
- pg_statisticの情報は停止・再起動しても保持される
- pg_stat_all_tablesの情報はプライマリとリードレプリカで同期されない
- pg_stat_all_tablesの情報は停止・再起動等により削除される
- 手動で統計情報を更新できないシステムカタログが存在する
pg_stat_all_tablesについて
pg_stat_all_tablesは、下記の記載通り、テーブルごとの統計情報を表示するビューです。最後にバキューム処理や統計情報更新が行われた時刻等を確認する際に参照することが多いビューかと存じます。
pg_stat_all_tablesビューは現在のデータベース内のテーブル（TOASTテーブルを含む）毎に１行の形式で、特定のテーブルへのアクセスに関する統計情報を表示します。 pg_stat_user_tablesおよびpg_stat_sys_tablesビューにも同じ情報が含まれますが、それぞれユーザテーブルとシステムテーブルのみにフィルタされています。
28.2. 統計情報コレクタ
実際に、Aurora PostgreSQLのプライマリとリードレプリカで、当該ビューの中身を確認いただくと分かるかと存じますが、起動時間が長く、自動バキュームが行われた回数が多くなるにつれて、内容に大きな差異が生じてきます。これは、冒頭にも記載させていただいた下記の特徴のためです。
- pg_stat_all_tablesの情報はプライマリとリードレプリカで同期されない
- pg_stat_all_tablesの情報は停止・再起動等により削除される
ビューであるため、実情報が同期されていればユーザ側で取得する結果に差異は生じないのが普通ですが、これは、恐らく次の仕様によるものと推測致します。
統計情報コレクタは収集した情報を他のPostgreSQLプロセスに一時ファイルを介して送信します。 これらのファイルはstats_temp_directoryで指名されたディレクトリ、デフォルトはpg_stat_tmp内に格納されます。 性能を向上させるために、stats_temp_directoryをRAMベースのファイルシステムを指し示すようにして、物理的なI/O要求を減らすことができます。 サーバが正しくシャットダウンした際は、統計情報がサーバの再起動を跨がって保持されるように、統計情報データの永続的なコピーがpg_statサブディレクトリに格納されます。 サーバ起動時にリカバリが実施される場合(例えば、即時シャットダウンやサーバクラッシュ、ポイントインタイムリカバリ)、統計カウンタをすべてリセットします。
28.2. 統計情報コレクタ
Aurora PostgreSQLはクラスターボリュームというストレージをプライマリとリードレプリカで共有しています。しかし、次の記載通り、一時ファイルは別です。上述の通り、統計情報は他のPostgreSQLプロセスに一時ファイルを介して送信されるため、プライマリとリードレプリカで差異が生じているものと考えられます。
Aurora PostgreSQL は、非永続的な一時ファイル用に別の一時ストレージを使用します。
Aurora PostgreSQL 用の一時ストレージの制限
上記から、実運用においてpg_stat_all_tablesを確認する際には、次のような方針にした方が良いものと考えられます。
- プライマリインスタンスの情報を確認する
- フェイルオーバー直後のプライマリインスタンスの情報は実態とかけ離れている可能性があることを考慮する
pg_statisticについて
一方でpg_statisticですが、こちらは下記の記載通り、実際の統計データを保持している「テーブル」です。また、プランナに利用されるデータを保持しています。
pg_statisticカタログはデータベースの内容に関する統計データを保存します。 項目はANALYZEで作成され、後に問い合わせプランナで使用されます。
52.50. pg_statistic
こちらも、実際にAurora PostgreSQLのプライマリとリードレプリカで、当該テーブルや当該テーブルを見やすくしたpg_statsビューの中身を確認いただくと分かるかと存じますが、プライマリ側で情報に変更があっても、きちんと同期されています。
また、実行計画も正常にプライマリとリードレプリカで同じものが選択されることが確認できます。そして、テーブルに保持されている情報ですので、インスタンスを停止したり再起動したりしても、情報がなくなるようなことはありません。
上記から、実運用において「本当に統計情報がプライマリとリードレプリカで同期されているのか」という点を確認する際には、次のような方針にした方が良いものと考えられます。
- pg_statistic(またはpg_stats)の中身が同じことを確認する
- explainを利用して実際に導かれる実行計画が同じことを確認する
手動の統計情報更新について
バッチ処理等でテーブルのデータを大量に更新後、手動でデータベース単位で統計情報を更新をするようなケースもあるものと存じます。この場合ですが、マスターユーザを利用している場合でも、次のように、特定のシステムカタログについては権限の問題から対象外となることが確認できました。
postgres=> analyze;
WARNING: skipping "pg_authid" --- only superuser can analyze it
WARNING: skipping "pg_subscription" --- only superuser can analyze it
WARNING: skipping "pg_database" --- only superuser can analyze it
WARNING: skipping "pg_db_role_setting" --- only superuser can analyze it
WARNING: skipping "pg_tablespace" --- only superuser can analyze it
WARNING: skipping "pg_pltemplate" --- only superuser can analyze it
WARNING: skipping "pg_auth_members" --- only superuser can analyze it
WARNING: skipping "pg_shdepend" --- only superuser can analyze it
WARNING: skipping "pg_shdescription" --- only superuser can analyze it
WARNING: skipping "pg_replication_origin" --- only superuser can analyze it
WARNING: skipping "pg_shseclabel" --- only superuser can analyze itしたがって、上記システムカタログの統計情報については、自動バキュームワーカーによる自動更新を行う以外に更新手段がない形となります。自動バキュームを無効にするケースは少ないかとは存じますが、無効にして手動による更新のみを行う運用の場合、上記のシステムカタログが肥大化したり、実態と統計情報に乖離が生じたりする可能性について考慮する必要があります。
参考: 確認方法
pg_stat_all_tablesやpg_statisticの仕様について確認した際の方法について記載します(プライマリとリードレプリカの処理を時系列順に横並びで表示)。
-- プライマリ -- リードレプリカ
postgres=> select inet_server_addr(); postgres=> select inet_server_addr();
inet_server_addr inet_server_addr
------------------ ------------------
192.168.xx.xx 192.168.yy.yy
(1 row) (1 row)
postgres=> \x postgres=> \x
Expanded display is on. Expanded display is on.
-- テーブル作成
postgres=> create table public.stats_test_table (col1 int primary key,col2 varchar(10));
CREATE TABLE
postgres=> \d public.stats_test_table postgres=> \d public.stats_test_table
Table "public.stats_test_table" Table "public.stats_test_table"
Column | Type | Collation | Nullable | Default Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+--------- --------+-----------------------+-----------+----------+---------
col1 | integer | | not null | col1 | integer | | not null |
col2 | character varying(10) | | | col2 | character varying(10) | | |
Indexes: Indexes:
"stats_test_table_pkey" PRIMARY KEY, btree (col1) "stats_test_table_pkey" PRIMARY KEY, btree (col1)
-- 作成時点で`seq_scan`に差異あり
postgres=> select * from pg_stat_all_tables where relname = 'stats_test_table'; postgres=> select * from pg_stat_all_tables where relname = 'stats_test_table';
-[ RECORD 1 ]-------+----------------- -[ RECORD 1 ]-------+-----------------
relid | 90141 relid | 90141
schemaname | public schemaname | public
relname | stats_test_table relname | stats_test_table
seq_scan | 1 seq_scan | 0
seq_tup_read | 0 seq_tup_read | 0
idx_scan | 0 idx_scan | 0
idx_tup_fetch | 0 idx_tup_fetch | 0
n_tup_ins | 0 n_tup_ins | 0
n_tup_upd | 0 n_tup_upd | 0
n_tup_del | 0 n_tup_del | 0
n_tup_hot_upd | 0 n_tup_hot_upd | 0
n_live_tup | 0 n_live_tup | 0
n_dead_tup | 0 n_dead_tup | 0
n_mod_since_analyze | 0 n_mod_since_analyze | 0
last_vacuum | last_vacuum |
last_autovacuum | last_autovacuum |
last_analyze | last_analyze |
last_autoanalyze | last_autoanalyze |
vacuum_count | 0 vacuum_count | 0
autovacuum_count | 0 autovacuum_count | 0
analyze_count | 0 analyze_count | 0
autoanalyze_count | 0 autoanalyze_count | 0
postgres=> select * from pg_stats where tablename = 'stats_test_table'; postgres=> select * from pg_stats where tablename = 'stats_test_table';
(0 rows) (0 rows)
-- テスト用の行挿入ファンクション作成
postgres=> create function public.stats_test_insert() returns int as
postgres-> '
postgres'> declare i int;
postgres'> begin
postgres'> for i in 1..10000 loop
postgres'> insert into public.stats_test_table values(i,''test'');
postgres'> end loop;
postgres'> return 0;
postgres'> end;
postgres'> '
postgres-> language 'plpgsql'
postgres-> ;
CREATE FUNCTION
postgres=> select public.stats_test_insert();
-[ RECORD 1 ]-----+--
stats_test_insert | 0
-- `pg_stat_all_tables`側は`n_tup_ins`、`n_live_tup`に差異が生じている
postgres=> select * from pg_stat_all_tables where relname = 'stats_test_table'; postgres=> select * from pg_stat_all_tables where relname = 'stats_test_table';
-[ RECORD 1 ]-------+----------------- -[ RECORD 1 ]-------+-----------------
relid | 90141 relid | 90141
schemaname | public schemaname | public
relname | stats_test_table relname | stats_test_table
seq_scan | 1 seq_scan | 0
seq_tup_read | 0 seq_tup_read | 0
idx_scan | 0 idx_scan | 0
idx_tup_fetch | 0 idx_tup_fetch | 0
n_tup_ins | 10000 n_tup_ins | 0
n_tup_upd | 0 n_tup_upd | 0
n_tup_del | 0 n_tup_del | 0
n_tup_hot_upd | 0 n_tup_hot_upd | 0
n_live_tup | 10000 n_live_tup | 0
n_dead_tup | 0 n_dead_tup | 0
n_mod_since_analyze | 10000 n_mod_since_analyze | 0
last_vacuum | last_vacuum |
last_autovacuum | last_autovacuum |
last_analyze | last_analyze |
last_autoanalyze | last_autoanalyze |
vacuum_count | 0 vacuum_count | 0
autovacuum_count | 0 autovacuum_count | 0
analyze_count | 0 analyze_count | 0
autoanalyze_count | 0 autoanalyze_count | 0
-- `pg_stats`側は差異なく同期されている
postgres=> select * from pg_stats where tablename = 'stats_test_table'; postgres=> select * from pg_stats where tablename = 'stats_test_table';
-[ RECORD 1 ]----------+------------------------------------------------- -[ RECORD 1 ]----------+-------------------------------------------------
schemaname | public schemaname | public
tablename | stats_test_table tablename | stats_test_table
attname | col1 attname | col1
inherited | f inherited | f
null_frac | 0 null_frac | 0
avg_width | 4 avg_width | 4
n_distinct | -1 n_distinct | -1
most_common_vals | most_common_vals |
most_common_freqs | most_common_freqs |
histogram_bounds | {1,100,200,～省略～,9800,9900,10000} histogram_bounds | {1,100,200,～省略～,9800,9900,10000}
correlation | 1 correlation | 1
most_common_elems | most_common_elems |
most_common_elem_freqs | most_common_elem_freqs |
elem_count_histogram | elem_count_histogram |
-[ RECORD 2 ]----------+------------------------------------------------- -[ RECORD 2 ]----------+-------------------------------------------------
schemaname | public schemaname | public
tablename | stats_test_table tablename | stats_test_table
attname | col2 attname | col2
inherited | f inherited | f
null_frac | 0 null_frac | 0
avg_width | 5 avg_width | 5
n_distinct | 1 n_distinct | 1
most_common_vals | {test} most_common_vals | {test}
most_common_freqs | {1} most_common_freqs | {1}
histogram_bounds | histogram_bounds |
correlation | 1 correlation | 1
most_common_elems | most_common_elems |
most_common_elem_freqs | most_common_elem_freqs |
elem_count_histogram | elem_count_histogram |
-- 同じ実行計画が選択されている
postgres=> explain verbose select * from public.stats_test_table where col1 > 1000 and col1 <=2000; postgres=> explain verbose select * from public.stats_test_table where col1 > 1000 and col1 <=2000;
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------- -[ RECORD 1 ]-------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Scan using stats_test_table_pkey on public.stats_test_table (cost=0.29..41.28 rows=1000 width=9) QUERY PLAN | Index Scan using stats_test_table_pkey on public.stats_test_table (cost=0.29..41.28 rows=1000 width=9)
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------- -[ RECORD 2 ]-------------------------------------------------------------------------------------------------------
QUERY PLAN | Output: col1, col2 QUERY PLAN | Output: col1, col2
-[ RECORD 3 ]------------------------------------------------------------------------------------------------------- -[ RECORD 3 ]-------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((stats_test_table.col1 > 1000) AND (stats_test_table.col1 <= 2000)) QUERY PLAN | Index Cond: ((stats_test_table.col1 > 1000) AND (stats_test_table.col1 <= 2000))
-- 実行計画を変えるために行を削除する
postgres=> delete from public.stats_test_table where col1 > 2000;
DELETE 8000
-- `pg_stat_all_tables`側での自動バキュームや統計情報更新情報も差異が生じている
postgres=> select * from pg_stat_all_tables where relname = 'stats_test_table'; postgres=> select * from pg_stat_all_tables where relname = 'stats_test_table';
-[ RECORD 1 ]-------+------------------------------ -[ RECORD 1 ]-------+-----------------
relid | 90141 relid | 90141
schemaname | public schemaname | public
relname | stats_test_table relname | stats_test_table
seq_scan | 2 seq_scan | 0
seq_tup_read | 10000 seq_tup_read | 0
idx_scan | 0 idx_scan | 0
idx_tup_fetch | 0 idx_tup_fetch | 0
n_tup_ins | 10000 n_tup_ins | 0
n_tup_upd | 0 n_tup_upd | 0
n_tup_del | 8000 n_tup_del | 0
n_tup_hot_upd | 0 n_tup_hot_upd | 0
n_live_tup | 2000 n_live_tup | 0
n_dead_tup | 0 n_dead_tup | 0
n_mod_since_analyze | 0 n_mod_since_analyze | 0
last_vacuum | last_vacuum |
last_autovacuum | 2020-12-01 21:24:09.70559+09 last_autovacuum |
last_analyze | last_analyze |
last_autoanalyze | 2020-12-01 21:24:09.730183+09 last_autoanalyze |
vacuum_count | 0 vacuum_count | 0
autovacuum_count | 1 autovacuum_count | 0
analyze_count | 0 analyze_count | 0
autoanalyze_count | 2 autoanalyze_count | 0
-- `pg_stats`側は差異なく同期されている
postgres=> select * from pg_stats where tablename = 'stats_test_table'; postgres=> select * from pg_stats where tablename = 'stats_test_table';
-[ RECORD 1 ]----------+------------------------------------------------- -[ RECORD 1 ]----------+-------------------------------------------------
schemaname | public schemaname | public
tablename | stats_test_table tablename | stats_test_table
attname | col1 attname | col1
inherited | f inherited | f
null_frac | 0 null_frac | 0
avg_width | 4 avg_width | 4
n_distinct | -1 n_distinct | -1
most_common_vals | most_common_vals |
most_common_freqs | most_common_freqs |
histogram_bounds | {1,20,40,～省略～,1960,1980,2000} histogram_bounds | {1,20,40,～省略～,1960,1980,2000}
correlation | 1 correlation | 1
most_common_elems | most_common_elems |
most_common_elem_freqs | most_common_elem_freqs |
elem_count_histogram | elem_count_histogram |
-[ RECORD 2 ]----------+------------------------------------------------- -[ RECORD 2 ]----------+-------------------------------------------------
schemaname | public schemaname | public
tablename | stats_test_table tablename | stats_test_table
attname | col2 attname | col2
inherited | f inherited | f
null_frac | 0 null_frac | 0
avg_width | 5 avg_width | 5
n_distinct | 1 n_distinct | 1
most_common_vals | {test} most_common_vals | {test}
most_common_freqs | {1} most_common_freqs | {1}
histogram_bounds | histogram_bounds |
correlation | 1 correlation | 1
most_common_elems | most_common_elems |
most_common_elem_freqs | most_common_elem_freqs |
elem_count_histogram | elem_count_histogram |
-- 同じ実行計画が選択されている
postgres=> explain verbose select * from public.stats_test_table where col1 > 1000 and col1 <=2000; postgres=> explain verbose select * from public.stats_test_table where col1 > 1000 and col1 <=2000;
-[ RECORD 1 ]------------------------------------------------------------------------------ -[ RECORD 1 ]------------------------------------------------------------------------------
QUERY PLAN | Seq Scan on public.stats_test_table (cost=0.00..41.00 rows=1000 width=9) QUERY PLAN | Seq Scan on public.stats_test_table (cost=0.00..41.00 rows=1000 width=9)
-[ RECORD 2 ]------------------------------------------------------------------------------ -[ RECORD 2 ]------------------------------------------------------------------------------
QUERY PLAN | Output: col1, col2 QUERY PLAN | Output: col1, col2
-[ RECORD 3 ]------------------------------------------------------------------------------ -[ RECORD 3 ]------------------------------------------------------------------------------
QUERY PLAN | Filter: ((stats_test_table.col1 > 1000) AND (stats_test_table.col1 <= 2000)) QUERY PLAN | Filter: ((stats_test_table.col1 > 1000) AND (stats_test_table.col1 <= 2000))
postgres=> postgres=>
コメント