PostgreSQL性能监控与故障排查实用指南

### PostgreSQL性能监控与故障排查实用指南 #### 1. 查看pg_stat_statements视图 在高可用性且处理大量查询流量的数据库中，记录每个查询并非理想做法。数据库管理员（DBAs）通常通过在`postgresql.conf`文件中设置`log_min_duration_statement`为合理的毫秒数，仅记录慢查询。然而，快速查询仍难以被察觉，频繁执行的查询可能将数据源缓存于内存中，虽不太可能产生I/O，但数据库可能每秒执行数千次低效或冗余查询，除了服务器负载升高外，我们难以察觉。不过，借助`pg_stat_statements`，我们可以解决这个问题。 ##### 1.1 准备工作 激活并使用此扩展需要修改`postgresql.conf`配置文件并重启PostgreSQL。需确保拥有PostgreSQL超级用户权限以及能重启服务的用户权限，如`postgres`或`root`系统用户。 ##### 1.2 操作步骤 - 安装`pg_stat_statements`模块： 1. 修改`postgresql.conf`中的`shared_preload_libraries`行，添加该模块： ```plaintext shared_preload_libraries = 'pg_stat_statements' ``` 2. 若使用PostgreSQL 9.1或更早版本，在`postgresql.conf`文件中添加： ```plaintext custom_variable_classes = 'pg_stat_statements' ``` 3. 使用类似以下命令重启PostgreSQL： ```plaintext pg_ctl -D /db/pgdata restart ``` 4. 以超级用户身份登录到任何需要访问`pg_stat_statements`的数据库，并执行以下SQL语句： ```sql CREATE EXTENSION pg_stat_statements; ``` - 为通用使用准备`pg_stat_statements`： 1. 以数据库超级用户身份执行以下SQL语句创建函数： ```sql CREATE OR REPLACE FUNCTION pg_stat_statements() RETURNS SETOF pg_stat_statements AS $$ SELECT * FROM pg_stat_statements; $$ LANGUAGE sql SECURITY DEFINER; ``` 2. 执行以下SQL语句保护创建的函数： ```sql REVOKE ALL ON FUNCTION pg_stat_statements() FROM PUBLIC; ``` 3. 使用以下SQL语句创建专门用于监控的用户： ```sql CREATE USER db_mon WITH PASSWORD 'somepass'; ``` 4. 使用以下SQL语句授予监控用户使用函数的权限： ```sql GRANT EXECUTE ON FUNCTION pg_stat_statements() TO db_mon; ``` - 以`db_mon`用户身份连接到PostgreSQL，并执行以下SQL语句查看`pg_stat_statements`的内容： ```sql SELECT * FROM pg_stat_statements(); ``` ##### 1.3 工作原理 首先，需将`pg_stat_statements`添加到`shared_preload_libraries`配置设置中，因为部分PostgreSQL模块需如此操作才能使用。若使用的是早于PostgreSQL 9.2的版本，`custom_variable_classes`设置可用于后续进一步配置该模块，而当前版本会自动处理。由于`pg_stat_statements`模块依赖激活外部库，所以重启PostgreSQL才能使其生效。加载模块后，需在需要使用该模块的数据库中安装访问它的必要函数，通过执行`CREATE EXTENSION`语句将这些函数注册到当前数据库。 后续步骤旨在让非超级用户也能使用`pg_stat_statements`模块。创建一个以定义者身份运行的函数，普通用户可借此查看`pg_stat_statements`的内容。为防止普通用户执行具有高权限的函数，撤销公共上下文的所有访问权限。若没有专门用于监控数据库活动的用户，则创建一个并授予其执行`pg_stat_statements()`的权限。 较新版本的PostgreSQL似乎每次发布都会为该视图添加更多字段，许多新字段专注于与磁盘计时和脏块相关的I/O，用于更高级的使用场景。常见的列如下： | 列名 | 描述 | | ------------ | -------------------------------------------------------------------- | | query | 显示被跟踪查询的前1024个字符 | | calls | 包含SQL语句的总执行次数 | | total_time | 提供处理查询所花费的总时间（以毫秒为单位） | | rows | 列出查询返回的总行数 | 通过这些列，我们可以进行诸多调查。例如，用`total_time`除以`calls`可得到平均执行速度；按`calls`列对数据排序可发现执行频率远高于大多数查询的异常值。 ##### 1.4 额外功能 - **重置统计信息**：`pg_stat_statements`视图中存储的统计信息会一直累积，直到被强制重置。若不想监控检查之间的值增量，可作为超级用户执行以下SQL语句重置模块状态并清除已收集的数据： ```sql SELECT pg_stat_statements_reset(); ``` - **捕获更多查询**：默认情况下，`pg_stat_statements`模块仅跟踪数据库操作期间遇到的前1000个查询。对于PostgreSQL 9.1以上版本通常足够，新版本在将查询包含到视图之前会移除SQL变量和常量，提供更好的聚合效果。但较旧版本或查询构造变化较大的数据库可能需要增加该数量。可在`postgresql.conf`文件中添加： ```plaintext pg_stat_statements.max = 10000 ``` 然后再次重启PostgreSQL，之后该模块将跟踪10000个查询，可根据情况尝试其他值。 #### 2. 使用strace进行调试 有时，借助内核本身是真正观察服务器进程的唯一方法，这种数据对于PostgreSQL活动的故障排查或研究非常有价值。Linux的`strace`实用工具可为服务器上运行的任何进程或服务提供详细的系统跟踪数据。用于PostgreSQL时，可针对数据库本身或其用于维护的任何后台进程，还能调试或检查任何客户端连接。 ##### 2.1 准备工作 使用`strace`存在一定限制，因它能高度访问进程信息，只有root级用户才能检查应用程序的活动，需确保具备此权限。同时，需打开一个PostgreSQL连接