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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

francs.tan 2019-08-12 08:39:20 浏览263
展开阅读全文

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等,涵盖大量实战用例!

_5_PostgreSQL_

网友评论

登录后评论
0/500
评论
francs.tan
+ 关注