Le 19/06/2015 01:07, Jeff Janes a écrit : > On Thu, Jun 18, 2015 at 3:05 PM, Oleg Serov <serovov@xxxxxxxxx > <mailto: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. > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
