Q:在PostgreSQL中跟踪和分析查询日志

在PostgreSQL中，跟踪和分析查询日志是排查性能瓶颈的重要步骤。

通过查看和分析查询日志，我们可以了解哪些查询在执行时遇到了问题，例如执行时间过长、资源消耗过大等。

以下是一些建议和步骤，帮助你有效地跟踪和分析PostgreSQL的查询日志。

 

启用查询日志
首先，你需要启用查询日志功能。这可以通过修改PostgreSQL的配置文件（通常是postgresql.conf）来实现。在配置文件中，找到以下两个参数并进行设置：

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M.log'
log_statement = 'all' # 可以设置为 'none', 'ddl', 'mod', 'all' 来控制记录的日志级别

将logging_collector设置为on以启用日志收集器，并设置日志的目录和文件名格式。log_statement参数决定了要记录哪些类型的SQL语句，通常设置为'all'以记录所有语句，但你也可以根据需要设置为其他级别。

其他几个重要参数说明：

log_rotation_age = 1440    #minute,多长时间创建新的文件记录日志。0 表示禁扩展。
log_rotation_size = 10240    #kb,文件多大后创建新的文件记录日志。0 表示禁扩展。
log_truncate_on_rotation = on #可重用同名日志文件

修改配置文件后，你需要重启PostgreSQL服务以使更改生效。

 

分析查询日志
一旦你开始收集查询日志，你就可以开始分析这些日志以查找性能问题。以下是一些建议：

1. 查找执行时间长的查询
通过查看日志中每个查询的执行时间，你可以找到执行时间较长的查询。这些查询可能是性能瓶颈的主要来源。你可以使用文本编辑器或命令行工具（如grep和awk）来搜索和排序这些查询。

例如，使用grep和awk来提取执行时间超过1秒的查询：

先切换到对应目录 

cd $PGDATA/pg_log
grep 'duration:' postgresql-*.log | awk '{ if ($4 > 1000) print }'

这个命令会输出所有执行时间超过1秒的查询及其相关信息，包括查询文本和执行时间。你可以根据这些信息进一步分析并优化这些查询。

2. 分析资源消耗
除了执行时间，你还可以查看查询对系统资源的消耗情况，如磁盘I/O、CPU使用率和内存使用情况。这有助于你了解查询是否因为资源争用而变慢。

3. 优化查询
一旦你找到了性能瓶颈的查询，你就可以开始优化这些查询。这可能包括：

• 优化查询语句，如使用更高效的JOIN方式、减少不必要的子查询等。
• 创建或调整索引，以加速查询。
• 调整数据库配置参数，以优化性能。

 

跟踪SQL慢语句

postgresql.conf需要设置以下参数：

log_statement = all    #需设置跟踪所有语句，否则只能跟踪出错信息
log_min_duration_statement = 5000    #milliseconds,记录执行5秒及以上的语句

注：当 log_statement=all 和 log_min_duration_statement 同时设置时，将跟踪所有语句，忽略log_min_duration_statement 设置。所以需按情况设置其中一个或两个值。

数据库执行加载配置

select pg_reload_conf();
show log_min_duration_statement;

针对某个用户或者某数据库（test）进行设置

 alter database test set log_min_duration_statement=5000; 

捕获正在查询的慢SQL

--执行时间超过 10s 的语句
select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '5 s'
--锁表
SELECT * FROM pg_stat_activity where datname='bms' and wait_event_type='Lock'--limit 10