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

Re: [PERFORMANCE] how to set wal_buffers

From: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, casanov(at)systemguards(dot)com(dot)ec
Subject: Re: [PERFORMANCE] how to set wal_buffers
Date: 2009-08-23 20:25:59
Message-ID: 3073cc9b0908231325n3b06ad4am3fa0da7a1e4c8a9d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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?

>
> On the other extreme, if you have many connections rapidly firing
> small transactions, and you hope for the WAL of many of them to all
> get flushed down to disk with a single fsync,
> then your wal_buffers should be big enough to hold the WAL data of all
> those transactions.  Running out of WAL space has a nasty effect on
> group commits.
>

that's exactly my situation... and i was thinking on raising
wal_buffers at least to hold variuos transactions... i can use
pg_stat_database.xact_commits to calculate an avg of transactions per
second...

plus i think we need a bit more space for transactions marked as
"synchrounous_commit to off"

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2009-08-23 21:41:39
Subject: Re: limiting results makes the query slower
Previous:From: Guillaume SmetDate: 2009-08-23 12:49:05
Subject: Re: Performance regression between 8.3 and 8.4 on heavy text indexing

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