Subject: Re: pg_restore takes more time on creation of rules
To: pgsql-admin@lists.postgresql.org
References: 
 <CA+t6e1=OWhBrXhyzQdTTdsobo9x82DaGH6J+euuGSAvLERYzcg@mail.gmail.com>
 <20190522154102.zpvieebklnvis5u3@development>
 <CA+t6e1kUNo7V=LtBp0bvFZDFkB-sXxW+SNFNmR6+YQU8N+Bgow@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <3a350cb9-aeca-e858-2c8b-3481080d1dcf@gmail.com>
Date: Wed, 22 May 2019 11:00:51 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.6.1
MIME-Version: 1.0
In-Reply-To: 
 <CA+t6e1kUNo7V=LtBp0bvFZDFkB-sXxW+SNFNmR6+YQU8N+Bgow@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------F9C4168CE5E578A32E2255A4"
Content-Language: en-US
Precedence: bulk

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

I'd redirect stderr to a file and tail it to monitor progress.

On 5/22/19 10:44 AM, Mariel Cherkassky wrote:
> By rules I mean DB rules (simillar to triggers but different)
>
> ‫בתאריך יום ד׳, 22 במאי 2019 ב-18:41 מאת ‪Tomas Vondra‬‏ 
> <‪tomas.vondra@2ndquadrant.com <mailto:tomas.vondra@2ndquadrant.com>‬‏>:‬
>
>     On Wed, May 22, 2019 at 06:26:49PM +0300, Mariel Cherkassky wrote:
>     >Hey,
>     >I'm trying to restore a cluster (9.2) from 3 binary dumps (pg_dump -Fc).
>     >Each dump contains only one database.
>     >The sizes :
>     >A-10GB
>     >B-20GB
>     >C-5GB.
>     >
>     >For unclear reason the restore of the third database is taking alot of
>     >time. It isnt stuck but it continues creating db rules. This database has
>     >more then 400K rules.
>     >
>
>     What do you mean by "rules"?
>
>     >I changed a few postgresql.conf parameters :
>     >shared_buffers = 2GB
>     >effective_cache_size = 65GB
>     >checkpoint_segments =20
>     >checkpoint_completion_target = 0.9
>     >maintenance_work_mem = 10GB
>     >checkpoint_timeout=30min
>     >work_mem=64MB
>     >autovacuum = off
>     >full_page_writes=off
>     >wal_buffers=50MB
>     >
>     >my machine has 31 cpu and 130GB of ram.
>     >
>     >Any idea why the restore of the two dbs takes about 15 minutes while the
>     >third db which is the smallest takes more than 1 hour ?  I restore the
>     >dump with pg_restore with 5 jobs (-j).
>     >
>
>     Well, presumably the third database has complexity in other places,
>     possibly spending a lot of time on CPU, while the other databases don't
>     have such issue.
>
>     What would help is a CPU profile, e.g. from perf.
>
>
>     regards
>
>     -- 
>     Tomas Vondra http://www.2ndQuadrant.com
>     PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

-- 
Angular momentum makes the world go 'round.

--------------F9C4168CE5E578A32E2255A4
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 bgcolor="#FFFFFF" text="#000000">
    I'd redirect stderr to a file and tail it to monitor progress.<br>
    <br>
    <div class="moz-cite-prefix">On 5/22/19 10:44 AM, Mariel Cherkassky
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CA+t6e1kUNo7V=LtBp0bvFZDFkB-sXxW+SNFNmR6+YQU8N+Bgow@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="rtl">
        <div style="" dir="ltr">By rules I mean DB rules (simillar to
          triggers but different)</div>
      </div>
      <br>
      <div class="gmail_quote">
        <div dir="rtl" class="gmail_attr">‫בתאריך יום ד׳, 22 במאי 2019
          ב-18:41 מאת ‪Tomas Vondra‬‏ &lt;‪<a
            href="mailto:tomas.vondra@2ndquadrant.com"
            moz-do-not-send="true">tomas.vondra@2ndquadrant.com</a>‬‏&gt;:‬<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
          Wed, May 22, 2019 at 06:26:49PM +0300, Mariel Cherkassky
          wrote:<br>
          &gt;Hey,<br>
          &gt;I'm trying to restore a cluster (9.2) from 3 binary dumps
          (pg_dump -Fc).<br>
          &gt;Each dump contains only one database.<br>
          &gt;The sizes :<br>
          &gt;A-10GB<br>
          &gt;B-20GB<br>
          &gt;C-5GB.<br>
          &gt;<br>
          &gt;For unclear reason the restore of the third database is
          taking alot of<br>
          &gt;time. It isnt stuck but it continues creating db rules.
          This database has<br>
          &gt;more then 400K rules.<br>
          &gt;<br>
          <br>
          What do you mean by "rules"?<br>
          <br>
          &gt;I changed a few postgresql.conf parameters :<br>
          &gt;shared_buffers = 2GB<br>
          &gt;effective_cache_size = 65GB<br>
          &gt;checkpoint_segments =20<br>
          &gt;checkpoint_completion_target = 0.9<br>
          &gt;maintenance_work_mem = 10GB<br>
          &gt;checkpoint_timeout=30min<br>
          &gt;work_mem=64MB<br>
          &gt;autovacuum = off<br>
          &gt;full_page_writes=off<br>
          &gt;wal_buffers=50MB<br>
          &gt;<br>
          &gt;my machine has 31 cpu and 130GB of ram.<br>
          &gt;<br>
          &gt;Any idea why the restore of the two dbs takes about 15
          minutes while the<br>
          &gt;third db which is the smallest takes more than 1 hour ?  I
          restore the<br>
          &gt;dump with pg_restore with 5 jobs (-j).<br>
          &gt;<br>
          <br>
          Well, presumably the third database has complexity in other
          places,<br>
          possibly spending a lot of time on CPU, while the other
          databases don't<br>
          have such issue.<br>
          <br>
          What would help is a CPU profile, e.g. from perf.<br>
          <br>
          <br>
          regards<br>
          <br>
          -- <br>
          Tomas Vondra                  <a
            href="http://www.2ndQuadrant.com" rel="noreferrer"
            target="_blank" moz-do-not-send="true">http://www.2ndQuadrant.com</a><br>
          PostgreSQL Development, 24x7 Support, Remote DBA, Training
          &amp; Services<br>
          <br>
        </blockquote>
      </div>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------F9C4168CE5E578A32E2255A4--