Skip site navigation (1) Skip section navigation (2)

Re: [PERFORMANCE] how to set wal_buffers

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORMANCE] how to set wal_buffers
Date: 2009-08-23 22:26:16
Message-ID: f67928030908231526i438f68d4q2ee34e1baadaf45d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Aug 23, 2009 at 1:25 PM, Jaime
Casanova<jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> On Thu, Aug 20, 2009 at 11:38 PM, Jeff Janes<jeff(dot)janes(at)gmail(dot)com> wrote:
>>> ---------- Forwarded message ----------
>>> From: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
>>> To: psql performance list <pgsql-performance(at)postgresql(dot)org>
>>> Date: Wed, 19 Aug 2009 19:25:11 -0500
>>> Subject: [PERFORMANCE] how to set wal_buffers
>>> Hi,
>>>
>>> Our fine manual says:
>>> """
>>> The amount of memory used in shared memory for WAL data. The default
>>> is 64 kilobytes (64kB). The setting need only be large enough to hold
>>> the amount of WAL data generated by one typical transaction, since the
>>> data is written out to disk at every transaction commit. This
>>> parameter can only be set at server start.
>>> """
>>
>> I don't care for that description for several reasons, but haven't
>> been able to come up with a good alternative.
>>
>> One problem is as you note.  How is the average user supposed to know
>> what is the size of the redo that is generated by a typical
>> transaction?
>>
>
> one way is if there is a way to know how many blocks have been written
> by postgres (even a total is usefull because we can divide that per
> pg_stat_database.xact_commits), maybe
> pg_stat_bgwriter.buffers_checkpoint can give us an idea of that?

No, you want the amount of WAL data written, not the tablespace data written,
which is what pg_stat_bgwriter gives you.  Just look at how fast your pg_xlogs
are being archived and turned over to determine that WAL volume (unless you
have archive_timeout set).

However, I don't think this will help.  The amount of WAL logs increases
dramatically right after a checkpoint, so looking at the bulk average doesn't
say anything about how much is being generated at the peak.  Does your
performance drop right after a checkpoint is started?

maybe the code bracketed by the probes
TRACE_POSTGRESQL_WAL_BUFFER_WRITE_DIRTY* should be counted
and reported under one of the stat tables.

Jeff

In response to

Responses

pgsql-performance by date

Next:From: Gábor FarkasDate: 2009-08-24 06:54:46
Subject: postgresql uses Hash-join, i need Nested-loop
Previous:From: Robert HaasDate: 2009-08-23 21:41:39
Subject: Re: limiting results makes the query slower

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group