On 05/31/2018 03:22 PM, Michael Paquier wrote: > On Thu, May 31, 2018 at 02:37:07PM +0200, Adrien Nayrat wrote: >> On 05/31/2018 03:34 AM, David Rowley wrote: >>> On 31 May 2018 at 06:44, Adrien Nayrat <adrien.nay...@anayrat.info> wrote: >>>> 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) >>> >>> It would be better to just: SELECT 1; to try to get the true overhead >>> of the additional logging code. >> >> Right, I wonder why I didn't have the idea :) > > Using prepared statements help also in reducing the load with > unnecessary planning. > -- > Michael >
Thanks, but the winner is Julien Rouhaud with only ";", I reach 115K TPS on my laptop :) Attached third version of the patch with documentation. -- Adrien NAYRAT
commit 26e30f7506ccdbca6a2d5c2d4942bd9985c3c9bc
Author: anayrat <adrien.nay...@anayrat.info>
Date: Wed May 30 20:27:54 2018 +0200
Add a new GUC, log_sample_rate, to log a fraction of queries.
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7bfbc87109..e4f7cb9c09 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5190,6 +5190,25 @@ local0.* /var/log/postgresql
</listitem>
</varlistentry>
+ <varlistentry id="guc-log-sample-rate" xreflabel="log_sample_rate">
+ <term><varname>log_sample_rate</varname> (<type>real</type>)
+ <indexterm>
+ <primary><varname>log_sample_rate</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Causes logging a fraction of the statements when log_min_duration_statement
+ is used. The default is 1, meaning log all statements longer than
+ log_min_duration_statement. Setting this to zero disable logging, same
+ as setting log_min_duration_statement to Minus-one.
+ Using log_sample_rate less than 1 can be helpful to log a sample of short
+ queries when the traffic is to important to log all queries (for example,
+ if you want queries parameters).
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
<para>
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index f4133953be..0197161ad1 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 sample.
*
* Returns:
* 0 if no logging is needed
@@ -2147,11 +2148,16 @@ check_log_duration(char *msec_str, bool was_logged)
* This odd-looking test for log_min_duration_statement being exceeded
* is designed to avoid integer overflow with very long durations:
* don't compute secs * 1000 until we've verified it will fit in int.
+ * Do not log if log_sample_rate = 0.
+ * Log a sample if log_sample_rate <= 1 and avoid unecessary random()
+ * call if log_sample_rate = 1.
*/
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))) &&
+ log_sample_rate != 0 && (log_sample_rate == 1 ||
+ 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 859ef931e7..c1ff5e71c8 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/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 9e39baf466..3f9a016003 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -447,6 +447,8 @@
# statements running at least this number
# of milliseconds
+#log_sample_rate = 1 # Fraction of logged statements. 1 means log all
+ # statements.
# - What to Log -
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index f462eabe59..f50a0d1f1b 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;
signature.asc
Description: OpenPGP digital signature
