PostgreSQL 慢查询引起的 CPU 占用过高

共码了1486个字
文内使用到的标签:

最近压测部门又反馈了新的问题:

  • 数据库中没有历史记录的时候,105 台设备上传记录,服务器的 CPU 占用低于30%。
  • 当数据库三种表各有 40 万数据的时候。 75 台设备同时上传记录,服务器的 CPU 占用会高于 80%。

还是和上次一样的图,不过这次换了个主角,从图里可以看到,postgre.exe 占了大部分的 CPU 占用:

接到问题后,第一反应是先打开 pgAmin 的面板,连接到对应的压力测试服务,得到了以下的指标:

图里面的第一个红色框,我标注了 Transactions Per Second,其中,淡蓝色表示事务数,绿色表示提交数,红色表示失败回滚的数量,这个指标大概在 400 以下,而且事务一直没有得到比较好的释放,稳定地波动。

第二个红色框,表示的则是 Block I/O,也维持在了一个比较高的水平。

经过一些资料的查询,决定先从慢查询这个因素查起。

PostgreSQL 提供了一个插件,可以方便的进行分析:pg_stat_statements,可以在官方文档中F.29. pg_stat_statements,找到对这个模块开启的方法:

该模块必须通过在 postgresql.confshared_preload_libraries 中增加 pg_stat_statements 来载入,因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。

数据库的配置文件,可以通过以下命令查询:

show config_file;

然后再配置文件上可以做出以下修改:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

接下来,利用插件和数据库系统里面的计数信息(如SQL执行时间累积等),而这些信息是不断累积的,包含了历史信息,为了更方便的排查当前的CPU过高问题,要先使用以下命令重置计数器:

create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();

然后,大概等待一段时间(例如1分钟),使计数器积累足够的信息,再次输入以下命令,查看最耗时的SQL:

select * from pg_stat_statements order by total_time desc limit 5;

再通过以下语句,查询读取Buffer次数最多的SQL:

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

以上的 2 条语句,都查询出了对应的结果,通过 explain 语句分别执行前 3 条语句,发现并未走索引(索引的可视化建立,可以参考 pgAdmin-Index Dialog),于是尝试建立了普通索引,效果变化如下:

可发现,在相同测试环境和条件下,Grafana 显示的 CPU 占用率已经有所下降,从 pgAdmin 面板来看,原有的 Transactions Per Second 指标,不会处于居高不下的状态,可以确认,这就是慢查询引起的性能波动。

最终,我们将所有影响的语句,都单独进行了分析,建立了各自所需的索引类型,再次得到以下的性能数据:

相同的测试条件,从 80% 已经稳定到了 20%,事务的处理方面,性能也有所提升,原本在 350 个事务中波动,现在可以支持 5 万个峰值,8 GB 的测试条件中,原有只能支持 90 台设备,现在已经可以支持到 300 台,经过一系列调整过后,得到了压测小组的赞赏 🙂

Prev:
Next:

《“PostgreSQL 慢查询引起的 CPU 占用过高”》 有 1 条评论

  1. 崔先森说道:

    不明觉厉..

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注