From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: pg_restore takes more time on creation of rules
Date: Wed, 22 May 2019 11:00:51 -0500
Message-ID: <3a350cb9-aeca-e858-2c8b-3481080d1dcf@gmail.com> (raw)
In-Reply-To: <CA+t6e1kUNo7V=LtBp0bvFZDFkB-sXxW+SNFNmR6+YQU8N+Bgow@mail.gmail.com>
References: <CA+t6e1=OWhBrXhyzQdTTdsobo9x82DaGH6J+euuGSAvLERYzcg@mail.gmail.com>
<20190522154102.zpvieebklnvis5u3@development>
<CA+t6e1kUNo7V=LtBp0bvFZDFkB-sXxW+SNFNmR6+YQU8N+Bgow@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: pg_restore takes more time on creation of rules
In-Reply-To: <3a350cb9-aeca-e858-2c8b-3481080d1dcf@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox