Re: Server Freezing

[Greg Smith <greg(at)2ndquadrant(dot)com>]

Waldomiro wrote:
> Is It possible the checkpoint is doing that? Or the archiving? How can 
> I see?
If you're using PostgreSQL 8.3 or later, you can turn on log_checkpoints 
and you'll get a note when each checkpoint finishes.  The parts that are 
more likely to slow the server down are right at the end, so if you see 
a bunch of slow queries around the same time as the checkpoint message 
appears in the logs, that's the likely cause.  Bad checkpoint behavior 
can certainly cause several seconds of freezing on a system with 32GB of 
RAM, because with that much data you can have quite a bit in the OS 
write cache that all gets forced out at the end of the checkpoint.

Finding when the checkpoints happen on 8.2 or earlier is much harder; I 
can tell you what to look for on Linux for example, but it's kind of 
painful to track them down.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com  www.2ndQuadrant.com