New GUC to sample log queries

Поиск
Список
Период
Сортировка
От Adrien Nayrat
Тема New GUC to sample log queries
Дата
Msg-id c30ee535-ee1e-db9f-fa97-146b9f62caed@anayrat.info
обсуждение исходный текст
Ответы Re: New GUC to sample log queries  (David Rowley <david.rowley@2ndquadrant.com>)
Список 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


Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: behave of --create-slot option
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode