Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux