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 1fgUT1-0003MD-Ml for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 12:25:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgUT0-0008Pa-75 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 12:25:02 +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 1fgUSz-0008PH-Hp for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 12:25:02 +0000 Received: from smtp202-pc.aruba.it ([62.149.157.202] helo=smtpg-pc.aruba.it) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgUSu-0000yY-Ne for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 12:25:00 +0000 Received: from DT02 ([93.63.211.242]) by smtpcm2-pc.aruba.it with bizsmtp id D0Qu1y00A5ELnP9010QuKY; Fri, 20 Jul 2018 14:24:55 +0200 From: "Alfonso Moscato" To: "'Fabio Pardi'" , References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it> <4c89b62a-c024-098f-b78e-326268ff3bbf@portavita.eu> In-Reply-To: <4c89b62a-c024-098f-b78e-326268ff3bbf@portavita.eu> Subject: R: repeated out of shared memory error - not related to max_locks_per_transaction Date: Fri, 20 Jul 2018 14:24:55 +0200 Message-ID: <00d501d42024$aaf7b920$00e72b60$@merqurio.it> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00D6_01D42035.6E81E8B0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJyTRksqbTjmmAqDGBi/OX9BtlRvgLaDVPXo0TdcyA= Content-Language: it DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aruba.it; s=a1; t=1532089495; bh=AJAIo/0S0ykXxuL6UjliQnvKrRBNf6gNLQXa80Xczn8=; h=From:To:Subject:Date:MIME-Version:Content-Type; b=NEim5ust5ehaqjf6YOkSG071yW3uT0n/mbYMsEn2wLxiCBfNmfioWtINUHxIb09sv p4cJDc743qjQRq/0Ev0n4zZp39DCTwBFI7gTUF+2FnoCCjD//PlpnDXtZiW8K4YCmI U4ByHRkXWwrHUrVcMFeO7/dW6w0pPMds9h6LIl02qCHXw7aLwGHgRDjNfhBp5sWp3e JIyRXoVMQxf59XgOnRr7XqzqAzVvlrEpN/Nwj0NUor5/NJISJup3rwVjdES1/deBre 7KR5tMGv6JNsJiT1bKoxJG1O0fRxoVilG2pq4N5IuPFnBlu7RulnS2MHOiJOhXWiSf jKEQfwHAS6JsQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00D6_01D42035.6E81E8B0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Fabio, thanks for your answer. The output of sysctl is: kernel.sem =3D 32000 1024000000 500 32000 kernel.sem_next_id =3D -1 kernel.sg-big-buff =3D 32768 kernel.shm_next_id =3D -1 kernel.shm_rmid_forced =3D 0 kernel.shmall =3D 18446744073692774399 kernel.shmmax =3D 18446744073692774399 kernel.shmmni =3D 4096 =20 effective_cache_size is 69GB (it was at the third row of my post) =20 this is the output of ipcs -lm: ------ Limiti della memoria condivisa -------- 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 =20 I agree they sould be bytes, as the number is very big, but ipcs says = kbytes. =20 Regards Alfonso Moscato =20 Da: Fabio Pardi =20 Inviato: venerd=C3=AC 20 luglio 2018 14:07 A: pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to = max_locks_per_transaction =20 Hi Alfonso, is best if you post sysctl settings with their name. Could you do that, = to avoid confusion? =20 If i recall correctly, this setting is not OK: max number of segments =3D 4096 =20 are you referrring to 'kernel.shmall', right? =20 =20 SHMALL should always be at least shmmax/PAGE_SIZE =20 =20 =20 =20 also, some other settings are not quite ok, like: =20 max seg size (kbytes) =3D 18014398509465599 max total shared memory (kbytes) =3D 18014398442373116 =20 =20 (and i think it is expressed in bytes, perhaps?) but those 2 should not = be the cause of your out of memory. =20 =20 =20 and effective_cache_size too, to be accurate. =20 =20 regards, =20 fabio pardi =20 =20 On 20/07/18 13:14, 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. =20 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 =20 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 =20 thanks =20 =20 =20 =20 ------=_NextPart_000_00D6_01D42035.6E81E8B0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi = Fabio,

thanks for your = answer.

The output of = sysctl is:

kernel.sem =3D = 32000=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = 1024000000=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 500=C2=A0=C2=A0=C2=A0=C2=A0 = 32000

kernel.sem_next_id = =3D -1

kernel.sg-big-buff = =3D 32768

kernel.shm_next_id = =3D -1

kernel.shm_rmid_for= ced =3D 0

kernel.shmall =3D = 18446744073692774399

kernel.shmmax =3D = 18446744073692774399

kernel.shmmni =3D = 4096

 

effective_cache_siz= e is 69GB (it was at the third row of my post)

 

this is the output = of ipcs -lm:

------ Limiti = della memoria condivisa --------

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

 

I agree they sould = be bytes, as the number is very big, but ipcs says = kbytes.

 

Regards<= /span>

Alfonso = Moscato

 

Da: Fabio Pardi <f.pardi@portavita.eu> =
Inviato: venerd=C3=AC 20 luglio 2018 14:07
A: = pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of = shared memory error - not related to = max_locks_per_transaction

 

Hi = Alfonso,

is best if you post sysctl =
settings with their name.=C2=A0 Could you do that, to avoid =
confusion?

 

If i recall correctly, this setting is not = OK:

max number of segments =3D =
4096
 
a=
re you referrring to 'kernel.shmall', =
right?
 =

 
SHMAL=
L should always be at least =
shmmax/PAGE_SIZE
=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 =

 
=
 
 
also=
, some other settings are not quite ok, =
like:
 
=
max seg size (kbytes) =3D =
18014398509465599
max total shared memory =
(kbytes) =3D =
18014398442373116
 
 
(and i think it is =
expressed in bytes, perhaps?) but those 2 should not be the cause of =
your out of =
memory.
 
 
 
and =
effective_cache_size too, to be =
accurate.
 
=
 
regards,
 
fabio =
pardi
 
 




On 20/07/18 13:14, 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: 
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_00D6_01D42035.6E81E8B0--