From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Fabio Pardi <f.pardi@portavita.eu>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: repeated out of shared memory error - not related to max_locks_per_transaction
Date: Fri, 20 Jul 2018 09:51:24 -0400
Message-ID: <5B51E8DC.3050606@sqlexec.com> (raw)
In-Reply-To: <5B51E362.1050700@sqlexec.com>
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>
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
>
>
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, f.pardi@portavita.eu, pgsql-admin@lists.postgresql.org
Subject: Re: repeated out of shared memory error - not related to max_locks_per_transaction
In-Reply-To: <5B51E8DC.3050606@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