Subject: Re: Out of memory error on automatic vacuum
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-performance@lists.postgresql.org
References: <a5c8d612-693e-1546-5cbf-6d928287bdc6@zunibal.com>
 <20191118122528.45k3jpgaxevsyser@development>
From: Ekaterina Amez <ekaterina.amez@zunibal.com>
Message-ID: <c59d5d5c-e629-22e4-851c-dd74f6d3c6ec@zunibal.com>
Date: Mon, 18 Nov 2019 15:02:16 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: <20191118122528.45k3jpgaxevsyser@development>
Content-Type: multipart/alternative;
 boundary="------------B964C9291380C086E6D0D920"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------B964C9291380C086E6D0D920
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit


El 18/11/19 a las 13:25, Tomas Vondra escribió:
> On Mon, Nov 18, 2019 at 12:41:27PM +0100, Ekaterina Amez wrote:
>> Hi all,
>>
>> This morning I was checking postgres servers logs, looking for errors 
>> (we've recently upgraded them and changed default config) and long 
>> running queries when I found one of the servers had really big logs 
>> since yesterday.  It was giving the error of this mail's subject: out 
>> of memory, failed on request of size XXX on automatic vacuum of table 
>> YYY. A quick search revealed me some postgresql-lists messages 
>> talking about work_mem and shared_buffers configuration options, some 
>> kernel config options too. Although all of them were messages from 
>> several years ago, I decided to cut my shared_buffers configured 
>> value and restart server: now it looks like error is gone. But I'd 
>> like to understand what's beyond the logged error (it's really long 
>> and refers to things about inner functionalities that I'm missing), 
>> how to detect what config options are possibly conflicting and, most 
>> important, I want to know if I've solved it right.
>>
>
> Unfortunately that's hard to say, without further data. The "out of
> memory" errors simply mean we called malloc() and it returned NULL,
> because the kernel was unable to provide the memory.
This (kernel unable to provide memory) was because no more RAM was 
available to allocate? It was because PG process did not have more 
memory assigned ready to use? Or is something unknown because it depends 
on the situations where the error is thrown?
>
> There probably were other processes using all the available RAM (the
> limit depends on various config values, e.g. overcommit). What were
> these processes doing we don't know :-(
>
> For example, there might be multiple complex queries, allocating
> several work_mem each, using quite a bit of memory. Or there might be a
> runaway query doing HashAgg allocating much more memory than predicted.
> Or maybe there was running a completely separate job (say, backup)
> allocating a lot of memory or dirtying data in page cache.

I've looked at cron and I've seen a scheduled process that finished a 
bit before the error began to log (o couple of minutes or so). Errors 
began on Sunday morning and this machine doesn't have much workload on 
work days, and less on weekend. I'll keep an eye on this log and if the 
problem appears again I'll try to track database activity and machine 
activity.

>
> There are countless options what might have happened. The memory context
> stats are nice, but it's just a snapshot from one particular process,
> and it does not seem very interesting (the total is just ~1MB, so
> nothing extreme). We still don't know what else was running.

When you talk about ~1MB are you getting this size from log lines like this?

<index>: *1024* total in 1 blocks; 476 free (0 chunks); 548 used


>
> Lowering shared_buffers certainly does reduce the memory pressure in
> general, i.e. there is 1GB of work for use by processes. It may be
> sufficient, hard to guess.
>
> I don't know if work_mem 64MB is too low, becuase it depends on what
> queries you're running etc. But you probably don't wat to increase that,
> as it allows processes to use more memory when executing queries, i.e.
> it increases memory pressure and makes OOM more likely.
>
> So you need to watch system monitoring, see how much memory is being
> used (excluding page cache) and consider reducing work_mem and/or
> max_connections if it's too close.

I'll do, thanks for your suggestions.


>
> regards
>
Regards,

Ekaterina


--------------B964C9291380C086E6D0D920
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p><br>
    </p>
    <div class="moz-cite-prefix">El 18/11/19 a las 13:25, Tomas Vondra
      escribió:<br>
    </div>
    <blockquote type="cite"
      cite="mid:20191118122528.45k3jpgaxevsyser@development">On Mon, Nov
      18, 2019 at 12:41:27PM +0100, Ekaterina Amez wrote:
      <br>
      <blockquote type="cite">Hi all,
        <br>
        <br>
        This morning I was checking postgres servers logs, looking for
        errors (we've recently upgraded them and changed default config)
        and long running queries when I found one of the servers had
        really big logs since yesterday.  It was giving the error of
        this mail's subject: out of memory, failed on request of size
        XXX on automatic vacuum of table YYY. A quick search revealed me
        some postgresql-lists messages talking about work_mem and
        shared_buffers configuration options, some kernel config options
        too. Although all of them were messages from several years ago,
        I decided to cut my shared_buffers configured value and restart
        server: now it looks like error is gone. But I'd like to
        understand what's beyond the logged error (it's really long and
        refers to things about inner functionalities that I'm missing),
        how to detect what config options are possibly conflicting and,
        most important, I want to know if I've solved it right.
        <br>
        <br>
      </blockquote>
      <br>
      Unfortunately that's hard to say, without further data. The "out
      of
      <br>
      memory" errors simply mean we called malloc() and it returned
      NULL,
      <br>
      because the kernel was unable to provide the memory.
      <br>
    </blockquote>
    This (kernel unable to provide memory) was because no more RAM was
    available to allocate? It was because PG process did not have more
    memory assigned ready to use? Or is something unknown because it
    depends on the situations where the error is thrown?<br>
    <blockquote type="cite"
      cite="mid:20191118122528.45k3jpgaxevsyser@development">
      <br>
      There probably were other processes using all the available RAM
      (the
      <br>
      limit depends on various config values, e.g. overcommit). What
      were
      <br>
      these processes doing we don't know :-(
      <br>
      <br>
      For example, there might be multiple complex queries, allocating
      <br>
      several work_mem each, using quite a bit of memory. Or there might
      be a
      <br>
      runaway query doing HashAgg allocating much more memory than
      predicted.
      <br>
      Or maybe there was running a completely separate job (say, backup)
      <br>
      allocating a lot of memory or dirtying data in page cache.
      <br>
    </blockquote>
    <p>I've looked at cron and I've seen a scheduled process that
      finished a bit before the error began to log (o couple of minutes
      or so). Errors began on Sunday morning and this machine doesn't
      have much workload on work days, and less on weekend. I'll keep an
      eye on this log and if the problem appears again I'll try to track
      database activity and machine activity. <br>
    </p>
    <blockquote type="cite"
      cite="mid:20191118122528.45k3jpgaxevsyser@development">
      <br>
      There are countless options what might have happened. The memory
      context
      <br>
      stats are nice, but it's just a snapshot from one particular
      process,
      <br>
      and it does not seem very interesting (the total is just ~1MB, so
      <br>
      nothing extreme). We still don't know what else was running.
      <br>
    </blockquote>
    <p>When you talk about ~1MB are you getting this size from log lines
      like this?<br>
    </p>
    <p>&lt;index&gt;: <b>1024</b> total in 1 blocks; 476 free (0
      chunks); 548 used</p>
    <p><br>
    </p>
    <blockquote type="cite"
      cite="mid:20191118122528.45k3jpgaxevsyser@development">
      <br>
      Lowering shared_buffers certainly does reduce the memory pressure
      in
      <br>
      general, i.e. there is 1GB of work for use by processes. It may be
      <br>
      sufficient, hard to guess.
      <br>
      <br>
      I don't know if work_mem 64MB is too low, becuase it depends on
      what
      <br>
      queries you're running etc. But you probably don't wat to increase
      that,
      <br>
      as it allows processes to use more memory when executing queries,
      i.e.
      <br>
      it increases memory pressure and makes OOM more likely.
      <br>
      <br>
      So you need to watch system monitoring, see how much memory is
      being
      <br>
      used (excluding page cache) and consider reducing work_mem and/or
      <br>
      max_connections if it's too close.
      <br>
    </blockquote>
    <p>I'll do, thanks for your suggestions.</p>
    <p><br>
    </p>
    <blockquote type="cite"
      cite="mid:20191118122528.45k3jpgaxevsyser@development">
      <br>
      regards
      <br>
      <br>
    </blockquote>
    <p>Regards,</p>
    <p>Ekaterina<br>
    </p>
  </body>
</html>

--------------B964C9291380C086E6D0D920--