実行環境
- Aurora PostgreSQL db.r6i.large (memory16GB)
- shared_buffersはデフォルトで1301957(*page_sizeすると)約10GB
- 検証用にshared_buffersを12800(=100MB)に設定(DBインスタンス再起動反映)
- 100MBのテーブル10個にselect *後、pg_buffercacheでキャッシュを確認
pg_buffercacheを確認するSQL
リファレンス
F.27. pg_buffercache
www.postgresql.jp
F.27.2. サンプル出力
regression=# SELECT n.nspname, c.relname, count(*) AS buffers FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) JOIN pg_namespace n ON n.oid = c.relnamespace GROUP BY n.nspname, c.relname ORDER BY 3 DESC LIMIT 10; nspname | relname | buffers ------------+------------------------+--------- public | delete_test_table | 593 public | delete_test_table_pkey | 494 pg_catalog | pg_attribute | 472 public | quad_poly_tbl | 353 public | tenk2 | 349 public | tenk1 | 349 public | gin_test_idx | 306 pg_catalog | pg_largeobject | 206 public | gin_test_tbl | 188 public | spgist_text_tbl | 182 (10 rows)
実際に実行
全bufferを確認するためlimit 10なしで実行する
SELECT
n.nspname
,c.relname
,count(*) AS buffers
FROM
pg_buffercache b
JOIN
pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid)
AND b.reldatabase IN(0,(
SELECT
oid
FROM
pg_database
WHERE
datname = current_database()
))
JOIN
pg_namespace n
ON n.oid = c.relnamespace
GROUP BY
n.nspname,
c.relname
ORDER BY
3 DESC
;出力
nspname | relname | buffers
------------+-----------------------------------------+---------
pfs | _2016_17_pbp | 1600
pfs | _2012_13_pbp | 1600
pfs | _2018_19_pbp | 1600
pfs | _2013_14_pbp | 1600
pfs | _2014_15_pbp | 1600
pfs | _2015_16_pbp | 1600
pfs | _2017_18_pbp | 1600
pfs | _2011_12_pbp | 1064
pg_catalog | pg_attribute | 44
pg_catalog | pg_statistic | 43
pg_toast | pg_toast_2619 | 20
pg_catalog | pg_class | 15
pg_catalog | pg_operator | 13
pg_catalog | pg_attribute_relid_attnum_index | 11
pg_catalog | pg_proc | 6
pg_catalog | pg_index | 6
pg_catalog | pg_proc_oid_index | 5
pg_catalog | pg_statistic_relid_att_inh_index | 5
pg_toast | pg_toast_2618 | 5
pg_catalog | pg_amop | 5
pg_catalog | pg_class_oid_index | 4
pg_catalog | pg_operator_oprname_l_r_n_index | 3
pg_catalog | pg_amproc_fam_proc_index | 3
pg_catalog | pg_class_relname_nsp_index | 3
pg_catalog | pg_type | 3
pg_catalog | pg_collation_oid_index | 2
pg_catalog | pg_opclass_oid_index | 2
pg_catalog | pg_database_datname_index | 2
pg_catalog | pg_database_oid_index | 2
pg_catalog | pg_operator_oid_index | 2
pg_catalog | pg_db_role_setting_databaseid_rol_index | 2
pg_catalog | pg_index_indexrelid_index | 2
pg_catalog | pg_amop_fam_strat_index | 2
pg_catalog | pg_amop_opr_fam_index | 2
pg_catalog | pg_namespace | 2
pg_catalog | pg_amproc | 2
pg_catalog | pg_opclass | 2
pg_catalog | pg_type_typname_nsp_index | 2
pg_catalog | pg_authid_oid_index | 2
pg_catalog | pg_authid_rolname_index | 2
pg_catalog | pg_type_oid_index | 2
pg_catalog | pg_tablespace_oid_index | 2
pg_catalog | pg_auth_members | 2
pg_toast | pg_toast_2619_index | 1
pg_catalog | pg_am | 1
pg_catalog | pg_auth_members_member_role_index | 1
pg_catalog | pg_auth_members_role_member_index | 1
pg_catalog | pg_authid | 1
pg_catalog | pg_cast | 1
pg_catalog | pg_cast_source_target_index | 1
pg_catalog | pg_collation | 1
pg_catalog | pg_database | 1
pg_catalog | pg_db_role_setting | 1
pg_catalog | pg_index_indrelid_index | 1
pg_catalog | pg_inherits_relid_seqno_index | 1
pg_catalog | pg_namespace_nspname_index | 1
pg_catalog | pg_namespace_oid_index | 1
pg_catalog | pg_opclass_am_name_nsp_index | 1
pg_catalog | pg_proc_proname_args_nsp_index | 1
pg_catalog | pg_rewrite | 1
pg_catalog | pg_rewrite_rel_rulename_index | 1
pg_catalog | pg_statistic_ext_relid_index | 1
pg_catalog | pg_tablespace | 1
pg_toast | pg_toast_2618_index | 1
(64 rows)
(64 rows)出力の要約
- buffersをすべて足すと12515
- 12515にblock_size(8KB)をかけると97.77MBとなり、ほぼこの環境のshared_buffers(12800=100MB)となる
- 最初やったときは12668で98.96MBだったけど、クラスタ停止起動直後に再実施したら若干合計値が下がった様子
- リファレンスより、pg_buffercacheは完全に正確ではないので若干の差異は許容する
- このことから、pg_buffercacheの各レコードはキャッシュに格納されるページ単位と同義。また、上記pg_buffercache出力SQLで各オブジェクトのbuffersに*block_sizeをかければバイト数を算出できる。
バイト数算出サンプル
リファレンスのSQLを修正してバイト数を算出する
SQL
SELECT
n.nspname
,c.relname
,count(*) AS buffers
,count(*)*8192/1024 AS KB
,count(*)*8192/1024/1024 AS MB
FROM
pg_buffercache b
JOIN
pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid)
AND b.reldatabase IN(0,(
SELECT
oid
FROM
pg_database
WHERE
datname = current_database()
))
JOIN
pg_namespace n
ON n.oid = c.relnamespace
GROUP BY
n.nspname,
c.relname
ORDER BY
3 DESC
;出力
nspname | relname | buffers | kb | mb
------------+-----------------------------------------+---------+-------+----
pfs | _2016_17_pbp | 1600 | 12800 | 12
pfs | _2012_13_pbp | 1600 | 12800 | 12
pfs | _2018_19_pbp | 1600 | 12800 | 12
pfs | _2013_14_pbp | 1600 | 12800 | 12
pfs | _2014_15_pbp | 1600 | 12800 | 12
pfs | _2015_16_pbp | 1600 | 12800 | 12
pfs | _2017_18_pbp | 1600 | 12800 | 12
pfs | _2011_12_pbp | 1064 | 8512 | 8
pg_catalog | pg_attribute | 44 | 352 | 0
pg_catalog | pg_statistic | 43 | 344 | 0
pg_toast | pg_toast_2619 | 20 | 160 | 0
pg_catalog | pg_class | 15 | 120 | 0
pg_catalog | pg_operator | 13 | 104 | 0
pg_catalog | pg_attribute_relid_attnum_index | 11 | 88 | 0
pg_catalog | pg_proc | 6 | 48 | 0
pg_catalog | pg_index | 6 | 48 | 0
pg_catalog | pg_proc_oid_index | 5 | 40 | 0
pg_catalog | pg_statistic_relid_att_inh_index | 5 | 40 | 0
pg_toast | pg_toast_2618 | 5 | 40 | 0
pg_catalog | pg_amop | 5 | 40 | 0
pg_catalog | pg_class_oid_index | 4 | 32 | 0
pg_catalog | pg_operator_oprname_l_r_n_index | 3 | 24 | 0
pg_catalog | pg_amproc_fam_proc_index | 3 | 24 | 0
pg_catalog | pg_class_relname_nsp_index | 3 | 24 | 0
pg_catalog | pg_type | 3 | 24 | 0
pg_catalog | pg_collation_oid_index | 2 | 16 | 0
pg_catalog | pg_opclass_oid_index | 2 | 16 | 0
pg_catalog | pg_database_datname_index | 2 | 16 | 0
pg_catalog | pg_database_oid_index | 2 | 16 | 0
pg_catalog | pg_operator_oid_index | 2 | 16 | 0
pg_catalog | pg_db_role_setting_databaseid_rol_index | 2 | 16 | 0
pg_catalog | pg_index_indexrelid_index | 2 | 16 | 0
pg_catalog | pg_amop_fam_strat_index | 2 | 16 | 0
pg_catalog | pg_amop_opr_fam_index | 2 | 16 | 0
pg_catalog | pg_namespace | 2 | 16 | 0
pg_catalog | pg_amproc | 2 | 16 | 0
pg_catalog | pg_opclass | 2 | 16 | 0
pg_catalog | pg_type_typname_nsp_index | 2 | 16 | 0
pg_catalog | pg_authid_oid_index | 2 | 16 | 0
pg_catalog | pg_authid_rolname_index | 2 | 16 | 0
pg_catalog | pg_type_oid_index | 2 | 16 | 0
pg_catalog | pg_tablespace_oid_index | 2 | 16 | 0
pg_catalog | pg_auth_members | 2 | 16 | 0
pg_toast | pg_toast_2619_index | 1 | 8 | 0
pg_catalog | pg_am | 1 | 8 | 0
pg_catalog | pg_auth_members_member_role_index | 1 | 8 | 0
pg_catalog | pg_auth_members_role_member_index | 1 | 8 | 0
pg_catalog | pg_authid | 1 | 8 | 0
pg_catalog | pg_cast | 1 | 8 | 0
pg_catalog | pg_cast_source_target_index | 1 | 8 | 0
pg_catalog | pg_collation | 1 | 8 | 0
pg_catalog | pg_database | 1 | 8 | 0
pg_catalog | pg_db_role_setting | 1 | 8 | 0
pg_catalog | pg_index_indrelid_index | 1 | 8 | 0
pg_catalog | pg_inherits_relid_seqno_index | 1 | 8 | 0
pg_catalog | pg_namespace_nspname_index | 1 | 8 | 0
pg_catalog | pg_namespace_oid_index | 1 | 8 | 0
pg_catalog | pg_opclass_am_name_nsp_index | 1 | 8 | 0
pg_catalog | pg_proc_proname_args_nsp_index | 1 | 8 | 0
pg_catalog | pg_rewrite | 1 | 8 | 0
pg_catalog | pg_rewrite_rel_rulename_index | 1 | 8 | 0
pg_catalog | pg_statistic_ext_relid_index | 1 | 8 | 0
pg_catalog | pg_tablespace | 1 | 8 | 0
pg_toast | pg_toast_2618_index | 1 | 8 | 0
(64 rows)
(64 rows)その他
Aurora PostgreSQLは起動時にキャッシュ暖機が実行されるのが売りだけど(=pg_prewarmを標準で別機構で備えてる的な)、クラスタの一時停止->起動後はキャッシュがない状態になっていた
再起動だとキャッシュ暖機される? 未確認
(まあどちらにしてもキャッシュされるオブジェクトをユーザー側で制御できるわけではなくAurora任せなので、当てにするなら再起動後にキャッシュ暖機するクエリを流す実装をしないといけなそう)