Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fgVSF-0006lH-IF for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 13:28:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgVSB-0001R8-NL for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 13:28:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fgVSB-0001R1-Ek for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 13:28:15 +0000 Received: from mout.perfora.net ([74.208.4.196]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fgVS8-0005uq-7H for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 13:28:14 +0000 Received: from [192.168.1.101] ([98.169.215.140]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LkRJt-1gI2zl2P51-00cNys; Fri, 20 Jul 2018 15:28:06 +0200 Message-ID: <5B51E362.1050700@sqlexec.com> Date: Fri, 20 Jul 2018 09:28:02 -0400 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Fabio Pardi CC: pgsql-admin@lists.postgresql.org Subject: Re: repeated out of shared memory error - not related to max_locks_per_transaction References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it> <5B51D72B.3040204@sqlexec.com> <5a94cee9-c60b-8635-2c48-98de7637e87a@portavita.eu> <5B51DCD9.4000701@sqlexec.com> <21a6bece-e927-fe85-31d3-6f06cc0ed4ff@portavita.eu> In-Reply-To: <21a6bece-e927-fe85-31d3-6f06cc0ed4ff@portavita.eu> Content-Type: multipart/alternative; boundary="------------040007060400000405080202" X-Provags-ID: V03:K1:dVsKmw/MBE0StoqmbrGAKH1Zef6QJv1kMy7WCAy4P1Tm3qoCesb 1yR31S+7IvAV37tW2kr+bcnsv9qWTjPp+3wP0eeSDKXA0wcFp1V1EljSBx9Sy4bmI1VtZsI 64eVRJbFalWvX6UbHwQkiR6oleOdLNu141bY6c8RmvAHsPp2hjHJRX6ljW7EXG9cyRwpVng F5degZpfibCXGAhqF72Yw== X-UI-Out-Filterresults: notjunk:1;V01:K0:73d4VolH54E=:/vIEWIb6PF/SDQAIGaUnlc BqoAIIKh8SQJlB02K846i5xb6FlFGjD2aL2dCli8jrHBRPsDn9mcEBQkwnVJGYxMecq02YyTQ K+jU5KLlpoUee2KwVH7dRwBxLJA5/xhnbWnRtzLQdKMzTOfoXTKjxMqxEooJFLXfoboqBiwuJ qw1WICO07E38he17StAbg0S0/BWWqmXU1KxIoGGi5syz3H0IZ6IiDKQ2z59fqCCUlc8IF558m 4VV+d1RGIIinydKlOEL8l1nfU6TrZ/pSHyf76fgRmHTDeIVvz3dGUCa1eKrjnpFTbmUK/SRQE BJwHAag2Q4tgPg/w0s7sGb8t1kisuD5drjfII03shq5CreVt691Qgfqz1wXuv7g7nthW7069R fEJb2Pc1Ts4K2RHNaKsyUt4GKTsqSy81NjDpGpGD3BKD4pfUlI2/IqUaRHD+5qfX/sIZYoRT6 3V5GT7ScmKv+vBjy23FUixMgMpT+zBd0K4ts3jJbM21URVotNoKf2VGFC5FkVlXYfOGZY0gYM hksjNXuBn7TNhO4SLFtMtKx+zEJ22kok/wFXjaqNMFoHVx+kcujSjOIZv256HE4MtIzBdviLj HNY/FPPf8/BlAzZ+qFaJGxOK4HMdweSS+rixKDP82YWSS7ETdm/WW9GMWYeZLUd9r71THoC96 vZLFpr73ouTkbWHvuWxQkr2w4vlHcc/BMToPUHGbp94/Ef4alK1R6JeP7vo/keE1W7klMJjVt NOo5bh/fejYt7tiH List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------040007060400000405080202 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 > 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 > 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 > 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 > 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 > > --------------040007060400000405080202 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit 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
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:

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



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:

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


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



--------------040007060400000405080202--