Re: repeated out of shared memory error - not related to max_locks_per_transaction

Hi Alfonso,

Normally we see the out of shared memory errors comes due to the disk space
/ kernel parameters issues. And it seems your kernel parameters have
default values.

Verify disk space or set kernel parameters based on your storage RAM size.

<http://www.shreeyansh.com>

On Fri, Jul 20, 2018 at 4:44 PM, Alfonso Moscato <
alfonso(dot)moscato(at)merqurio(dot)it> 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
>
>
>
>