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 1fgUhm-0004Cj-96 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 12:40:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgUhk-0006K7-NK for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 12:40:16 +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 1fgUhk-0006Jx-GV for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 12:40:16 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fgUhh-0004oy-GL for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 12:40:16 +0000 Received: from [192.168.1.101] ([98.169.215.140]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0M2NAw-1fyzYW1FR6-00s7YD; Fri, 20 Jul 2018 14:40:04 +0200 Message-ID: <5B51D822.5040003@sqlexec.com> Date: Fri, 20 Jul 2018 08:40:02 -0400 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Alfonso Moscato CC: "'Campbell, Lance'" , pgsql-admin@lists.postgresql.org Subject: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it> <5B51D72B.3040204@sqlexec.com> <00f101d42026$a32e17a0$e98a46e0$@merqurio.it> In-Reply-To: <00f101d42026$a32e17a0$e98a46e0$@merqurio.it> Content-Type: multipart/alternative; boundary="------------000500060505000309030402" X-Provags-ID: V03:K1:/bfv2AmHmF6gn9o6qoF7UY5NyX4LIQvkQYI6K+DBfmTCTo+R0ze PpPwIOYZY1i9WhOlLb+3xi/CkQ6nV6q6pBhS7nvCJ7ePs6Kj+we3pV7LKHgMBSAGOke9C/2 M7wWi8rh+ygryFJV/spdXsmArmKBHn+12Papm99NorGmLwCzJ5ysBe1i1EmBMYhdryF8wC/ O9xIhqF9dpjKvK2efDIZw== X-UI-Out-Filterresults: notjunk:1;V01:K0:lgQ5Tnp4+Iw=:P52VFnvhTQRw+Eeso8tEoK EbJb9Zqvr1hr1qcSAukBLTQfFXU1/K05KIFBQj0LyjR94tpVXtPYlBbk7SsEWskOC5LLwIH+v t8R5H5GFrmrR84ftb2pg70Q0MfsqP7466Hh1wEd0XEPxyaBmobQ2jwCx7RKvt6o0ze5B25Ex9 XwahjrZXjWuc/wuankQHqr92c3cckBFvolN/faIHjycO/RlmBVj0oSjS3Dns1KCL9rpYVuVv3 c6GHEGrTa7lCmrXffT/o/7CtFCBu7N7fuBs1aXn5NYrVSR/zMwZAfaWUE51h6KmgMSCgmnSIt ISQQJ7mTmU1LuLtY9vdhT3Z3OFK5YoVlrbhg1Huvkgge30ar+ZnD9f/AaesuRI/vwDyPURBDK OnIWK3rwKnNPpyub0B/fxLen3JvBI39zMt7pp3wUflf5baYH0GhmbdsdhrFiM/mFD5C4LwnWO P3zsuLLP6MEH/PXsFTEAZvY2OtD67040L02tC5y91tsf1PokRBx1qNPITSt4BzqqHXkdZkjnr qxVZEsjRSHgxj+Ooo2FvcOcfVIX2wLkhp5EOuNMCsYXDmgNwvVWbczZPPTvW9/mfQf6iJlxAI IpJeODfFBNCD5+NbldSnBk1/2g9mIzJ8j2Z68hSAW9DJRqkAV605l3lhUHeszKTDsCeWTSwOs yDnCEUbEdD+Erv8Z2iDycXF+FDtKVpOlFb8O4iRJjbP2SETJmxEYC+EGLHC/w7EF0SU2TJyIf SPm6HGB1Hf2OYO0z List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------000500060505000309030402 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Try reducing it further just to make sure. > Alfonso Moscato > Friday, July 20, 2018 8:39 AM > > I thought it could have be. > > Work_mem was 131mb and I reduced it to 60mb. Errors keep starting > after one day and half. > > Regards > > alfonso > > *Da:*MichaelDBA > *Inviato:* venerd́ 20 luglio 2018 14:36 > *A:* Campbell, Lance > *Cc:* Alfonso Moscato ; > pgsql-admin@lists.postgresql.org > *Oggetto:* Re: repeated out of shared memory error - not related to > max_locks_per_transaction > > Perhaps your "work_mem" setting is causing the memory problems. Try > reducing it to see if that alleviates the problem. > > Regards, > Michael Vitale > > > 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 > > --------------000500060505000309030402 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Try reducing it further just to make sure.

Friday, July 20, 2018 8:39 AM

I thought it could have be.

Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after one day and half.

Regards

alfonso

 

Da: MichaelDBA <MichaelDBA@sqlexec.com>
Inviato: venerd́ 20 luglio 2018 14:36
A: Campbell, Lance <lance@illinois.edu>
Cc: Alfonso Moscato <alfonso.moscato@merqurio.it>; pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction

 

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



--------------000500060505000309030402--