From: "Alfonso Moscato" <alfonso.moscato@merqurio.it>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-admin@lists.postgresql.org>
References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it>
 <17662.1532096551@sss.pgh.pa.us>
In-Reply-To: <17662.1532096551@sss.pgh.pa.us>
Subject: R: repeated out of shared memory error - not related to
 max_locks_per_transaction
Date: Fri, 20 Jul 2018 16:38:21 +0200
Message-ID: <016801d42037$4f5d4630$ee17d290$@merqurio.it>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQJyTRksqbTjmmAqDGBi/OX9BtlRvgJEoSREo0msSmA=
Content-Language: it
Precedence: bulk

Hi Tom,
i agree with you, there is something allocating and not releasing =
memory.
This because the error starts after many hours.
I have some extensions installed, namely:
"plpgsql"
"mysql_fdw"
"multicorn"
"pg_cron"
"tds_fdw"
"pg_stat_statements"
I have 200 connections and 384 max_locks_per_transaction in =
postgresql.conf,
that brings the ttal number og locks to 76.800.
I have surely very big queries, some with more than 50 tables, =
subqueries,
and so on.
As for your questions:
1) lock table entries needed: we check regularly the number of locks. It
doesn't seem they usually exceed some hundreds. I will create a cron to
check them regularly.
2) We have some very complex functions, touching many tables and =
returning
1/2 million rows
3) we don't use any kind of personalised locks. The db is set to
read_committed isolation level.
Just two question:
How could I understand which extension is consuming shared memory? I =
can't
figure how.
Is there some way other than increasing max_locks_per_transaction to =
drive
postgres to allocate more shared memory?


-----Messaggio originale-----
Da: Tom Lane <tgl@sss.pgh.pa.us>=20
Inviato: venerd=EC 20 luglio 2018 16:23
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

"Alfonso Moscato" <alfonso.moscato@merqurio.it> writes:
> We are getting crazy with "out of shared memory" errors, and we can't=20
> figure the reason.

I don't think any of the advice posted so far has anything to do with =
your
problem --- certainly, fooling with work_mem is unrelated.  PG shared =
memory
is a fixed-size arena (for any one setting of shared_buffers,
max_connections, max_locks_per_transaction, and a couple other =
variables)
and most of its contents are pre-allocated at postmaster start.  What =
you
are describing sounds like a long-term leak of additional, post-startup
shmem allocations, eventually running out of the available slop in the =
shmem
arena.  work_mem, and other user-visible knobs, have nothing to do with =
this
because those control allocations in process private memory not shmem.

I'm pretty sure that the *only* post-startup shmem allocations in the =
core
code are for lock table entries.  However, if you're running any =
non-core
extensions, it's possible that one of them does such allocations and has =
a
logic error that results in a shmem leak.

As an amelioration measure, you could raise max_locks_per_transaction, =
which
will increase the arena size without actually eating any additional =
space
immediately at startup.  That might not cure the problem, but at least =
it
would increase the interval at which you have to restart the server.

As for real solutions, I'd first look harder at the question of how many
lock table entries you need.  The fact that you only see a few dozen =
active
entries when you look (after a failure) doesn't prove a thing about what =
the
max transient requirement is.  Do you have any applications that touch a
whole lot of tables in a single transaction?  Are you using any =
user-defined
(advisory) locks, and if so what's the usage pattern like for those?

The "bug in an extension" theory also needs investigation.

			regards, tom lane