Hey Everyone,
With my investigations into our streaming slave having a continuously
growing pg_xlog directory (
http://archives.postgresql.org/pgsql-general/2012-08/msg00124.php) I think
I've hit upon what's going on.
Using pg_controldata I've found that our streaming slave hasn't run a
checkpoint for over 2 weeks. Or I suppose more specifically hasn't
completed a checkpoint in 2 weeks. The server was synced and started up
on the 27th so there was 4 days where it looks like the checkpoints where
happening correctly. I have attempted a manual checkpoint on the slave with
no effect (it returns instantly to me in the client but the pg_controldata
never shows any change). On my testing system the automatic checkpoint
runs successfully every 5 minutes and removes the wal logs it no longer
needs to get to that restartpoint.
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5680552055141687295
Database cluster state: in archive recovery
*pg_control last modified: Tue 31 Jul 2012 12:43:59 PM MDT*
Latest checkpoint location: 178/5344C730
Prior checkpoint location: 178/4E8C1938
Latest checkpoint's REDO location: 178/4FC5E8E0
Latest checkpoint's TimeLineID: 10
Latest checkpoint's NextXID: 0/291590955
Latest checkpoint's NextOID: 67986575
Latest checkpoint's NextMultiXactId: 122827
Latest checkpoint's NextMultiOffset: 296304
Latest checkpoint's oldestXID: 64713442
Latest checkpoint's oldestXID's DB: 16408
Latest checkpoint's oldestActiveXID: 260568245
*Time of latest checkpoint: Tue 31 Jul 2012 12:35:53 PM MDT*
Minimum recovery ending location: 199/EC6C0BA0
Backup start location: 0/0
Current wal_level setting: hot_standby
Current max_connections setting: 600
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 256
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Compared to our master
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5680552055141687295
Database cluster state: in production
pg_control last modified: Mon 13 Aug 2012 11:35:35 AM MDT
Latest checkpoint location: 199/EC5E4218
Prior checkpoint location: 199/EBEFB120
Latest checkpoint's REDO location: 199/EC033110
Latest checkpoint's TimeLineID: 10
Latest checkpoint's NextXID: 0/291590955
Latest checkpoint's NextOID: 75744399
Latest checkpoint's NextMultiXactId: 122877
Latest checkpoint's NextMultiOffset: 296404
Latest checkpoint's oldestXID: 91782681
Latest checkpoint's oldestXID's DB: 16408
Latest checkpoint's oldestActiveXID: 291583665
Time of latest checkpoint: Mon 13 Aug 2012 11:31:35 AM MDT
Minimum recovery ending location: 0/0
Backup start location: 0/0
Current wal_level setting: hot_standby
Current max_connections setting: 600
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 256
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Which appears to correctly be running it's automated checkpoints every 5
minutes. Our checkpoint configuration is currently:
checkpoint_completion_target | 0.8 |
Time spent flushing dirty buffers during checkpoint, as fraction of
checkpoint interval.
checkpoint_segments | 128 |
Sets the maximum distance in log segments between automatic WAL checkpoints.
checkpoint_timeout | 5min |
Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning | 30s |
Enables warnings if checkpoint segments are filled more frequently than
this.
The settings are the same on both the slave and the master.
We don't currently have log_checkpoints enabled on our slave (I'm going to
make the change and schedule a resync for this evening to see if the log
reveals anything)
I've done some googling and can't really see anything that will allow me to
see the status of the checkpoints in progress currently.
Just for more info we're running postgres 9.1.1 build from source on 2 X64
Cent OS5.8 machines. The total size of our /data directory is currently
94.5 gigs which is currently all in 1 DB with 2193 schemas (each of which
has around 1300 objects).. From the what I'm able to pull of the slave
there isn't constant IO happening like the checkpoints are running and just
thrashing the disks. If there's any other information that may be helpful
I can pull it.
Anyone have any suggestions as to next roads to take (besides turning on
the checkpoint logging and trying to see what's going on)?
Thanks
