Well, checkpoint_segments was the old way we had for users to determine how "big" the transaction log should be. Users with a large server or a lot of transactions per second would set it high, and users with small VMs and a slow database could set it low. This had some problems, though:
- The default setting worked for pretty much nobody, so you always had to adjust it.
- The WAL* always used the maximum space available, even if it wasn't needed.
- Figuring out the largest size your WAL could be required some math and a knowledge of version-specific PostgreSQL internals.
( ( checkpoint_segments * 2 ) + 1 ) * 16MB ) + ( wal_keep_segments * 16MB )
... which meant that people generally sized it by picking an arbitrary number and then adjusting up or down based on feedback.
The new parameters are way simpler:
- min_wal_size: the minimum size the transaction log will be;
- max_wal_size: the maximum size the transaction log will be (but see below)
However, that isn't the really cool part. Heikki did an amazing thing, in that the WAL is sized dynamically based on how much was used during the previous cycles. So you can set max_wal_size to some high value (default is 1GB), and not worry about PostgreSQL using a bunch of extra disk space if it's not needed. This means that we can set a default which will be "good enough" for 80% of our users, and we have. This makes me do a little happy dance.
The other 20% may want to tune, still though, so here's some guidelines:
- if you know your database write traffic is "bursty", with long periods of inactivity followed by furious writes, increase min_wal_size;
- if you do bulk loads larger than 1GB, increase max_wal_size to the size of a bulk load;
- if you write more than 1GB of data every 10 minutes, increase max_wal_size;
- if you get "checkpoints occurring to frequently" error messages, try increasing both parameters incrementally.
* WAL == Write Ahead Log == Transaction Log == XLog
Post a Comment