暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL cheat functions - (内存上下文\planner内容\memory context等常用函数)

PostgreSQL cheat functions - (内存上下文\planner内容\memory context等常用函数)

digoal 2018-09-08
420

作者

digoal

日期

2018-09-08

标签

PostgreSQL , memory context , plan , pid signal , ...

背景

https://github.com/MasaoFujii/pg_cheat_funcs

Fujii Masao MasaoFujii PostgreSQL committer, DBA at NTT DATA, maybe good husband & father.

这个插件汇集了常用的PG扩展函数（将来也许会引入到PG社区中），比如

1、查看内存上下文，

2、查看SQL的绑定变量的通用成本，

3、plan成本状态（算法见《PostgreSQL 12 preview - plan_cache_mode参数控制强制使用plan cache或强制custom plan (force_custom_plan and force_generic_plan)》 《执行计划选择算法 与 绑定变量 - PostgreSQL prepared statement: SPI_prepare, prepare|execute COMMAND, PL/pgsql STYLE: custom & generic plan cache》 ），

4、给PID发信号，

5、设置进程优先级，

6、设置nextxid，nextoid，

7、字符串转换，

8、进制转换，

9、压缩，解压缩等。

10、打印内存。

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. ```

例子

```
. ./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、打印内存上下文

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、文本压缩

```

postgres=# select pglz_compress(repeat(md5(random()::text),1024)); pglz_compress

\x00800040006135663437636166003162353066626137006637356161363639003532316233336139ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f
20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f2
0ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f
20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20e7
(1 row)
```

3、解压缩

```
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、查看绑定变量QUERY的PLAN COSE。

``` 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)

```

参考

https://github.com/MasaoFujii/pg_cheat_funcs

https://github.com/postgrespro/memstat

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

postgresqldigoal
文章转载自digoal，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论