Quantcast

how to measure wal_buffer usage

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

how to measure wal_buffer usage

netllama
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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: how to measure wal_buffer usage

Albe Laurenz *EXTERN*
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: how to measure wal_buffer usage

netllama
On Fri, Mar 16, 2012 at 2:45 AM, Albe Laurenz <[hidden email]> 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                                    [hidden email]
LlamaLand                       https://netllama.linux-sxs.org

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: how to measure wal_buffer usage

Albe Laurenz *EXTERN*
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Loading...