Обсуждение: Is there any way to measure disk activity for each query?

Поиск
Список
Период
Сортировка

Is there any way to measure disk activity for each query?

От
Oleg Serov
Дата:
Hello!

I'm wondering, if there any way to measure how much disk-io were generated by a query?



--
Best Regards,
Oleg

Re: Is there any way to measure disk activity for each query?

От
Jeff Janes
Дата:
On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov <serovov@gmail.com> wrote:
Hello!

I'm wondering, if there any way to measure how much disk-io were generated by a query?

For an individual query execution, you can explain it with 

explain (analyze, buffers) select .....

It will report on the pages hit in the buffer cache versus the pages read.  However, for pages which were hit in the OS filesystem cache, those will be reported as if they were read from disk.  There is no way (that I know of) to distinguish at the statement level true disk io from OS caching.  The best way may be to turn track_io_timing on, then you can see how much time it spent waiting on pages.  If not much time was spent, then it must be coming from the OS cache.

If you enable pg_stat_statements extension, you can get the same data summed over all natural calls of the same query string.  'Natural' meaning executions from applications, not just queries manually decorated with 'explain (analyze,buffers)'.  This too is best used in conjunction with track_io_timing.

I've been thinking about making individual statements which exceed log_min_duration_statement log their track_io_timing numbers and their rusage numbers into the server log, rather than just their wall-time durations as it does now.  I'm not sure how that idea is going to work out yet, though.  Anyway, it wouldn't be until version 9.6 at minimum.

Also, for temp file, see log_temp_files config parameter.

Cheers,

Jeff

Re: Is there any way to measure disk activity for each query?

От
Julien Rouhaud
Дата:
Le 19/06/2015 01:07, Jeff Janes a écrit :
> On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov <serovov@gmail.com
> <mailto:serovov@gmail.com>> wrote:
>
>     Hello!
>
>     I'm wondering, if there any way to measure how much disk-io were
>     generated by a query?
>
>
> For an individual query execution, you can explain it with
>
> explain (analyze, buffers) select .....
>
> It will report on the pages hit in the buffer cache versus the pages
> read.  However, for pages which were hit in the OS filesystem cache,
> those will be reported as if they were read from disk.  There is no way
> (that I know of) to distinguish at the statement level true disk io from
> OS caching.  The best way may be to turn track_io_timing on, then you
> can see how much time it spent waiting on pages.  If not much time was
> spent, then it must be coming from the OS cache.
>
> If you enable pg_stat_statements extension, you can get the same data
> summed over all natural calls of the same query string.  'Natural'
> meaning executions from applications, not just queries manually
> decorated with 'explain (analyze,buffers)'.  This too is best used in
> conjunction with track_io_timing.
>
> I've been thinking about making individual statements which
> exceed log_min_duration_statement log their track_io_timing numbers and
> their rusage numbers into the server log, rather than just their
> wall-time durations as it does now.  I'm not sure how that idea is going
> to work out yet, though.  Anyway, it wouldn't be until version 9.6 at
> minimum.
>
> Also, for temp file, see log_temp_files config parameter.
>

Also, if you need current disk activity for a query, you can use tools
like pg_activity of pg_view to monitor it.

And if you are using postgres 9.4 or more, you can also use
pg_stat_statement and pg_stat_kcache extensions to get actual disk reads
and writes for all normalized queries.

Regards.

> Cheers,
>
> Jeff


--
Julien Rouhaud
http://dalibo.com - http://dalibo.org