Thread: repeated out of shared memory error - not related to max_locks_per_transaction
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
Re: repeated out of shared memory error - not related tomax_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@merqurio.it> À: pgsql-admin@lists.postgresql.org 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
Re: repeated out of shared memory error - not related tomax_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
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
R: repeated out of shared memory error - not related to max_locks_per_transaction
Hi Fabio,
thanks for your answer.
The output of sysctl is:
kernel.sem = 32000 1024000000 500 32000
kernel.sem_next_id = -1
kernel.sg-big-buff = 32768
kernel.shm_next_id = -1
kernel.shm_rmid_forced = 0
kernel.shmall = 18446744073692774399
kernel.shmmax = 18446744073692774399
kernel.shmmni = 4096
effective_cache_size is 69GB (it was at the third row of my post)
this is the output of ipcs -lm:
------ Limiti della memoria condivisa --------
max number of segments = 4096
max seg size (kbytes) = 18014398509465599
max total shared memory (kbytes) = 18014398442373116
min seg size (bytes) = 1
I agree they sould be bytes, as the number is very big, but ipcs says kbytes.
Regards
Alfonso Moscato
Da: Fabio Pardi <f.pardi@portavita.eu>
Inviato: venerdì 20 luglio 2018 14:07
A: pgsql-admin@lists.postgresql.org
Oggetto: 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 figurethe reason.We have postgresql "PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu9.6.9-2.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.020160609, 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) andsome long queries run every half an hour.Everything works fine, except that after 1 day and half we start receiving alot 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 receivethese messages we have no more than 50/100 locks totally.Restarting the server usually works fine for one day and hal more, and thenmessages restart.Looking at the log, we see that this error starts casually, sometimes onvery small queries, returning some kbytes of data.We have tried a lot of different configurations. we have tried with pgtuneand pgconfig 2.0.Currently, we have:max_connections = 200shared_buffers = 23GBeffective_cache_size = 69GBmaintenance_work_mem = 2GBcheckpoint_completion_target = 0.9wal_buffers = 16MBdefault_statistics_target = 100random_page_cost = 4effective_io_concurrency = 2work_mem = 60293kBmin_wal_size = 2GBmax_wal_size = 4GBmax_worker_processes = 4max_parallel_workers_per_gather = 2max_locks_per_transaction = 384but we tried with work_mem to 130mb, shared_buffer to a maximum fo 40gb,effective_cache to 4gbshared memory limits are very big:max number of segments = 4096max seg size (kbytes) = 18014398509465599max total shared memory (kbytes) = 18014398442373116min seg size (bytes) = 1thanks
R: repeated out of shared memory error - not related to max_locks_per_transaction
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@free.fr <066ce286@free.fr> Inviato: venerdì 20 luglio 2018 14:01 A: pgsql-admin@lists.postgresql.org 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@merqurio.it> À: pgsql-admin@lists.postgresql.org 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, withsome 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_transactionto 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
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of sharedbuffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available forPostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files inmemory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with theSQL planning. Double check the documentation. Lance Sent from my iPad > On Jul 20, 2018, at 6:15 AM, Alfonso Moscato <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 > > >
Re: repeated out of shared memory error - not related to max_locks_per_transaction
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AMI would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
R: repeated out of shared memory error - not related to max_locks_per_transaction
I experimented a lot with shared buffers, but I haven't seen any difference. With shared_buffers set to 16 gb I start seeing errors after one day and half, and with 40gb they start exactly at the same moment. One would expect that if something is consuming shared memory, setting it to more than double should make some difference, but this isn't the case. Alfonso -----Messaggio originale----- Da: Campbell, Lance <lance@illinois.edu> Inviato: venerdì 20 luglio 2018 14:33 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 I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning. Double check the documentation. Lance Sent from my iPad > On Jul 20, 2018, at 6:15 AM, Alfonso Moscato <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 > > >
R: repeated out of shared memory error - not related to max_locks_per_transaction
I thought it could have be.
Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half.
Regards
alfonso
Da: MichaelDBA <MichaelDBA@sqlexec.com>
Inviato: venerdì 20 luglio 2018 14:36
A: Campbell, Lance <lance@illinois.edu>
Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AM
I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: R: repeated out of shared memory error - not related to max_locks_per_transaction
Friday, July 20, 2018 8:39 AM<!-- /* Font Definitions */ @font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",sans-serif;color:black;} a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;} p.msonormal0, li.msonormal0, div.msonormal0{mso-style-name:msonormal;mso-margin-top-alt:auto;margin-right:0cm;mso-margin-bottom-alt:auto;margin-left:0cm;font-size:11.0pt;font-family:"Calibri",sans-serif;color:black;} span.StileMessaggioDiPostaElettronica18{mso-style-type:personal-reply;font-family:"Calibri",sans-serif;color:windowtext;} .MsoChpDefault{mso-style-type:export-only;font-size:10.0pt;} @page WordSection1{size:612.0pt 792.0pt;margin:70.85pt 2.0cm 2.0cm 2.0cm;} div.WordSection1{page:WordSection1;} --> I thought it could have be.
Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half.
Regards
alfonso
Da: MichaelDBA <MichaelDBA@sqlexec.com>
Inviato: venerdì 20 luglio 2018 14:36
A: Campbell, Lance <lance@illinois.edu>
Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:35 AMPerhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AMI would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
R: R: repeated out of shared memory error - not related to max_locks_per_transaction
I took the setting from pgtune:
https://pgtune.leopard.in.ua/#/
Anyway, I will try to further reduce it.
It is high because we have a very complex database, queries with a lot of tables, and million of records.
Furthermore, I see a lot of free memory.
Da: MichaelDBA <MichaelDBA@sqlexec.com>
Inviato: venerdì 20 luglio 2018 14:40
A: Alfonso Moscato <alfonso.moscato@merqurio.it>
Cc: 'Campbell, Lance' <lance@illinois.edu>; pgsql-admin@lists.postgresql.org
Oggetto: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction
Try reducing it further just to make sure.
Friday, July 20, 2018 8:39 AM
I thought it could have be.
Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half.
Regards
alfonso
Da: MichaelDBA <MichaelDBA@sqlexec.com>
Inviato: venerdì 20 luglio 2018 14:36
A: Campbell, Lance <lance@illinois.edu>
Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:35 AM
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael VitaleFriday, July 20, 2018 8:32 AM
I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
work_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AMI would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: repeated out of shared memory error - not related to max_locks_per_transaction
Regards,
Michael Vitale
Friday, July 20, 2018 8:47 AMwork_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:Friday, July 20, 2018 8:35 AMPerhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AMI would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: R: repeated out of shared memory error - not related tomax_locks_per_transaction
What are your values for : autovacuum_max_workers max_prepared_transactions block_size wal_block_size ? ----- Mail original ----- De: "Alfonso Moscato" <alfonso.moscato@merqurio.it> À: "MichaelDBA" <MichaelDBA@sqlexec.com>, "Lance' 'Campbell" <lance@illinois.edu> Cc: pgsql-admin@lists.postgresql.org Envoyé: Vendredi 20 Juillet 2018 14:39:01 Objet: R: repeated out of shared memory error - not related to max_locks_per_transaction I thought it could have be. Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half. Regards alfonso Da: MichaelDBA <MichaelDBA@sqlexec.com> Inviato: venerdì 20 luglio 2018 14:36 A: Campbell, Lance <lance@illinois.edu> Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem. Regards, Michael Vitale Campbell, Lance Friday, July 20, 2018 8:32 AM I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of sharedbuffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQLto run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory.So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQLplanning. Double check the documentation. Lance Sent from my iPad
R: R: repeated out of shared memory error - not related to max_locks_per_transaction
autovacuum_max_workers = 3 max_prepared_transactions = 0 block_size = 8192 wal_block_size = 8192 -----Messaggio originale----- Da: 066ce286@free.fr <066ce286@free.fr> Inviato: venerdì 20 luglio 2018 15:07 A: Alfonso Moscato <alfonso.moscato@merqurio.it> Cc: pgsql-admin@lists.postgresql.org Oggetto: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction What are your values for : autovacuum_max_workers max_prepared_transactions block_size wal_block_size ? ----- Mail original ----- De: "Alfonso Moscato" <alfonso.moscato@merqurio.it> À: "MichaelDBA" <MichaelDBA@sqlexec.com>, "Lance' 'Campbell" <lance@illinois.edu> Cc: pgsql-admin@lists.postgresql.org Envoyé: Vendredi 20 Juillet 2018 14:39:01 Objet: R: repeated out of shared memory error - not related to max_locks_per_transaction I thought it could have be. Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half. Regards alfonso Da: MichaelDBA <MichaelDBA@sqlexec.com> Inviato: venerdì 20 luglio 2018 14:36 A: Campbell, Lance <lance@illinois.edu> Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem. Regards, Michael Vitale Campbell, Lance Friday, July 20, 2018 8:32 AM I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of sharedbuffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQLto run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory.So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQLplanning. Double check the documentation. Lance Sent from my iPad
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
Nope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
I do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael Vitale
Friday, July 20, 2018 8:47 AMwork_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:Friday, July 20, 2018 8:35 AMPerhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AMI would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: repeated out of shared memory error - not related to max_locks_per_transaction
When you consume more memory than is available on your machine you can start to see out of
out of memory
errors within your Postgres logs, or in worse cases the OOM killer can start to randomly kill running processes to free up memory. An out of memory error in Postgres simply errors on the query you’re running, where as the the OOM killer in linux begins killing running processes which in some cases might even include Postgres itself.When you see an
out of memory
error you either want to increase the overall RAM on the machine itself by upgrading to a larger instance OR you want to decrease the amount of memory that work_mem
uses. Yes, you read that right: out-of-memory it’s better to decrease work_mem
instead of increase since that is the amount of memory that can be consumed by each process and too many operations are leveraging up to that much memory.https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/
Regards,
Michael Vitale
Friday, July 20, 2018 9:19 AMNope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
On 20/07/18 15:00, MichaelDBA wrote:Friday, July 20, 2018 9:00 AMI do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael Vitale
Friday, July 20, 2018 8:47 AMwork_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:Friday, July 20, 2018 8:35 AMPerhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AMI would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: repeated out of shared memory error - not related to max_locks_per_transaction
To show some perspective – Let's assume you have some queries that do 4-10 Sort/Hash operations. And you have 100 max_connections limit. This means that theoretically you can get yp to 1000 x work_mem usage! (100 connections, each running the 10-sort/hash query).
Above means, that while increasing work_mem is cool, you have to be sure not create situation when pg will try to allocate more memory than there physically is. A lot of “out of memory" errors reported to PostgreSQL mailing lists came from users which set their work_mem to large values like 512MB or more, and then ran some complex queries. On machine with (relatively) small amount of ram.
Friday, July 20, 2018 9:28 AMwrong again, Fabio. PostgreSQL is not coded to manage memory usage in the way you think it does with work_mem. Here is a quote from Citus about the dangers of setting work_mem too high.
When you consume more memory than is available on your machine you can start to see out ofout of memory
errors within your Postgres logs, or in worse cases the OOM killer can start to randomly kill running processes to free up memory. An out of memory error in Postgres simply errors on the query you’re running, where as the the OOM killer in linux begins killing running processes which in some cases might even include Postgres itself.
When you see anout of memory
error you either want to increase the overall RAM on the machine itself by upgrading to a larger instance OR you want to decrease the amount of memory thatwork_mem
uses. Yes, you read that right: out-of-memory it’s better to decreasework_mem
instead of increase since that is the amount of memory that can be consumed by each process and too many operations are leveraging up to that much memory.
https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/
Regards,
Michael VitaleFriday, July 20, 2018 9:19 AMNope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
On 20/07/18 15:00, MichaelDBA wrote:Friday, July 20, 2018 9:00 AMI do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael Vitale
Friday, July 20, 2018 8:47 AMwork_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:Friday, July 20, 2018 8:35 AMPerhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
Michael,
I think we are talking about 2 different scenarios.
1) the single operation is using more than work_mem -> gets spilled to disk. like: a big sort. That's what i mentioned.
2) there are many many concurrent operations, and one more of them wants to allocate work_mem but the memory on the server is exhausted at that point. -> in that case you will get 'out of memory'. That's what you are referring to.
Given the description of the problem (RAM and Postgres settings) and the fact that Alfonso says that "there is a lot of free memory" i think is unlikely that we are in the second situation described here above.
regards,
fabio pardi
wrong again, Fabio. PostgreSQL is not coded to manage memory usage in the way you think it does with work_mem. Here is a quote from Citus about the dangers of setting work_mem too high.
When you consume more memory than is available on your machine you can start to see out ofout of memory
errors within your Postgres logs, or in worse cases the OOM killer can start to randomly kill running processes to free up memory. An out of memory error in Postgres simply errors on the query you’re running, where as the the OOM killer in linux begins killing running processes which in some cases might even include Postgres itself.
When you see anout of memory
error you either want to increase the overall RAM on the machine itself by upgrading to a larger instance OR you want to decrease the amount of memory thatwork_mem
uses. Yes, you read that right: out-of-memory it’s better to decreasework_mem
instead of increase since that is the amount of memory that can be consumed by each process and too many operations are leveraging up to that much memory.
https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/
Regards,
Michael VitaleFriday, July 20, 2018 9:19 AMNope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
On 20/07/18 15:00, MichaelDBA wrote:Friday, July 20, 2018 9:00 AMI do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael Vitale
Friday, July 20, 2018 8:47 AMwork_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:Friday, July 20, 2018 8:35 AMPerhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael Vitale
Friday, July 20, 2018 8:32 AMI would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: repeated out of shared memory error - not related to max_locks_per_transaction
"Alfonso Moscato" <alfonso.moscato@merqurio.it> writes: > We are getting crazy with "out of shared memory" errors, and we can't figure > the reason. I don't think any of the advice posted so far has anything to do with your problem --- certainly, fooling with work_mem is unrelated. PG shared memory is a fixed-size arena (for any one setting of shared_buffers, max_connections, max_locks_per_transaction, and a couple other variables) and most of its contents are pre-allocated at postmaster start. What you are describing sounds like a long-term leak of additional, post-startup shmem allocations, eventually running out of the available slop in the shmem arena. work_mem, and other user-visible knobs, have nothing to do with this because those control allocations in process private memory not shmem. I'm pretty sure that the *only* post-startup shmem allocations in the core code are for lock table entries. However, if you're running any non-core extensions, it's possible that one of them does such allocations and has a logic error that results in a shmem leak. As an amelioration measure, you could raise max_locks_per_transaction, which will increase the arena size without actually eating any additional space immediately at startup. That might not cure the problem, but at least it would increase the interval at which you have to restart the server. As for real solutions, I'd first look harder at the question of how many lock table entries you need. The fact that you only see a few dozen active entries when you look (after a failure) doesn't prove a thing about what the max transient requirement is. Do you have any applications that touch a whole lot of tables in a single transaction? Are you using any user-defined (advisory) locks, and if so what's the usage pattern like for those? The "bug in an extension" theory also needs investigation. regards, tom lane
R: repeated out of shared memory error - not related to max_locks_per_transaction
Michael, Fabio,
moreover, i get the message “Out of shared memory”, not “out of memory”.
Anyway, I can confirm that when errors began there where more than 10gb of free memory.
Regards
Alfonso
Da: Fabio Pardi <f.pardi@portavita.eu>
Inviato: venerdì 20 luglio 2018 15:57
A: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Michael,
I think we are talking about 2 different scenarios.
1) the single operation is using more than work_mem -> gets spilled to disk. like: a big sort. That's what i mentioned.
2) there are many many concurrent operations, and one more of them wants to allocate work_mem but the memory on the server is exhausted at that point. -> in that case you will get 'out of memory'. That's what you are referring to.
Given the description of the problem (RAM and Postgres settings) and the fact that Alfonso says that "there is a lot of free memory" i think is unlikely that we are in the second situation described here above.
regards,
fabio pardi
On 20/07/18 15:28, MichaelDBA wrote:
wrong again, Fabio. PostgreSQL is not coded to manage memory usage in the way you think it does with work_mem. Here is a quote from Citus about the dangers of setting work_mem too high.
When you consume more memory than is available on your machine you can start to see out ofout of memory
errors within your Postgres logs, or in worse cases the OOM killer can start to randomly kill running processes to free up memory. An out of memory error in Postgres simply errors on the query you’re running, where as the the OOM killer in linux begins killing running processes which in some cases might even include Postgres itself.
When you see anout of memory
error you either want to increase the overall RAM on the machine itself by upgrading to a larger instance OR you want to decrease the amount of memory thatwork_mem
uses. Yes, you read that right: out-of-memory it’s better to decreasework_mem
instead of increase since that is the amount of memory that can be consumed by each process and too many operations are leveraging up to that much memory.
https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/
Regards,
Michael VitaleFriday, July 20, 2018 9:19 AM
Nope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
On 20/07/18 15:00, MichaelDBA wrote:
Friday, July 20, 2018 9:00 AM
I do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael VitaleFriday, July 20, 2018 8:47 AM
work_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:
Friday, July 20, 2018 8:35 AM
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael VitaleFriday, July 20, 2018 8:32 AM
I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: R: repeated out of shared memory error - not related to max_locks_per_transaction
Regards,
Michael Vitale
Friday, July 20, 2018 10:23 AM<!-- /* Font Definitions */ @font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face{font-family:Lato;panose-1:2 15 5 2 2 2 4 3 2 3;} @font-face{font-family:"Courier 10 Pitch";panose-1:0 0 0 0 0 0 0 0 0 0;} @font-face{font-family:"Courier 10\000D\000A";panose-1:0 0 0 0 0 0 0 0 0 0;} @font-face{font-family:"Courier\000D\000A 10";panose-1:0 0 0 0 0 0 0 0 0 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",sans-serif;color:black;} a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;} code{mso-style-priority:99;font-family:"Courier New";} tt{mso-style-priority:99;font-family:"Courier New";} p.msonormal0, li.msonormal0, div.msonormal0{mso-style-name:msonormal;mso-margin-top-alt:auto;margin-right:0cm;mso-margin-bottom-alt:auto;margin-left:0cm;font-size:11.0pt;font-family:"Calibri",sans-serif;color:black;} span.StileMessaggioDiPostaElettronica21{mso-style-type:personal-reply;font-family:"Calibri",sans-serif;color:windowtext;} .MsoChpDefault{mso-style-type:export-only;font-size:10.0pt;} @page WordSection1{size:612.0pt 792.0pt;margin:70.85pt 2.0cm 2.0cm 2.0cm;} div.WordSection1{page:WordSection1;} --> Michael, Fabio,
moreover, i get the message “Out of shared memory”, not “out of memory”.
Anyway, I can confirm that when errors began there where more than 10gb of free memory.
Regards
Alfonso
Da: Fabio Pardi <f.pardi@portavita.eu>
Inviato: venerdì 20 luglio 2018 15:57
A: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Michael,
I think we are talking about 2 different scenarios.
1) the single operation is using more than work_mem -> gets spilled to disk. like: a big sort. That's what i mentioned.
2) there are many many concurrent operations, and one more of them wants to allocate work_mem but the memory on the server is exhausted at that point. -> in that case you will get 'out of memory'. That's what you are referring to.
Given the description of the problem (RAM and Postgres settings) and the fact that Alfonso says that "there is a lot of free memory" i think is unlikely that we are in the second situation described here above.
regards,fabio pardi
On 20/07/18 15:28, MichaelDBA wrote:
Friday, July 20, 2018 9:56 AMMichael,
I think we are talking about 2 different scenarios.
1) the single operation is using more than work_mem -> gets spilled to disk. like: a big sort. That's what i mentioned.
2) there are many many concurrent operations, and one more of them wants to allocate work_mem but the memory on the server is exhausted at that point. -> in that case you will get 'out of memory'. That's what you are referring to.
Given the description of the problem (RAM and Postgres settings) and the fact that Alfonso says that "there is a lot of free memory" i think is unlikely that we are in the second situation described here above.
regards,fabio pardi
On 20/07/18 15:28, MichaelDBA wrote:Friday, July 20, 2018 9:28 AMwrong again, Fabio. PostgreSQL is not coded to manage memory usage in the way you think it does with work_mem. Here is a quote from Citus about the dangers of setting work_mem too high.
When you consume more memory than is available on your machine you can start to see out ofout of memory
errors within your Postgres logs, or in worse cases the OOM killer can start to randomly kill running processes to free up memory. An out of memory error in Postgres simply errors on the query you’re running, where as the the OOM killer in linux begins killing running processes which in some cases might even include Postgres itself.
When you see anout of memory
error you either want to increase the overall RAM on the machine itself by upgrading to a larger instance OR you want to decrease the amount of memory thatwork_mem
uses. Yes, you read that right: out-of-memory it’s better to decreasework_mem
instead of increase since that is the amount of memory that can be consumed by each process and too many operations are leveraging up to that much memory.
https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/
Regards,
Michael VitaleFriday, July 20, 2018 9:19 AMNope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
On 20/07/18 15:00, MichaelDBA wrote:Friday, July 20, 2018 9:00 AMI do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael Vitale
R: repeated out of shared memory error - not related to max_locks_per_transaction
Hi Tom, i agree with you, there is something allocating and not releasing memory. This because the error starts after many hours. I have some extensions installed, namely: "plpgsql" "mysql_fdw" "multicorn" "pg_cron" "tds_fdw" "pg_stat_statements" I have 200 connections and 384 max_locks_per_transaction in postgresql.conf, that brings the ttal number og locks to 76.800. I have surely very big queries, some with more than 50 tables, subqueries, and so on. As for your questions: 1) lock table entries needed: we check regularly the number of locks. It doesn't seem they usually exceed some hundreds. I will create a cron to check them regularly. 2) We have some very complex functions, touching many tables and returning 1/2 million rows 3) we don't use any kind of personalised locks. The db is set to read_committed isolation level. Just two question: How could I understand which extension is consuming shared memory? I can't figure how. Is there some way other than increasing max_locks_per_transaction to drive postgres to allocate more shared memory? -----Messaggio originale----- Da: Tom Lane <tgl@sss.pgh.pa.us> Inviato: venerdì 20 luglio 2018 16:23 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 "Alfonso Moscato" <alfonso.moscato@merqurio.it> writes: > We are getting crazy with "out of shared memory" errors, and we can't > figure the reason. I don't think any of the advice posted so far has anything to do with your problem --- certainly, fooling with work_mem is unrelated. PG shared memory is a fixed-size arena (for any one setting of shared_buffers, max_connections, max_locks_per_transaction, and a couple other variables) and most of its contents are pre-allocated at postmaster start. What you are describing sounds like a long-term leak of additional, post-startup shmem allocations, eventually running out of the available slop in the shmem arena. work_mem, and other user-visible knobs, have nothing to do with this because those control allocations in process private memory not shmem. I'm pretty sure that the *only* post-startup shmem allocations in the core code are for lock table entries. However, if you're running any non-core extensions, it's possible that one of them does such allocations and has a logic error that results in a shmem leak. As an amelioration measure, you could raise max_locks_per_transaction, which will increase the arena size without actually eating any additional space immediately at startup. That might not cure the problem, but at least it would increase the interval at which you have to restart the server. As for real solutions, I'd first look harder at the question of how many lock table entries you need. The fact that you only see a few dozen active entries when you look (after a failure) doesn't prove a thing about what the max transient requirement is. Do you have any applications that touch a whole lot of tables in a single transaction? Are you using any user-defined (advisory) locks, and if so what's the usage pattern like for those? The "bug in an extension" theory also needs investigation. regards, tom lane
R: repeated out of shared memory error - not related to max_locks_per_transaction
Hi Evan,
I have tried shared_buffers from 8gb to 40gb, and max_locks_per_transaction from 128 to 384.
I don’t see relevant differences: errors start more or less after one day and half.
Regards
alfonso
Da: Evan Bauer <evanbauer@mac.com>
Inviato: venerdì 20 luglio 2018 16:40
A: Alfonso Moscato <alfonso.moscato@merqurio.it>
Cc: Fabio Pardi <f.pardi@portavita.eu>; MichaelDBA <MichaelDBA@sqlexec.com>; pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Alfonso,
There was a lot of buzz when 9.6 came out around getting the “Out of shared memory” message due to virtual lock space consumption. I see that you have set a high value (max_locks_per_transaction = 384), but have you tried adjusting it to see if that either increases or decreases the amount of time before the messages appear and you have to restart pg?
Regards,
- Evan
Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer
On Jul 20, 2018, at 10:23, Alfonso Moscato <alfonso.moscato@merqurio.it> wrote:
Michael, Fabio,
moreover, i get the message “Out of shared memory”, not “out of memory”.
Anyway, I can confirm that when errors began there where more than 10gb of free memory.
Regards
Alfonso
Da: Fabio Pardi <f.pardi@portavita.eu>
Inviato: venerdì 20 luglio 2018 15:57
A: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Michael,
I think we are talking about 2 different scenarios.
1) the single operation is using more than work_mem -> gets spilled to disk. like: a big sort. That's what i mentioned.
2) there are many many concurrent operations, and one more of them wants to allocate work_mem but the memory on the server is exhausted at that point. -> in that case you will get 'out of memory'. That's what you are referring to.
Given the description of the problem (RAM and Postgres settings) and the fact that Alfonso says that "there is a lot of free memory" i think is unlikely that we are in the second situation described here above.
regards,fabio pardi
On 20/07/18 15:28, MichaelDBA wrote:
wrong again, Fabio. PostgreSQL is not coded to manage memory usage in the way you think it does with work_mem. Here is a quote from Citus about the dangers of setting work_mem too high.
When you consume more memory than is available on your machine you can start to see out ofout of memory
errors within your Postgres logs, or in worse cases the OOM killer can start to randomly kill running processes to free up memory. An out of memory error in Postgres simply errors on the query you’re running, where as the the OOM killer in linux begins killing running processes which in some cases might even include Postgres itself.
When you see anout of memory
error you either want to increase the overall RAM on the machine itself by upgrading to a larger instance OR you want to decrease the amount of memory thatwork_mem
uses. Yes, you read that right: out-of-memory it’s better to decreasework_mem
instead of increase since that is the amount of memory that can be consumed by each process and too many operations are leveraging up to that much memory.
https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/
Regards,
Michael VitaleFriday, July 20, 2018 9:19 AM
Nope Michael,
if 'stuff' gets spilled to disk does not end up in an error. It will silently write a file to disk for the time being and then deleted it when your operation is finished.
period.
Based on your log settings, it might appear in the logs, under 'temporary file created..'.
regards,
fabio pardi
On 20/07/18 15:00, MichaelDBA wrote:
Friday, July 20, 2018 9:00 AM
I do not think that is true. Stuff just gets spilled to disk when the work_mem buffers would exceed the work_mem constraint. They are not constrained by what real memory is available, hence the memory error! They will try to get memory even if it is not available as long as work_mem buffers threshold is not reached.
Regards,
Michael VitaleFriday, July 20, 2018 8:47 AM
work_mem cannot be the cause of it for the simple reason that if the memory needed by your query overflows work_mem, it will spill to disk
regards,
fabio pardi
On 20/07/18 14:35, MichaelDBA wrote:
Friday, July 20, 2018 8:35 AM
Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem.
Regards,
Michael VitaleFriday, July 20, 2018 8:32 AM
I would also lookup the definition of shared buffers and effective cache. If I remember correctly you can think of shared buffers as how much memory total PostgreSQL has to work with. Effective cache is how much memory is available for PostgreSQL to run, shared buffers, as well as an estimate of how much memory is available to the OS to cache files in memory. So effective cache should be equal to or larger than shared buffers. Effective cache is used to help with the SQL planning.
Double check the documentation.
Lance
Sent from my iPad
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
What is the systems memory settings (cat /proc/meminfo)? How much swap space is allocated to the system? And more importantlywhat’s the Commit Limit?
R: repeated out of shared memory error - not related to max_locks_per_transaction
Hi Rui. root@pg:/etc/postgresql/9.6/main# cat /proc/meminfo MemTotal: 92813728 kB MemFree: 21016632 kB MemAvailable: 80814496 kB Buffers: 196328 kB Cached: 67843352 kB SwapCached: 0 kB Active: 67040236 kB Inactive: 3374184 kB Active(anon): 7700380 kB Inactive(anon): 2937896 kB Active(file): 59339856 kB Inactive(file): 436288 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 3905532 kB SwapFree: 3905532 kB Dirty: 496 kB Writeback: 0 kB AnonPages: 2374804 kB Mapped: 6664260 kB Shmem: 8263524 kB Slab: 696048 kB SReclaimable: 658492 kB SUnreclaim: 37556 kB KernelStack: 4464 kB PageTables: 422148 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 50312396 kB Committed_AS: 27622712 kB VmallocTotal: 34359738367 kB VmallocUsed: 0 kB VmallocChunk: 0 kB HardwareCorrupted: 0 kB AnonHugePages: 0 kB CmaTotal: 0 kB CmaFree: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB DirectMap4k: 81856 kB DirectMap2M: 94289920 kB -----Messaggio originale----- Da: Rui DeSousa <rui@crazybean.net> Inviato: venerdì 20 luglio 2018 16:45 A: Alfonso Moscato <alfonso.moscato@merqurio.it> Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction What is the systems memory settings (cat /proc/meminfo)? How much swap space is allocated to the system? And more importantlywhat’s the Commit Limit?
Re: repeated out of shared memory error - not related to max_locks_per_transaction
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.
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
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
Alfonso, Do you happened to know how large the process is when it starts giving error messages, anywhere near 47GB? — but we tried with work_mem to 130mb, shared_buffer to a maximum fo 40gb, Having shared_buffer to 40GB would be two high for the current configuration and I would expect to see out of memory errorsgiven that your system commit limit is 47GB; However, with shared_buffers at 23GB I don’t think you should be hittingthe 47GB limit — are you? To increase the commit limit you can add more swap and/or adjust the overcommit ratio.
Re: R: repeated out of shared memory error - not related to max_locks_per_transaction
"Alfonso Moscato" <alfonso.moscato@merqurio.it> writes: > I have some extensions installed, namely: > "plpgsql" > "mysql_fdw" > "multicorn" > "pg_cron" > "tds_fdw" > "pg_stat_statements" plpgsql doesn't use any shared memory. pg_stat_statements does but it's a one-time, fixed-size allocation. The others you mention perhaps deserve investigation; I'm not familiar with their innards. The fact that the problem seems to be related to the passage of time and not to any particular application action might perhaps lead one to suspect pg_cron. But that's a leap of logic unsupported by any facts. > I have 200 connections and 384 max_locks_per_transaction in postgresql.conf, > that brings the ttal number og locks to 76.800. Right, or more accurately, enough shmem space for that many locks will be reserved at postmaster start. > I have surely very big queries, some with more than 50 tables, subqueries, > and so on. Well, 50-table queries are not your problem, no matter how much data they process. Transactions that touch, say, 100000 different tables might be your problem. I've heard of people having to raise max_locks_per_transaction in order to run pg_dump against databases with lots of tables, for example. > As for your questions: > 1) lock table entries needed: we check regularly the number of locks. It > doesn't seem they usually exceed some hundreds. I will create a cron to > check them regularly. You would really need to catch the state of the lock table just when the error is reported --- a check even a few seconds before that might not show any distress. > How could I understand which extension is consuming shared memory? I can't > figure how. AFAIK there isn't any good way except to check their source code for ShmemAlloc* and ShmemInit* calls. > Is there some way other than increasing max_locks_per_transaction to drive > postgres to allocate more shared memory? That's the only knob that wouldn't also produce a matching increase in the size of postmaster-startup allocations. There isn't any real downside to raising it; the only effect is to increase the size of the shmem segment. regards, tom lane
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
Hi, On 2018-07-20 13:14:53 +0200, Alfonso Moscato wrote: > We are getting crazy with "out of shared memory" errors, and we can't figure > the reason. What makes them "crazy"? Could you please give the exact error message and statement it ran? Are any of your sessions running with serializable isolation? Greetings, Andres Freund
Re: repeated out of shared memory error - not related to max_locks_per_transaction
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.
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.On Fri, Jul 20, 2018 at 4:44 PM, Alfonso Moscato <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
R: repeated out of shared memory error - not related to max_locks_per_transaction
Hi andres, I said crazy beecause when it starts, there are many of them every few seconds, and the only way to stop them is to restart postgres. All of our sessions run in read_committed mode. Regards Alfonso -----Messaggio originale----- Da: Andres Freund <andres@anarazel.de> Inviato: venerdì 20 luglio 2018 17:14 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 Hi, On 2018-07-20 13:14:53 +0200, Alfonso Moscato wrote: > We are getting crazy with "out of shared memory" errors, and we can't > figure the reason. What makes them "crazy"? Could you please give the exact error message and statement it ran? Are any of your sessions running with serializable isolation? Greetings, Andres Freund
R: repeated out of shared memory error - not related to max_locks_per_transaction
Hi Rui, we have 100 forked postgres processes, I don't know how to determine if we are hitting the 47Gb limit. Any suggestion? -----Messaggio originale----- Da: Rui DeSousa <rui@crazybean.net> Inviato: venerdì 20 luglio 2018 17:03 A: Alfonso Moscato <alfonso.moscato@merqurio.it> Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction Alfonso, Do you happened to know how large the process is when it starts giving error messages, anywhere near 47GB? — but we tried with work_mem to 130mb, shared_buffer to a maximum fo 40gb, Having shared_buffer to 40GB would be two high for the current configuration and I would expect to see out of memory errorsgiven that your system commit limit is 47GB; However, with shared_buffers at 23GB I don’t think you should be hittingthe 47GB limit — are you? To increase the commit limit you can add more swap and/or adjust the overcommit ratio.
R: repeated out of shared memory error - not related to max_locks_per_transaction
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.
On Fri, Jul 20, 2018 at 8:27 PM, Shreeyansh Dba <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.
On Fri, Jul 20, 2018 at 4:44 PM, Alfonso Moscato <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
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
Just using the common tools like htop or (ps aux); specifically VIRT column in htop or VSZ column in ps. Basically, howmuch virtual memory is allocated to the process (the total address space of the process). > On Jul 20, 2018, at 11:38 AM, Alfonso Moscato <alfonso.moscato@merqurio.it> wrote: > > Hi Rui, > we have 100 forked postgres processes, I don't know how to determine if we are hitting the 47Gb limit. > Any suggestion? > > > -----Messaggio originale----- > Da: Rui DeSousa <rui@crazybean.net> > Inviato: venerdì 20 luglio 2018 17:03 > A: Alfonso Moscato <alfonso.moscato@merqurio.it> > Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org > Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction > > > Alfonso, > > Do you happened to know how large the process is when it starts giving error messages, anywhere near 47GB? > > — but we tried with work_mem to 130mb, shared_buffer to a maximum fo 40gb, > > Having shared_buffer to 40GB would be two high for the current configuration and I would expect to see out of memory errorsgiven that your system commit limit is 47GB; However, with shared_buffers at 23GB I don’t think you should be hittingthe 47GB limit — are you? > > To increase the commit limit you can add more swap and/or adjust the overcommit ratio. > > > > > >
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
Just using the common tools like htop or (ps aux); specifically VIRT column in htop or VSZ column in ps. Basically, how much virtual memory is allocated to the process (the total address space of the process). > On Jul 20, 2018, at 11:38 AM, Alfonso Moscato wrote: > > Hi Rui, > we have 100 forked postgres processes, I don't know how to determine if we are hitting the 47Gb limit. > Any suggestion? > > > -----Messaggio originale----- > Da: Rui DeSousa > Inviato: venerdì 20 luglio 2018 17:03 > A: Alfonso Moscato > Cc: Tom Lane ; pgsql-admin@lists.postgresql.org > Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction > > > Alfonso, > > Do you happened to know how large the process is when it starts giving error messages, anywhere near 47GB? > > — but we tried with work_mem to 130mb, shared_buffer to a maximum fo 40gb, > > Having shared_buffer to 40GB would be two high for the current configuration and I would expect to see out of memory errors given that your system commit limit is 47GB; However, with shared_buffers at 23GB I don’t think you should be hitting the 47GB limit — are you? > > To increase the commit limit you can add more swap and/or adjust the overcommit ratio. > > > > > >
Re: repeated out of shared memory error - not relatedto max_locks_per_transaction
This thread went cold very quickly … we are experiencing the exact same symptoms on one of our test systems. Was a solution found or are the investigations / work ongoing ?
We are happy to provide any information that may be useful for our case.
David Morton
Senior Platform Engineer
EROAD Level 3, 260 Oteha Valley Road, Albany, Auckland, New Zealand PO Box 305 394, Triton Plaza, North Shore 0757, Auckland, NZ |
This message is for the named person's use only. It may contain confidential, privileged, private or proprietary information. No confidentiality is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print or copy any part of this message if you are not the intended recipient. EROAD Limited reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of EROAD Limited. To the maximum extent permitted by law, EROAD Limited is not liable (including in negligence) for viruses or other defects or for changes made to this email or to any attachments. Before opening or using attachments, please check them for viruses and other defects.
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
On Jul 29, 2018, at 10:31 PM, David Morton <david.morton@eroad.com> wrote:This thread went cold very quickly … we are experiencing the exact same symptoms on one of our test systems. Was a solution found or are the investigations / work ongoing ?We are happy to provide any information that may be useful for our case.
Re: repeated out of shared memory error - not related tomax_locks_per_transaction
Hi David,
while symptoms might be the same, the cause is likely to be different.
If I were you, I would open a new thread, and properly describe your problem and your setup.
regards,
fabio pardi
This thread went cold very quickly … we are experiencing the exact same symptoms on one of our test systems. Was a solution found or are the investigations / work ongoing ?
We are happy to provide any information that may be useful for our case.
.ExternalClass * {line-height: 100%} .ExternalClass, .ExternalClass p, .ExternalClass span, .ExternalClass font, .ExternalClass td, .ExternalClass div { line-height: 100%;} p {margin:0; padding:0; margin-bottom:0;}
David Morton
Senior Platform Engineer
EROAD
Level 3, 260 Oteha Valley Road, Albany, Auckland, New Zealand
PO Box 305 394, Triton Plaza, North Shore 0757, Auckland, NZ
This message is for the named person's use only. It may contain confidential, privileged, private or proprietary information. No confidentiality is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print or copy any part of this message if you are not the intended recipient. EROAD Limited reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of EROAD Limited. To the maximum extent permitted by law, EROAD Limited is not liable (including in negligence) for viruses or other defects or for changes made to this email or to any attachments. Before opening or using attachments, please check them for viruses and other defects.
R: repeated out of shared memory error - not related to max_locks_per_transaction
I’ve not given up. I’m just collecting more data to better understand the root cause.
I was suggested to thorough investigate extensions.
Just to recap, I started with (I am including here only relevant data, you can find the complete configuration in the first email) max_locks_per_transaction at 384, 92GB of RAM, 23Gb of shared buffers, and
"plpgsql"
"mysql_fdw"
"multicorn" (ldap access)
"pg_cron"
"tds_fdw"
"pg_stat_statements"
Since then, I removed tds_fdw and pg_stat_statements, and I stopped all the queries using mysql_fdw. Multicorn refreshes a materialized view every 30 minutes, and so I don’t think it could be responsible of a continuous increase, minute after minute.
We are working to install pgagent and remove pg_cron too.
Then, I’ve been collecting shared memory usage every minute.
I see a constant increase of shared memory, more or less 200k every minute. When all users stop it decreases and sometimes it stops, but then it restarts without a clear reason.
AFTER removing pg_stat_statements and increasing max_locks_per_transaction to 512 we haven’t experienced anymore “out of shared memory” messages.
I don’t know if I’ve just postponed the moment they will start, or if something is really changed (I’m inclined to not be too optimist, as the increase continues)
As for now we are at
lun 30 lug 2018, 11.07.01, CEST | Shmem: 20255156 kB
You can see here shared memory increase during last Friday morning:
ven 27 lug 2018, 10.50.01, CEST | Shmem: 19767352 kB
ven 27 lug 2018, 10.49.01, CEST | Shmem: 19767136 kB
ven 27 lug 2018, 10.48.01, CEST | Shmem: 19766320 kB
ven 27 lug 2018, 10.47.01, CEST | Shmem: 19766208 kB
ven 27 lug 2018, 10.46.01, CEST | Shmem: 19765952 kB
ven 27 lug 2018, 10.45.01, CEST | Shmem: 19765792 kB
ven 27 lug 2018, 10.44.01, CEST | Shmem: 19765616 kB
ven 27 lug 2018, 10.43.01, CEST | Shmem: 19765480 kB
ven 27 lug 2018, 10.42.01, CEST | Shmem: 19765408 kB
ven 27 lug 2018, 10.41.01, CEST | Shmem: 19765232 kB
ven 27 lug 2018, 10.40.01, CEST | Shmem: 19765072 kB
ven 27 lug 2018, 10.39.01, CEST | Shmem: 19764920 kB
ven 27 lug 2018, 10.38.01, CEST | Shmem: 19764768 kB
ven 27 lug 2018, 10.37.01, CEST | Shmem: 19764576 kB
ven 27 lug 2018, 10.36.01, CEST | Shmem: 19764520 kB
ven 27 lug 2018, 10.35.01, CEST | Shmem: 19764312 kB
ven 27 lug 2018, 10.34.01, CEST | Shmem: 19764112 kB
ven 27 lug 2018, 10.33.01, CEST | Shmem: 19763984 kB
ven 27 lug 2018, 10.32.01, CEST | Shmem: 19763976 kB
ven 27 lug 2018, 10.31.01, CEST | Shmem: 19763976 kB
ven 27 lug 2018, 10.30.01, CEST | Shmem: 19763560 kB
ven 27 lug 2018, 10.29.01, CEST | Shmem: 19763424 kB
ven 27 lug 2018, 10.28.01, CEST | Shmem: 19763224 kB
ven 27 lug 2018, 10.27.01, CEST | Shmem: 19763144 kB
ven 27 lug 2018, 10.26.01, CEST | Shmem: 19763080 kB
ven 27 lug 2018, 10.25.01, CEST | Shmem: 19762920 kB
ven 27 lug 2018, 10.24.01, CEST | Shmem: 19762776 kB
ven 27 lug 2018, 10.23.02, CEST | Shmem: 19762648 kB
ven 27 lug 2018, 10.22.02, CEST | Shmem: 19762296 kB
ven 27 lug 2018, 10.21.01, CEST | Shmem: 19762056 kB
ven 27 lug 2018, 10.20.01, CEST | Shmem: 19761848 kB
ven 27 lug 2018, 10.19.01, CEST | Shmem: 19761576 kB
ven 27 lug 2018, 10.18.01, CEST | Shmem: 19761488 kB
ven 27 lug 2018, 10.17.01, CEST | Shmem: 19761408 kB
ven 27 lug 2018, 10.16.01, CEST | Shmem: 19761900 kB
ven 27 lug 2018, 10.15.01, CEST | Shmem: 19761680 kB
ven 27 lug 2018, 10.14.01, CEST | Shmem: 19760544 kB
ven 27 lug 2018, 10.13.01, CEST | Shmem: 19760472 kB
ven 27 lug 2018, 10.12.01, CEST | Shmem: 19760336 kB
ven 27 lug 2018, 10.11.01, CEST | Shmem: 19760240 kB
ven 27 lug 2018, 10.10.01, CEST | Shmem: 19760168 kB
ven 27 lug 2018, 10.09.01, CEST | Shmem: 19760024 kB
ven 27 lug 2018, 10.08.01, CEST | Shmem: 19759232 kB
ven 27 lug 2018, 10.07.01, CEST | Shmem: 19759096 kB
ven 27 lug 2018, 10.06.01, CEST | Shmem: 19758848 kB
ven 27 lug 2018, 10.05.01, CEST | Shmem: 19758664 kB
ven 27 lug 2018, 10.04.01, CEST | Shmem: 19758528 kB
ven 27 lug 2018, 10.03.01, CEST | Shmem: 19758328 kB
ven 27 lug 2018, 10.02.01, CEST | Shmem: 19758040 kB
ven 27 lug 2018, 10.01.01, CEST | Shmem: 19757840 kB
ven 27 lug 2018, 10.00.01, CEST | Shmem: 19757696 kB
ven 27 lug 2018, 09.59.01, CEST | Shmem: 19757656 kB
ven 27 lug 2018, 09.58.01, CEST | Shmem: 19757488 kB
ven 27 lug 2018, 09.57.01, CEST | Shmem: 19757376 kB
ven 27 lug 2018, 09.56.01, CEST | Shmem: 19757240 kB
ven 27 lug 2018, 09.55.01, CEST | Shmem: 19757184 kB
ven 27 lug 2018, 09.54.01, CEST | Shmem: 19757152 kB
ven 27 lug 2018, 09.53.01, CEST | Shmem: 19746856 kB
ven 27 lug 2018, 09.52.01, CEST | Shmem: 19740408 kB
ven 27 lug 2018, 09.51.01, CEST | Shmem: 19739976 kB
ven 27 lug 2018, 09.50.01, CEST | Shmem: 19739848 kB
ven 27 lug 2018, 09.49.01, CEST | Shmem: 19739584 kB
ven 27 lug 2018, 09.48.01, CEST | Shmem: 19739480 kB
ven 27 lug 2018, 09.47.01, CEST | Shmem: 19739344 kB
ven 27 lug 2018, 09.46.01, CEST | Shmem: 19739152 kB
ven 27 lug 2018, 09.45.01, CEST | Shmem: 19739096 kB
ven 27 lug 2018, 09.44.01, CEST | Shmem: 19739072 kB
ven 27 lug 2018, 09.43.01, CEST | Shmem: 19739000 kB
ven 27 lug 2018, 09.42.01, CEST | Shmem: 19738920 kB
ven 27 lug 2018, 09.41.01, CEST | Shmem: 19738792 kB
ven 27 lug 2018, 09.40.01, CEST | Shmem: 19738600 kB
ven 27 lug 2018, 09.39.01, CEST | Shmem: 19738464 kB
ven 27 lug 2018, 09.38.01, CEST | Shmem: 19738384 kB
ven 27 lug 2018, 09.37.01, CEST | Shmem: 19738136 kB
ven 27 lug 2018, 09.36.01, CEST | Shmem: 19738008 kB
ven 27 lug 2018, 09.35.01, CEST | Shmem: 19737872 kB
ven 27 lug 2018, 09.34.01, CEST | Shmem: 19737824 kB
ven 27 lug 2018, 09.33.01, CEST | Shmem: 19737728 kB
ven 27 lug 2018, 09.32.01, CEST | Shmem: 19737600 kB
ven 27 lug 2018, 09.31.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.30.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.29.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.28.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.27.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.26.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.25.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.24.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.23.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.22.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.21.01, CEST | Shmem: 19732200 kB
ven 27 lug 2018, 09.20.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.19.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.18.02, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.17.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.16.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.15.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.14.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.13.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.12.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.11.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.10.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.09.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.08.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.07.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.06.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.05.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.04.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.03.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.02.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.01.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 09.00.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.59.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.58.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.57.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.56.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.55.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.54.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.53.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.52.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.51.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.50.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.49.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.48.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.47.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.46.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.45.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.44.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.43.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.42.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.41.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.40.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.39.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.38.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.37.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.36.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.35.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.34.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.33.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.32.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.31.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.30.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.29.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.28.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.27.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.26.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.25.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.24.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.23.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.22.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.21.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.20.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.19.01, CEST | Shmem: 19724008 kB
ven 27 lug 2018, 08.18.01, CEST | Shmem: 19724008 kB
Regards
Alfonso
Da: David Morton <david.morton@eroad.com>
Inviato: lunedì 30 luglio 2018 04:32
A: Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>; Rui DeSousa <rui@crazybean.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction
This thread went cold very quickly … we are experiencing the exact same symptoms on one of our test systems. Was a solution found or are the investigations / work ongoing ?
We are happy to provide any information that may be useful for our case.
David Morton
Senior Platform Engineer
MOB +64 21 544 503 EMAIL david.morton@eroad.com
| EROAD
|
This message is for the named person's use only. It may contain confidential, privileged, private or proprietary information. No confidentiality is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print or copy any part of this message if you are not the intended recipient. EROAD Limited reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorised to state them to be the views of EROAD Limited. To the maximum extent permitted by law, EROAD Limited is not liable (including in negligence) for viruses or other defects or for changes made to this email or to any attachments. Before opening or using attachments, please check them for viruses and other defects.