"PostgreSQL 12: 新增 log_statement_sample_rate 参数控制数据库日志中慢SQL百分比"

2022-04-05 21:41:22

PostgreSQL 提供的 log_min_duration_statement 参数设置后，数据库中执行时间超出设置值的SQL将记录到数据库中，此参数对所有库所有SQL都有效。维护PostgreSQL生产库时，数据库日志出现高频慢SQL实属正常，若其中一条比较繁忙的SQL若执行时间超过 log_min_duration_statement 设置值，那么数据库日志中将存在大量此条SQL的日志，这个日志量是很惊人的，多则一天上百GB。

PostgreSQL 12 提供了一个新的参数，能够有效的控制超出 log_min_duration_statement 参数设置值的SQL的日志量，这个参数为 log_statement_sample_rate，本文简单测试下。

发行说明

Allow logging of only a percentage of statements and transactions meeting log_min_duration_statement criteria (Adrien Nayrat)

The parameters log_statement_sample_rate and log_transaction_sample_rate control this.

本文后续仅介绍 log_statement_sample_rate， log_transaction_sample_rate 参数使用场景不是很多，不做介绍。

关于 log_statement_sample_rate

Determines the fraction of statements that exceed log_min_duration_statement to be logged. The default is 1.0, meaning log all such statements. Setting this to zero disables logging by duration, same as setting log_min_duration_statement to -1. log_statement_sample_rate is helpful when the traffic is too high to log all queries.

备注: 参数 log_statement_sample_rate 控制执行时间超出 log_min_duration_statement 参数设置值的 SQL 写数据库日志的百分比，默认值 1 ，表示比例为 100%，0 表示不记录，对于比较繁忙的生产库，此参数能有效缓解数据库日志量，减少无效日志。

环境准备

计划使用 pgbench 对数据库进行压力测试，执行大量SQL。

创建测试表并插入 100 万数据，如下:

CREATE TABLE log_sample(id int8,name text,creat_time timestamp(0) without time zone default clock_timestamp());    
INSERT INTO  log_sample(id,name)  select n,n||'_test'  from generate_series(1,1000000) n;  
ALTER TABLE log_sample ADD PRIMARY KEY (id);  
VACUUM ANALYZE log_sample;

编写脚本 select_id.sql ，如下:

\set v_id random(1,1000000)

select name from log_sample where id=:v_id;

场景一: log_statement_sample_rate = 1.0

postgresql.conf 设置 log_statement_sample_rate 值 1.0，log_min_duration_statement 设置为 0，并执行 pg_ctl reload 使参数生效，如下:

log_statement_sample_rate = 1.0
log_min_duration_statement = 0

log_min_duration_statement 设置为 0 表示记录执行时间超出 0 毫秒的SQL都记录，方便测试。

清空 $PGDATA/pg_log 目录下的数据库日志，之后执行 pgbench，如下:

[pg12@pghost1 pgbench]$ pgbench -n -M prepared -c 2 -j 2 -T 60 -U pguser mydb -f select_id.sql
transaction type: select_id.sql
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 60 s
number of transactions actually processed: 498949
latency average = 0.241 ms
tps = 8315.763633 (including connections establishing)
tps = 8316.384134 (excluding connections establishing)

查看 pg_log 目录的数据库日志量为 242 MB 左右，如下:

[pg12@pghost1 pgbench]$ du -sm $PGDATA/pg_log
242     /pgdata/pg12/pg_root/pg_log

场景二: log_statement_sample_rate = 0.2

postgresql.conf 设置 log_statement_sample_rate 值 0.2，log_min_duration_statement 设置为 0，并执行 pg_ctl reload 使参数生效，如下:

log_statement_sample_rate = 0.2
log_min_duration_statement = 0

清空 $PGDATA/pg_log 目录下的数据库日志，之后执行 pgbench，如下:

[pg12@pghost1 pgbench]$ pgbench -n -M prepared -c 2 -j 2 -T 60 -U pguser mydb -f select_id.sql
transaction type: select_id.sql
scaling factor: 1
query mode: prepared
number of clients: 2
number of threads: 2
duration: 60 s
number of transactions actually processed: 640501
latency average = 0.187 ms
tps = 10674.933384 (including connections establishing)
tps = 10675.548084 (excluding connections establishing)

查看 pg_log 目录的数据库日志量为 62 MB 左右，如下:

[pg12@pghost1 pgbench]$ du -sm $PGDATA/pg_log
62      /pgdata/pg12/pg_root/pg_log

总结

测试场景二的日志量确实只有场景一的 25% 左右，大幅减少了无效日志，同时 tps 也有一小幅度提升，可能是因为少写了大量数据库日志，缓解了CPU、IO资源压力。

参考

Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

原文链接: https://postgres.fun/20190717141000.html

新书推荐

最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例!

码农公寓