On Fri, Mar 16, 2012 at 2:45 AM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> 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@xxxxxxxxx LlamaLand https://netllama.linux-sxs.org -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
