From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Alfonso Moscato <alfonso.moscato@merqurio.it>
Cc: Fabio Pardi <f.pardi@portavita.eu>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction
Date: Fri, 20 Jul 2018 10:37:31 -0400
Message-ID: <5B51F3AB.1050608@sqlexec.com> (raw)
In-Reply-To: <015801d42035$2fed3fa0$8fc7bee0$@merqurio.it>
References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it>
<C6379276-7F3F-4C53-AD89-BEB59B430169@illinois.edu>
<5B51D72B.3040204@sqlexec.com>
<5a94cee9-c60b-8635-2c48-98de7637e87a@portavita.eu>
<5B51DCD9.4000701@sqlexec.com>
<21a6bece-e927-fe85-31d3-6f06cc0ed4ff@portavita.eu>
<5B51E362.1050700@sqlexec.com>
<0468ca31-f6cc-dc4a-9ce6-42b4da927b06@portavita.eu>
<015801d42035$2fed3fa0$8fc7bee0$@merqurio.it>
coolio, work_mem is a red herring here. I think me and Fabio are on the
same page now since his last elaboration about work_mem.
Regards,
Michael Vitale
> Alfonso Moscato <mailto:alfonso.moscato@merqurio.it>
> Friday, July 20, 2018 10:23 AM
>
> 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:
>
> Fabio Pardi <mailto:f.pardi@portavita.eu>
> Friday, July 20, 2018 9:56 AM
>
> 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:
>
> 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
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, alfonso.moscato@merqurio.it, f.pardi@portavita.eu, pgsql-admin@lists.postgresql.org
Subject: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction
In-Reply-To: <5B51F3AB.1050608@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox