Re: PG quitting sporadically!!

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD