作者
digoal
日期
2021-03-01
标签
PostgreSQL , 瓶颈分析
背景
state text
Current overall state of this backend. Possible values are:
- active: The backend is executing a query.
- idle: The backend is waiting for a new client command.
- idle in transaction: The backend is in a transaction, but is not currently executing a query.
- idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.
- fastpath function call: The backend is executing a fast-path function.
- disabled: This state is reported if track_activities is disabled in this backend.
session_time double precision
Time spent by database sessions in this database, in milliseconds (note that statistics are only updated when the state of a session changes, so if sessions have been idle for a long time, this idle time won't be included)
active_time double precision
有可能是wait占用的时间,不过没关系,通过等待统计可以分析出来. Time spent executing SQL statements in this database, in milliseconds (this corresponds to the states active and fastpath function call in pg_stat_activity)
idle_in_transaction_time double precision
Time spent idling while in a transaction in this database, in milliseconds (this corresponds to the states idle in transaction and idle in transaction (aborted) in pg_stat_activity)
sessions bigint
Total number of sessions established to this database
sessions_abandoned bigint
Number of database sessions to this database that were terminated because connection to the client was lost
sessions_fatal bigint
Number of database sessions to this database that were terminated by fatal errors
sessions_killed bigint
Number of database sessions to this database that were terminated by operator intervention
stats_reset timestamp with time zone
Time at which these statistics were last reset
有没有瓶颈?
记录两个时间点的pg_stat_database内容.
1、时间间隔
ts2(stats_reset) - ts1(stats_reset)
2、会话时间片消耗
ts2(sum(active_time)) - ts1(sum(active_time))
3、实例所在主机的cpu 核数 (或通过cgroup限制的PG 实例实际可使用cpu逻辑核数)
cpu cores
4、如果(会话时间片消耗 除以 时间间隔 大于 cpucores), 就有瓶颈, 说明这个时间周期数据库实例满负荷运行.
会话时间片消耗 / 时间间隔 > cpu cores
瓶颈是什么?
使用pg_wait_sampling采集数据库等待事件, 可以了解: 实例层面什么等待最多? 数据库层面什么等待最多? 是哪些TOP SQL累计的等待?
《PostgreSQL 等待事件 及 等待采样统计 (pg_wait_sampling) 发布新版本 1.1.2》
《PostgreSQL 等待事件 及 等待采样统计(pg_wait_sampling)》
《powa4 PostreSQL Workload Analyzer - PostgreSQL监控工具、带WEB展示 - 索引推荐,等待事件分析,命中率,配置变更跟踪等》
https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_wait_sampling.html#pg-wait-sampling-doc
如何消灭瓶颈?
参考这里面的性能优化视频. 包括: 分析SQL, 数据库参数, 系统与存储优化, 业务逻辑优化等手段.
《2019-PostgreSQL 2天体系化培训 - 视频每周更新》
《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级》
参考
《PostgreSQL 活跃会话历史记录插件 - pgsentinel 类似performance insight》
《PostgreSQL 13 preview - wait event sample - 等待事件统计(插件,hook) - ASH - performance insight》
《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》
《PostgreSQL 14 preview - SaaS特性 - 会话建立、资源消耗时间片 统计 - pg_stat_database 指标增加》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
