Label
PostgreSQL , memory context , plan , pid signal , ...
background
https://github.com/MasaoFujii/pg_cheat_funcs
Fujii Masao MasaoFujii PostgreSQL committer, DBA at NTT DATA, maybe good husband & father.
This plug-in brings together common PG extension functions (which may be introduced into the PG community in the future), such as
1. View the memory context,
2. View the general cost of SQL binding variables,
3. plan cost status (for the algorithm, see PostgreSQL 12 Preview - plan cache mode parameter control force use of plan cache or force custom plan (force custom plan and force generic plan) Execution plan selection algorithm and binding variables - PostgreSQL prepared statement: spi_prepare, prepare|execute command, PL / PgSQL style: Custom & generic plan cache )，
4. Send signal to PID,
5. Set process priority,
6. Set nextxid and nextoid,
7. String conversion,
8. Base conversion,
9. Compress, decompress, etc.
10. Print memory.
https://github.com/postgrespro/memstat
Module adds statistic report about memory contexts in local and all backends. Functions: setof (name text, level integer, nblocks bigint, freechunks bigint, totalspace bigint, freespace bigint) local_memory_stats() prints memory context's statistic for current backend setof (pid integer, name text, level integer, nblocks bigint, freechunks bigint, totalspace bigint, freespace bigint) instance_memory_stats() prints memory context's statistic for all alive backend, works if library was preloaded via shared_preload_libraries. view: memory_stats prints per backend summary memory statistics To use instance_memory_stats() it's needed to add memstat library to shared_preload_libraries. And it should be last in that list! GUC variable: memstat.period = 10 # seconds Module collects memory statistics at a begining of each query and it could be expensive on highloaded instances, so, this variable set minimal time between statistic obtaining.
Example
. ./env11.sh git clone https://github.com/MasaoFujii/pg_cheat_funcs cd pg_cheat_funcs/ USE_PGXS=1 make USE_PGXS=1 make install
postgres=# create extension pg_cheat_funcs ; CREATE EXTENSION
1. Print memory context
postgres=# select * from pg_stat_get_memory_context(); name | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes --------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------ TopMemoryContext | | 0 | 312552 | 11 | 40520 | 21 | 272032 dynahash | TopMemoryContext | 1 | 8192 | 1 | 1456 | 0 | 6736 TopTransactionContext | TopMemoryContext | 1 | 8192 | 1 | 7744 | 1 | 448 PL/pgSQL function | TopMemoryContext | 1 | 16384 | 2 | 7176 | 1 | 9208 dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632 dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632 dynahash | TopMemoryContext | 1 | 24528 | 2 | 2624 | 0 | 21904 dynahash | TopMemoryContext | 1 | 8192 | 1 | 2096 | 0 | 6096 dynahash | TopMemoryContext | 1 | 8192 | 1 | 1584 | 0 | 6608 dynahash | TopMemoryContext | 1 | 24576 | 2 | 10760 | 3 | 13816 RowDescriptionContext | TopMemoryContext | 1 | 8192 | 1 | 6896 | 0 | 1296 MessageContext | TopMemoryContext | 1 | 32768 | 3 | 10904 | 1 | 21864 dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632 dynahash | TopMemoryContext | 1 | 32768 | 3 | 16832 | 8 | 15936 TransactionAbortContext | TopMemoryContext | 1 | 32768 | 1 | 32512 | 0 | 256 dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632 TopPortalContext | TopMemoryContext | 1 | 8192 | 1 | 7664 | 0 | 528 PortalContext | TopPortalContext | 2 | 1024 | 1 | 592 | 0 | 432 ExecutorState | PortalContext | 3 | 49208 | 4 | 15576 | 3 | 33632 printtup | ExecutorState | 4 | 8192 | 1 | 7936 | 0 | 256 Table function arguments | ExecutorState | 4 | 8192 | 1 | 7936 | 0 | 256 ExprContext | ExecutorState | 4 | 8192 | 1 | 4536 | 0 | 3656 dynahash | TopMemoryContext | 1 | 16384 | 2 | 3512 | 2 | 12872 CacheMemoryContext | TopMemoryContext | 1 | 524288 | 7 | 20960 | 26 | 503328 index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424 index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424 index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424 index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 1 | 1424 index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 1 | 1368 EventTriggerCache | CacheMemoryContext | 2 | 8192 | 1 | 7936 | 2 | 256 dynahash | EventTriggerCache | 3 | 8192 | 1 | 2624 | 0 | 5568 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 relation rules | CacheMemoryContext | 2 | 16384 | 5 | 7352 | 0 | 9032 index info | CacheMemoryContext | 2 | 2048 | 2 | 648 | 2 | 1400 index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344 index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 592 | 3 | 1456 index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936 index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 2 | 1424 index info | CacheMemoryContext | 2 | 2048 | 2 | 672 | 3 | 1376 index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 624 | 2 | 1424 index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 3072 | 2 | 1136 | 2 | 1936 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320 index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 680 | 2 | 1368 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320 index info | CacheMemoryContext | 2 | 1024 | 1 | 48 | 0 | 976 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 704 | 3 | 1344 index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 760 | 2 | 1288 index info | CacheMemoryContext | 2 | 1024 | 1 | 16 | 0 | 1008 index info | CacheMemoryContext | 2 | 2048 | 2 | 728 | 1 | 1320 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 index info | CacheMemoryContext | 2 | 2048 | 2 | 952 | 1 | 1096 WAL record construction | TopMemoryContext | 1 | 49768 | 2 | 6368 | 0 | 43400 dynahash | TopMemoryContext | 1 | 8192 | 1 | 2624 | 0 | 5568 MdSmgr | TopMemoryContext | 1 | 8192 | 1 | 7432 | 0 | 760 dynahash | TopMemoryContext | 1 | 8192 | 1 | 560 | 0 | 7632 dynahash | TopMemoryContext | 1 | 104120 | 2 | 2624 | 0 | 101496 ErrorContext | TopMemoryContext | 1 | 8192 | 1 | 7936 | 0 | 256 (121 rows)
2. Text compression
postgres=# select pglz_compress(repeat(md5(random()::text),1024)); pglz_compress ------------------------------------------------------------------------------------------------------------------------------------- \x00800040006135663437636166003162353066626137006637356161363639003532316233336139ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f 20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f2 0ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f 20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20e7 (1 row)
3. Decompress
pglz_decompress(bytea) postgres=# select pglz_compress(repeat(md5(random()::text),8)); pglz_compress -------------------------------------------------------------------------------------------- \x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce (1 row) postgres=# select pglz_decompress('\x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce'); pglz_decompress ------------------------------------------------------------------------------ 26fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22 bcb0dbef8ac05641a897 (1 row)
4. View the PLAN COSE of the bound variable QUERY.
postgres=# prepare a (name) as select * from pg_class where relname=$1; PREPARE postgres=# select * from pg_cached_plan_source('a'); generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom --------------+-------------------+------------------+---------------+-------------- -1 | 0 | 0 | f | f (1 row) postgres=# execute a('abc'); relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound ---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------- ----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+-------------- (0 rows) postgres=# select * from pg_cached_plan_source('a'); generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom --------------+-------------------+------------------+---------------+-------------- -1 | 4.61208554676785 | 1 | f | f (1 row) postgres=# execute a('abc'); relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound ---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------- ----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+-------------- (0 rows) postgres=# select * from pg_cached_plan_source('a'); generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom --------------+-------------------+------------------+---------------+-------------- -1 | 9.22417109353571 | 2 | f | f (1 row)