Hello hackers,
In case of OLTP trafic it is hard to catch fast queries in logs (for example,
you want to know parameters for only few queries).
You have to put log_min_duration_statement to 0, do a reload, wait a few
seconds/minutes, back log_min_duration_statement to a previous value and reload
again.
In this time, you can cross your fingers impact will not be important and keep
an eye on log size.
I suggest to sample logs, like sample_rate for auto_explain [1]. Attached patch
introduce a new GUC, log_sample_rate, 1 means all queries will be logged (same
behavior as now).
Here is a naive SELECT only bench with a dataset which fit in ram (scale factor
= 100) and PGDATA and log on a ramdisk:
shared_buffers = 4GB
seq_page_cost = random_page_cost = 1.0
logging_collector = on (no rotation)
pgbench -c 4 -S -T 60 bench
master :
log_min_duration_statement = 0
TPS: 22562
log size: 1353746 lines (172MB)
log_min_duration_statement = -1
TPS: 25654
log size: 0 lines
patched:
log_min_duration_statement = 0
log_sample_rate = 1
TPS: 22548
log size: 1352873 lines (171MB)
log_min_duration_statement = 0
log_sample_rate = 0.1
TPS: 24802
log size: 148709 lines (19MB)
log_min_duration_statement = 0
log_sample_rate = 0.01
TPS: 25245
log size: 15344 lines (2MB)
log_min_duration_statement = 0
log_sample_rate = 0
TPS: 25858
log size: 0 lines
log_min_duration_statement = -1
log_sample_rate = 1
TPS: 25599
log size: 0 lines
I don't know the cost of random() call?
With log_sample_rate = 0.01 we got 15K lines of logs and you are close to
log_min_duration_statement = -1. Difference between log_min_duration_statement =
0 and -1 is about 12% performance drop on my laptop.
I will update documentation and postgresql.conf.sample later.
Thanks,
1: https://www.postgresql.org/docs/current/static/auto-explain.html
--
Adrien NAYRAT
commit 01a45625b82af0c3201f944fe91163c590a23f12
Author: anayrat <adrien.nay...@anayrat.info>
Date: Wed May 30 20:27:54 2018 +0200
Add a new GUC to log a fraction of queries.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index f4133953be..65c3cfdb7d 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2115,7 +2115,8 @@ check_log_statement(List *stmt_list)
/*
* check_log_duration
- * Determine whether current command's duration should be logged
+ * Determine whether current command's duration should be logged.
+ * If log_sample_rate < 1.0, log only a fraction of the command.
*
* Returns:
* 0 if no logging is needed
@@ -2151,7 +2152,8 @@ check_log_duration(char *msec_str, bool was_logged)
exceeded = (log_min_duration_statement == 0 ||
(log_min_duration_statement > 0 &&
(secs > log_min_duration_statement / 1000 ||
- secs * 1000 + msecs >= log_min_duration_statement)));
+ secs * 1000 + msecs >= log_min_duration_statement))) &&
+ (random() < log_sample_rate * MAX_RANDOM_VALUE);
if (exceeded || log_duration)
{
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index ee1444c427..b219682b83 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -455,6 +455,7 @@ int log_min_messages = WARNING;
int client_min_messages = NOTICE;
int log_min_duration_statement = -1;
int log_temp_files = -1;
+double log_sample_rate = 1.0;
int trace_recovery_messages = LOG;
int temp_file_limit = -1;
@@ -3257,6 +3258,17 @@ static struct config_real ConfigureNamesReal[] =
NULL, NULL, NULL
},
+ {
+ {"log_sample_rate", PGC_SUSET, LOGGING_WHEN,
+ gettext_noop("Fraction of statements to log."),
+ gettext_noop("1.0 log all statements."),
+ NULL
+ },
+ &log_sample_rate,
+ 1.0, 0.0, 1.0,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0.0, 0.0, 0.0, NULL, NULL, NULL
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 3d13a33b94..4480756954 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -252,6 +252,7 @@ extern PGDLLIMPORT int log_min_messages;
extern PGDLLIMPORT int client_min_messages;
extern int log_min_duration_statement;
extern int log_temp_files;
+extern double log_sample_rate;
extern int temp_file_limit;
