Reducing the postgresql.conf, parameter at a time

One of the more useful bits of
PostgreSQL documentation I ever worked on is Tuning Your PostgreSQL
Server.  When that was written in the summer of 2008, a few months after the
release of PostgreSQL 8.3, it was hard to find any similar guide that
was both (relatively) concise and current. Since then, myself and
many other PostgreSQL contributors have helped keep that document up
to date as changes to PostgreSQL were made.

The interesting and helpful trend
during that period is that parameters keep disappearing from the set
of ones you need to worry about. In PostgreSQL 8.2, there was a long
list of parameters you likely needed to adjust for good performance
on a PostgreSQL server: shared_buffers, effective_cache_size,
checkpoint_segments, autovacuum, max_fsm_pages,
default_statistics_target, work_mem, wal_buffers, and (if using
partitioning) constraint_exclusion.

8.3 made autovacuum default to being
turned on with reasonable behavior, along with removing a few
background writer parameters that caused nothing but trouble (they
never made it onto the list). 8.4 removed the need for the two
max_fsm_* parameters, increased default_statistics_target to a much
better starting value, and made setting constraint_exclusion
unnecessary in the most common cases. That’s six less parameters
that you are likely to need to adjust.

Unfortunately version 9.0 only made
server configuration more complicated. And newer Linux kernels even
pushed default behavior backwards. Starting with Linux kernel
2.6.33, the default value picked for wal_sync_method changed to
open_datasync. This turns out to have terrible performance
implications for PostgreSQL, particularly when combined with the low
default setting for wal_buffers in the server.

But the march toward better default
behavior has recently resumed for what is eventually planned to be
PostgreSQL 9.1. During the last CommitFest, a patch originated Marti
Raudsepp to fix the wal_sync_method problem was committed
after some heavy arguments over what form that change should take.
Discovering that this behavior change broke PostgreSQL altogether
when running on ext4 with “data=journal” option helped
settle the right thing to do here by default.

Two parameters I don’t recommend
touching in most cases are commit_siblings and commit_delay,
artifacts of an older attempt to improve performance on systems with
slow commit times (which includes most systems that don’t have a
battery-backed write cache for accelerating that area). Nowadays
turning off the synchronous_commit parameter introduced in 8.3 is
much more likely to help here. While these are unlikely to improve
performance, people who do try setting them have suffered more than
necessary from the downsides of that decision. The worst-case
behavior here was improved considerably in a patch I wrote to optimize how the logic those parameters control executes.

And this week the latest parameter to
be effectively eliminated in most cases is wal_buffers. A change I
suggested was commited to set this automatically as a percentage of the size (about 3%)
allocated to the normally much larger shared_buffers parameters.
This sets the value of wal_buffers to the normal upper limit of its
effective range, 16MB, once you’re allocated at least 512MB to
shared_buffers. And if you’ve increased shared_buffers from its tiny
default at all, you’ll get a corresponding improvement in this
important commit performance parameter. You’ll have to go out of
your way to break the setting of this parameter to hit the bad
situations possible in earlier versions.

Having the amount of configuration you
need to do to the server by default get less complicated is always
worthwhile, and seeing parameters disappear form the critical list is
a welcome change. What’s next? The core issues with allocating
shared memory on UNIX-derived operating systems, particularly Linux,
make it very difficult to eliminate shared_buffers. And concerns
over the server taking over the system altogether limit the ability
to automatically set parameters like work_mem to the right range.
Some proposals for better managing the working memory pool have been
suggested, so that one might see some improvement.

The next parameter I have my eye on is
checkpoint_segments. After adding just extra logging in this area in
the last CommitFest, there are some improvements in this area nearing
commit now to actually improve checkpoint behavior. I hope to
eventually switch over checkpoint tuning to be strictly controlled
via time-oriented parameters, rather than requiring users to
understand the mechanics of how the write-ahead log works to tune the
system. There’s still too many ugly situations possible here to do
that in time for 9.1, but setting the segments count automatically is
feasible to target for 9.2.