LoginSignup
7
0

More than 3 years have passed since last update.

@fujii_masao

PostgreSQLのバックエンドプロセスのメモリ使用状況を調査する方法

Posted at

デバッガを使ってMemoryContextStats(TopMemoryContext)を呼び出すことで、各バックエンドプロセスのメモリ使用状況を調査できます。例えば、PID=1000のバックエンドプロセスについてメモリの使用状況を調査したい場合は、以下のようなコマンドを実行します。

$ cat << EOF > /tmp/monitor_memory
call MemoryContextStats(TopMemoryContext)
quit
EOF

$ lldb -p 1000 -s /tmp/monitor_memory
$ rm /tmp/monitor_memory

このコマンドにより、バックエンドプロセスのメモリ使用状況がPostgreSQLのサーバログなどに出力されます。例えば、PostgreSQL12で、プリペアド文を10個作成したバックエンドに対して上記コマンドを実行すると、以下のような出力がメモリ使用状況として得られます。メモリの使用状況からも、プリペアド文が10個作成されているのが確認できます。

TopMemoryContext: 68720 total in 5 blocks; 15744 free (15 chunks); 52976 used
  Prepared Queries: 16384 total in 2 blocks; 6624 free (4 chunks); 9760 used
  Operator lookup cache: 24576 total in 2 blocks; 10720 free (4 chunks); 13856 used
  RowDescriptionContext: 8192 total in 1 blocks; 6888 free (0 chunks); 1304 used
  MessageContext: 8192 total in 1 blocks; 6888 free (1 chunks); 1304 used
  Operator class cache: 8192 total in 1 blocks; 520 free (0 chunks); 7672 used
  smgr relation table: 16384 total in 2 blocks; 4552 free (3 chunks); 11832 used
  TransactionAbortContext: 32768 total in 1 blocks; 32512 free (0 chunks); 256 used
  Portal hash: 8192 total in 1 blocks; 520 free (0 chunks); 7672 used
  TopPortalContext: 8192 total in 1 blocks; 7936 free (1 chunks); 256 used
  Relcache by OID: 16384 total in 2 blocks; 3432 free (3 chunks); 12952 used
  CacheMemoryContext: 524288 total in 7 blocks; 116496 free (1 chunks); 407792 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge9 AS SELECT * FROM t WHERE i = 9;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge8 AS SELECT * FROM t WHERE i = 8;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge7 AS SELECT * FROM t WHERE i = 7;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge6 AS SELECT * FROM t WHERE i = 6;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge5 AS SELECT * FROM t WHERE i = 5;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge4 AS SELECT * FROM t WHERE i = 4;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge3 AS SELECT * FROM t WHERE i = 3;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge2 AS SELECT * FROM t WHERE i = 2;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (0 chunks); 2440 used: PREPARE hoge1 AS SELECT * FROM t WHERE i = 1;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    CachedPlanSource: 4096 total in 3 blocks; 1656 free (7 chunks); 2440 used: PREPARE hoge0 AS SELECT * FROM t WHERE i = 0;
      CachedPlanQuery: 4096 total in 3 blocks; 1232 free (1 chunks); 2864 used
    index info: 2048 total in 2 blocks; 664 free (1 chunks); 1384 used: pg_db_role_setting_databaseid_rol_index
    index info: 2048 total in 2 blocks; 640 free (2 chunks); 1408 used: pg_opclass_am_name_nsp_index
    index info: 2048 total in 2 blocks; 936 free (2 chunks); 1112 used: pg_foreign_data_wrapper_name_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_enum_oid_index
    index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: pg_class_relname_nsp_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_foreign_server_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_publication_pubname_index
    index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: pg_statistic_relid_att_inh_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_cast_source_target_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_language_name_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_transform_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_collation_oid_index
    index info: 3072 total in 2 blocks; 1152 free (2 chunks); 1920 used: pg_amop_fam_strat_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_index_indexrelid_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_ts_template_tmplname_index
    index info: 2048 total in 2 blocks; 640 free (2 chunks); 1408 used: pg_ts_config_map_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_opclass_oid_index
    index info: 2048 total in 2 blocks; 936 free (2 chunks); 1112 used: pg_foreign_data_wrapper_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_event_trigger_evtname_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_statistic_ext_name_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_publication_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_ts_dict_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_event_trigger_oid_index
    index info: 3072 total in 2 blocks; 1152 free (2 chunks); 1920 used: pg_conversion_default_index
    index info: 3072 total in 2 blocks; 1064 free (2 chunks); 2008 used: pg_operator_oprname_l_r_n_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_trigger_tgrelid_tgname_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_enum_typid_label_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_ts_config_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_user_mapping_oid_index
    index info: 2048 total in 2 blocks; 640 free (2 chunks); 1408 used: pg_opfamily_am_name_nsp_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_foreign_table_relid_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_type_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_aggregate_fnoid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_constraint_oid_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_rewrite_rel_rulename_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_ts_parser_prsname_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_ts_config_cfgname_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_ts_parser_oid_index
    index info: 2048 total in 2 blocks; 664 free (2 chunks); 1384 used: pg_publication_rel_prrelid_prpubid_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_operator_oid_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_namespace_nspname_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_ts_template_oid_index
    index info: 2048 total in 2 blocks; 640 free (2 chunks); 1408 used: pg_amop_opr_fam_index
    index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: pg_default_acl_role_nsp_obj_index
    index info: 2048 total in 2 blocks; 640 free (2 chunks); 1408 used: pg_collation_name_enc_nsp_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_publication_rel_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_range_rngtypid_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_ts_dict_dictname_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_type_typname_nsp_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_opfamily_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_statistic_ext_oid_index
    index info: 2048 total in 2 blocks; 936 free (2 chunks); 1112 used: pg_statistic_ext_data_stxoid_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_class_oid_index
    index info: 2048 total in 2 blocks; 640 free (2 chunks); 1408 used: pg_proc_proname_args_nsp_index
    index info: 2048 total in 2 blocks; 936 free (2 chunks); 1112 used: pg_partitioned_table_partrelid_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_transform_type_lang_index
    index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: pg_attribute_relid_attnum_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_proc_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_language_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_namespace_oid_index
    index info: 3072 total in 2 blocks; 1064 free (2 chunks); 2008 used: pg_amproc_fam_proc_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_foreign_server_name_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_attribute_relid_attnam_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_conversion_oid_index
    index info: 2048 total in 2 blocks; 664 free (2 chunks); 1384 used: pg_user_mapping_user_server_index
    index info: 2048 total in 2 blocks; 664 free (2 chunks); 1384 used: pg_subscription_rel_srrelid_srsubid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_sequence_seqrelid_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_conversion_name_nsp_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_authid_oid_index
    index info: 2048 total in 2 blocks; 664 free (2 chunks); 1384 used: pg_auth_members_member_role_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_subscription_oid_index
    index info: 2048 total in 2 blocks; 968 free (2 chunks); 1080 used: pg_tablespace_oid_index
    index info: 2048 total in 2 blocks; 640 free (2 chunks); 1408 used: pg_shseclabel_object_index
    index info: 2048 total in 2 blocks; 936 free (2 chunks); 1112 used: pg_replication_origin_roname_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_database_datname_index
    index info: 2048 total in 2 blocks; 696 free (2 chunks); 1352 used: pg_subscription_subname_index
    index info: 2048 total in 2 blocks; 936 free (2 chunks); 1112 used: pg_replication_origin_roiident_index
    index info: 2048 total in 2 blocks; 664 free (2 chunks); 1384 used: pg_auth_members_role_member_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_database_oid_index
    index info: 2048 total in 2 blocks; 880 free (1 chunks); 1168 used: pg_authid_rolname_index
  WAL record construction: 49776 total in 2 blocks; 6352 free (0 chunks); 43424 used
  PrivateRefCount: 8192 total in 1 blocks; 2592 free (0 chunks); 5600 used
  MdSmgr: 8192 total in 1 blocks; 7808 free (0 chunks); 384 used
  LOCALLOCK hash: 8192 total in 1 blocks; 520 free (0 chunks); 7672 used
  Timezones: 104128 total in 2 blocks; 2592 free (0 chunks); 101536 used
  ErrorContext: 8192 total in 1 blocks; 7936 free (3 chunks); 256 used
Grand total: 1176992 bytes in 254 blocks; 336984 free (196 chunks); 840008 used

MemoryContextStats()により出力されるメモリ使用状況は、最大で100行であることに注意してください。100行以上メモリの使用状況を出力したい場合は、代わりに、出力したい最大の行数を引数にしてMemoryContextStatsDetail()を使います。例えば、メモリ使用状況を最大1万行出力したい場合は、以下のようなコマンドを実行します。

$ cat << EOF > /tmp/monitor_memory
call MemoryContextStatsDetail(TopMemoryContext, 10000)
quit
EOF

$ lldb -p 1000 -s /tmp/monitor_memory
$ rm /tmp/monitor_memory
7
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
0