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

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

Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

On 29th of November 2018, Alvaro Herrera committed patch:

Add log_statement_sample_rate parameter
 
This allows to set a lower log_min_duration_statement value without
incurring excessive log traffic (which reduces performance).  This can
be useful to analyze workloads with lots of short queries.
 
Author: Adrien Nayrat
 
Discussion: https://postgr.es/m/-ee1e-db9f-fa97-@anayrat.info

Continue reading Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

What logging has least overhead?

When working with PostgreSQL you generally want to get information about slow queries. The usual approach is to set log_min_duration_statement to some low(ish) value, run your app, and then analyze logs.

But you can log to many places – flat file, flat file on another disk, local syslog, remote syslog. And – perhaps, instead of log_min_duration_statement – just use pg_stat_statements?

Well, I wondered about it, and decided to test.

Continue reading What logging has least overhead?

Understanding postgresql.conf : log*

After loooong pause, adding next (well, second) post to the “series“. This time, I'd like to describe how logging works. And I don't mean binary logging (WAL), but the log for us, humans, to read.

Before I will go to the postgresql.conf options, let me first describe what happens when PostgreSQL wants to log something.

Continue reading Understanding postgresql.conf : log*

Logging queries – how?

One of the questions that pop up frequently on IRC is how to see queries are now executed on the server, and what queries were earlier.

Theoretically answer to this is simple – pg_stat_activity and log_min_duration_statement. Or log_statement. What is the difference? That's exactly why I'm writing this post.

Continue reading Logging queries – how?