Too frequent checkpoints ? - Mailing list pgsql-hackers

I was looking at the vacuum/visibility bug that Jeff Janes reported
and brought up the server with the data directory he has shared. With
his configuration,

# - Checkpoints -

checkpoint_segments = 1     # in logfile segments, min 1, 16MB each
checkpoint_timeout = 30s        # range 30s-1h

# - Archiving -

archive_mode = on       # allows archiving to be done               # (change requires restart)
archive_command = 'echo archive_command %p %f `date`'       # command
to use to archive a logfile segment               # placeholders: %p = path of file to archive               #
    %f = file name only               # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p
 
/mnt/server/archivedir/%f'
archive_timeout = 30        # force a logfile segment switch after this               # number of seconds; 0 disables


I see the following messages coming on the console:

3092  2013-02-08 02:29:01.239 PST:LOG:  checkpoints are occurring too
frequently (0 seconds apart)
3092  2013-02-08 02:29:01.239 PST:HINT:  Consider increasing the
configuration parameter "checkpoint_segments".
3092  2013-02-08 02:29:01.239 PST:LOG:  checkpoint starting: xlog
3092  2013-02-08 02:29:01.246 PST:LOG:  checkpoint complete: wrote 0
buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1
recycled; write=0.000 s, sync=0.000 s, total=0.007 s; sync files=0,
longest=0.000 s, average=0.000 s
archive_command pg_xlog/0000000100000057000000F5
0000000100000057000000F5 Fri Feb 8 15:59:01 IST 2013
3092  2013-02-08 02:29:31.278 PST:LOG:  checkpoints are occurring too
frequently (0 seconds apart)
3092  2013-02-08 02:29:31.278 PST:HINT:  Consider increasing the
configuration parameter "checkpoint_segments".
3092  2013-02-08 02:29:31.278 PST:LOG:  checkpoint starting: xlog
3092  2013-02-08 02:29:31.285 PST:LOG:  checkpoint complete: wrote 0
buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1
recycled; write=0.000 s, sync=0.000 s, total=0.006 s; sync files=0,
longest=0.000 s, average=0.000 s
archive_command pg_xlog/0000000100000057000000F6
0000000100000057000000F6 Fri Feb 8 15:59:31 IST 2013
3092  2013-02-08 02:30:01.316 PST:LOG:  checkpoints are occurring too
frequently (0 seconds apart)
3092  2013-02-08 02:30:01.316 PST:HINT:  Consider increasing the
configuration parameter "checkpoint_segments".
3092  2013-02-08 02:30:01.316 PST:LOG:  checkpoint starting: xlog
archive_command pg_xlog/0000000100000057000000F7
0000000100000057000000F7 Fri Feb 8 16:00:01 IST 2013
3092  2013-02-08 02:30:01.323 PST:LOG:  checkpoint complete: wrote 0
buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1
recycled; write=0.000 s, sync=0.000 s, total=0.006 s; sync files=0,
longest=0.000 s, average=0.000 s
3092  2013-02-08 02:30:31.327 PST:LOG:  checkpoints are occurring too
frequently (0 seconds apart)
3092  2013-02-08 02:30:31.327 PST:HINT:  Consider increasing the
configuration parameter "checkpoint_segments".
3092  2013-02-08 02:30:31.327 PST:LOG:  checkpoint starting: xlog
archive_command pg_xlog/0000000100000057000000F8
0000000100000057000000F8 Fri Feb 8 16:00:31 IST 2013
3092  2013-02-08 02:30:31.334 PST:LOG:  checkpoint complete: wrote 0
buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1
recycled; write=0.000 s, sync=0.000 s, total=0.007 s; sync files=0,
longest=0.000 s, average=0.000 s

I wonder if this is all expected. The database is getting ZERO
activity. There are no connections open at this time. The checkpoints
are happening at every 30 seconds and new WAL files are being created,
AFAIK because the old ones are getting archived. Can't we be smart
about not archiving new files if we did not generate any new WAL since
the last archive ?

Also, the log says "checkpoints are occurring too frequently (0
seconds apart)". But that looks wrong too. Checkpoints are really
happening at 30 seconds apart and not 0 as the log message claims.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee