使用pg_stat_monitor扩展更好地理解postgresql的负载
2022-05-09 21:48 abce 阅读(589) 评论(0) 编辑 收藏 举报pg_stat_monitor已经进入GA状态。
postgres=# \dx List of installed extensions -[ RECORD 1 ]----------------------------------------------------------------------- Name | pg_stat_statements Version | 1.8 Schema | public Description | track planning and execution statistics of all SQL statements executed -[ RECORD 2 ]----------------------------------------------------------------------- Name | plpgsql Version | 1.0 Schema | pg_catalog Description | PL/pgSQL procedural language postgres=# \x Expanded display is on. postgres=# select * from pg_stat_statements; -[ RECORD 2 ]-------+-------------------------------------------------------- userid | 16384 dbid | 16608 queryid | -7945632213382375966 query | select ai_myid, imdb_id, year, title, json_column from movies_normalized_meta where ai_myid = $1 plans | 0 total_plan_time | 0 min_plan_time | 0 max_plan_time | 0 mean_plan_time | 0 stddev_plan_time | 0 calls | 61559 total_exec_time | 27326.783784999938 min_exec_time | 0.062153 max_exec_time | 268.55287599999997 mean_exec_time | 0.44391208084927075 stddev_exec_time | 2.522740928486301 rows | 61559 shared_blks_hit | 719441 shared_blks_read | 1031 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 wal_records | 6 wal_fpi | 0 wal_bytes | 336
可以看到，这个特殊的语句，已经执行了61559次，总共消耗了27326毫秒，平均每次0.44毫秒。
如果该语句是写数据，还可以看到生成的wal统计信息等。这对找到哪些语句没用在内存中命中而执行了物理读写、哪些语句可能会导致wal日志膨胀是非常有用的。
虽然这些数据很有用，但是还可以做的更好。尤其是，很难区分问题是变好了，还是变的更糟糕了。例如，特殊的语句执行了61k次，其中60k次执行的时长是0.01毫秒，但是剩下1k次，执行的时长是1000毫秒。收集足够的信息，可以
虽然这些数据很棒，但它还可以更好。具体来说，很难确定问题是变得更糟还是变得更好。此外，如果执行61K次的特定查询以 0.01ms 60K次和 1000ms 1K次运行会怎样？需要在这里收集足够的数据，以便围绕优化做出更好、更有针对性的决策。 这是pg_stat_monitor可以提供帮助的地方。
来看看pg_stat_monitor的输出示例：
postgres=#
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_monitor ;
-[ RECORD 1 ]-------+---------
bucket | 3
bucket_start_time | 2022-04-27 20:13:00
userid | movie_json_user
datname | movie_json_test
client_ip | 172.31.33.208
queryid | 82650C255980E05
top_queryid |
query | select ai_myid, imdb_id, year, title, json_column from movies_normalized_meta where ai_myid = $1
comments |
planid |
query_plan |
top_query |
application_name |
relations | {public.movies_normalized_meta}
cmd_type | 1
cmd_type_text | SELECT
elevel | 0
sqlcode |
message |
calls | 18636
total_exec_time | 9022.0356
min_exec_time | 0.055
max_exec_time | 60.7575
mean_exec_time | 0.4841
stddev_exec_time | 1.568
rows_retrieved | 18636
plans_calls | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time | 0
shared_blks_hit | 215919
shared_blks_read | 1
shared_blks_dirtied | 39
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 0
blk_write_time | 0
resp_calls | {17946,629,55,6,0,0,0,0,0,0}
cpu_user_time | 3168.0737
cpu_sys_time | 1673.599
wal_records | 9
wal_fpi | 0
wal_bytes | 528
state_code | 3
state | FINISHED
可以看到，新增了很多额外的数据。让我们来比较一下：
统计信息多出了19个列。统计信息粒度更细了一点。
这里，首先要介绍一些buckets的概念。什么是桶呢?桶是时间的配置切片。除了将所有信息都放到单个大的桶中，现在你可以将查询状态分开放入时间切片桶，这样可以看到查询性能的变化过程。默认最大的桶数是10，保存60秒的数据。
这意味着你可以使用你喜欢的时间序列数据库来轻松地查询数据，以获得更多的历史分析功能。我们在内部使用这些存储桶将数据提取到我们的查询分析工具中，并将它们存储在 click house 时间序列数据库中，以提供更多的分析功能。
pg_stat_statement和pg_stat_monitor保留的数据期限是不同的：如果你想长期存储查询数据，可以将pg_stat_monitor和其它监控工具配置使用。
此外，你会注意到包含用户/连接详细信息。许多应用程序使用同一个用户，但有多个客户端连接。通过客户端IP分解数据有助于追踪导致问题的恶意用户或应用程序服务器。
但我想强调一些我最感兴趣的新指标和功能。对我来说，最有趣的是收集直方图数据的能力。这使你能够查看查询是否偏离正常。我们的支持工程师一直关注的关键问题之一是 P99 延迟如何，这有助于解决这个问题。可以在此处看到 PMM监控和管理利用这些功能：
开启这个直方图之后，我们可以查看和追踪查询的性能是否偏离了常规。
此外，你还可以看到cpu time信息。为什么cpu time也很重要呢？查询时间包含等待磁盘和等待网络资源。如果你的系统有cpu瓶颈，则耗时最长的查询可能是问题，也可能不是问题。
最后，你可以配置pg_stat_monitor以存储来自先前运行的查询的解释计划。当计划随着时间而改变时，这非常有用，如果你正试图重现一两个小时前发生的事情。
获得额外的洞察能力和了解工作负载至关重要，而pg_stat_monitor可以帮助做到这两点。 pg_stat_monitor支持端到端可追溯性、跨可配置时间窗口的聚合统计信息和查询执行时间，PMM将这一点可视化并让用户更深入地了解 PostgreSQL 行为。
