Re: proposal - log_full_scan

so 17. 4. 2021 v 20:51 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> so 17. 4. 2021 v 20:36 odesílatel Justin Pryzby <pryzby(at)telsasoft(dot)com>
> napsal:
>
>> On Sat, Apr 17, 2021 at 05:22:59PM +0200, Pavel Stehule wrote:
>> > so 17. 4. 2021 v 17:09 odesílatel Justin Pryzby <pryzby(at)telsasoft(dot)com>
>> napsal:
>> >
>> > > On Sat, Apr 17, 2021 at 04:36:52PM +0200, Pavel Stehule wrote:
>> > > > today I worked on postgres's server used for critical service.
>> Because the
>> > > > application is very specific, we had to do final tuning on
>> production
>> > > > server. I fix lot of queries, but I am not able to detect fast
>> queries that
>> > > > does full scan of middle size tables - to 1M rows. Surely I
>> wouldn't log
>> > > > all queries. Now, there are these queries with freq 10 per sec.
>> > > >
>> > > > Can be nice to have a possibility to set a log of queries that do
>> full
>> > > > scan and read more tuples than is specified limit or that does full
>> scan of
>> > > > specified tables.
>> > > >
>> > > > What do you think about the proposed feature?
>> > >
>> > > Are you able to use auto_explain with auto_explain.log_min_duration ?
>> >
>> > Unfortunately, I cannot use it. This server executes 5K queries per
>> > seconds, and I am afraid to decrease log_min_duration.
>> >
>> > The logs are forwarded to the network and last time, when users played
>> with
>> > it, then they had problems with the network.
>> ..
>> > The fullscan of this table needs about 30ms and has 200K rows. So
>> > decreasing log_min_duration to this value is very risky.
>>
>> auto_explain.sample_rate should allow setting a sufficiently low value of
>> log_min_duration. It exists since v9.6.
>>
>>
> It cannot help - these queries are executed a few times per sec. In same
> time this server execute 500 - 1000 other queries per sec
>

maybe this new option for server and for auto_explain can be just simple

log_seqscan = (minimum number of tuples from one relation)
auto_explain.log_seqscan = (minimum number of tuples from one relation)

This is a similar feature like log_temp_files. Next step can be
implementing this feature like a table option.

What do you think about it?

Regards

Pavel

The extension like pg_qualstat is good, but it does different work. In
complex applications I need to detect buggy (forgotten) queries - last week
I found two queries over bigger tables without predicates. So the qualstat
doesn't help me. This is an application for a government with few (but for
government typical) specific: 1) the life cycle is short (one month), 2)
there is not slow start - from first moment the application will be used by
more hundred thousands people, 3) the application is very public - so any
issues are very interesting for press and very unpleasant for politics, and
in next step for all suppliers (there are high penalty for failures), and
an admins are not happy from external extensions, 4) the budget is not too
big - there is not any performance testing environment

First stages are covered well today. We can log and process very slow
queries, and fix it immediately - with CREATE INDEX CONCURRENTLY I can do
it well on production servers too without high risk.

But the detection of some bad not too slow queries is hard. And as an
external consultant I am not able to install any external extensions to the
production environment for fixing some hot issues, The risk is not
acceptable for project managers and I understand. So I have to use only
tools available in Postgres.

> Regards
>
> Pavel
>
>
>> --
>> Justin
>>
>