Re: simple update queries take a long time - postgres 8.3.1

On Wed, 2 Apr 2008, Tomasz Ostrowski wrote:

> Even 64 (I like round numbers) would not be too much. This
> would make 1GB of data in WALs.

It's much worse than that. Assume the system starts a checkpoint after
checkpoint_segments worth of WAL files have been used. It may take that
long before the checkpoint is finished, which means twice that many will
be active (the old ones that haven't been reconciled yet and the new ones
that have been written while processing the checkpoint). The formula in
8.2 and earlier versions was that you can expect up to
2*checkpoint_segments+1 worth of them around because of this. For
checkpoint_segments=64, that makes 129*16MB=2GB.

The situation is more complicated in 8.3 because of how
checkpoint_completion_target is implemented. There you can expect up to
3*checkpoint_segments + 1 files, worst-case. To be more exact, the usual
expectation is that you'll have up to (2 + checkpoint_completion_target) *
checkpoint_segments + 1, which as you can see approaches
3*checkpoint_segments for large target values. This is all covered at
http://www.postgresql.org/docs/8.3/static/wal-configuration.html

The numbers aren't so round here anymore with this change. I threw out
100 as a suggested setting, which might result in up to 4.8GB of WAL
files. The recovery time from a crash with this many around will be
painful, but if that's the only way to smooth regular system response
you're stuck with it.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD