From: Alfonso Moscato <alfonso.moscato@merqurio.it>
To: 'Shreeyansh Dba' <shreeyansh2014@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: R: repeated out of shared memory error - not related to max_locks_per_transaction
Date: Fri, 20 Jul 2018 17:40:32 +0200
Message-ID: <019f01d4203f$fef2a3d0$fcd7eb70$@merqurio.it> (raw)
In-Reply-To: <CAGDYbUMBhAuezw9X1WBMgFDReW=w63a4MuEGzXodzNCs6j5LSw@mail.gmail.com>
References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it>
<CAGDYbUP-+krXkuF7=LLiwOzB8xtjfOAJsqG4aXKYO4mg7ESV4A@mail.gmail.com>
<CAGDYbUMBhAuezw9X1WBMgFDReW=w63a4MuEGzXodzNCs6j5LSw@mail.gmail.com>
Definitely disk space is not an issue:
File system 1K-blocchi Usati Disponib. Uso% Montato su
udev 46388372 0 46388372 0% /dev
tmpfs 9281376 9396 9271980 1% /run
/dev/sda1 30830588 1801120 27440324 7% /
tmpfs 46406864 4 46406860 1% /dev/shm
tmpfs 5120 0 5120 0% /run/lock
tmpfs 46406864 0 46406864 0% /sys/fs/cgroup
/dev/sdd1 464323324 71361120 369352916 17% /var
/dev/sdb2 29049840 45008 27506108 1% /tmp
tmpfs 9281376 0 9281376 0% /run/user/1005
tmpfs 9281376 0 9281376 0% /run/user/1000
I will try to set differently shmem limits
Thanks
alfonso
Da: Shreeyansh Dba <shreeyansh2014@gmail.com>
Inviato: venerdì 20 luglio 2018 17:18
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
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@gmail.com <mailto:shreeyansh2014@gmail.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.moscato@merqurio.it <mailto:alfonso.moscato@merqurio.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alfonso.moscato@merqurio.it, shreeyansh2014@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction
In-Reply-To: <019f01d4203f$fef2a3d0$fcd7eb70$@merqurio.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox