Re: Why shared_buffers max is 8GB? - Mailing list pgsql-performance
| From | Martin French |
|---|---|
| Subject | Re: Why shared_buffers max is 8GB? |
| Date | |
| Msg-id | OF7E8C9A55.BB147107-ON80257CA7.004CCB1D-80257CA7.004D57E6@romaxtech.com Whole thread Raw |
| In response to | Re: Why shared_buffers max is 8GB? (Markella Skempri <markella_skembri@hotmail.com>) |
| List | pgsql-performance |
Markella Skempri <markella_skembri@hotmail.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@postgresql-consulting.com ; Alexey Vasiliev ;
> pgsql-performance@postgresql.org ; pgsql-performance-owner@postgresql.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@romaxtech.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.
=================================================
>
> 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@postgresql-consulting.com ; Alexey Vasiliev ;
> pgsql-performance@postgresql.org ; pgsql-performance-owner@postgresql.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@romaxtech.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.
=================================================
pgsql-performance by date: