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 1fgUoh-0004Zf-G1 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 12:47:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgUof-0003RU-25 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 12:47:25 +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 1fgUoW-0002io-A6 for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 12:47:16 +0000 Received: from smtp200-pc.aruba.it ([62.149.157.200] helo=smtpg-pc.aruba.it) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgUoR-00052A-Et for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 12:47:15 +0000 Received: from DT02 ([93.63.211.242]) by smtpcm2-pc.aruba.it with bizsmtp id D0n91y0095ELnP9010n9Kv; Fri, 20 Jul 2018 14:47:09 +0200 From: "Alfonso Moscato" To: "'MichaelDBA'" Cc: "'Campbell, Lance'" , References: <00a601d4201a$e2291a10$a67b4e30$@merqurio.it> <5B51D72B.3040204@sqlexec.com> <00f101d42026$a32e17a0$e98a46e0$@merqurio.it> <5B51D822.5040003@sqlexec.com> In-Reply-To: <5B51D822.5040003@sqlexec.com> Subject: R: R: repeated out of shared memory error - not related to max_locks_per_transaction Date: Fri, 20 Jul 2018 14:47:10 +0200 Message-ID: <010901d42027$c6838b30$538aa190$@merqurio.it> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_010A_01D42038.8A0F8F80" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJyTRksqbTjmmAqDGBi/OX9BtlRvgJ6PMbvAeA0gCoCJvsWUgGfuh+0oxqroJA= Content-Language: it DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aruba.it; s=a1; t=1532090829; bh=tt4jntoapQ03aBoGn0gzMUjdFkeNbsCaRqu1mUmE19Y=; h=From:To:Subject:Date:MIME-Version:Content-Type; b=AtNV6YM63jEkTtHbtbUJoutGPdAmLli0b7MdZRQxR0qyTFhYHJjFM9WHWLDYruTkX BnTEtlU/UTKwWq+C3CQU+HjusfdmtH1+C29lt7e1ovLRGV+ZjpMsvKGrXJzGYN+ofL vlU2YoTFIerDyQBqjXzFtanH8V8tYNXCjsSaCo5+ER4/kZDte/vmSsxPgaxpV9tNQY LGnBNpVO/6KQtTHPESIQEplyLLYIuJYOCFlw9Tvbsav6AFUgGBeGTKQVH6r48uqjvt T5nAhDLQUTPRW4akJKo9S9iKqqjn9+bfAz5VQFerBy5iVni7R+r0k26WG4WZ7I5rzd rMscUs4wTdkrw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_010A_01D42038.8A0F8F80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I took the setting from pgtune: https://pgtune.leopard.in.ua/#/ =20 Anyway, I will try to further reduce it. It is high because we have a very complex database, queries with a lot = of tables, and million of records. Furthermore, I see a lot of free memory. =20 Da: MichaelDBA =20 Inviato: venerd=EC 20 luglio 2018 14:40 A: Alfonso Moscato Cc: 'Campbell, Lance' ; = pgsql-admin@lists.postgresql.org Oggetto: Re: R: repeated out of shared memory error - not related to max_locks_per_transaction =20 Try reducing it further just to make sure. Alfonso Moscato =20 Friday, July 20, 2018 8:39 AM I thought it could have be.=20 Work_mem was 131mb and I reduced it to 60mb. Errors keep starting after = one day and half. Regards alfonso =20 Da: MichaelDBA = Inviato: venerd=EC 20 luglio 2018 14:36 A: Campbell, Lance Cc: Alfonso Moscato ; pgsql-admin@lists.postgresql.org =20 Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction =20 Perhaps your "work_mem" setting is causing the memory problems. Try reducing it to see if that alleviates the problem. Regards, Michael Vitale =20 MichaelDBA =20 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 =20 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.=20 Lance Sent from my iPad =20 =20 ------=_NextPart_000_010A_01D42038.8A0F8F80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I took the setting = from pgtune:

https://pgtune.leopard.in.ua/#/<= /a>

 

Anyway, I will try = to further reduce it.

It is = high because we have a very complex database, queries with a lot of = tables, and million of records.

Furthermore, I see = a lot of free memory.

 

 

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=

 <= /o:p>

Da: MichaelDBA <MichaelDBA@sqlexec.com>=
Inviato: venerd=EC 20 luglio 2018 14:36
A: = Campbell, Lance <lance@illinois.edu>
C= c: Alfonso Moscato <alfonso.moscato@merqurio.= it>; pgsql-admin@lists.postgr= esql.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

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

 

 

------=_NextPart_000_010A_01D42038.8A0F8F80--