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

Re: auto-sizing wal_buffers

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Magnus Hagander <magnus(at)hagander(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: auto-sizing wal_buffers
Date: 2011-01-15 06:51:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Tom Lane wrote:
> I think we need to keep the override capability until the autotune
> algorithm has proven itself in the field for a couple of years.
> I agree with Josh that a negative value should be used to select the
> autotune method.

Agreed on both fronts.  Attached patch does the magic.  Also available 
in branch "walbuffers" from git://

By changing only shared_buffers I get the following quite reasonable 
automatic behavior:

$ psql -c "SELECT name,unit,boot_val,setting,current_setting(name) FROM 
pg_settings WHERE name IN ('wal_buffers','shared_buffers')"
      name      | unit | boot_val | setting | current_setting
 shared_buffers | 8kB  | 1024     | 3072    | 24MB
 wal_buffers    | 8kB  | -1       | 96      | 768kB

 shared_buffers | 8kB  | 1024     | 4096    | 32MB
 wal_buffers    | 8kB  | -1       | 128     | 1MB

 shared_buffers | 8kB  | 1024     | 16384   | 128MB
 wal_buffers    | 8kB  | -1       | 512     | 4MB

 shared_buffers | 8kB  | 1024     | 131072  | 1GB
 wal_buffers    | 8kB  | -1       | 2048    | 16MB

 shared_buffers | 8kB  | 1024     | 262144  | 2GB
 wal_buffers    | 8kB  | -1       | 2048    | 16MB

If you've set it to the auto-tuning behavior, you don't see that setting 
of -1 in the SHOW output; you see the value it's actually been set to.  
The only way to know that was set automatically is to look at boot_val 
as I've shown here.  I consider this what admins would prefer, as the 
easy way to expose the value that was used.  I would understand if 
people considered it a little odd though.  Since you can't change it 
without a postgresql.conf edit and a server start anyway, and it's 
tersely documented in the sample postgresql.conf what -1 does, I don't 
see this being a problem for anyone in the field.

To try and clear up some of the confusion around how the earlier 
documentation suggests larger values of this aren't needed, I added the 
following updated description of how this has been observed to work for 
admins in practice:

!         Since the data is written out to disk at every transaction commit,
!         the setting many only need to be be large enough to hold the 
!         of WAL data generated by one typical transaction.  Larger values,
!         typically at least a few megabytes, can improve write performance
!         on a busy server where many clients are committing at once.
!         Extremely large settings are unlikely to provide additional 

And to make this easy as possible to apply if I got this right, here's 
some proposed commit text:

Automatically set wal_buffers to be proportional
to the size of shared_buffers.  Make it 1/32
as large when the auto-tuned behavior, which
is the default and set with a value of -1,
is used.  The previous default of 64kB is still
enforced as a minimum value.  The maximum
automatic value is limited to 16MB.

(Note that this not exactly what I put in my own commit message if you 
grab from my repo, that had a typo)

Greg Smith   2ndQuadrant US    greg(at)2ndQuadrant(dot)com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support
"PostgreSQL 9.0 High Performance":

Attachment: auto-wal-buffers-v1.patch
Description: text/x-patch (5.0 KB)

In response to


pgsql-hackers by date

Next:From: Greg SmithDate: 2011-01-15 07:01:36
Subject: Re: auto-sizing wal_buffers
Previous:From: Noah MischDate: 2011-01-15 06:30:16
Subject: Re: ALTER TYPE 0: Introduction; test cases

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