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

On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov <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.

Cheers,

Jeff