[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@xxxxxxxxxxxx>' Key retrieval: Send an E-Mail to getpgpkey@xxxxxxxxx E-Mail: peter.schuller@xxxxxxxxxxxx Web: http://www.scode.org
Attachment:
pgpy1q0Lprngb.pgp
Description: PGP signature
