On Thu, 14 Feb 2008, Phoenix Kiula wrote:
Suddenly, the postmaster either hogs up memory or just croaks and doesn't respond. The write process has become horribly slow.
You should consider whether that's because a checkpoint is happening at that point. You didn't mention anything about your disk+controller information to have an idea how likely that is. Consider increasing checkpoint_warning=3600 so that you'll always get a note in the logs when a checkpoint happens; if those line up with your client disconnects that will be telling you something.
On the logging size, you may want to also enable log_min_duration_statement ; around 500 (milliseconds) would be a reasonable starting value. That will show you what queries are taking a long time to handle.
I am on a 4GB server with Apache 2.2.6 and PgSql 8.2.3.
Be aware that when 8.2.3 was released, 8.2 had only been out for two months. There's another 11 months worth of accumulated bug fixes in 8.2.6, including some that can cause the server to slow or crash. It's not a difficult upgrade (no changes to the database) and you should consider it. There are plenty of known and already fixed problems in 8.2.3 you could be running into.
max_connections = 150 maintenance_work_mem = 512MB shared_buffers = 330MB work_mem = 100MB
That's a really high setting for work_mem with this many connections; are you aware that combination can easily use 15GB of RAM? You should decrease that to around 10MB with the size of your server and greatly reduce one possible source for running out of memory. It's possible to increase that value just for some individual queries if there's some known set of ones that really need more memory to work efficiently (looking at the minimum duration logs should give you guidance here).
I'd normally suggest increasing shared_buffers instead as well, but you should rule out checkpoints before doing that (increasing shared_buffes can make checkpoint issues worse). You could also decrease maintenance_work_mem quite a bit from where you've got it at now to reduce another possible source for large memory allocations.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
