Postgres 16: Buffer cache hit ratio and I/O times in pg_stat_io
This is E64 of “5mins of Postgres”. Today, we talk about two improvements to the new pg_stat_io view in Postgres 16: tracking shared buffer hits and tracking I/O time.
Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel.
Transcript
Let's jump in!
Tracking shared buffer hits and I/O times in pg_stat_io
We previously spoke about pg_stat_io in a prior 5mins of Postgres episode, as well as this blog post. Just for quick reference, pg_stat_io lets you track all the I/O activity on your database in one aggregate view in a way that wasn't really possible before Postgres 16.
Shortly before the feature freeze for this new Postgres release, two new exciting additions got added to pg_stat_io.
Track shared buffer hits in pg_stat_io
The first addition is the ability to track shared buffer hits in pg_stat_io. This was authored by Melanie Plageman and committed by Andres Freund. What this does is that it tracks the shared buffer hits in pg_stat_io so that you can calculate a useful cache hit ratio for your whole database server. The way it enables this is by letting you count the hits on the database level whilst filtering out reads that are done by buffer access strategies.
The motivation here is that oftentimes people recommend looking at shared cache hit ratio as a way of saying “is your Postgres database still reading from memory most of the time, are things in cache most of the time, or do you need to, for example, have more hardware or optimize your database?”
The way that these recommendations are made today is that they're looking, for example, at pg_stat_database. They're looking at the blocks read and the blocks hit counters.
They essentially represent how many blocks were read into shared buffers and how many blocks were already existing in shared buffers when you were trying to read something, and so the disk read was skipped because that was already in cache. The problem with this methodology of counting this as cache hit ratio is that it neglects that there are certain operations in Postgres that do not use the full amount of shared buffers. Most importantly, for example if you have a large sequential scan or if you have VACUUM running, those use what are called buffer access strategies. In the new pg_stat_io view, there is a way to track these separately.
Normal I/O operations
There is the concept of a ”normal" I/O operation, this is if you just have, let's say, a query doing an index scan, loading a couple of blocks for that. You have "vacuum", which is one of the bulk access strategies that is used by autovacuum processes for example. And there is bulk read and bulk write. Bulk read, for example, could be a sequential scan over a large table.
Previously, what would happen is that if you counted the hits and the reads. You would actually see a higher amount of reads because of such bulk reads, because they could never keep the whole table in the shared buffer cache. That would lead to cache hit ratio numbers being incorrect and to feel like you're not having things in cache, and even if you increased your shared buffers by a lot, it wouldn't have improved your shared cache hit ratio.
With this new addition, you can say that the I/O context is "normal", and then you can look at this new hits counter, which is in pg_stat_io. That way we can add up the reads, add up the hits and then say how many hits divided by that sum for the "normal" I/O context. This leads to us having an actual, correct cache hit ratio for our database server.
Track I/O times in pg_stat_io
The second addition that was made to pg_stat_io, also by Melanie Plageman, is to track I/O times in pg_stat_io. This is really exciting, because it adds a whole other dimension to this counting of I/O activity. Previously, pg_stat_io was just counting individual operations, but you didn't really know how long that operation was taking.
Previously, pg_stat_io was just counting individual operations, but you didn't really know how long that operation was taking.
That is a problem, because behind the scenes there is an operating system page cache that can influence whether things are in cache and also how I/O is batched together. One I/O is not the same as another I/O. One might actually be really slow, the other one might be really fast.
One I/O is not the same as another I/O.
What matters is when you see a spike in I/O activity, was it also a spike in I/O time? And so with this new addition of the read time or the write time columns you can now see how much time the system actually spent reading data or writing data, and through that have a closer correlation with the actual underlying I/O activity.
All in all, two more reasons why pg_stat_io is going to be an amazing addition to Postgres 16. I look forward to using it on production databases. And we'll see what else future Postgres releases bring to this new view.
Thanks for joining us for episode 64 of 5mins of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!