[翻译]利用pg_stat_statments分析业务瓶颈
[翻译]利用pg_stat_statments分析业务瓶颈
利用pg_stat_statments分析业务瓶颈
1、查看系统负载
如果希望减少整个系统的负载，可以按照总时间来查看您的语句：
select
(total_exec_time + total_plan_time)::int as total_time,
total_exec_time::int,
total_plan_time::int,
mean_exec_time::int,
calls,
query
from
pg_stat_statements
order by
total_time desc
limit 50;返回所有调用中花费时间最多的50个查询。这意味着频繁执行的快查询可能排在不经常执行的慢查询前面。这可能是查询使用最多系统资源的一个很好的方式。
如果您使用的是 Postgres 版本 12（或更早版本），您将无法访问planning time，并且您还需要分别用 total_time 和 mean_time 替换 total_exec_time 和 mean_exec_time。
如果您使用的是 Postgres 版本 13（或更高版本）并注意到您的 total_plan_time 列全为零，您可能需要查看pg_stat_statements.track_planning（默认情况下处于关闭状态）。
2、查看热门查询
如果您的客户抱怨性能太烂，而您的主要目标是加快最慢的查询，您可以同时查看您的热门查询，例如：
select
(mean_exec_time + mean_plan_time)::int as mean_time,
mean_exec_time::int,
mean_plan_time::int,
calls,
query
from
pg_stat_statements
--where
-- userid = 99999
-- and calls > 1
order by
mean_time desc
limit 50;这与上面的例子非常相似，关于版本 13 之前和之后的警告是一样的！
在注释掉的 where 子句中，您可以看到用于减少结果干扰的选项。对 userid 进行过滤可以帮助从用户那里移除那些无关紧要的慢速查询。类似地，如果您让人们执行您希望排除的一次性慢速查询，则限制查询执行次数超过最小次数会很方便。
3、减少IO
考虑系统资源使用的另一种方法是考虑缓冲区。缓冲区统计信息对查询优化非常有用，可以通过他们查询整体工作负载。
将所有缓冲区统计信息加在一起，以提供一个非常粗略的“完成工作”的代理：
select
shared_blks_hit + shared_blks_read + shared_blks_dirtied + shared_blks_written + local_blks_hit + local_blks_read + local_blks_dirtied + local_blks_written + temp_blks_read + temp_blks_written as total_buffers,
(total_exec_time + total_plan_time)::int as total_time,
calls,
shared_blks_hit as sbh,
shared_blks_read as sbr,
shared_blks_dirtied as sbd,
shared_blks_written as sbw,
local_blks_hit as lbh,
local_blks_read as lbr,
local_blks_dirtied as lbd,
local_blks_written as lbw,
temp_blks_read as tbr,
temp_blks_written as tbr,
query
from
pg_stat_statements
order by
total_buffers desc
limit 50;您可能希望将这些缓冲区统计信息中的一些与上面的查询混合搭配，例如查看您按总时间排名的每个查询的 total_buffers。
在这种情况下，我更喜欢查看带有块号的列，但是如果您更喜欢以字节为单位查看它们中的任何一个，您可能会喜欢函数pg_size_pretty() — 如果这样做，请记住乘以您的块大小（默认为 8192）。
4、调整JIT设置
从 Postgres 15 开始，pg_stat_statements 中有了JIT编译统计（和 I/O 计时，但那些可以等到另一天）。我看到很多人遇到过早启动 JIT 编译的问题，这对他们的查询和/或工作负载造成了净损害。下面是一个示例查询，我们可以使用它来查看 JIT 编译时间最长的查询（占时间的百分比）：
select
((jit_generation_time + jit_inlining_time + jit_optimization_time + jit_emission_time)/(total_exec_time + total_plan_time)) as jit_total_time_percent,
calls,
jit_functions,
jit_generation_time,
jit_inlining_count,
jit_inlining_time,
jit_optimization_count,
jit_optimization_time,
jit_emission_count,
jit_emission_time,
query
from
pg_stat_statements
order by
jit_total_time_percent desc
limit 50;即时编译花费总时间的百分比进行排序。
5、其他常用SQL
最耗IO SQL，单次调用最耗IO SQL TOP 5：
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;总最耗IO SQL TOP 5：
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;最耗时 SQL，单次调用最耗时 SQL TOP 5：
select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;总最耗时 SQL TOP 5：
select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;响应时间抖动最严重 SQL：
select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;最耗共享内存 SQL：
select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;最耗临时空间 SQL：
select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;本文分享自 yanzongshuaiDBA 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体分享计划 ，欢迎热爱写作的你一起参与！