From: "Alfonso Moscato" <alfonso.moscato@merqurio.it>
To: <066ce286@free.fr>,
	<pgsql-admin@lists.postgresql.org>
References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it>
 <1745624490.130025832.1532088079202.JavaMail.root@zimbra82-e14.priv.proxad.net>
In-Reply-To: 
 <1745624490.130025832.1532088079202.JavaMail.root@zimbra82-e14.priv.proxad.net>
Subject: R: repeated out of shared memory error - not related to
 max_locks_per_transaction
Date: Fri, 20 Jul 2018 14:30:54 +0200
Message-ID: <00e001d42025$80d2bb80$82783280$@merqurio.it>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQLNKJtprlyY8naH+IbwA/ikAIhNgaKl+ZMA
Content-Language: it
Precedence: bulk

I did.
This is a dedicated server.

root@pg:/var/log/postgresql# ipcs -pm

------ Shared Memory Creator/Last-op PIDs --------
shmid      proprietario cpid       lpid
131072     postgres   12879      15299

root@pg:/var/log/postgresql# ipcs -m

------ Segm. Memoria Condivisa --------
chiave     shmid      proprietario perms      byte       nattch     =
stato
0x0052e2c1 131072     postgres   600        56         83

-----Messaggio originale-----
Da: 066ce286@free.fr <066ce286@free.fr>=20
Inviato: venerd=C3=AC 20 luglio 2018 14:01
A: pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to =
max_locks_per_transaction

Hi,

Try to run

ipcs -m
(and also ipcs -pm)


To check who and wich process is using the shm.


----- Mail original -----
De: "Alfonso Moscato" <alfonso.moscato@merqurio.it>
=C3=80: pgsql-admin@lists.postgresql.org
Envoy=C3=A9: Vendredi 20 Juillet 2018 13:14:53
Objet: repeated out of shared memory error - not related to =
max_locks_per_transaction

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