PGSQL执行突然变慢的排查和处理

作者：Digital Observer（施嘉伟）
Oracle ACE Pro: Database
PostgreSQL ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师

排查步骤和方法：

1. 监视系统资源

使用操作系统工具监视系统资源，如 top 或 htop。

2. 检查执行计划

使用 EXPLAIN 或 EXPLAIN ANALYZE 查看查询计划。

EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_condition;

3. 统计信息更新

查看某张表的统计信息收集状态，如果近期没有手动或自动收集统计信息，则手动收集更新统计信息。

select relname,last_analyze,last_autoanalyze from pg_stat_all_tables where relname = 'your_table';

手动更新表的统计信息。

ANALYZE your_table;

4. 表碎片整理

执行 VACUUM 操作。

VACUUM your_table;

5. 检查索引

查看表的索引使用情况。

SELECT * FROM pg_stat_all_indexes WHERE tablename = 'your_table';

#通过查看pg_stat_all_indexes系统视图中的idx_scan来查看索引的使用情况，其代表的是在这个表上发起的索引扫描的次数，例如：
SELECT * FROM pg_stat_all_indexes WHERE relname = 'test';
 relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch 
-------+------------+------------+---------+--------------+----------+--------------+---------------
 66827 |      75596 | public     | test    | test_id_idx  |       34 |          610 |           294

6. 检查并发连接

检查并发连接数，看一下是不是有太多的并发。

#统计所有连接数
SELECT count(*) FROM pg_stat_activity;

#统计活跃连接数
SELECT count(*) FROM pg_stat_activity where state = 'active';

7. 数据库配置

检查 PostgreSQL 配置文件。

cat /path/to/postgresql.conf

8. 调整优化器选项

如果一系列手段都无法满足执行计划走更优的索引扫描，可以在事务中选择关闭优化器的全表扫描选项。

begin;
SET enable_seqscan = off;
select xxx from xxx where xxx = xxx;
end;

9. 日志和错误检查

查看 PostgreSQL 的日志文件,查看有没有连接超时或者失败等情况。

tail -n 100 $PGDATA/log/postgresql-Tue.csv

10. 数据库健康检查

使用 pg_stat_statements、pg_stat_activity、pg_locks 等监控工具。

11. 版本升级

考虑将 PostgreSQL 升级到最新版本。

模拟案例：

假设有一个表 employees，我们执行查询时突然变慢：

-- 步骤1：检查执行计划
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

-- 步骤2：统计信息更新
ANALYZE employees;

-- 步骤3：检查索引
SELECT * FROM pg_stat_user_indexes WHERE tablename = 'employees';

-- 步骤4：监视系统资源
-- 使用操作系统工具监视 CPU、内存、磁盘和网络使用情况

-- 步骤5：查询缓存
SET enable_seqscan = off;

-- 步骤6：检查并发连接
SELECT count(*) FROM pg_stat_activity;

-- 步骤7：日志和错误检查
-- 查看 PostgreSQL 的日志文件

-- 步骤8：表碎片整理
VACUUM employees;

-- 步骤9：版本升级
-- 考虑将 PostgreSQL 升级到最新版本

-- 步骤10：数据库配置
-- 检查 PostgreSQL 配置文件

-- 步骤11：数据库健康检查
-- 使用工具如 pg_stat_statements、pg_stat_activity、pg_locks 等监控数据库的运行状况

在执行上述步骤时，你可以观察输出和日志，找到潜在的性能问题，并根据具体情况采取进一步的优化措施。