Thread: Will shared_buffers crash a server

As for the question in the title, no, if the server starts, shared
buffers should not be the reason for a subsequent crash.

In debian, it is common that the maximum allowed shared memory setting
on your kernel will prevent a server from even starting, but I guess
that's not your problem (because it did start).

Normally under heavy load, a machine could potential stall, even seem to hang, but not crash.
And judging from your description of the situation your only change was in shared memory (IPC shm) usage, right?
I would advise to immediately run all sorts of (offline/online) hardware tests (especially memtest86) on your machine,
before blaming postgesql for anything.

Στις Friday 29 April 2011 10:13:08 ο/η Qiang Wang έγραψε:
> Hei:
>
>
> We have PostgreSQL 8.3 running on Debian Linux server. We built an applicantion using PHP programming language and
Postgresdatabase. There are appoximatly 150 users using the software constantly. We had some performance degration
beforeand after some studies we figured out we will need to tune PostgreSQL configurations.  
>
>
> We have 10GB memory and we tuned PostgreSQL as follow:
> - max_connection = 100
> - effective_cache_size = 5GB
> - shared_buffer = 2GB
> - wal_buffer = 30MB
> - work_mem = 50MB
>
> However we suffered 2 times server crashes after tunning the configuration. Does anyone have any idea how this can
happen?
>
> BR
>
> Kevin Wang
>



--
Achilleas Mantzios

On Fri, Apr 29, 2011 at 1:13 AM, Qiang Wang <forest_qiang@yahoo.com> wrote:
>
> We have 10GB memory and we tuned PostgreSQL as follow:

> - max_connection = 100
> - work_mem = 50MB

You do know that work_mem is PER SORT right?  Not per connection or
per user or per database.  If all 100 of those connections needs to do
a large sort at once (unlikely but possible, especially if under heavy
load) then you could have pgsql trying to allocate 50000MB.  If you
have the occasional odd jobs that really need 50MB work_mem then set
it for a single user or connection and leave the other users in the 1
to 4MB range until you can be sure you're not running your db server
out of memory.

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

Look in your log files for clues.  postgresql logs as well as system
logs.  What exact symptoms, if any, can you tell us of the crash?

___________________________________________________

This email is intended for the named recipient. The information contained
in it is confidential. You should not copy it for any purposes, nor
disclose its contents to any other party. If you received this email
in error, please notify the sender immediately via email, and delete
it from your computer.

Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.

PCI Compliancy: Please note, we do not send or wish to receive banking,
credit or debit card information by email or any other form of
communication.

Please try our new on-line ordering system at http://www.cromwell.co.uk/ice

Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__________________________________________________

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