Re: proposal - log_full_scan - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: proposal - log_full_scan |
| Date | |
| Msg-id | CAFj8pRDby=rRZksJd3+piveLhjrGY+PH=9DmRnwHN-eGu5PB=A@mail.gmail.com Whole thread Raw |
| In response to | Re: proposal - log_full_scan (Pavel Stehule <pavel.stehule@gmail.com>) |
| Responses |
Re: proposal - log_full_scan
(Julien Rouhaud <rjuju123@gmail.com>)
|
| List | pgsql-hackers |
so 17. 4. 2021 v 20:51 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
so 17. 4. 2021 v 20:36 odesílatel Justin Pryzby <pryzby@telsasoft.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@telsasoft.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.
RegardsPavel--
Justin
pgsql-hackers by date: