Tom Lane wrote:
> * How much "overhead" disk space are you willing to let Postgres use?
>
> ... The third one is a bit weird but
> I don't see any other good way to set the checkpoint parameters.
The way I think about the checkpoint settings is:
1. Set checkpoint_timeout to the max. time you're willing to spend in
recovery in case of crash or power loss.
2. Set checkpoint_segments to a high value. "High" meaning high enough
that you'll never reach it in practice. The purpose is just to keep you
from running out of disk space if something weird happens.
The amount of downtime one is willing to accept in case of power loss is
a good question to ask because it doesn't require any knowledge of how
PostgreSQL works; it can be answered directly from the application
requirements. And if the DBA/developer don't know the answer, he needs
to figure it out, because it's a very important question not only for
the database but in general.
I believe checkpoint_timeout correlates quite well with the max. time
required in recovery. If it took 10 minutes to generate X amount of WAL,
replaying that WAL will need to do at most the same amount of I/O, which
should take roughly the same amount of time, regardless of whether the
I/O was sequential or random. If the system wasn't busy doing updates
during between the checkpoints, it will of course take less.
As with all settings, the tool will need to explain the tradeoff.
Smaller checkpoint_timeout means more checkpointing which means more I/O
which means less average TPS and more WAL generated (because of
full_page_writes).
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
