New GUC to sample log queries - Mailing list pgsql-hackers

From Adrien Nayrat
Subject New GUC to sample log queries
Date
Msg-id c30ee535-ee1e-db9f-fa97-146b9f62caed@anayrat.info
Whole thread Raw
Responses Re: New GUC to sample log queries  (David Rowley <david.rowley@2ndquadrant.com>)
List pgsql-hackers
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


Attachment

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: behave of --create-slot option
Next
From: Alvaro Herrera
Date:
Subject: Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode