Thread: how to measure wal_buffer usage
After reading this interesting article on shared_buffers and wal_buffers: http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html it got me wondering if my settings were ideal. Is there some way to measure wal_buffer usage in real time, so that I could simply monitor it for some period of time, and then come up with a way of determining if the current setting is sufficient? I tried googling, but every reference that I've found simply defaults to the "trial & error" approach to performance tuning.
Lonni J Friedman wrote: > After reading this interesting article on shared_buffers and wal_buffers: > http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html > > it got me wondering if my settings were ideal. Is there some way to > measure wal_buffer usage in real time, so that I could simply monitor > it for some period of time, and then come up with a way of determining > if the current setting is sufficient? > > I tried googling, but every reference that I've found simply defaults > to the "trial & error" approach to performance tuning. You can use the contrib module pg_buffercache to inspect the shared buffers. If almost all your shared buffers have high use count (4 or 5), shared_buffers may be too small. If not, consider reducing shared_buffers. It's probably better to start with a moderate value and tune upwards. You can also look at pg_statio_all_tables and pg_statio_all_indexes and calculate the buffer hit ratio. If that is low, that's also an indication that shared_buffers is too small. You should distinguish between tables and indexes: it is usually more important that indexes are cached. Try to observe these things over time, for example by taking snapshots every n minutes and storing the results in a table. Yours, Laurenz Albe
On Fri, Mar 16, 2012 at 2:45 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Lonni J Friedman wrote: >> After reading this interesting article on shared_buffers and wal_buffers: >> http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html >> >> it got me wondering if my settings were ideal. Is there some way to >> measure wal_buffer usage in real time, so that I could simply monitor >> it for some period of time, and then come up with a way of determining >> if the current setting is sufficient? >> >> I tried googling, but every reference that I've found simply defaults >> to the "trial & error" approach to performance tuning. > > You can use the contrib module pg_buffercache to inspect the shared buffers. > If almost all your shared buffers have high use count (4 or 5), > shared_buffers may be too small. If not, consider reducing shared_buffers. pg_buffercache only reports on the buffer_cache, it does not report any data on the wal_cache. > > It's probably better to start with a moderate value and tune upwards. > > You can also look at pg_statio_all_tables and pg_statio_all_indexes and > calculate the buffer hit ratio. If that is low, that's also an indication > that shared_buffers is too small. Isn't this also specific to the buffer_cache rather than the wal_cache? > > You should distinguish between tables and indexes: > it is usually more important that indexes are cached. > > Try to observe these things over time, for example by taking > snapshots every n minutes and storing the results in a table. > > Yours, > Laurenz Albe -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand https://netllama.linux-sxs.org
Lonni J Friedman wrote: >>> After reading this interesting article on shared_buffers and wal_buffers: >>> http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html >>> >>> it got me wondering if my settings were ideal. Is there some way to >>> measure wal_buffer usage in real time, so that I could simply monitor >>> it for some period of time, and then come up with a way of determining >>> if the current setting is sufficient? >>> >>> I tried googling, but every reference that I've found simply defaults >>> to the "trial & error" approach to performance tuning. >> >> You can use the contrib module pg_buffercache to inspect the shared buffers. >> If almost all your shared buffers have high use count (4 or 5), >> shared_buffers may be too small. If not, consider reducing shared_buffers. > > pg_buffercache only reports on the buffer_cache, it does not report > any data on the wal_cache. You are right. >> It's probably better to start with a moderate value and tune upwards. >> >> You can also look at pg_statio_all_tables and pg_statio_all_indexes and >> calculate the buffer hit ratio. If that is low, that's also an indication >> that shared_buffers is too small. > > Isn't this also specific to the buffer_cache rather than the wal_cache? Correct. I don't know how to inspect usage WAL cache usage. Yours, Laurenz Albe