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 1fgWJT-0001HL-VZ for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 14:23:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgWJR-0006hc-O7 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Jul 2018 14:23:17 +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 1fgWJR-0006hT-3M for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 14:23:17 +0000 Received: from smtp202-pc.aruba.it ([62.149.157.202] helo=smtpg-pc.aruba.it) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fgWJL-0003hg-Ux for pgsql-admin@lists.postgresql.org; Fri, 20 Jul 2018 14:23:15 +0000 Received: from DT02 ([93.63.211.242]) by smtpcm2-pc.aruba.it with bizsmtp id D2P91y0055ELnP9012P9Mc; Fri, 20 Jul 2018 16:23:10 +0200 From: "Alfonso Moscato" To: "'Fabio Pardi'" , "'MichaelDBA'" Cc: 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> In-Reply-To: <0468ca31-f6cc-dc4a-9ce6-42b4da927b06@portavita.eu> Subject: R: repeated out of shared memory error - not related to max_locks_per_transaction Date: Fri, 20 Jul 2018 16:23:10 +0200 Message-ID: <015801d42035$2fed3fa0$8fc7bee0$@merqurio.it> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0159_01D42045.F3776F30" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJyTRksqbTjmmAqDGBi/OX9BtlRvgJ6PMbvAeA0gCoCb1YPZgKS/XPbAf+ECiMCJe5MugJIr6Djot1328A= Content-Language: it DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aruba.it; s=a1; t=1532096590; bh=HCSezg8ljCs+1fo+qwuZo3Pq/5VsGwISTLiGEtR9LgA=; h=From:To:Subject:Date:MIME-Version:Content-Type; b=Bp2kZN2MVQTUuVUXvW0D6QgGgMFnjgjgoBW7IDRT/bbtaYfqiB/Ln4Ae3sRkCV96X hBrYy/ygVjtG5uIB4Htw97wj3uA5kWivpRen9wCT0FreNwjiZ9+hLR3V6ZnD+vePd3 KCo6+M6TKY3mMrdtNhac6kham4sawkmKRy+vTZColE9FiXEEEh4PYIwhzQdJIU3pQ1 WcHsTCdoSy3q6jFiE95e0EccPHAnIMkU+OCBW5GrEJKAyCq58tUPjpIQkBCnXnnec8 75hgMv/Dsi9ZVOxDF02i6xswKp18TyCKzJ/ht4ejBHyBIM+KOvXce270T4uf9V8xRW OQMDZinC2mNTA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0159_01D42045.F3776F30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Michael, Fabio, moreover, i get the message =93Out of shared memory=94, not =93out of = memory=94. 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=EC 20 luglio 2018 15:57 A: MichaelDBA Cc: pgsql-admin@lists.postgresql.org 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,=20 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=92re 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=92s 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 =20 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 =20 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 =20 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 =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 =20 ------=_NextPart_000_0159_01D42045.F3776F30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Michael, = Fabio,

moreover, i get = the message “Out of shared memory”, not “out of = memory”.

Anyway, I can = confirm that when errors began =A0there where more than 10gb of free = memory.

Regards<= /span>

Alfonso<= /span>

 

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

 

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.

 

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

 

 

 

 

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’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&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’s 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/configuring-work-me= m-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:

 

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


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

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_0159_01D42045.F3776F30--