作者
digoal
日期
2020-03-24
标签
PostgreSQL , pgsentinel , 历史活跃会话快照
背景
用来记录历史活跃会话, stat_statements快照等. 回看历史问题
https://github.com/pgsentinel/pgsentinel
还有一个 https://github.com/akardapolov/ASH-Viewer
he worker is controlled by the following GUCs:
Parameter name Data type Description Default value Min value
pgsentinel_ash.sampling_period int4 Period for history sampling in seconds 1 1
pgsentinel_ash.max_entries int4 Size of pg_active_session_history in-memory ring buffer 1000 1000
pgsentinel.db_name char database the worker should connect to postgres
pgsentinel_pgssh.max_entries int4 Size of pg_stat_statements_history in-memory ring buffer 1000 1000
pgsentinel_pgssh.enable boolean enable pg_stat_statements_history false
PostgreSQL provides session activity. However, in order to gather activity
behavior user have to sample the pg_stat_activity view multiple times. pgsentinel is an extension to record active session history and also link the activity with query statistics (pg_stat_statements).
The module must be loaded by adding pgsentinel to shared_preload_libraries in postgresql.conf, means that a server restart is needed to add or remove the module.
When pgsentinel is enabled, it collects the history of session activity:
It's implemented as in-memory ring buffer where samples are written with given (configurable) period. Therefore, user can see some number of recent samples depending on history size (configurable).
In combination with pg_stat_statements this extension can also link the session activity with query statistics.
To get more granular queries statistic pgsentinel extension samples the pg_stat_statements view:
at the same time it is sampling the active sessions
only for the queryid that were associated to an active session (if any) during the sampling
pgsentinel launches special background worker for gathering the sessions activity.
历史pg_stat_activity活跃会话记录, 增加了几个列的信息
You could see it as samplings of pg_stat_activity providing more information:
ash_time: the sampling time
top_level_query: the top level statement (in case PL/pgSQL is used)
query: the statement being executed (not normalised, as it is in pg_stat_statements, means you see the values)
cmdtype: the statement type (SELECT,UPDATE,INSERT,DELETE,UTILITY,UNKNOWN,NOTHING)
queryid: the queryid of the statement which links to pg_stat_statements
blockers: the number of blockers
blockerpid: the pid of the blocker (if blockers = 1), the pid of one blocker (if blockers > 1)
blocker_state: state of the blocker (state of the blockerpid)
《PostgreSQL 13 preview - wait event sample - 等待事件统计(插件,hook) - ASH - performance insight》
《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
