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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
