I did. This is a dedicated server. root@pg:/var/log/postgresql# ipcs -pm ------ Shared Memory Creator/Last-op PIDs -------- shmid proprietario cpid lpid 131072 postgres 12879 15299 root@pg:/var/log/postgresql# ipcs -m ------ Segm. Memoria Condivisa -------- chiave shmid proprietario perms byte nattch stato 0x0052e2c1 131072 postgres 600 56 83 -----Messaggio originale----- Da: 066ce286@xxxxxxx <066ce286@xxxxxxx> Inviato: venerdì 20 luglio 2018 14:01 A: pgsql-admin@xxxxxxxxxxxxxxxxxxxx Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction Hi, Try to run ipcs -m (and also ipcs -pm) To check who and wich process is using the shm. ----- Mail original ----- De: "Alfonso Moscato" <alfonso.moscato@xxxxxxxxxxx> À: pgsql-admin@xxxxxxxxxxxxxxxxxxxx Envoyé: Vendredi 20 Juillet 2018 13:14:53 Objet: repeated out of shared memory error - not related to max_locks_per_transaction 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
