pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrepeated out of shared memory error - not related to max_locks_per_transaction
22+ messages / 11 participants
[nested] [flat]
* repeated out of shared memory error - not related to max_locks_per_transaction
@ 2018-07-20 11:14 Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:01 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction 066ce286@free.fr
2018-07-20 12:07 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:57 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-07-20 15:14 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Andres Freund <andres@anarazel.de>
0 siblings, 6 replies; 22+ messages in thread
From: Alfonso Moscato @ 2018-07-20 11:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
@ 2018-07-20 12:01 ` 066ce286@free.fr
5 siblings, 0 replies; 22+ messages in thread
From: 066ce286@free.fr @ 2018-07-20 12:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
@ 2018-07-20 12:07 ` Fabio Pardi <f.pardi@portavita.eu>
5 siblings, 0 replies; 22+ messages in thread
From: Fabio Pardi @ 2018-07-20 12:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 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
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
@ 2018-07-20 12:32 ` Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
5 siblings, 1 reply; 22+ messages in thread
From: Campbell, Lance @ 2018-07-20 12:32 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
@ 2018-07-20 12:35 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 12:47 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
0 siblings, 1 reply; 22+ messages in thread
From: MichaelDBA @ 2018-07-20 12:35 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 <mailto:lance@illinois.edu>
> 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
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
@ 2018-07-20 12:47 ` Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:00 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 22+ messages in thread
From: Fabio Pardi @ 2018-07-20 12:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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:
> 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 <mailto:lance@illinois.edu>
>> 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
>>
>>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 12:47 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
@ 2018-07-20 13:00 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:19 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
0 siblings, 1 reply; 22+ messages in thread
From: MichaelDBA @ 2018-07-20 13:00 UTC (permalink / raw)
To: Fabio Pardi <f.pardi@portavita.eu>; +Cc: pgsql-admin@lists.postgresql.org
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
> Fabio Pardi <mailto:f.pardi@portavita.eu>
> Friday, 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:
>
> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
> 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 Vitale
>
>
> Campbell, Lance <mailto:lance@illinois.edu>
> 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
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 12:47 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:00 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
@ 2018-07-20 13:19 ` Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:28 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 22+ messages in thread
From: Fabio Pardi @ 2018-07-20 13:19 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org
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:
> 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
>
>
>> Fabio Pardi <mailto:f.pardi@portavita.eu>
>> Friday, 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:
>>
>> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
>> 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 Vitale
>>
>>
>> Campbell, Lance <mailto:lance@illinois.edu>
>> 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
>>
>>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 12:47 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:00 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:19 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
@ 2018-07-20 13:28 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:51 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:56 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
0 siblings, 2 replies; 22+ messages in thread
From: MichaelDBA @ 2018-07-20 13:28 UTC (permalink / raw)
To: Fabio Pardi <f.pardi@portavita.eu>; +Cc: pgsql-admin@lists.postgresql.org
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 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
> Fabio Pardi <mailto:f.pardi@portavita.eu>
> Friday, 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:
>
> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
> 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 Vitale
>
>
>
> Fabio Pardi <mailto:f.pardi@portavita.eu>
> Friday, 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:
>
> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
> 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 Vitale
>
>
> Campbell, Lance <mailto:lance@illinois.edu>
> 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
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 12:47 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:00 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:19 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:28 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
@ 2018-07-20 13:51 ` MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 0 replies; 22+ messages in thread
From: MichaelDBA @ 2018-07-20 13:51 UTC (permalink / raw)
To: Fabio Pardi <f.pardi@portavita.eu>; +Cc: pgsql-admin@lists.postgresql.org
Here is another quote from depesz about the dangers of setting work_mem
too high...
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.
> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
> Friday, July 20, 2018 9:28 AM
> 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 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
>
> Fabio Pardi <mailto:f.pardi@portavita.eu>
> Friday, 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:
>
> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
> 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 Vitale
>
>
>
> Fabio Pardi <mailto:f.pardi@portavita.eu>
> Friday, 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:
>
> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
> 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 Vitale
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:32 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 12:47 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:00 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:19 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:28 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction MichaelDBA <MichaelDBA@sqlexec.com>
@ 2018-07-20 13:56 ` Fabio Pardi <f.pardi@portavita.eu>
1 sibling, 0 replies; 22+ messages in thread
From: Fabio Pardi @ 2018-07-20 13:56 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org
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 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
>> Fabio Pardi <mailto:f.pardi@portavita.eu>
>> Friday, 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:
>>
>> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
>> 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 Vitale
>>
>>
>>
>> Fabio Pardi <mailto:f.pardi@portavita.eu>
>> Friday, 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:
>>
>> MichaelDBA <mailto:MichaelDBA@sqlexec.com>
>> 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 Vitale
>>
>>
>> Campbell, Lance <mailto:lance@illinois.edu>
>> 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
>>
>>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
@ 2018-07-20 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
5 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2018-07-20 14:22 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: pgsql-admin@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
@ 2018-07-20 14:44 ` Rui DeSousa <rui@crazybean.net>
2018-07-20 15:02 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 22+ messages in thread
From: Rui DeSousa @ 2018-07-20 14:44 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
What is the systems memory settings (cat /proc/meminfo)? How much swap space is allocated to the system? And more importantly what’s the Commit Limit?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
@ 2018-07-20 15:02 ` Rui DeSousa <rui@crazybean.net>
2018-07-20 16:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
0 siblings, 1 reply; 22+ messages in thread
From: Rui DeSousa @ 2018-07-20 15:02 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 15:02 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
@ 2018-07-20 16:35 ` Rui DeSousa <rui@crazybean.net>
2018-07-21 16:11 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>
0 siblings, 1 reply; 22+ messages in thread
From: Rui DeSousa @ 2018-07-20 16:35 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
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 <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 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.
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 15:02 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 16:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
@ 2018-07-21 16:11 ` Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>
2018-07-30 02:31 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction David Morton <david.morton@eroad.com>
0 siblings, 1 reply; 22+ messages in thread
From: Alfonso Moscato (Merqurio) @ 2018-07-21 16:11 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
Ah yes. You meant virt. It’s around 32gb. On Monday I ‘ll be more precise Regards
On Fri, Jul 20, 2018 at 6:36 PM +0200, "Rui DeSousa" <rui@crazybean.net> wrote:
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.
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 15:02 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 16:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-21 16:11 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>
@ 2018-07-30 02:31 ` David Morton <david.morton@eroad.com>
2018-07-30 03:28 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-30 07:56 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Fabio Pardi <f.pardi@portavita.eu>
0 siblings, 2 replies; 22+ messages in thread
From: David Morton @ 2018-07-30 02:31 UTC (permalink / raw)
To: 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 <pgsql-admin@lists.postgresql.org>
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
Level 3, 260 Oteha Valley Road, Albany, Auckland, New Zealand
PO Box 305 394, Triton Plaza, North Shore 0757, Auckland, NZ
TEL +64 9 927 4700 | TOLL FREE 0800 4 EROAD | www.eroad.co.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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 15:02 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 16:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-21 16:11 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>
2018-07-30 02:31 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction David Morton <david.morton@eroad.com>
@ 2018-07-30 03:28 ` Rui DeSousa <rui@crazybean.net>
1 sibling, 0 replies; 22+ messages in thread
From: Rui DeSousa @ 2018-07-30 03:28 UTC (permalink / raw)
To: David Morton <david.morton@eroad.com>; +Cc: Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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.
>
What’s the configuration of your system including /proc/meminfo. =
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:22 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 15:02 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-20 16:35 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Rui DeSousa <rui@crazybean.net>
2018-07-21 16:11 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>
2018-07-30 02:31 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction David Morton <david.morton@eroad.com>
@ 2018-07-30 07:56 ` Fabio Pardi <f.pardi@portavita.eu>
1 sibling, 0 replies; 22+ messages in thread
From: Fabio Pardi @ 2018-07-30 07:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 problemand your setup.
regards,
fabio pardi
On 30/07/18 04:31, David Morton 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.
>
>
>
>
> *David Morton*
> Senior Platform Engineer
>
> MOB +64 21 544 503 EMAIL david.morton@eroad.com <mailto:david.morton@eroad.com>
>
>
>
> EROAD Logo <https://www.eroad.co.nz?utm_source=email-signature&utm_medium=logo;
>
> *EROAD*
> Level 3, 260 Oteha Valley Road, Albany, Auckland, New Zealand
> PO Box 305 394, Triton Plaza, North Shore 0757, Auckland, NZ
>
> TEL +64 9 927 4700 TOLL FREE 0800 4 EROAD
> www.eroad.co.nz <https://www.eroad.co.nz?utm_source=email-signature&utm_medium=text-link; - Twitter <http://www.twitter.com/EROADGlobal; - LinkedIn <https://www.linkedin.com/company/234629;
>
>
> 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.
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
@ 2018-07-20 14:57 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-07-20 15:18 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Shreeyansh Dba <shreeyansh2014@gmail.com>
5 siblings, 1 reply; 22+ messages in thread
From: Shreeyansh Dba @ 2018-07-20 14:57 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: pgsql-admin@lists.postgresql.org
Hi Alfonso,
Normally we see the out of shared memory errors comes due to the disk space
/ kernel parameters issues. And it seems your kernel parameters have
default values.
Verify disk space or set kernel parameters based on your storage RAM size.
<http://www.shreeyansh.com;
On Fri, Jul 20, 2018 at 4:44 PM, Alfonso Moscato <
alfonso.moscato@merqurio.it> 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
>
>
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 14:57 ` Re: repeated out of shared memory error - not related to max_locks_per_transaction Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2018-07-20 15:18 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Shreeyansh Dba @ 2018-07-20 15:18 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: pgsql-admin@lists.postgresql.org
In addition to this.
We have faced these out of shared memory error in one of our customer
environment where we observed the same below values:
max seg size (kbytes) = 18014398509465599
max total shared memory (kbytes) = 18014398442373116
Due to this memory allocation not happened at the various
processes level which resulted to out of memory errors. And by setting the
new values as per our RAM resolved the out of shared memory error.
<http://www.shreeyansh.com;
On Fri, Jul 20, 2018 at 8:27 PM, Shreeyansh Dba <shreeyansh2014@gmail.com>
wrote:
> Hi Alfonso,
>
> Normally we see the out of shared memory errors comes due to the disk
> space / kernel parameters issues. And it seems your kernel parameters have
> default values.
>
> Verify disk space or set kernel parameters based on your storage RAM size.
>
>
>
> <http://www.shreeyansh.com;
>
> On Fri, Jul 20, 2018 at 4:44 PM, Alfonso Moscato <
> alfonso.moscato@merqurio.it> 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
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: repeated out of shared memory error - not related to max_locks_per_transaction
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
@ 2018-07-20 15:14 ` Andres Freund <andres@anarazel.de>
5 siblings, 0 replies; 22+ messages in thread
From: Andres Freund @ 2018-07-20 15:14 UTC (permalink / raw)
To: Alfonso Moscato <alfonso.moscato@merqurio.it>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2018-07-30 07:56 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-07-20 11:14 repeated out of shared memory error - not related to max_locks_per_transaction Alfonso Moscato <alfonso.moscato@merqurio.it>
2018-07-20 12:01 ` 066ce286@free.fr
2018-07-20 12:07 ` Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 12:32 ` Campbell, Lance <lance@illinois.edu>
2018-07-20 12:35 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 12:47 ` Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:00 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:19 ` Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 13:28 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:51 ` MichaelDBA <MichaelDBA@sqlexec.com>
2018-07-20 13:56 ` Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-07-20 14:44 ` Rui DeSousa <rui@crazybean.net>
2018-07-20 15:02 ` Rui DeSousa <rui@crazybean.net>
2018-07-20 16:35 ` Rui DeSousa <rui@crazybean.net>
2018-07-21 16:11 ` Alfonso Moscato (Merqurio) <alfonso.moscato@merqurio.it>
2018-07-30 02:31 ` David Morton <david.morton@eroad.com>
2018-07-30 03:28 ` Rui DeSousa <rui@crazybean.net>
2018-07-30 07:56 ` Fabio Pardi <f.pardi@portavita.eu>
2018-07-20 14:57 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-07-20 15:18 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-07-20 15:14 ` Andres Freund <andres@anarazel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox