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 1fgV1K-0005Kb-2B for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 13:00:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgV1G-00087j-QA for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 13:00:26 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fgV1G-00087V-Bn for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 13:00:26 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fgV19-0001uS-0F for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 13:00:25 +0000 Received: from [192.168.1.101] ([98.169.215.140]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0M7adN-1fuqdP3gcJ-00xN10; Fri, 20 Jul 2018 15:00:13 +0200 Message-ID: <5B51DCD9.4000701@sqlexec.com> Date: Fri, 20 Jul 2018 09:00:09 -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> In-Reply-To: <5a94cee9-c60b-8635-2c48-98de7637e87a@portavita.eu> Content-Type: multipart/alternative; boundary="------------050209020501060409060205" X-Provags-ID: V03:K1:53f69Z9INshRLdMb2RXnYzsvWktZ/+FCpA1Sco6xcHyPUareMNC 4jX++zqZQbW/1XZfSVl+a3fGASwsImWF5k2zdoFhCiKOw2/kmYU33Q5WGi/7GfL9RzwB2KU wg/AEY6bcaziIXqN9lsI81EWzyV2pzCyPIgcScR3qO0DioHW1bRikmdCyM+54Ao4T8V+XjG Dq/SpQYdU6YuPBsKTBZKQ== X-UI-Out-Filterresults: notjunk:1;V01:K0:6/qJ64aMlrQ=:R3ZAOOKKs7NH8da9So4jeP kZCiNim5hf75Rn4xNFS3LtdKC0BEY5JodmrTA+AWluC5PTgmS8XkVbXn/H0bDS0X9wcTo3x4P enUGuSY4mMFBL6DwuW6g4oBX13KUbM+oZNGHFieTIfUOmpi63MPbGTUsDsbcHcW+cbMu1hIOL 0oiOMGloTy0BbHqByujjtGtFt9QEpI2WC9O1s50D6JdKnGQgpf3HQ0zuNDs+px2oHFwC9A7XK 1W+B08tF+L4Xk0Z+DvDaBkDZ2YgVbqiE/b3+e3UqMSoX3WMq8/++Q88iHMcD7dtgwscAggG7w pTQEk9IOOV50dZNlp/0ovYlnfO6QuVh6fM+6mFVd6PPgT1xOPl8BITfucj3GyzANmxJqcbQlB mQOWM4X6I3BU53qbGZfm0Wq0JvAk6sVcyufCsIv6vHJO1O+Ke2yRDb8HcHyte5/M7fVkTxU0W 8KZHNe2KUEtka0QAffXP9BQkOwhYqSolq7TRWFF1SGqji/7mTB3FDDG2bYM6PqB5oEseDhw/F L8rVhEI4S8JhpvmzrJLWM9BKuxW8dgdqRdWGAd0UcF83WbfLpUaxaTajnMiE2XM/aPPqi+f+p ksbzV2/UxDrkEvgiHnsFGQIlErkXfevQGp5GvmYdW63aIVdNrBEGbXK8V2YHJ6ZEESOqN/7N5 I0Q0wsM8VnWViUUy14uUor/L7Vbkegihp7jW0aRH210jm2YnzGF1GrQ+IrhxIjB72dFo+7FBM nS0q+L+jYHvuNot0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------050209020501060409060205 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > > --------------050209020501060409060205 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit 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



--------------050209020501060409060205--