[PERFORM] Identifying the nature of blocking I/O

[for the purpose of this post, 'blocking' refers to an I/O operation
taking a long time for reasons other than the amount of work the I/O
operation itself actually implies; not to use of blocking I/O calls or
anything like that]

Hello,

I have a situation in which deterministic latency is a lot more
important than throughput.

I realize this is a hugely complex topic and that there is inteaction
between many different things (pg buffer cache, os buffer cache, raid
controller caching, wal buffers, storage layout, etc). I already know
several things I definitely want to do to improve things.

But in general, it would be very interesting to see, at any given
moment, what PostgreSQL backends are actually blocking on from the
perspective of PostgreSQL.

So for example, if I have 30 COMMIT:s that are active, to know whether
it is simply waiting on the WAL fsync or actually waiting on a data
fsync because a checkpoint is being created. or similarly, for
non-commits whether they are blocking because WAL buffers is full and
writing them out is blocking, etc.

This would make it easier to observe and draw conclusions when
tweaking different things in pg/the os/the raid controller.

Is there currently a way of dumping such information? I.e., asking PG
"what are backends waiting on right now?".

--
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org