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

In addition to this.

We have faced these out of shared memory error in one of our customer
environment where we observed the same below values:

max seg size (kbytes) = 18014398509465599
max total shared memory (kbytes) = 18014398442373116

Due to this memory allocation not happened at the various
processes level which resulted to out of memory errors. And by setting the
new values as per our RAM resolved the out of shared memory error.

<http://www.shreeyansh.com>

On Fri, Jul 20, 2018 at 8:27 PM, Shreeyansh Dba <shreeyansh2014(at)gmail(dot)com>
wrote:

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