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资源压力。
参考
原文链接: https://postgres.fun/20190717141000.html
新书推荐
最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!