"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。

建立測試表并插入 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           

[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等，涵蓋大量實戰用例!