postgresql进程cpu过高分析和解决方案_谷满满

网络投稿 02-07 8129

只有活跃的cpu才会使cpu的使用增高。一：通过pg_stat_activity视图查询慢查询或高处理时长的进程

select pid, ? ? ? ?datname, ? ? ? ?usename, ? ? ? ?client_addr, ? ? ? ?application_name, ? ? ? ?state, ? ? ? ?backend_start, ? ? ? ?xact_start, ? ? ? ?xact_stay, ? ? ? ?query_start, ? ? ? ?query_stay, ? ? ? ?replace(query, chr(10), ' ') as query from ? (select pgsa.pid as pid, ? ? ? ? ? pgsa.datname as datname, ? ? ? ? ? pgsa.usename as usename, ? ? ? ? ? pgsa.client_addr client_addr, ? ? ? ? ? pgsa.application_name as application_name, ? ? ? ? ? pgsa.state as state, ? ? ? ? ? pgsa.backend_start as backend_start, ? ? ? ? ? pgsa.xact_start as xact_start, ? ? ? ? ? extract(epoch ? ? ? ? ? ? ? ? ? from (now() - pgsa.xact_start)) as xact_stay, ? ? ? ? ? pgsa.query_start as query_start, ? ? ? ? ? extract(epoch ? ? ? ? ? ? ? ? ? from (now() - pgsa.query_start)) as query_stay, ? ? ? ? ? pgsa.query as query ? ?from pg_stat_activity as pgsa ? ?where pgsa.state != 'idle' ? ? ?and pgsa.state != 'idle in transaction' ? ? ?and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 10;

执行结果分析

sql执行结果中的pid，在任务管理器的详细信息中，找到相同的pid，确认cpu占用。

1、postgresql的某些进程cpu很高时，确认其慢查询。

2、postgresql的每个进程cpu都在正常范围内时，确认是否为活动连接陡增，造成的cpu上升。

慢查询造成cpu增加的解决方案

上述SQL执行结果中的query信息，为慢查询记录。需要对其中的语句进行SQL优化。假设query内容为空，但是xact_stay有很长是，考虑为事务耗时太长，需要对程序进行具体分析。

1：结束慢查询

select pg_cancel_backend(pid) from pg_stat_activity where ?query like '%<query text>%' and pid != pg_backend_pid(); select pg_terminate_backend(pid) from pg_stat_activity where ?query like '%<query text>%' and pid != pg_backend_pid();

2、慢查询优化

（1）执行ANALYZE [$Table]或VACUUM ANZLYZE [$Table]

对查询涉及的表，执行ANALYZE [$Table]或VACUUM ANZLYZE [$Table]语句，更新表的统计信息，使查询计划更准确。为避免对业务影响，最好在业务低峰执行。说明：[$Table]为查询涉及的表。

（2）对其中的Table Scan涉及的表，建立索引。

选择一条如下SQL语句执行，查看SQL的执行计划，第一条SQL语句不会实际执行SQL语句，第二条SQL语句会实际执行而且能得到详细的执行信息，对其中的Table Scan涉及的表，建立索引。 explain [$Query_Text] explain (buffers true, analyze true, verbose true) [$Query_Text]

说明：[$Query_Text]为SQL文件或语句。

（3）重新编写SQL语句

重新编写SQL语句，去除掉不必要的子查询、改写UNION ALL、使用JOIN CLAUSE固定连接顺序等；

优化点：使用COPY语句代替多个Insert语句；将多个SQL语句组成一个事务以减少提交事务的开销；从一个索引中提取多条记录时使用CLUSTER；从一个查询结果中取出部分记录时使用LIMIT；使用预编译式查询（Prepared Query)；使用ANALYZE以保持精确的优化统计；定期使用 VACUUM 或 pg_autovacuum；进行大量数据更改时先删除索引（然后重建索引）；

活动连接陡增的确认方法

select count( * ) from pg_stat_activity where state not like '%idle';

依据执行结果确认，活跃的连接数是否比平时多很多。

活动连接陡增的解决方案：

活动连接数陡增时，需要进一步确认陡增的原因。如：

1、数据库连接未使用连接池，造成的活动连接陡增，则需要将程序的DB连接改为连接池。

2、使用缓存中间件，如Redis，减少DB的访问操作。