From: "Alfonso Moscato" <alfonso.moscato@merqurio.it>
To: "'Campbell, Lance'" <lance@illinois.edu>
Cc: <pgsql-admin@lists.postgresql.org>
References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it>
 <C6379276-7F3F-4C53-AD89-BEB59B430169@illinois.edu>
In-Reply-To: <C6379276-7F3F-4C53-AD89-BEB59B430169@illinois.edu>
Subject: R: repeated out of shared memory error - not related to
 max_locks_per_transaction
Date: Fri, 20 Jul 2018 14:37:21 +0200
Message-ID: <00e201d42026$67f91450$37eb3cf0$@merqurio.it>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQJyTRksqbTjmmAqDGBi/OX9BtlRvgJ6PMbvo0ff/fA=
Content-Language: it
Precedence: bulk

I experimented a lot with shared buffers, but I haven't seen any =
difference.
With shared_buffers set to 16 gb I start seeing errors after one day and
half, and with 40gb they start exactly at the same moment.
One would expect that if something is consuming shared memory, setting =
it to
more than double should make some difference, but this isn't the case.
Alfonso

-----Messaggio originale-----
Da: Campbell, Lance <lance@illinois.edu>=20
Inviato: venerd=EC 20 luglio 2018 14:33
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

I would also lookup the definition of shared buffers and effective =
cache.
If I remember correctly you can think of shared buffers as how much =
memory
total PostgreSQL has to work with.  Effective cache is how much memory =
is
available for PostgreSQL to run, shared buffers, as well as an estimate =
of
how much memory is available to the OS to cache files in memory.  So
effective cache should be equal to or larger than shared buffers.  =
Effective
cache is used to help with the SQL planning.

Double check the documentation. =20

Lance

Sent from my iPad

> On Jul 20, 2018, at 6:15 AM, Alfonso Moscato =
<alfonso.moscato@merqurio.it>
wrote:
>=20
> We are getting crazy with "out of shared memory" errors, and we can't=20
> figure the reason.
> We have postgresql "PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu=20
> 9.6.9-2.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9)=20
> 5.4.0 20160609, 64-bit", the server has 92gb of ram, it is a mixed=20
> environment (mostly OLTP, with some DW), with 100 sessions constantly=20
> open (a CRM) and some long queries run every half an hour.
> Everything works fine, except that after 1 day and half we start=20
> receiving a lot of "out of shared memory" messages.
> I am sure it is not related with the usual max_locks_per_transaction=20
> issue, because we have set max_locks_per_transaction to 384, and when=20
> 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=20
> on very small queries, returning some kbytes of data.
> We have tried a lot of different configurations. we have tried with=20
> 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=20
> 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=20
> (kbytes) =3D 18014398442373116 min seg size (bytes) =3D 1
>=20
> thanks
>=20
>=20
>=20