R: repeated out of shared memory error - not related to max_locks_per_transaction - Mailing list pgsql-admin

Hi Fabio,

thanks for your answer.

The output of sysctl is:

kernel.sem = 32000      1024000000      500     32000

kernel.sem_next_id = -1

kernel.sg-big-buff = 32768

kernel.shm_next_id = -1

kernel.shm_rmid_forced = 0

kernel.shmall = 18446744073692774399

kernel.shmmax = 18446744073692774399

kernel.shmmni = 4096

effective_cache_size 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 = 4096

max seg size (kbytes) = 18014398509465599

max total shared memory (kbytes) = 18014398442373116

min seg size (bytes) = 1

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

Regards

Alfonso Moscato

Hi Alfonso,

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

If i recall correctly, this setting is not OK:

max number of segments = 4096

are you referrring to 'kernel.shmall', right?

SHMALL should always be at least shmmax/PAGE_SIZE

also, some other settings are not quite ok, like:

max seg size (kbytes) = 18014398509465599

max total shared memory (kbytes) = 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

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 = 200

shared_buffers = 23GB

effective_cache_size = 69GB

maintenance_work_mem = 2GB

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 100

random_page_cost = 4

effective_io_concurrency = 2

work_mem = 60293kB

min_wal_size = 2GB

max_wal_size = 4GB

max_worker_processes = 4

max_parallel_workers_per_gather = 2

max_locks_per_transaction = 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 = 4096

max seg size (kbytes) = 18014398509465599

max total shared memory (kbytes) = 18014398442373116

min seg size (bytes) = 1

thanks