Search Postgresql Archives

Re: how to measure wal_buffer usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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:
>> 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

Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:

[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux