Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fgXWL-0005T6-Px for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 15:40:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgXWJ-00047x-PW for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 15:40:39 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fgXWJ-00047p-9T for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 15:40:39 +0000 Received: from smtp207-pc.aruba.it ([62.149.157.207] helo=smtpg-pc.aruba.it) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgXWE-0005ZA-Gf for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 15:40:38 +0000 Received: from DT02 ([93.63.211.242]) by smtpcm2-pc.aruba.it with bizsmtp id D3gX1y00b5ELnP9013gYKs; Fri, 20 Jul 2018 17:40:32 +0200 From: "Alfonso Moscato" To: "'Shreeyansh Dba'" Cc: References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it> In-Reply-To: Subject: R: repeated out of shared memory error - not related to max_locks_per_transaction Date: Fri, 20 Jul 2018 17:40:32 +0200 Message-ID: <019f01d4203f$fef2a3d0$fcd7eb70$@merqurio.it> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01A0_01D42050.C27CD360" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJyTRksqbTjmmAqDGBi/OX9BtlRvgKPNFxnAj0Jj9ujNYOXwA== Content-Language: it DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aruba.it; s=a1; t=1532101232; bh=7FGdNenob9KmI3DZY3pDzZ2BF+H/rpUzdvbtZdnZy9k=; h=From:To:Subject:Date:MIME-Version:Content-Type; b=fuIIS8uV+gytdlOAnt0RCvukFYKhCjB1iUNQ8sjEM/n+rAVVahrKQtFjHNG8Ttc5p QzSsMofAg/1cnGHe9uPUguvKMSS9EQu1VZ9kzzYZ6nZz3LqPGDGfEIfjr5Z52vQN+E 6sF74A0h1w+KCPbi9ZjYU9jTjAQ8wgu6YIeXPMRcCfcmc6a0es1qPIvl+nOqLuLX6U kZ2+9xEjrjfIUJy4nZg1GWxsibmzB3AQVK7oYiEv5xGPv3Lemt3RbXq3kAwAcl1PYT +bc6NtSILTJOl1FmtS2koBxGufj1kuKiLh4PNhrss6JwpmnqCb8ysEJ9YL6hJO0Q1P WY/poszrBglQw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_01A0_01D42050.C27CD360 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Definitely disk space is not an issue: =20 File system 1K-blocchi Usati Disponib. Uso% Montato su udev 46388372 0 46388372 0% /dev tmpfs 9281376 9396 9271980 1% /run /dev/sda1 30830588 1801120 27440324 7% / tmpfs 46406864 4 46406860 1% /dev/shm tmpfs 5120 0 5120 0% /run/lock tmpfs 46406864 0 46406864 0% /sys/fs/cgroup /dev/sdd1 464323324 71361120 369352916 17% /var /dev/sdb2 29049840 45008 27506108 1% /tmp tmpfs 9281376 0 9281376 0% /run/user/1005 tmpfs 9281376 0 9281376 0% /run/user/1000 =20 I will try to set differently shmem limits Thanks alfonso =20 Da: Shreeyansh Dba =20 Inviato: venerd=C3=AC 20 luglio 2018 17:18 A: Alfonso Moscato Cc: pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to = max_locks_per_transaction =20 In addition to this. We have faced these out of shared memory error in one of our customer = environment where we observed the same below values: max seg size (kbytes) =3D 18014398509465599 max total shared memory (kbytes) =3D 18014398442373116 Due to this memory allocation not happened at the various processes = level which resulted to out of memory errors. And by setting the new = values as per our RAM resolved the out of shared memory error.=20 =20 =20 On Fri, Jul 20, 2018 at 8:27 PM, Shreeyansh Dba = > wrote: Hi Alfonso, Normally we see the out of shared memory errors comes due to the disk = space / kernel parameters issues. And it seems your kernel parameters = have default values. =20 Verify disk space or set kernel parameters based on your storage RAM = size. =20 =20 =20 On Fri, Jul 20, 2018 at 4:44 PM, Alfonso Moscato = > = wrote: We are getting crazy with "out of shared memory" errors, and we can't = figure the reason. We have postgresql "PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) = 5.4.0 20160609, 64-bit", the server has 92gb of ram, it is a mixed environment (mostly OLTP, with some DW), with 100 sessions constantly open (a CRM) = and some long queries run every half an hour. Everything works fine, except that after 1 day and half we start = receiving a lot of "out of shared memory" messages. I am sure it is not related with the usual max_locks_per_transaction = issue, because we have set max_locks_per_transaction to 384, and when we = receive these messages we have no more than 50/100 locks totally. Restarting the server usually works fine for one day and hal more, and = then messages restart. Looking at the log, we see that this error starts casually, sometimes on very small queries, returning some kbytes of data. We have tried a lot of different configurations. we have tried with = pgtune and pgconfig 2.0. Currently, we have:=20 max_connections =3D 200 shared_buffers =3D 23GB effective_cache_size =3D 69GB maintenance_work_mem =3D 2GB checkpoint_completion_target =3D 0.9 wal_buffers =3D 16MB default_statistics_target =3D 100 random_page_cost =3D 4 effective_io_concurrency =3D 2 work_mem =3D 60293kB min_wal_size =3D 2GB max_wal_size =3D 4GB max_worker_processes =3D 4 max_parallel_workers_per_gather =3D 2 max_locks_per_transaction =3D 384 but we tried with work_mem to 130mb, shared_buffer to a maximum fo 40gb, effective_cache to 4gb shared memory limits are very big:=20 max number of segments =3D 4096 max seg size (kbytes) =3D 18014398509465599 max total shared memory (kbytes) =3D 18014398442373116 min seg size (bytes) =3D 1 thanks =20 =20 ------=_NextPart_000_01A0_01D42050.C27CD360 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Definitely disk space = is not an issue:

 

File = system=C2=A0=C2=A0=C2=A0 1K-blocchi=C2=A0=C2=A0=C2=A0 Usati Disponib. = Uso% Montato su

udev=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 46388372=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0 = 46388372=C2=A0=C2=A0 0% /dev

tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 9281376=C2=A0=C2=A0=C2=A0=C2=A0 = 9396=C2=A0=C2=A0 9271980=C2=A0=C2=A0 1% /run

/dev/sda1=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 30830588=C2=A0 1801120=C2=A0 27440324=C2=A0=C2=A0 7% = /

tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 46406864=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 4=C2=A0 = 46406860=C2=A0=C2=A0 1% /dev/shm

tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 5120=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 5120=C2=A0=C2=A0 0% = /run/lock

tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 46406864=C2=A0=C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0=C2=A00=C2=A0 46406864=C2=A0=C2=A0 0% = /sys/fs/cgroup

/dev/sdd1=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 464323324 71361120 369352916=C2=A0 17% = /var

/dev/sdb2=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 29049840=C2=A0=C2=A0=C2=A0 45008=C2=A0 = 27506108=C2=A0=C2=A0 1% /tmp

tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 9281376=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0=C2=A0 = 9281376=C2=A0=C2=A0 0% /run/user/1005

tmpfs=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 9281376=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0=C2=A0=C2=A0 = 9281376=C2=A0 =C2=A00% /run/user/1000

 

I will try to set differently shmem = limits

Thanks

alfonso

 

Da: Shreeyansh Dba = <shreeyansh2014@gmail.com>
Inviato: venerd=C3=AC 20 = luglio 2018 17:18
A: Alfonso Moscato = <alfonso.moscato@merqurio.it>
Cc: = pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of = shared memory error - not related to = max_locks_per_transaction

 

In = addition to this.

We have  faced these out of shared memory = error in one of our customer environment where we observed the same = below values:

max seg size (kbytes) =3D = 18014398509465599
max total shared memory (kbytes) =3D = 18014398442373116

Due to this memory allocation not = happened at the various processes level which resulted to out = of memory errors. And by setting the new values as per our RAM = resolved the out of shared memory = error. 


<= div>

 

On Fri, = Jul 20, 2018 at 8:27 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> = wrote:

Hi = Alfonso,


Normally = we see the out of shared memory errors comes due to the disk space = / kernel parameters issues. And it seems your kernel parameters have = default values.

 

Verify disk space or set kernel parameters = based on your storage RAM size.

 

<= p class=3DMsoNormal>

<= div>

 

On Fri, = Jul 20, 2018 at 4:44 PM, Alfonso Moscato <alfonso.moscato@merqurio.it> = wrote:

We are getting crazy with "out of = shared memory" errors, and we can't figure
the reason.
We = have postgresql "PostgreSQL 9.6.9 on x86_64-pc-linux-gnu = (Ubuntu
9.6.9-2.pgdg16.04+1), compiled by gcc (Ubuntu = 5.4.0-6ubuntu1~16.04.9) 5.4.0
20160609, 64-bit", the server has = 92gb of ram, it is a mixed environment
(mostly OLTP, with some DW), = with 100 sessions constantly open (a CRM) and
some long queries run = every half an hour.
Everything works fine, except that after 1 day = and half we start receiving a
lot of "out of shared memory" = messages.
I am sure it is not related with the usual = max_locks_per_transaction issue,
because we have set = max_locks_per_transaction to 384, and when we receive
these messages = we have no more than 50/100 locks totally.
Restarting the server = usually works fine for one day and hal more, and then
messages = restart.
Looking at the log, we see that this error starts casually, = sometimes on
very small queries, returning some kbytes of data.
We = have tried a lot of different configurations. we have tried with = pgtune
and pgconfig 2.0.

Currently, we have: =
max_connections =3D 200
shared_buffers =3D = 23GB
effective_cache_size =3D 69GB
maintenance_work_mem =3D = 2GB
checkpoint_completion_target =3D 0.9
wal_buffers =3D = 16MB
default_statistics_target =3D 100
random_page_cost =3D = 4
effective_io_concurrency =3D 2
work_mem =3D = 60293kB
min_wal_size =3D 2GB
max_wal_size =3D = 4GB
max_worker_processes =3D 4
max_parallel_workers_per_gather =3D = 2
max_locks_per_transaction =3D 384
but we tried with work_mem to = 130mb, shared_buffer to a maximum fo 40gb,
effective_cache to = 4gb

shared memory limits are very big:
max number of segments = =3D 4096
max seg size (kbytes) =3D 18014398509465599
max total = shared memory (kbytes) =3D 18014398442373116
min seg size (bytes) =3D = 1

thanks


 

 

------=_NextPart_000_01A0_01D42050.C27CD360--