Waiting for PostgreSQL 13 – Allow sampling of statements depending on duration

On 6th of November 2019, Tomas Vondra committed patch:

Allow sampling of statements depending on duration
 
This allows logging a sample of statements, without incurring excessive
log traffic (which may impact performance).  This can be useful when
analyzing workloads with lots of short queries.
 
The sampling is configured using two new GUC parameters:
 
 * log_min_duration_sample - minimum required statement duration
 
 * log_statement_sample_rate - sample rate (0.0 - 1.0)
 
Only statements with duration exceeding log_min_duration_sample are
considered for sampling. To enable sampling, both those GUCs have to
be set correctly.
 
The existing log_min_duration_statement GUC has a higher priority, i.e.
statements with duration exceeding log_min_duration_statement will be
always logged, irrespectedly of how the sampling is configured. This
means only configurations
 
  log_min_duration_sample < log_min_duration_statement
 
do actually sample the statements, instead of logging everything.
 
Author: Adrien Nayrat
 
Discussion: https://postgr.es/m/-a8f7-3be2-155a-@anayrat.info

Description is pretty obvious, but just in case.

We have now quite a lot of various settings to log queries:

There was also committed, but later reverted log_statement_sample_rate.

This new patch, adds log_min_duration_sample and re-introduces log_statement_sample_rate.

Basically we set two new gucs like this:

log_min_duration_sample = 100
log_statement_sample_rate = 0.5

Which would mean, that Pg will log 50% (0.5) of all queries above 100ms.

Of course this can be used together with log_min_duration_statement, where we could have something like:

log_min_duration_sample = 100
log_statement_sample_rate = 0.1
log_min_duration_statement = 500

Where we log 10% of queries over 100ms, and all of queries above 500 ms.

Pretty cool. Thanks to all involved.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.