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. 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

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