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

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。

使用 pgbench 初步化数据,如下:

[pg12@pghost1 pg_root]$ pgbench -i -U pguser mydb
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.22 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

场景一: 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 pg_root]$ pgbench -n -c 2 -j 2 -T 60 -U pguser mydb
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 2
duration: 60 s
number of transactions actually processed: 65975
latency average = 1.819 ms
tps = 1099.549756 (including connections establishing)
tps = 1099.669986 (excluding connections establishing)

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

[pg12@pghost1 pg12]$ du -sm $PGDATA/pg_log
106     /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 pg_log]$ pgbench -n -c 2 -j 2 -T 60 -U pguser mydb
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 2
duration: 60 s
number of transactions actually processed: 72793
latency average = 1.649 ms
tps = 1213.183848 (including connections establishing)
tps = 1213.295352 (excluding connections establishing)

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

[pg12@pghost1 pg_log]$ du -sm $PGDATA/pg_log
18      /pgdata/pg12/pg_root/pg_log
我来评几句
登录后评论

已发表评论数()

相关站点

热门文章