Re: auto-sizing wal_buffers

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://github.com/greg2ndQuadrant/postgres.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 
amount
!         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 
benefit.

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books