この記事は NTTコムウェア Advent Calendar 2023 の8日目の記事です。
はじめに
こんにちは、NTTコムウェアの川本です。
データベース、主にオープンソースの DBMS である PostgreSQL 関連の機能・性能の検証や技術サポートなどをしています。また PostgreSQL やその周辺ツールの開発コミュニティ活動も行っています。
PostgreSQL は起動時に確保する全てのプロセスで共有するメモリ領域と、各プロセスが必要に応じて個別で確保するメモリ領域を扱います。それぞれどれぐらいのメモリ量を確保するか、パラメータで設定できるもの (shared_buffers, temp_buffers, work_mem, etc.) もありますが、実際はメモリ量を設定できない領域もあります。
PostgreSQL を運用していてサーバのメモリ使用量の増加理由に心当たりがないとき、個々のバックエンドプロセス1がどんな用途でメモリを確保しているのか、PostgreSQL の外から推察することは難しいです。
これまではサーバに接続し、調査対象のバックエンドプロセスにデバッガ (gdb とか lldb とか) でアタッチして、デバッグ用関数を呼び出す必要がありました 2 が PostgreSQL14 から自身が接続しているバックエンドプロセスがどのような用途でメモリを確保しているか確認できるシステムカタログと、他のプロセスのメモリ状況をログに書き出させる関数が実装されています。
(事前知識) Memory Context について
Memory Context は PostgreSQL が実装しているメモリ管理のための機能です。PostgreSQL は確保したメモリ領域を用途ごとに Memory Context 単位で管理、破棄(メモリ解放)できるようにすることでメモリ管理を簡素化しています。
バックエンドプロセスは多くの Memory Context を持って動作します。
各 Memory Context は親子関係を持っていて(木構造)、不要になった Memory Context を破棄するとその子の Memory Context も破棄するというメモリリークを防ぐ仕組みがありますが、特定の Memory Context が肥大化したり、大量の Memory Context が作成されることで、バックエンドプロセスが使用するメモリ領域が膨らみ、サーバメモリを逼迫することもあります。
OS からみると PostgreSQL のプロセスがメモリを使用していることまでしかわからないですが、そのプロセスが確保している Memory Context を確認することで何の用途にメモリを使用しているのかがわかるということです。
メモリ状況を確認してみる
バックエンドプロセスがメモリを確保する操作として、一時テーブルと Prepared Statement を使用して Memory Context がどのように変化するのか見てみます。
今回は PostgreSQL 16.1 で確認しています。
pg_backend_memory_context ビュー
自身が接続しているバックエンドプロセスのメモリ状況は pg_backend_memory_context を参照して確認することができます。
例として、PostgreSQL の起動直後に psql で接続してすぐに確認した状態のメモリ状況を見てみます。接続しただけですが、バックエンドプロセスは 114 個の Memory Context を持っています。主にはシステムカタログテーブルに付与されているインデックス情報のキャッシュとなっています。
Memory Context は木構造なので、parent(親ノード)、level(階層) といった情報も見えます。
$ /usr/pgsql-16/bin/initdb -D 16/data
$ /usr/pgsql-16/bin/pg_ctl start -D 16/data
$ /usr/pgsql-16/bin/psql
psql (16.1)
Type "help" for help.
postgres=# select * from pg_backend_memory_contexts;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
--------------------------+------------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 97664 | 5 | 13376 | 7 | 84288
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1616 | 0 | 6576
TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7752 | 1 | 440
RowDescriptionContext | | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
MessageContext | | TopMemoryContext | 1 | 65536 | 4 | 38144 | 6 | 27392
Operator class cache | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
PgStat Shared Ref Hash | | TopMemoryContext | 1 | 7216 | 2 | 688 | 0 | 6528
PgStat Shared Ref | | TopMemoryContext | 1 | 4096 | 3 | 1880 | 2 | 2216
PgStat Pending | | TopMemoryContext | 1 | 8192 | 4 | 4376 | 12 | 3816
smgr relation table | | TopMemoryContext | 1 | 16384 | 2 | 4640 | 2 | 11744
TransactionAbortContext | | TopMemoryContext | 1 | 32768 | 1 | 32504 | 0 | 264
Portal hash | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
TopPortalContext | | TopMemoryContext | 1 | 8192 | 1 | 7664 | 0 | 528
PortalContext | <unnamed> | TopPortalContext | 2 | 1024 | 1 | 584 | 0 | 440
ExecutorState | | PortalContext | 3 | 32816 | 3 | 1184 | 0 | 31632
printtup | | ExecutorState | 4 | 8192 | 1 | 7928 | 0 | 264
Table function arguments | | ExecutorState | 4 | 8192 | 1 | 7888 | 0 | 304
ExprContext | | ExecutorState | 4 | 8192 | 1 | 6616 | 0 | 1576
Relcache by OID | | TopMemoryContext | 1 | 16384 | 2 | 3584 | 2 | 12800
CacheMemoryContext | | TopMemoryContext | 1 | 1048576 | 8 | 460432 | 3 | 588144
relation rules | pg_backend_memory_contexts | CacheMemoryContext | 2 | 4096 | 3 | 216 | 1 | 3880
index info | pg_db_role_setting_databaseid_rol_index | CacheMemoryContext | 2 | 2048 | 2 | 584 | 2 | 1464
index info | pg_user_mapping_user_server_index | CacheMemoryContext | 2 | 2048 | 2 | 728 | 2 | 1320
index info | pg_user_mapping_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_type_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_type_typname_nsp_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_ts_template_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_ts_template_tmplname_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_ts_parser_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_ts_parser_prsname_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_ts_dict_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_ts_dict_dictname_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_ts_config_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_ts_config_cfgname_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_ts_config_map_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 2 | 1840
index info | pg_transform_type_lang_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_transform_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_tablespace_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_subscription_rel_srrelid_srsubid_index | CacheMemoryContext | 2 | 2048 | 2 | 728 | 2 | 1320
index info | pg_subscription_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_subscription_subname_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_statistic_relid_att_inh_index | CacheMemoryContext | 2 | 3072 | 2 | 1200 | 1 | 1872
index info | pg_statistic_ext_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_statistic_ext_name_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_statistic_ext_data_stxoid_inh_index | CacheMemoryContext | 2 | 2048 | 2 | 728 | 2 | 1320
index info | pg_sequence_seqrelid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_replication_origin_roname_index | CacheMemoryContext | 2 | 1024 | 1 | 40 | 0 | 984
index info | pg_replication_origin_roiident_index | CacheMemoryContext | 2 | 1024 | 1 | 40 | 0 | 984
index info | pg_class_relname_nsp_index | CacheMemoryContext | 2 | 2048 | 2 | 544 | 1 | 1504
index info | pg_range_rngtypid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_range_rngmultitypid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_publication_rel_prrelid_prpubid_index | CacheMemoryContext | 2 | 2048 | 2 | 728 | 2 | 1320
index info | pg_publication_rel_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_publication_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_publication_namespace_pnnspid_pnpubid_index | CacheMemoryContext | 2 | 2048 | 2 | 728 | 2 | 1320
index info | pg_publication_namespace_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 40 | 0 | 984
index info | pg_publication_pubname_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_proc_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_proc_proname_args_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 2 | 1840
index info | pg_partitioned_table_partrelid_index | CacheMemoryContext | 2 | 1024 | 1 | 40 | 0 | 984
index info | pg_parameter_acl_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_parameter_acl_parname_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_opfamily_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_opfamily_am_name_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 2 | 1840
index info | pg_operator_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_operator_oprname_l_r_n_index | CacheMemoryContext | 2 | 3072 | 2 | 1168 | 1 | 1904
index info | pg_namespace_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_namespace_nspname_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_language_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_language_name_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_foreign_table_relid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_foreign_server_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_foreign_server_name_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_foreign_data_wrapper_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 40 | 0 | 984
index info | pg_foreign_data_wrapper_name_index | CacheMemoryContext | 2 | 1024 | 1 | 40 | 0 | 984
index info | pg_event_trigger_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_event_trigger_evtname_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_enum_typid_label_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_enum_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_default_acl_role_nsp_obj_index | CacheMemoryContext | 2 | 3072 | 2 | 1200 | 1 | 1872
index info | pg_conversion_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_constraint_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_conversion_name_nsp_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_conversion_default_index | CacheMemoryContext | 2 | 3072 | 2 | 1168 | 1 | 1904
index info | pg_collation_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_collation_name_enc_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 2 | 1840
index info | pg_opclass_am_name_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 2 | 1840
index info | pg_cast_source_target_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_auth_members_role_member_index | CacheMemoryContext | 2 | 3072 | 2 | 1200 | 1 | 1872
index info | pg_attribute_relid_attnam_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_amop_fam_strat_index | CacheMemoryContext | 2 | 3072 | 2 | 1168 | 1 | 1904
index info | pg_amop_opr_fam_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 2 | 1840
index info | pg_aggregate_fnoid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_shseclabel_object_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 2 | 1840
index info | pg_auth_members_member_role_index | CacheMemoryContext | 2 | 3072 | 2 | 1200 | 1 | 1872
index info | pg_authid_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_authid_rolname_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_database_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_database_datname_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
index info | pg_trigger_tgrelid_tgname_index | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288
index info | pg_rewrite_rel_rulename_index | CacheMemoryContext | 2 | 2048 | 2 | 616 | 1 | 1432
index info | pg_amproc_fam_proc_index | CacheMemoryContext | 2 | 3072 | 2 | 880 | 1 | 2192
index info | pg_opclass_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_index_indexrelid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_attribute_relid_attnum_index | CacheMemoryContext | 2 | 2048 | 2 | 544 | 1 | 1504
index info | pg_class_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
WAL record construction | | TopMemoryContext | 1 | 50200 | 2 | 6376 | 0 | 43824
PrivateRefCount | | TopMemoryContext | 1 | 8192 | 1 | 2648 | 0 | 5544
MdSmgr | | TopMemoryContext | 1 | 8192 | 1 | 7544 | 0 | 648
LOCALLOCK hash | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
GUCMemoryContext | | TopMemoryContext | 1 | 24576 | 2 | 12240 | 4 | 12336
GUC hash table | | GUCMemoryContext | 2 | 32768 | 3 | 12704 | 5 | 20064
Timezones | | TopMemoryContext | 1 | 104112 | 2 | 2648 | 0 | 101464
ErrorContext | | TopMemoryContext | 1 | 8192 | 1 | 7928 | 4 | 264
(114 rows)
一時テーブル (一時バッファ) の Memory Context
temp_buffers = 8MB(初期値)の環境です。
テスト用に pgbench でテーブルを作っておきます。
$ /usr/pgsql-16/bin/pgbench -i postgres
一時テーブルを作成して Memory Context を確認します。
postgres=# create temp table tmp1 as
postgres-# select * from pgbench_accounts where aid % 4000 = 0;
SELECT 25
postgres=# select * from pg_backend_memory_contexts ;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
---------------------------+------------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 97664 | 5 | 13808 | 25 | 83856
TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7752 | 0 | 440
LocalBufferContext | | TopMemoryContext | 1 | 143408 | 2 | 7928 | 0 | 135480
Local Buffer Lookup Table | | TopMemoryContext | 1 | 16384 | 2 | 2576 | 2 | 13808
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1616 | 0 | 6576
Btree proof lookup cache | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
.
.
.
一時テーブルを作成すると以下の Memory Context が追加されています。
LocalBufferContext | | TopMemoryContext | 1 | 143408 | 2 | 7928 | 0 | 135480
Local Buffer Lookup Table | | TopMemoryContext | 1 | 16384 | 2 | 2576 | 2 | 13808
LocalBufferContext をソースコードで確認してみると、src/backend/storage/buffer/localbuf.c が見つかって、ヘッダコメントに以下のように一時テーブルで使用するものだと書いてあります。どうやら LocalBufferContext は 143408 bytes メモリを使用してテーブルデータを持っています。
/*-------------------------------------------------------------------------
*
* localbuf.c
* local buffer manager. Fast buffer manager for temporary tables,
* which never need to be WAL-logged or checkpointed, etc.
*
* Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
* Portions Copyright (c) 1994-5, Regents of the University of California
*
*
* IDENTIFICATION
* src/backend/storage/buffer/localbuf.c
*
*-------------------------------------------------------------------------
*/
ところで、PostgreSQL は一時バッファの最大メモリ量を temp_buffers で指定できました。
一時テーブルを、設定値の 8MB 以上になるように試してみます。pgbench_accounts は 13MB 程度のようなので、そのまま使いましょう。
$ /usr/pgsql-16/bin/psql
psql (16.1)
Type "help" for help.
postgres=# select pg_size_pretty(pg_table_size(oid)) from pg_class where relname ='pgbench_accounts';
pg_size_pretty
----------------
13 MB
(1 row)
postgres=# create temp table tmp2 as
select * from pgbench_accounts;
SELECT 100000
postgres=# select * from pg_backend_memory_contexts ;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
---------------------------+------------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 97664 | 5 | 12992 | 16 | 84672
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1616 | 0 | 6576
TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7752 | 1 | 440
LocalBufferContext | | TopMemoryContext | 1 | 8425808 | 8 | 7928 | 0 | 8417880
Local Buffer Lookup Table | | TopMemoryContext | 1 | 65536 | 4 | 10528 | 11 | 55008
TableSpace cache | | TopMemoryContext | 1 | 8192 | 1 | 2128 | 0 | 6064
Operator lookup cache | | TopMemoryContext | 1 | 24576 | 2 | 10792 | 3 | 13784
RowDescriptionContext | | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
.
.
.
LocalBufferContext の total_size が 8425808 bytes になりました。だいたい 8MB で、temp_buffers の設定が効いていることもわかります。
Prepared Statement の Memory Context
Prepared Statement (プリペアド文) は変数を持ったクエリの構文解析や書き換えを先に行って保存しておき、実行時に呼び出すことで構文解析作業の繰り返しを防止したり、実行計画のキャッシュを行えるようになるものです。PREPARE コマンドでプリペアド文を作成して、EXECUTE コマンドでプリペアド文を指定して実行します。
上記のようなことを実現するために、バックエンドプロセスではプリペアド文を保存するためのメモリが動的に確保されます。
$ /usr/pgsql-16/bin/psql
psql (16.1)
Type "help" for help.
postgres=# prepare ps1(int) as select * from pgbench_accounts where aid = $1;
PREPARE
postgres=# select * from pg_backend_memory_contexts ;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
--------------------------+--------------------------------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 97664 | 5 | 13400 | 16 | 84264
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1616 | 0 | 6576
TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7752 | 1 | 440
Prepared Queries | | TopMemoryContext | 1 | 16384 | 2 | 6696 | 3 | 9688
Btree proof lookup cache | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
Type information cache | | TopMemoryContext | 1 | 24368 | 2 | 2648 | 0 | 21720
TableSpace cache | | TopMemoryContext | 1 | 8192 | 1 | 2128 | 0 | 6064
Operator lookup cache | | TopMemoryContext | 1 | 24576 | 2 | 10792 | 3 | 13784
RowDescriptionContext | | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
MessageContext | | TopMemoryContext | 1 | 65536 | 4 | 38144 | 5 | 27392
Operator class cache | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
smgr relation table | | TopMemoryContext | 1 | 32768 | 3 | 16880 | 8 | 15888
PgStat Shared Ref Hash | | TopMemoryContext | 1 | 7216 | 2 | 688 | 0 | 6528
PgStat Shared Ref | | TopMemoryContext | 1 | 4096 | 3 | 152 | 2 | 3944
PgStat Pending | | TopMemoryContext | 1 | 16384 | 5 | 13584 | 40 | 2800
TransactionAbortContext | | TopMemoryContext | 1 | 32768 | 1 | 32504 | 0 | 264
Portal hash | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
TopPortalContext | | TopMemoryContext | 1 | 8192 | 1 | 7664 | 0 | 528
PortalContext | <unnamed> | TopPortalContext | 2 | 1024 | 1 | 584 | 0 | 440
ExecutorState | | PortalContext | 3 | 32816 | 3 | 504 | 0 | 32312
printtup | | ExecutorState | 4 | 8192 | 1 | 7928 | 0 | 264
Table function arguments | | ExecutorState | 4 | 8192 | 1 | 7888 | 0 | 304
ExprContext | | ExecutorState | 4 | 8192 | 1 | 6216 | 0 | 1976
Relcache by OID | | TopMemoryContext | 1 | 16384 | 2 | 3584 | 2 | 12800
CacheMemoryContext | | TopMemoryContext | 1 | 524288 | 7 | 40704 | 1 | 483584
relation rules | pg_backend_memory_contexts | CacheMemoryContext | 2 | 4096 | 3 | 216 | 1 | 3880
CachedPlanSource | prepare ps1(int) as select * from pgbench_accounts where aid = $1; | CacheMemoryContext | 2 | 4096 | 3 | 1696 | 3 | 2400
CachedPlanQuery | | CachedPlanSource | 3 | 4096 | 3 | 1304 | 0 | 2792
index info | pg_class_tblspc_relfilenode_index | CacheMemoryContext | 2 | 2048 | 2 | 656 | 2 | 1392
index info | pg_statistic_ext_relid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024
.
.
.
Prepared Queries、CachedPlanSource 、CachedPlanQuery といった Memory Context が現れました。CachedPlanSource の ident では対象のプリペアド文が記録されています。
PostgreSQL におけるプリペアド文の実行計画のキャッシュ機能は、詳細は省きますが3、6 回目の実行から有効になります。というわけで、6 回実行した後のメモリ状況を見ると、キャッシュされた実行計画の CachedPlan が追加されました。
postgres=# execute ps1(6); //6 回目の実行
aid | bid | abalance | filler
-----+-----+----------+--------------------------------------------------------------------------------------
6 | 1 | 0 |
(1 row)
postgres=# select * from pg_backend_memory_contexts ;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
--------------------------+--------------------------------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 97664 | 5 | 13808 | 13 | 83856
TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7752 | 0 | 440
TableSpace cache | | TopMemoryContext | 1 | 8192 | 1 | 2128 | 0 | 6064
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1616 | 0 | 6576
Prepared Queries | | TopMemoryContext | 1 | 16384 | 2 | 6696 | 3 | 9688
Operator lookup cache | | TopMemoryContext | 1 | 24576 | 2 | 10792 | 3 | 13784
RowDescriptionContext | | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
MessageContext | | TopMemoryContext | 1 | 32768 | 3 | 5416 | 0 | 27352
Operator class cache | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
smgr relation table | | TopMemoryContext | 1 | 32768 | 3 | 16880 | 8 | 15888
PgStat Shared Ref Hash | | TopMemoryContext | 1 | 7216 | 2 | 688 | 0 | 6528
PgStat Shared Ref | | TopMemoryContext | 1 | 4096 | 3 | 584 | 2 | 3512
PgStat Pending | | TopMemoryContext | 1 | 8192 | 4 | 7808 | 30 | 384
TransactionAbortContext | | TopMemoryContext | 1 | 32768 | 1 | 32504 | 0 | 264
Portal hash | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
TopPortalContext | | TopMemoryContext | 1 | 8192 | 1 | 7664 | 1 | 528
PortalContext | <unnamed> | TopPortalContext | 2 | 1024 | 1 | 584 | 0 | 440
ExecutorState | | PortalContext | 3 | 32816 | 3 | 776 | 0 | 32040
printtup | | ExecutorState | 4 | 8192 | 1 | 7928 | 0 | 264
Table function arguments | | ExecutorState | 4 | 8192 | 1 | 7888 | 0 | 304
ExprContext | | ExecutorState | 4 | 8192 | 1 | 6376 | 0 | 1816
Relcache by OID | | TopMemoryContext | 1 | 16384 | 2 | 3584 | 2 | 12800
CacheMemoryContext | | TopMemoryContext | 1 | 524288 | 7 | 76344 | 0 | 447944
CachedPlan | prepare ps1(int) as select * from pgbench_accounts where aid = $1; | CacheMemoryContext | 2 | 4096 | 3 | 672 | 0 | 3424
index info | pg_statistic_ext_relid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024
index info | pgbench_accounts_pkey | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_index_indrelid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
relation rules | pg_backend_memory_contexts | CacheMemoryContext | 2 | 4096 | 3 | 216 | 1 | 3880
CachedPlanSource | prepare ps1(int) as select * from pgbench_accounts where aid = $1; | CacheMemoryContext | 2 | 4096 | 3 | 1696 | 8 | 2400
CachedPlanQuery | | CachedPlanSource | 3 | 4096 | 3 | 1304 | 0 | 2792
index info | pg_db_role_setting_databaseid_rol_index | CacheMemoryContext | 2 | 2048 | 2 | 584 | 2 | 1464
index info | pg_opclass_am_name_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 3 | 1840
index info | pg_foreign_data_wrapper_name_index | CacheMemoryContext | 2 | 1024 | 1 | 40 | 0 | 984
index info | pg_enum_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 72 | 0 | 952
.
.
.
2つプリペアド文を用意するとそれぞれのメモリが確保されます。
postgres=# prepare ps2(int) as select * from pgbench_accounts where bid = $1;
PREPARE
postgres=# select * from pg_backend_memory_contexts ;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
--------------------------+--------------------------------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 97664 | 5 | 13808 | 13 | 83856
TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7752 | 0 | 440
TableSpace cache | | TopMemoryContext | 1 | 8192 | 1 | 2128 | 0 | 6064
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1616 | 0 | 6576
Prepared Queries | | TopMemoryContext | 1 | 16384 | 2 | 6696 | 3 | 9688
Operator lookup cache | | TopMemoryContext | 1 | 24576 | 2 | 10792 | 3 | 13784
RowDescriptionContext | | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
MessageContext | | TopMemoryContext | 1 | 32768 | 3 | 5416 | 0 | 27352
Operator class cache | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
smgr relation table | | TopMemoryContext | 1 | 32768 | 3 | 16880 | 8 | 15888
PgStat Shared Ref Hash | | TopMemoryContext | 1 | 7216 | 2 | 688 | 0 | 6528
PgStat Shared Ref | | TopMemoryContext | 1 | 4096 | 3 | 584 | 2 | 3512
PgStat Pending | | TopMemoryContext | 1 | 8192 | 4 | 7808 | 30 | 384
TransactionAbortContext | | TopMemoryContext | 1 | 32768 | 1 | 32504 | 0 | 264
Portal hash | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
TopPortalContext | | TopMemoryContext | 1 | 8192 | 1 | 7664 | 1 | 528
PortalContext | <unnamed> | TopPortalContext | 2 | 1024 | 1 | 584 | 0 | 440
ExecutorState | | PortalContext | 3 | 32816 | 3 | 776 | 0 | 32040
printtup | | ExecutorState | 4 | 8192 | 1 | 7928 | 0 | 264
Table function arguments | | ExecutorState | 4 | 8192 | 1 | 7888 | 0 | 304
ExprContext | | ExecutorState | 4 | 8192 | 1 | 6376 | 0 | 1816
Relcache by OID | | TopMemoryContext | 1 | 16384 | 2 | 3584 | 2 | 12800
CacheMemoryContext | | TopMemoryContext | 1 | 524288 | 7 | 76344 | 0 | 447944
CachedPlanSource | prepare ps2(int) as select * from pgbench_accounts where bid = $1; | CacheMemoryContext | 2 | 4096 | 3 | 1696 | 3 | 2400
CachedPlanQuery | | CachedPlanSource | 3 | 4096 | 3 | 1304 | 0 | 2792
CachedPlan | prepare ps1(int) as select * from pgbench_accounts where aid = $1; | CacheMemoryContext | 2 | 4096 | 3 | 672 | 0 | 3424
index info | pg_statistic_ext_relid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024
index info | pgbench_accounts_pkey | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_index_indrelid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
relation rules | pg_backend_memory_contexts | CacheMemoryContext | 2 | 4096 | 3 | 216 | 1 | 3880
CachedPlanSource | prepare ps1(int) as select * from pgbench_accounts where aid = $1; | CacheMemoryContext | 2 | 4096 | 3 | 1696 | 8 | 2400
CachedPlanQuery | | CachedPlanSource | 3 | 4096 | 3 | 1304 | 0 | 2792
index info | pg_db_role_setting_databaseid_rol_index | CacheMemoryContext | 2 | 2048 | 2 | 584 | 2 | 1464
index info | pg_opclass_am_name_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1232 | 3 | 1840
.
.
.
DEALLOCATE コマンドを使用するとその分の Memory Context の削除も確認できます。
postgres=# deallocate ps1;
DEALLOCATE
postgres=# select * from pg_backend_memory_contexts ;
name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
--------------------------+--------------------------------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | | 0 | 97664 | 5 | 13808 | 13 | 83856
TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7752 | 0 | 440
TableSpace cache | | TopMemoryContext | 1 | 8192 | 1 | 2128 | 0 | 6064
Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1616 | 0 | 6576
Prepared Queries | | TopMemoryContext | 1 | 16384 | 2 | 6696 | 3 | 9688
Operator lookup cache | | TopMemoryContext | 1 | 24576 | 2 | 10792 | 3 | 13784
RowDescriptionContext | | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296
MessageContext | | TopMemoryContext | 1 | 32768 | 3 | 5416 | 0 | 27352
Operator class cache | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
smgr relation table | | TopMemoryContext | 1 | 32768 | 3 | 16880 | 8 | 15888
PgStat Shared Ref Hash | | TopMemoryContext | 1 | 7216 | 2 | 688 | 0 | 6528
PgStat Shared Ref | | TopMemoryContext | 1 | 4096 | 3 | 584 | 2 | 3512
PgStat Pending | | TopMemoryContext | 1 | 8192 | 4 | 7808 | 30 | 384
TransactionAbortContext | | TopMemoryContext | 1 | 32768 | 1 | 32504 | 0 | 264
Portal hash | | TopMemoryContext | 1 | 8192 | 1 | 592 | 0 | 7600
TopPortalContext | | TopMemoryContext | 1 | 8192 | 1 | 7664 | 1 | 528
PortalContext | <unnamed> | TopPortalContext | 2 | 1024 | 1 | 584 | 0 | 440
ExecutorState | | PortalContext | 3 | 32816 | 3 | 776 | 0 | 32040
printtup | | ExecutorState | 4 | 8192 | 1 | 7928 | 0 | 264
Table function arguments | | ExecutorState | 4 | 8192 | 1 | 7888 | 0 | 304
ExprContext | | ExecutorState | 4 | 8192 | 1 | 6376 | 0 | 1816
Relcache by OID | | TopMemoryContext | 1 | 16384 | 2 | 3584 | 2 | 12800
CacheMemoryContext | | TopMemoryContext | 1 | 524288 | 7 | 76344 | 0 | 447944
CachedPlanSource | prepare ps2(int) as select * from pgbench_accounts where bid = $1; | CacheMemoryContext | 2 | 4096 | 3 | 1696 | 3 | 2400
CachedPlanQuery | | CachedPlanSource | 3 | 4096 | 3 | 1304 | 0 | 2792
index info | pg_statistic_ext_relid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024
index info | pgbench_accounts_pkey | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
index info | pg_index_indrelid_index | CacheMemoryContext | 2 | 2048 | 2 | 912 | 0 | 1136
.
.
.
他のプロセスのメモリ状況は？
pg_backend_memory_contexts を参照することで、今接続しているバックエンドプロセスのメモリ状況を確認できましたが、実際には他のプロセスの状況を把握したいことが多いと思います。
そんな時には pid を引数として、そのプロセスの Memocy Context の内容をログに出力させる関数を利用します。
pg_log_backend_memory_contexts(pid) 関数
一時テーブルを作成しただけのセッションを用意して、別のセッションからそれを指定して pg_log_backend_memory_contexts 関数を実行します。
postgres=# select pid, backend_type, query from pg_stat_activity;
pid | backend_type | query
---------+------------------------------+--------------------------------------------------------
2364787 | autovacuum launcher |
2364788 | logical replication launcher |
2377723 | client backend | create temp table tmp1 as +
| | select * from pgbench_accounts where aid % 4000 = 0;
2377836 | client backend | select pid, backend_type, query from pg_stat_activity;
2364784 | background writer |
2364783 | checkpointer |
2364786 | walwriter |
(7 rows)
postgres=# select pg_log_backend_memory_contexts(2377723);
pg_log_backend_memory_contexts
--------------------------------
t
(1 row)
すると、ログに以下のように記録されました。
2023-11-29 10:31:00.438 JST [2377723] LOG: logging memory contexts of PID 2377723
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 0; TopMemoryContext: 97664 total in 5 blocks; 16552 free (29 chunks); 81112 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; LocalBufferContext: 143408 total in 2 blocks; 7928 free (0 chunks); 135480 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; Local Buffer Lookup Table: 16384 total in 2 blocks; 2576 free (2 chunks); 13808 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; TableSpace cache: 8192 total in 1 blocks; 2128 free (0 chunks); 6064 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; Operator lookup cache: 24576 total in 2 blocks; 10792 free (3 chunks); 13784 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; RowDescriptionContext: 8192 total in 1 blocks; 6896 free (0 chunks); 1296 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; MessageContext: 8192 total in 1 blocks; 6896 free (1 chunks); 1296 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; Operator class cache: 8192 total in 1 blocks; 592 free (0 chunks); 7600 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; smgr relation table: 32768 total in 3 blocks; 16880 free (8 chunks); 15888 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; PgStat Shared Ref Hash: 7216 total in 2 blocks; 688 free (0 chunks); 6528 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; PgStat Shared Ref: 4096 total in 3 blocks; 224 free (2 chunks); 3872 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; PgStat Pending: 16384 total in 5 blocks; 15960 free (51 chunks); 424 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; Portal hash: 8192 total in 1 blocks; 592 free (0 chunks); 7600 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; TopPortalContext: 8192 total in 1 blocks; 7928 free (1 chunks); 264 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; Relcache by OID: 16384 total in 2 blocks; 3584 free (2 chunks); 12800 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; CacheMemoryContext: 524288 total in 7 blocks; 67792 free (9 chunks); 456496 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 512 free (2 chunks); 1536 used: pg_class_tblspc_relfilenode_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_statistic_ext_relid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pgbench_accounts_pkey
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_index_indrelid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 944 free (1 chunks); 2128 used: pg_depend_reference_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 944 free (1 chunks); 2128 used: pg_depend_depender_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; EventTriggerCache: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 3; Event Trigger Cache: 8192 total in 1 blocks; 2648 free (0 chunks); 5544 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 584 free (2 chunks); 1464 used: pg_db_role_setting_databaseid_rol_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1232 free (3 chunks); 1840 used: pg_opclass_am_name_nsp_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 40 free (0 chunks); 984 used: pg_foreign_data_wrapper_name_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_enum_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 544 free (2 chunks); 1504 used: pg_class_relname_nsp_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_foreign_server_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_publication_pubname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 912 free (2 chunks); 2160 used: pg_statistic_relid_att_inh_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_cast_source_target_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_language_name_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_transform_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_collation_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1168 free (3 chunks); 1904 used: pg_amop_fam_strat_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_index_indexrelid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_ts_template_tmplname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1232 free (3 chunks); 1840 used: pg_ts_config_map_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_opclass_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 40 free (0 chunks); 984 used: pg_foreign_data_wrapper_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 40 free (0 chunks); 984 used: pg_publication_namespace_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_event_trigger_evtname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_statistic_ext_name_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_publication_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_ts_dict_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_event_trigger_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1168 free (3 chunks); 1904 used: pg_conversion_default_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 880 free (3 chunks); 2192 used: pg_operator_oprname_l_r_n_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_trigger_tgrelid_tgname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_enum_typid_label_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_ts_config_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_user_mapping_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1232 free (3 chunks); 1840 used: pg_opfamily_am_name_nsp_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_foreign_table_relid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_type_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_aggregate_fnoid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_constraint_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_rewrite_rel_rulename_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_ts_parser_prsname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_ts_config_cfgname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_ts_parser_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used: pg_publication_rel_prrelid_prpubid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_operator_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_namespace_nspname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_ts_template_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1088 free (2 chunks); 1984 used: pg_amop_opr_fam_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 984 free (2 chunks); 2088 used: pg_default_acl_role_nsp_obj_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1232 free (3 chunks); 1840 used: pg_collation_name_enc_nsp_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_publication_rel_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_range_rngtypid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_ts_dict_dictname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 544 free (2 chunks); 1504 used: pg_type_typname_nsp_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_opfamily_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_statistic_ext_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used: pg_statistic_ext_data_stxoid_inh_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_class_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1232 free (3 chunks); 1840 used: pg_proc_proname_args_nsp_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 40 free (0 chunks); 984 used: pg_partitioned_table_partrelid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_range_rngmultitypid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_transform_type_lang_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 544 free (2 chunks); 1504 used: pg_attribute_relid_attnum_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_proc_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_language_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_namespace_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 880 free (3 chunks); 2192 used: pg_amproc_fam_proc_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_foreign_server_name_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 544 free (2 chunks); 1504 used: pg_attribute_relid_attnam_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used: pg_publication_namespace_pnnspid_pnpubid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_conversion_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used: pg_user_mapping_user_server_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used: pg_subscription_rel_srrelid_srsubid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_sequence_seqrelid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_conversion_name_nsp_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_authid_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1200 free (3 chunks); 1872 used: pg_auth_members_member_role_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_subscription_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_parameter_acl_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_tablespace_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 72 free (0 chunks); 952 used: pg_parameter_acl_parname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1232 free (3 chunks); 1840 used: pg_shseclabel_object_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 40 free (0 chunks); 984 used: pg_replication_origin_roname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_database_datname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 760 free (2 chunks); 1288 used: pg_subscription_subname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 1024 total in 1 blocks; 40 free (0 chunks); 984 used: pg_replication_origin_roiident_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 3072 total in 2 blocks; 1200 free (3 chunks); 1872 used: pg_auth_members_role_member_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_database_oid_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; index info: 2048 total in 2 blocks; 912 free (0 chunks); 1136 used: pg_authid_rolname_index
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; WAL record construction: 50200 total in 2 blocks; 6376 free (0 chunks); 43824 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; PrivateRefCount: 8192 total in 1 blocks; 2648 free (0 chunks); 5544 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; MdSmgr: 8192 total in 1 blocks; 7768 free (0 chunks); 424 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; LOCALLOCK hash: 8192 total in 1 blocks; 592 free (0 chunks); 7600 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; GUCMemoryContext: 24576 total in 2 blocks; 12240 free (4 chunks); 12336 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 2; GUC hash table: 32768 total in 3 blocks; 12704 free (5 chunks); 20064 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; Timezones: 104112 total in 2 blocks; 2648 free (0 chunks); 101464 used
2023-11-29 10:31:00.438 JST [2377723] LOG: level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
2023-11-29 10:31:00.438 JST [2377723] LOG: Grand total: 1390760 bytes in 199 blocks; 313040 free (210 chunks); 1077720 used
ぱっと見は面食らいますが、ちゃんと見ると pg_backend_memory_context ビューと同じような内容が得られています。
オンプレ環境だけでなく、PostgreSQL ベースのマネージド DB サービスでも pg_log_backend_memory_contexts 関数を使用できる場合があります。Amazon RDS for PostgreSQL 15 では以下のようにログに書き出すことが出来ました。
おわりに
Memory Context は公式ドキュメントでもあまり触れられていないような PostgreSQL 内部の話となるため、実際には今回紹介したビューを参照したりやログ出力をさせるだけで解析することは難しいですが、PostgreSQL はオープンソース！公開されているソースコードを検索して (grep -rn "MEMORY_CONTEXT_NAME") 書かれているコメントなどを読むことで、そのプロセスが何の用途にメモリを使用しているのか調査することが可能です。
もし、システム運用中に PostgreSQL が何にメモリを消費しているかわからーん！となった場合には、この記事を思い出してみてください。
記載されている会社名、製品名、サービス名は、各社の商標または登録商標です。
参考
PostgreSQL 14 モニタリング新機能紹介（PostgreSQL カンファレンス #24、2021/06/08）
-
クライアントからの接続を受けて生成される、実際に SQL 処理を行うプロセス。 ↩
-
PostgreSQLのバックエンドプロセスのメモリ使用状況を調査する方法 で紹介されています。 ↩
-
PREPARE コマンドのマニュアル の Notes にある説明の通りです。 ↩