Re: Why shared_buffers max is 8GB?

Markella Skempri <markella_skembri(at)hotmail(dot)com> wrote on 26/03/2014
13:47:47:

>
> Thanks Martin,
> However this is a database dedicated server and nothing much else is
> running on it. Also, I never saw this happening with 9.2 – but I
> can’t vouch for the size of files that I was uploading.
>
> From: Martin French
> Sent: Wednesday, March 26, 2014 1:32 PM
> To: Markella Skempri
> Cc: desmodemone ; ik(at)postgresql-consulting(dot)com ; Alexey Vasiliev ;
> pgsql-performance(at)postgresql(dot)org ;
pgsql-performance-owner(at)postgresql(dot)org
> Subject: Re: [PERFORM] Why shared_buffers max is 8GB?
>
>
> > I wanted to follow up from this question. I’m running on 9.3.4
> > My DB server has 32GB ram so I have assigned 8GB
> > shared_buffer_memory. It is quite a big db but with not much
> > traffic. When there is traffic, it’s usually big.
> >
> > Lately, the kernel has been killing the postmaster for having
> > assigned too much shared memory. Latest crash was when loading a
> 500MB file.
> >
> > Should I reduce the shared buffers in order for this to be more
robust?
> >
> > Thanks
> > Markella
>
> It may be that other memory settings are contributing towards this
> (work_mem, maintenance_work_mem, max_connections etc).
>
> I would suggest that the OOM killer is working as intended and
> something is not quite right within the config.
>
> You may want to review the memory consumption at peak times taking
> into consideration anything else running on the machine.
> =============================================
>

My course of action here would be to halve all the memory settings and
benchmark performance, then increment in small amounts periodically
repeating the file load situation until the crash happens again, obviously
with a keen eye on the postgres logs as well as on the syslog output.

The other *potentially dangerous* thing you could do is to alter the OOM
killers behaviour in a couple of ways (assuming PostgreSQL wasn't compiled
with -DLINUX_OOM_ADJ=0).

This can be done like so:
1. Alter the Kernel behaviour with:
sysctl -w vm.overcommit_memory=2
2. Tell the Kernel not to kill Postmaster Process.
echo -17 >> /proc/$(ps -ef | grep postmaster | grep -v grep | awk '{print
$2}')/oom_adj

I cannot state enough that this could cause unpredictable behaviour of the
OOM killer, and thus; the box itself, and is not 100% guaranteed to stop
the OOM killer taking Postgres out.

=============================================

Romax Technology Limited
A limited company registered in England and Wales.
Registered office:
Rutherford House
Nottingham Science and Technology Park
Nottingham
NG7 2PZ
England
Registration Number: 2345696
VAT Number: 526 246 746

Telephone numbers:
+44 (0)115 951 88 00 (main)

For other office locations see:
http://www.romaxtech.com/Contact
=================================
===============
E-mail: info(at)romaxtech(dot)com
Website: www.romaxtech.com
=================================

================
Confidentiality Statement
This transmission is for the addressee only and contains information that
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf
of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.
=================================================