On Wed, 23 Jan 2008, Tory M Blue wrote:
I have hundreds of thousands of updates, inserts a day. But what I'm seeing is my server appears to "deallocate" memory (for the lack of a better term) and performance goes to heck, slow response, a sub second query takes anywhere from 6-40 seconds to complete when this happens.
Generally if you have a system doing many updates and inserts that slows for that long, it's because it hit a checkpoint. I'm not sure what your memory-related issues are but it's possible that might be from a backlog of sessions using memory that are stuck behind the checkpoint, particularly since you mention simple query connections stacking up during these periods.
In any case you should prove/disprove this is checkpoint-related behavior before you chase down something more esoteric. There's a quick intro to this area in the "Monitoring checkpoints" section of http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm and the later sections go into what you can do about it.
I suspect I've outgrown our initial postgres config, or there are more sysctl or other kernel tweaks that need to happen.
You should post a list of what you're changed from the defaults. You're analyzing from the perspective where you assume it's a memory problem and a look at your config will give a better idea whether that's possible or not. Other good things to mention: exact 8.2 version, OS, total memory, outline of disk configuration.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
