From: Marti Raudsepp <marti@juffo.org>
To: Qiang Wang <forest_qiang@yahoo.com>
Cc: French, Martin <frenchm@cromwell.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: {Spam} Will shared_buffers crash a server
Date: Sun, 1 May 2011 19:18:05 +0300
Message-ID: <BANLkTimLMzZM-0BHMcLJibfsYH2G4xniew@mail.gmail.com> (raw)
In-Reply-To: <81976671721DF04B9DCA6ECD87941A40290FDDBD@roundway.Cromwell-tools.co.uk>
References: <343857.89994.qm@web63001.mail.re1.yahoo.com>
<81976671721DF04B9DCA6ECD87941A40290FDDBD@roundway.Cromwell-tools.co.uk>
Qiang Wang <forest_qiang@yahoo.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: marti@juffo.org, forest_qiang@yahoo.com, frenchm@cromwell.co.uk
Subject: Re: {Spam} Will shared_buffers crash a server
In-Reply-To: <BANLkTimLMzZM-0BHMcLJibfsYH2G4xniew@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox