Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I don't know the parameter "checkpoint_segments" very well, My personal views is that it means the number of wal log files; I configured the value of the parameter to 64, but I can see 131 files in the pg_xlog directory.

Can someone explain the parameter?

--detail
postgres=# show checkpoint_segments;
 checkpoint_segments 
---------------------
 64
(1 row)

[postgres@pg_root]$ cd $PGDATA
[postgres@pg_root]$ ls -lrt pg_xlog | wc -l
132
share|improve this question

migrated from stackoverflow.com Apr 16 '11 at 22:44

This question came from our site for professional and enthusiast programmers.

1 Answer 1

For background: WAL (write-ahead logging) is the mechanism for ensuring that the database can be recovered in the event of a crash without requiring the data files be flushed after every write. Basically, before any change is written to the data files, it is first logged. In the event of a crash, the log can be used to redo any changes that didn't make it to disk.

Each file in pg_xlog is a WAL segment. checkpoint_segments, along with checkpoint_timeout, controls when a WAL checkpoint is done (a checkpoint is done after checkpoint_segments segments have been filled or checkpoint_timeout seconds have passed, whichever comes first); a checkpoint is a moment in time where it is guaranteed that the data files have been flushed, so the redo process doesn't need to consider any transactions in the log from before the checkpoint.

According to the documentation (which is where I cribbed most of the above from), prior to 8.3 there will normally be 2 * checkpoint_segments + 1 segment files in the pg_xlog directory; since 8.3, the expected number is (2 + checkpoint_completion_target) * checkpoint_segments + 1 (or wal_keep_segments in 9.0, if that is greater). In any case, 131 segment files for a checkpoint_segments of 64 is near the minimum to be expected.

share|improve this answer
    
According to your answer, I understand the parameter now, Thanks you very much ! By the way, can you explain checkpoint_completion_target? I am new to postgresql, thanks ! –  francs Mar 18 '11 at 3:37
1  
Again according to the documentation, it controls how fast the checkpoint writes are performed. Setting it to 0.0 would be equivalent to the pre-8.3 behavior, writing as fast as possible and possibly temporarily overloading the computer's IO subsystem. Setting it to 1.0 would try to time it so that the checkpoint finishes just as it is time to do another checkpoint. Setting it somewhere in between causes it to write at a speed somewhere in between. –  Anomie Mar 18 '11 at 3:43
    
Suppose the value of checkpoint_timeout is 30 min, and the value of checkpoint_completion_target is 0.6, Is every checkpoint process shoud finish it's job in 18 min(0.6 * 30) ? –  francs Mar 18 '11 at 5:23
    
I believe it looks at activity levels too. So even if checkpoint_timeout is 30 minutes, if activity levels indicate that checkpoints are required every 5 minutes it would aim at completing the checkpoint in 3 minutes. I don't know any details about how this estimation might work, though. –  Anomie Mar 18 '11 at 11:11
    
okey,thans for your explalation ! –  francs Mar 28 '11 at 9:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.