Subject: Re: 13.4 on RDS, SSL SYSCALL EOF on restore
To: pgsql-admin@lists.postgresql.org
References: <202110082245.4wq5lie5euyx@alvherre.pgsql>
 <202110082248.xmzxkwo5gup7@alvherre.pgsql>
 <CAOC+FBV2+mp-BJZfkjPaQCs_vf3UGpS8Z+N8mBj3i4SiZf1KWQ@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <07ed72a9-52dc-2c26-596e-7e80315ceb11@gmail.com>
Date: Sun, 10 Oct 2021 01:10:23 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.13.0
MIME-Version: 1.0
In-Reply-To: 
 <CAOC+FBV2+mp-BJZfkjPaQCs_vf3UGpS8Z+N8mBj3i4SiZf1KWQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------B3C1BCEEFF072F2793CB0F5A"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/07ed72a9-52dc-2c26-596e-7e80315ceb11%40gmail.com>
Precedence: bulk

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

What if you use a bigger instance (and maybe provisioned IO, too) just for 
the time it takes to restore the database, and then modify it down to a 
"production sized" instance?

On 10/9/21 1:20 PM, Wells Oliver wrote:
> Thanks all. Reducing maintenance_work_mem to 1GB and keeping 
> shared_buffers at 4GB did allow the restore to complete. It took ~770m 
> with 16 processes and that configuration, but only ~500m with 
> maintenance_work_mem at 2GB, shared_buffers at 4GB, and 8 processes. Of 
> course, with maintenance_work_mem at 2GB and 16 processes, we ran out of 
> memory and kaboom.
>
> If anyone has any more parameter values I should try to improve on that 
> restore time, I'd love to hear them.
>
> Thanks for the help here.
>
>
> On Fri, Oct 8, 2021 at 3:48 PM Alvaro Herrera <alvherre@alvh.no-ip.org 
> <mailto:alvherre@alvh.no-ip.org>> wrote:
>
>     On 2021-Oct-08, Alvaro Herrera wrote:
>
>     > On 2021-Oct-08, Wells Oliver wrote:
>     >
>     > > Dug out some more logging:
>     >
>     > > 2021-10-08 20:35:08 UTC::@:[12682]:LOG:  server process (PID 3970)
>     was terminated by signal 9: Killed
>     > > 2021-10-08 20:35:08 UTC::@:[12682]:DETAIL:  Failed process was
>     running: CREATE INDEX ...
>     >
>     > So what's happening here is that the instance is running out of RAM
>     > while creating some index, and the kernel is killing the process.  I
>     > would probably blame the combination of shared_buffers=4GB with
>     > maintenance_work_mem=2GB, together with the instance's total RAM.
>
>     Also, maybe RDS could be smarter about this situation.
>
>     -- 
>     Álvaro Herrera              Valdivia, Chile  —
>     https://www.EnterpriseDB.com/ <https://www.EnterpriseDB.com/>
>     "La primera ley de las demostraciones en vivo es: no trate de usar el
>     sistema.
>     Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)
>
>
>
> -- 
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>

-- 
Angular momentum makes the world go 'round.

--------------B3C1BCEEFF072F2793CB0F5A
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>
    What if you use a bigger instance (and maybe provisioned IO, too)
    just for the time it takes to restore the database, and then modify
    it down to a "production sized" instance?<br>
    <br>
    <div class="moz-cite-prefix">On 10/9/21 1:20 PM, Wells Oliver wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAOC+FBV2+mp-BJZfkjPaQCs_vf3UGpS8Z+N8mBj3i4SiZf1KWQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Thanks all. Reducing maintenance_work_mem to 1GB
        and keeping shared_buffers at 4GB did allow the restore to
        complete. It took ~770m with 16 processes and that
        configuration, but only ~500m with maintenance_work_mem at 2GB,
        shared_buffers at 4GB, and 8 processes. Of course, with
        maintenance_work_mem at 2GB and 16 processes, we ran out of
        memory and kaboom.
        <div><br>
        </div>
        <div>If anyone has any more parameter values I should try to
          improve on that restore time, I'd love to hear them.</div>
        <div><br>
        </div>
        <div>Thanks for the help here.</div>
        <div><br>
        </div>
      </div>
      <br>
      <div class="gmail_quote">
        <div dir="ltr" class="gmail_attr">On Fri, Oct 8, 2021 at 3:48 PM
          Alvaro Herrera &lt;<a href="mailto:alvherre@alvh.no-ip.org"
            moz-do-not-send="true">alvherre@alvh.no-ip.org</a>&gt;
          wrote:<br>
        </div>
        <blockquote class="gmail_quote" style="margin:0px 0px 0px
          0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On
          2021-Oct-08, Alvaro Herrera wrote:<br>
          <br>
          &gt; On 2021-Oct-08, Wells Oliver wrote:<br>
          &gt; <br>
          &gt; &gt; Dug out some more logging:<br>
          &gt; <br>
          &gt; &gt; 2021-10-08 20:35:08 UTC::@:[12682]:LOG:  server
          process (PID 3970) was terminated by signal 9: Killed<br>
          &gt; &gt; 2021-10-08 20:35:08 UTC::@:[12682]:DETAIL:  Failed
          process was running: CREATE INDEX ...<br>
          &gt; <br>
          &gt; So what's happening here is that the instance is running
          out of RAM<br>
          &gt; while creating some index, and the kernel is killing the
          process.  I<br>
          &gt; would probably blame the combination of
          shared_buffers=4GB with<br>
          &gt; maintenance_work_mem=2GB, together with the instance's
          total RAM.<br>
          <br>
          Also, maybe RDS could be smarter about this situation.<br>
          <br>
          -- <br>
          Álvaro Herrera              Valdivia, Chile  —  <a
            href="https://www.EnterpriseDB.com/" rel="noreferrer"
            target="_blank" moz-do-not-send="true">https://www.EnterpriseDB.com/</a><br>
          "La primera ley de las demostraciones en vivo es: no trate de
          usar el sistema.<br>
          Escriba un guión que no toque nada para no causar daños."
          (Jakob Nielsen)<br>
        </blockquote>
      </div>
      <br clear="all">
      <div><br>
      </div>
      -- <br>
      <div dir="ltr" class="gmail_signature">
        <div dir="ltr">
          <div>Wells Oliver<br>
            <a href="mailto:wellsoliver@gmail.com" target="_blank"
              moz-do-not-send="true">wells.oliver@gmail.com</a></div>
        </div>
      </div>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------B3C1BCEEFF072F2793CB0F5A--