From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Lonni J Friedman *EXTERN*" <netllama(at)gmail(dot)com>, "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: how to measure wal_buffer usage |
Date: | 2012-03-16 09:45:29 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C2079CF26F@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | aspenbr | 2012-03-16 11:21:59 | Re: Problem for restoure data base Postgre |
Previous Message | Simon Riggs | 2012-03-16 08:26:19 | Re: Optimise PostgreSQL for fast testing |