利用pg_stat_statments分析业务瓶颈
如果希望减少整个系统的负载,可以按照总时间来查看您的语句:
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(默认情况下处于关闭状态)。
如果您的客户抱怨性能太烂,而您的主要目标是加快最慢的查询,您可以同时查看您的热门查询,例如:
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 进行过滤可以帮助从用户那里移除那些无关紧要的慢速查询。类似地,如果您让人们执行您希望排除的一次性慢速查询,则限制查询执行次数超过最小次数会很方便。
考虑系统资源使用的另一种方法是考虑缓冲区。缓冲区统计信息对查询优化非常有用,可以通过他们查询整体工作负载。
将所有缓冲区统计信息加在一起,以提供一个非常粗略的“完成工作”的代理:
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)。
从 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;
即时编译花费总时间的百分比进行排序。
最耗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 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!