pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

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:00:09 -0400
Message-ID: <5B51DCD9.4000701@sqlexec.com> (raw)
In-Reply-To: <5a94cee9-c60b-8635-2c48-98de7637e87a@portavita.eu>
References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it>
	<C6379276-7F3F-4C53-AD89-BEB59B430169@illinois.edu>
	<5B51D72B.3040204@sqlexec.com>
	<5a94cee9-c60b-8635-2c48-98de7637e87a@portavita.eu>

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
>
>

view thread (22+ messages)  latest in thread

Message-ID: <5B51DCD9.4000701@sqlexec.com>
Permalink:  ../5B51DCD9.4000701@sqlexec.com/
Also on:    postgresql.org/message-id/5B51DCD9.4000701@sqlexec.com

copy link · copy postgr.es

reply

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: <5B51DCD9.4000701@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