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

Le 19/06/2015 01:07, Jeff Janes a écrit :
> On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov <serovov(at)gmail(dot)com
> <mailto:serovov(at)gmail(dot)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