Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 10AAA1337BE6 for ; Sun, 1 May 2011 13:18:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 32300-02 for ; Sun, 1 May 2011 16:18:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vx0-f174.google.com (mail-vx0-f174.google.com [209.85.220.174]) by mail.postgresql.org (Postfix) with ESMTP id 8A0371337BD2 for ; Sun, 1 May 2011 13:18:37 -0300 (ADT) Received: by vxi39 with SMTP id 39so3789453vxi.19 for ; Sun, 01 May 2011 09:18:36 -0700 (PDT) Received: by 10.52.100.74 with SMTP id ew10mr4599064vdb.283.1304266715655; Sun, 01 May 2011 09:18:35 -0700 (PDT) MIME-Version: 1.0 Received: by 10.220.195.136 with HTTP; Sun, 1 May 2011 09:18:05 -0700 (PDT) In-Reply-To: <81976671721DF04B9DCA6ECD87941A40290FDDBD@roundway.Cromwell-tools.co.uk> References: <343857.89994.qm@web63001.mail.re1.yahoo.com> <81976671721DF04B9DCA6ECD87941A40290FDDBD@roundway.Cromwell-tools.co.uk> From: Marti Raudsepp Date: Sun, 1 May 2011 19:18:05 +0300 Message-ID: Subject: Re: {Spam} Will shared_buffers crash a server To: Qiang Wang Cc: "French, Martin" , pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201105/2 X-Sequence-Number: 43511 Qiang Wang 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