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 1fgWdW-0002KX-BO for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 14:44:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgWdU-0001fJ-KW for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 14:44:00 +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 1fgWdU-0001fC-Bd for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 14:44:00 +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 1fgWdP-0007Yx-OG for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 14:43:59 +0000 Received: from DT02 ([93.63.211.242]) by smtpcm2-pc.aruba.it with bizsmtp id D2ju1y0085ELnP9012jufj; Fri, 20 Jul 2018 16:43:54 +0200 From: "Alfonso Moscato" To: "'Evan Bauer'" Cc: "'Fabio Pardi'" , "'MichaelDBA'" , 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> <5B51E362.1050700@sqlexec.com> <0468ca31-f6cc-dc4a-9ce6-42b4da927b06@portavita.eu> <015801d42035$2fed3fa0$8fc7bee0$@merqurio.it> In-Reply-To: Subject: R: repeated out of shared memory error - not related to max_locks_per_transaction Date: Fri, 20 Jul 2018 16:43:55 +0200 Message-ID: <017701d42038$15be56c0$413b0440$@merqurio.it> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0178_01D42048.D94949A0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJyTRksqbTjmmAqDGBi/OX9BtlRvgJ6PMbvAeA0gCoCb1YPZgKS/XPbAf+ECiMCJe5MugJIr6DjAp14S68B10imbKK52ELA Content-Language: it DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aruba.it; s=a1; t=1532097834; bh=/kcq+YUH2yVoa9k09jKjYgeTpQY7GfgvVpW/ZMdWJc4=; h=From:To:Subject:Date:MIME-Version:Content-Type; b=fA2PIq4dL3/o2eO0XY80KfP4oWbODgppmTClcTJjsZ483PVGjLkJxh9sf0YnKIzgn hyZlKlOVRr2fV/ffrJuURHByIRPxaVSMf23zJKNlnuY8aZapOpgVGLnxvLcIhAdwtf gh5PTmvgptKGMzsk4gXZDYILqt0mOi40NgWGsJl4x4r5X8eI2nkj7/q9zt6WBhjDbV qzNxmbQc+LP60yC1oe52mu2ReBEUTQ3DKDMEWQ1YnUYqLNJtPqv26vlnIwv0TwKcLV ID0plnVlh05zZL14M8Y3nFLl+RkCRtTcTQnZsY8xnkZAFw7R5DJFy5aXEFw6yqNILn 4Eb/nfnnkQjnw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0178_01D42048.D94949A0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Evan, I have tried shared_buffers from 8gb to 40gb, and = max_locks_per_transaction from 128 to 384. I don=E2=80=99t see relevant differences: errors start more or less = after one day and half. Regards alfonso =20 Da: Evan Bauer =20 Inviato: venerd=C3=AC 20 luglio 2018 16:40 A: Alfonso Moscato Cc: Fabio Pardi ; MichaelDBA = ; pgsql-admin@lists.postgresql.org Oggetto: Re: repeated out of shared memory error - not related to = max_locks_per_transaction =20 Alfonso, =20 There was a lot of buzz when 9.6 came out around getting the = =E2=80=9COut of shared memory=E2=80=9D message due to virtual lock space = consumption. I see that you have set a high value = (max_locks_per_transaction =3D 384), but have you tried adjusting it to = see if that either increases or decreases the amount of time before the = messages appear and you have to restart pg? =20 Regards, =20 - Evan =20 Evan Bauer eb@evanbauer.com =20 +1 646 641 2973 Skype: evanbauer On Jul 20, 2018, at 10:23, Alfonso Moscato > wrote: =20 Michael, Fabio, moreover, i get the message =E2=80=9COut of shared memory=E2=80=9D, not = =E2=80=9Cout of memory=E2=80=9D. Anyway, I can confirm that when errors began there where more than 10gb = of free memory. Regards Alfonso =20 Da: Fabio Pardi >=20 Inviato: venerd=C3=AC 20 luglio 2018 15:57 A: MichaelDBA > Cc: pgsql-admin@lists.postgresql.org = =20 Oggetto: Re: repeated out of shared memory error - not related to = max_locks_per_transaction =20 Michael, I think we are talking about 2 different scenarios. 1) the single operation is using more than work_mem -> gets spilled to = disk. like: a big sort. That's what i mentioned. 2) there are many many concurrent operations, and one more of them wants = to allocate work_mem but the memory on the server is exhausted at that = point. -> in that case you will get 'out of memory'. That's what you are = referring to. =20 Given the description of the problem (RAM and Postgres settings) and the = fact that Alfonso says that "there is a lot of free memory" i think is = unlikely that we are in the second situation described here above. regards, fabio pardi =20 =20 =20 =20 On 20/07/18 15:28, MichaelDBA wrote: 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=E2=80=99re 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=E2=80=99s 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-postgre= s/ 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..'. =20 regards, fabio pardi =20 =20 On 20/07/18 15:00, MichaelDBA wrote: =20 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 =20 regards, fabio pardi =20 =20 On 20/07/18 14:35, MichaelDBA wrote: =20 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.=20 Lance Sent from my iPad =20 =20 =20 =20 ------=_NextPart_000_0178_01D42048.D94949A0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi Evan,

I have tried shared_buffers from = 8gb to 40gb, and max_locks_per_transaction from 128 to = 384.

I don=E2=80=99t see relevant = differences: errors start more or less after one day and = half.

Regards

alfonso

 

Da: Evan Bauer = <evanbauer@mac.com>
Inviato: venerd=C3=AC 20 luglio = 2018 16:40
A: Alfonso Moscato = <alfonso.moscato@merqurio.it>
Cc: Fabio Pardi = <f.pardi@portavita.eu>; MichaelDBA <MichaelDBA@sqlexec.com>; = pgsql-admin@lists.postgresql.org
Oggetto: Re: repeated out of = shared memory error - not related to = max_locks_per_transaction

 

Alfonso,

 

There was a lot of buzz when 9.6 came out around = getting the =E2=80=9COut of shared memory=E2=80=9D message due to = virtual lock space consumption.  I see that you have set a high = value (max_locks_per_transaction =3D 384), but have you tried adjusting = it to see if that either increases or decreases the amount of time = before the messages appear and you have to restart = pg?

 

Regards,

 

- = Evan

 

Evan Bauer
eb@evanbauer.com
+1 646 641 = 2973
Skype: evanbauer



On Jul 20, 2018, at 10:23, Alfonso Moscato <alfonso.moscato@merqurio.it> wrote:

 

Da: Fabio Pardi <f.pardi@portavita.eu> 
Inviato: venerd=C3=AC 20 luglio 2018 = 15:57
A: MichaelDBA <MichaelDBA@sqlexec.com>
= Cc: pgsql-admin@lists.postgr= esql.org
Oggetto: Re: repeated out of shared = memory error - not related to = max_locks_per_transaction

 

Michael,<= /span>

I think = we are talking about 2 different scenarios.<= /span>

1) the = single operation is using more than work_mem -> gets spilled to disk. = like: a big sort. That's what i mentioned.<= /span>

2) there = are many many concurrent operations, and one more of them wants to = allocate work_mem but the memory on the server is exhausted at that = point. -> in that case you will get 'out of memory'. That's what you = are referring to.<= /span>

 <= /o:p>

Given = the description of the problem (RAM and Postgres settings) and the fact = that Alfonso says that "there is a lot of free memory" i think = is unlikely that we are in the second situation described here = above.<= /span>


regards,

=

fabio pardi<= /span>

 <= /o:p>

 <= /o:p>

 <= /o:p>

 

On 20/07/18 15:28, = MichaelDBA wrote:

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.

Wh= en you consume more memory than is available on your machine you can = start to see out of out of memory&n= bsp;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=E2=80=99re = 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&n= bsp;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&n= bsp;uses. Yes, you read that right: out-of-memory it=E2=80=99s better to = decrease work_mem&n= bsp;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/configur= ing-work-mem-on-postgres/

Regards,
Michael = Vitale


Friday, July 20, 2018 9:19 = AM

Nope Michael,<= /span>

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.<= /span>

period.<= /span>

Based on your log settings, it might appear = in the logs, under 'temporary file created..'.<= /span>

 <= /span>

regards,<= /o:p>

fabio pardi<= /span>

 <= /span>

 

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<= /span>

 <= /span>

regards,<= /span>

fabio pardi<= /span>

 <= /span>

 <= /span>

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

 

 

<= p class=3DMsoNormal = style=3D'background:white'> 

=

 

------=_NextPart_000_0178_01D42048.D94949A0--