Re: Is there any way to measure disk activity for each query? - Mailing list pgsql-general

From Julien Rouhaud
Subject Re: Is there any way to measure disk activity for each query?
Date
Msg-id 558478CC.8080006@dalibo.com
Whole thread Raw
In response to Re: Is there any way to measure disk activity for each query?  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Postgres SIGALRM timer
Next
From: Adrian Klaver
Date:
Subject: Re: Postgresql 9.2 has standby server lost data?