Thread: Tracking IO Queries

On Wed, Jul 13, 2016 at 3:31 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
> Hi all,
>
> I got some IO spikes on my master server.

How is that diagnosed?  Is it read or write or can't you tell?

> But the point is that I was unable
> to find the query that caused that, because the query didn't take more than
> 300ms to run (300ms is the time that my alerts are settled)...

It might not be a query at all.  It could be checkpoints or some other
background task.

> Is there any way to track those queries? Maybe with pg_stat_statement?

You could turn on track_io_timing, and then look at the blk_read_time
and blk_write_time in pg_stat_statements.  But, you will have to
either reset the statements frequently, or save snapshots of it
frequently and then diff them.  Because otherwise the spikes will be
averaged out into the background.

Or you could lower log_min_duration_statement to something less than 300ms.

I'd also turn on log_checkpoints.

Cheers,

Jeff