PostgreSQL数据库CPU占用率高的故障处理指南

PostgreSQL是世界上最流行的数据库之一。

中央处理单元(CPU)使用率是检查PostgreSQL DB(数据库)实例的关键指标之一。检查CPU利用率可以了解DB是否遇到性能问题，如低效的SQL查询、缺乏索引和争用。

在看到PostgreSQL DB实例的高CPU之后，找到问题的根本原因是至关重要的。查询可能写得很差、太频繁或太重。

这篇博文描述了一些有用的SQL查询，可以在PostgreSQL DB实例上运行，以调查高CPU利用率。它还将帮助您了解影响CPU使用的数据库实例中运行的内容。

SQL查询#1 -连接摘要

导致高CPU利用率的PostgreSQL DB模式之一是大量的活动连接。下面的SQL查询列出了:

1. 总连接数
2. 非空闲连接数
3. 最大可用连接数
4. 连接利用率

select A.total_connections, A.non_idle_connections, B.max_connections, round((100 * A.total_connections::numeric / B.max_connections::numeric)， 2) connections_utilitzation_pctg from (select count(1) as total_connections, sum(case when state!)='idle' then 1 else 0 end) as non_idle_connections from pg_stat_activity) A， (select set as max_connections from pg_settings where name='max_connections') B;

下面是一个SQL输出的例子:

total_connections | non_idle_connections | max_connections | connections_utilization_pctg  -------------------+----------------------+-----------------+------------------------------ 3457 | | 9057 | 38.17

在上面的输出示例中，PostgreSQL DB实例当前有3,457个连接，到DB服务器的最大并发连接数为9,057个，这意味着占用了38.17%的连接槽。非空闲连接数为3。

SQL查询#1的建议是什么?

SQL查询1的建议是检查在PostgreSQL DB实例上运行的会话，尝试使用EXPLAIN来识别和分析长时间运行的、写得不好的、过于频繁的查询。如果每个CPU核心的活动连接数超过一个，建议检查和调优使用DB的应用程序。

查询最大连接数，使用如下SQL命令:

显示max_connections;

通常，默认值是100个连接。

如果连接数接近给定PostgreSQL实例设置的最大连接数，建议分析应用程序活动和/或应用程序逻辑，减少到达DB的连接数，调优max_connections参数，或扩展DB实例。

此外，我建议使用和/或调优连接池:外部连接池，如PgBouncer，或内部连接池，如Java的HikariCP或Go的PGX。

参数max_connections定义了到PostgreSQL DB Server的最大并发连接数。修改此参数需要在修改后重新启动PostgreSQL DB实例，新值才能生效。对于这个实例，建议将max_connections PostgreSQL参数设置为峰值负载下预期的最大连接数。

另一方面，max_connections参数需要与PostgreSQL DB机器的可用资源保持一致。2022世界杯阿根廷预选赛赛程由于每个PostgreSQL DB连接分配一块shared_buffer内存以及非共享内存，因此应该仔细地进行调优，以避免系统内存不足问题。

为了调整参数max_connections和其他PostgreSQL数据库实例的关键参数，我建议使用免费的在线工具，例如，PostgreSQL配置生成器https://www.pgconfig.org．

SQL查询#2 -每个数据库的非空闲连接分布

使用下面的查询来查看每个数据库的非空闲连接数的分布，按降序排序:

Select datname为db_name, count(1)为num_non_idle_connections from pg_stat_activity where state!='idle' group by 1 order by 2 desc;

下面是一个SQL输出的例子:

db_name | num_non_idle_connections  ----------------------------+-------------------------- 6 my_db_3 my_db_2 my_db_1 | 133 | | 3

在上面的示例中，顶级非空闲会话在数据库my_db_1上运行。

SQL Query #2的建议是什么?

在这种情况下，建议检查PostgreSQL DB实例上运行的数据库my_db_1的会话，试图识别长时间运行的、写得不好的、过于频繁的查询。

SQL Query #3——每个数据库和每个查询的非空闲连接分布

检查每个数据库和每个查询的非空闲连接的分布，按降序排序，如下例所示:

Select datname为db_name, query, count(1)为num_non_idle_connections from pg_stat_activity='idle' group by 1,2 order by 3 desc;

结果集中的输出文本可能看起来太长。在这种情况下，修改后的查询版本会很有帮助，如下面的例子所示:

Select datname为db_name, substr(query, 1,200) short_query, count(1)为num_non_idle_connections from pg_stat_activity where state!='idle' group by 1,2 order by 3 desc;

下面是一个SQL输出的例子:

db_name | short_query | num_non_idle_connections  ----------+---------------------------------------+-------------------------- 40 db_1 db_1 | select * from table_1 | | select * from table_2 | 1

在这个例子中，连接到db_1的应用程序/客户端正在运行两个PostgreSQL查询。上面的查询从table_1中选择数据，它有40个非空闲连接。

SQL Query #3的建议是什么?

在这种情况下，建议检查具有最高非空闲连接的SQL查询。大量的非空闲连接可能表明无效、不可伸缩的体系结构或工作负载，与系统资源不匹配。2022世界杯阿根廷预选赛赛程

SQL查询#4 -非空闲会话的详细信息

列出耗时超过5秒的非空闲PostgreSQL会话，按运行时间降序排序，如下例所示:

Select now()-query_start为runtime, pid为process_id, datname为db_name, client_addr, client_hostname, query from pg_stat_activity where state!='idle' and now() - query_start > '5 seconds'::interval order by 1 desc;

如果结果集看起来太宽，修改后的查询版本可以像下面的例子一样运行:

Select now()-query_start为运行时，pid为process_id, datname为db_name, client_addr, client_hostname, substr(查询，1200)the_query from pg_stat_activity where state!='idle' and now() - query_start > '5 seconds'::interval order by 1 desc;

下面是一个SQL输出的例子:

运行时| process_id | db_name | client_addr | client_hostname |查询  ----------+------------+----------+----------------+-----------------+---------------- 00:12:34 | 7770 | db_1 | 192.168.12.208 | |选择……

对于每个长时间运行的查询，结果集包含相应的运行时、进程id、数据库名称、客户端地址和主机名。

SQL Query #4的建议是什么?

在某些场景中，长时间运行的查询可能导致高CPU利用率。在这些情况下，应该分析并适当调优结果集中获得的查询。

如果查询运行时间过长，导致DB CPU和其他资源的高负载，您可能希望显式地终止它。2022世界杯阿根廷预选赛赛程使用命令终止PostgreSQL数据库会话。

选择pg_terminate_backend (< process_id >);

SQL查询#5 -运行频繁的SQL查询

PostgreSQL数据库中CPU使用率高的根本原因可能不是一个必要的长时间运行的查询。每秒运行数百次的快速但过于频繁的查询也会导致较高的CPU利用率。

要查找最频繁的PostgreSQL查询，运行如下示例所示的SQL查询:

a as (select dbid, queryid, query, calls from pg_stat_statements)， b as (select dbid, queryid, query, calls from pg_stat_statements, pg_sleep(1))， select pd。Datname = db_name, substr = db_name。查询，1,400)作为the_query, sum(b.s-a.s)作为runs_per_second from a, b, pg_database pd where a.dbid= b.dbid and a.c queryid = b.c queryid and p.d oid=a。Dbid group by 1, order by 3;

下面是输出的一个例子:

db_name | the_query | runs_per_second  ----------------------------------+------------------------------------------------------------ db_1 |选择……从…在那里……| 10 db_1 | insert into…Values(…)| 2

这个结果集包括查询列表和相应的频率:每个查询每秒运行多少次。

SQL Query #5的建议是什么?

检查SQL查询和相应的应用程序逻辑。尝试改进应用程序架构以使用缓存机制。这将有助于防止在PostgreSQL数据库服务器上过于频繁地运行SQL查询。

SQL查询#6 - PostgreSQL数据库每个数据库和每个查询的CPU分布

这个查询检查每个数据库中的每个查询使用了多少CPU。它提供了一个按cpu最密集查询降序排序的结果集。

对于PostgreSQL版本12及更早的版本:

选择pss。userid, pss。dbid pd。Datname为db_name，取整(pss。Total_time::数字，2)作为Total_time，电话,圆(pss。Mean_time::数值，2)作为平均值，取整((100 * pss)。total_time / sum(pss.total_time::numeric) OVER ())::numeric, 2) as cpu_portion_pctg，查询pg_stat_statements pss, pg_database pd WHERE pd.oid=pss。dbid ORDER BY pass。total_time DESC LIMIT 30;

对于从13开始的PostgreSQL版本:

选择pss。userid, pss。dbid pd。Datname为db_name, round()Total_exec_time + pss.total_plan_time)::数字，2)作为total_time, pss。调用round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2)作为mean, round((100 * (pss. exec_time))Total_exec_time + pss.total_plan_time) / sum((pss。total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg, pss.查询pg_stat_statements pss, pg_database pd WHERE pd.oid=pss。dbid ORDER BYtotal_exec_time + pss.total_plan_time)

如果输出文本太长，SQL查询#6可以修改如下:

对于PostgreSQL版本12及更早的版本:

选择pss。userid, pss。dbid pd。Datname为db_name，取整(pss。Total_time::数字，2)作为Total_time，电话,圆(pss。Mean_time::数值，2)作为平均值，取整((100 * pss)。total_time / sum(pss.total_time::numeric) OVER ())::numeric, 2) as cpu_portion_pctg, substr(pss. total_time::numeric)pg_stat_statements pss, pg_database pd WHERE pss. oid=pss. sqldbid ORDER BY pass。total_time DESC LIMIT 30;

对于从13开始的PostgreSQL版本:

选择pss。userid, pss。dbid pd。Datname为db_name, round()Total_exec_time + pss.total_plan_time)::数字，2)作为total_time, pss。调用round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2)作为mean, round((100 * (pss. exec_time))Total_exec_time + pss.total_plan_time) / sum((pss。total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg, substr(pss. plan_time)pg_stat_statements pss, pg_database pd WHERE pss. oid=pss. sqldbid ORDER BYtotal_exec_time + pss.total_plan_time)

下面是一个SQL输出的例子:

Userid | dbid | db_name | total_time | calls | mean | cpu_portion_pctg | short_query 16409 | 16410 | db_1 | 27349172.12 | 3905898 | 7.00 | 25.15 | select…From table_1 16409 | 16410 | db_1 | 391755.00 | 105 | 3731.00 | 16.76 | select…从表2…

从输出示例中，您可以看到:

• 第一个查询(即，select…from table_1)占用了大部分CPU，因为有大量的调用。我建议查看应用程序运行连接到PostgreSQL数据库的查询的频率。

• 还要检查第二个查询(即，select…from table_2)，因为执行该语句的平均时间为3731 ms，超过了3秒。

SQL Query #6的建议是什么?

检查使用大量CPU或时间的SQL查询。此外，查找具有高平均时间和/或大量调用的查询。

如果查询的输出显示“特权不足”，则应运行以下命令，如下例所示:

GRANT pg_read_all_stats TO 

SQL查询#5和#6是基于PostgreSQL的pg_stat_statements扩展。pg_stat_statements扩展允许跟踪PostgreSQL DB实例上运行的顶级/所有SQL语句的统计信息。在运行查询之前，应该为PostgreSQL DB实例启用pg_stat_statements。

要启用pg_stat_statements，请设置PostgreSQL服务器配置参数pg_stat_statements。以管理员身份连接到数据库PostgreSQL，执行如下命令:

创建扩展pg_stat_statements;

pg_stat_statements。跟踪配置参数控制哪些语句被模块计数。指定top来跟踪顶级语句(由客户端直接发出的语句)，指定all来跟踪嵌套语句(例如在函数中调用的语句)，或者指定none来禁用语句统计信息收集。默认值为top。只有超级用户可以更改此设置。

为了验证pg_stat_statements扩展是否被启用，下面的命令应该返回一些正数的记录:

Select count(1) from pg_stat_statements;

重置pg_stat_statements收集的所有统计信息，执行如下命令:

选择pg_stat_statements_reset ();

将PostgreSQL查询结果保存到文件的方法如下:

Postgres => \o some_output_file。TRC postgres=> select * from some_table_1;Postgres => select * from some_table_2;Postgres => select * from some_table_3;Postgres => \q dmitryr@dmitryr-mac my_postgres % ls -rtogla some_output_file。trc -rw-r——r——1 59 Oct 24 23:32 some_output_file. txtTRC dmitryr@dmitryr-mac my_postgres %

查询PostgreSQL数据库表的统计信息

过时的PostgreSQL统计数据可能是导致高CPU利用率的另一个根本原因。当统计数据不更新时，PostgreSQL查询规划器可能会为查询生成低效的执行计划，从而导致整个PostgreSQL DB Server的性能下降。

要查看PostgreSQL数据库服务器中每个表的统计数据更新的最后日期和时间，请连接到数据库并运行以下查询:

从pg_stat_all_tables中查询schemaname = 'public'的schemaname、DATE_TRUNC('minute'， last_analyze)、DATE_TRUNC('minute'， last_autoanalyze)、DATE_TRUNC('minute'， last_autoanalyze)、last_autoanalyze的顺序。

下面是一个SQL输出的例子:

schemaname | relname | last_analyze | last_autoanalyze  ------------+---------------------------------------------------------+------------------------+------------------------ 公共| my_table_1 | 2022-11-05 04:05:00 + 00公共| | my_table_3 | 2022-11-05 04:05:00 + 00公共| | my_table_2 | 2022-11-05 04:05:00 + 00 |

SQL Query #7的建议是什么?

确保定期分析表格。

要手动收集特定表及其关联索引的统计信息，运行命令:

分析< table_name >;

SQL查询#8 -检查PostgreSQL数据库膨胀

在数据更新密集的情况下，无论是频繁的UPDATE还是INSERT / DELETE操作，PostgreSQL表及其索引都会变得臃肿。膨胀是指由表或索引分配的磁盘空间，现在可供数据库重用，但尚未回收。由于这种膨胀，PostgreSQL DB Server的性能会下降，从而可能导致高CPU利用率的场景。

在正常的PostgreSQL操作中，由于更新而被删除或过时的元组不会从表中物理移除——它们会存储在那里，直到发出VACUUM命令。VACUUM释放被“死”元组占用的空间。因此，有必要定期执行VACUUM，特别是对于经常更改的表。

要查看死元组的信息，以及在PostgreSQL数据库服务器上为特定数据库的每个表运行vacuum / autovacuum时，连接到数据库并运行以下查询:

select schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup, DATE_TRUNC('minute'， last_vacuum)， DATE_TRUNC('minute'， last_autovacuum)， last_autovacuum from pg_stat_all_tables where schemaname = 'public' order by n_dead_tup desc;

下面是输出的一个例子:

schemaname | relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum公共| table_1 | 30237555 | 30237555 | 26858 | 30184398 | 142226 | 2022-11-05 04:00:00 + 00公共| | table_4 | 26204826 | 0 | 26204826 | 26204826 | 11688 | 2022-11-05 04:03:00 + 00 | 2022-11-04 16:13:00 + 00公共| table_2 | 25934622 | 0 | 25934622 | 25934622 | 11647 | 2022-11-05 04:03:00 + 00 | 2022-11-04 00:15:00 + 00公共| table_3 | 577825 | 4573009 | 0 | 11132 | 6476040 |2012-11-05 04:04:00+00 |

SQL Query #8的建议是什么?

确保桌子定期吸尘。

要对一个特定的表及其所有关联的索引运行VACUUM (regular, not FULL)，运行命令:

真空< table_name >;

VACUUM可以通过自动真空过程与统计数据收集一起运行。Autovacuum是一个后台进程，自动执行VACUUM和ANALYZE命令。

检查PostgreSQL数据库表的统计和膨胀

您还可以将SQL查询#7和#8合并为单个SQL查询。

从pg_stat_all_tables表中选择schemaname = 'public'，顺序为n_dead_tup desc的schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze;

SQL Query #9的建议是什么?

获取一个表的列表，这些表要么从未被分析过，要么是很久以前分析过的，要么是自上次收集DB统计信息并进行真空运行以来发生了很多变化的。调整PostgreSQL进程的autovacuum，以确保一个表或它的索引被修改的频率越高，执行vacuum和分析的频率就越高。

要收集DB统计信息，并对PostgreSQL DB实例的所有DB的所有对象进行真空(常规，而不是FULL)，请运行以下命令:

vacuumdb -h  -p  -U  -j 4 -z -v -a

要收集DB统计数据，并对某个特定数据库的所有对象进行真空(常规，而不是FULL)，请运行以下命令:

-h  -p  -U  -j 4 -z -v 

结论

在这篇博文中，我研究了针对PostgreSQL数据库中高CPU利用率问题的最全面和最有效的故障排除实践。我还对可用于解决高CPU利用率问题的不同类型的SQL查询进行了深入分类，并提供了如何使用这些查询的示例。毫无疑问，这些知识将成为任何生产任务关键型PostgreSQL DB环境的一部分。