Re: {Spam} Will shared_buffers crash a server

Qiang Wang <forest_qiang(at)yahoo(dot)com> wrote:
> We have PostgreSQL 8.3 running on Debian Linux server. We built an
> applicantion using PHP programming language and Postgres database. There are
> appoximatly 150 users using the software constantly. We had some performance
> degration before and after some studies we figured out we will need to tune
> PostgreSQL configurations.

> However we suffered 2 times server crashes after tunning the configuration.
> Does anyone have any idea how this can happen?

Could you explain in more detail, *how* it crashed?

On Linux, the first suspect for crashes is usually the OOM
(out-of-memory) killer. When the kernel thinks it's run out of memory,
it picks a task and kills it. Due to the way PostgreSQL uses shared
memory, it's more likely to be killed than other processes.

To figure out whether you've suffered an OOM kill, run "dmesg", you
would see something like:
[2961426.424851] postgres invoked oom-killer: gfp_mask=0x201da,
order=0, oomkilladj=0
[2961426.424857] postgres cpuset=/ mems_allowed=0
[2961426.424861] Pid: 932, comm: postgres Not tainted 2.6.31-22-server
#65-Ubuntu
[2961426.424863] Call Trace:
...

The first step in solving OOM kills is disabling memory overcommit;
add 'vm.overcommit_memory = 0' to /etc/sysctl.conf and run the command
'echo 0 > /proc/sys/vm/overcommit_memory'

This doesn't prevent OOM kills entirely, but usually reduces them
significantly, queries will now abort with an "out of memory" error if
they're responsible for memory exhaustion.

You can also reduce the chance that PostgreSQL is chosen for killing,
by changing its oom_adj, documented here:
http://blog.credativ.com/en/2010/03/postgresql-and-linux-memory-management.html

Regards,
Marti