On Wed, Feb 15, 2012 at 4:12 PM, Andres Freund
<andres@anarazel.de> wrote:
> > > all of these 1000 files get filled up in less than 5 mins, there are
> > > chances that system will slow down due to high IO and CPU.
> > As far as I know there is no data loss issue with a lot of checkpoint
> > segments.
> Data loss would be an issue when there is a server crash or pg_xlog crash
> etc. That many number of pg_xlog files (1000) would contribute to huge data
> loss (data changes not synced to the base are not guaranteed). Of-course,
> this is not related to the current situation. Normally we calculate the
> checkpoint completion time, IO pressure, CPU load and the threat to the
> data loss when we configure checkpoint_segments.
I think you might be misunderstanding something. A high number of
checkpoint_segments can lead to slower recovery - all those changes need to be
reapplied - but it won't lead to lost data. The data inside the wal will be
fsynced at appropriate times (commit; background writer; too much written).
Recovery would take time because all the changes in WAL files of pg_xlog (which is high) must be replayed to reach consistent state. When disaster strikes and if pg_xlogs are not available and data in WAL is not fsynced yet, then recovery is not possible and data loss will be huge. It also depends on how much data is not fsynced.
Thanks,
VB