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:27:40 -0500
Message-ID: <7b2dfc1b-bd47-d800-4465-2614a07ffd12@gmail.com> (raw)
In-Reply-To: <CA+t6e1ktyE9kv=GfAUgHDmvvdj=TUx5n=V=sO8ok4pQNVbxamg@mail.gmail.com>
References: <CA+t6e1=OWhBrXhyzQdTTdsobo9x82DaGH6J+euuGSAvLERYzcg@mail.gmail.com>
<20190522154102.zpvieebklnvis5u3@development>
<CA+t6e1kUNo7V=LtBp0bvFZDFkB-sXxW+SNFNmR6+YQU8N+Bgow@mail.gmail.com>
<3a350cb9-aeca-e858-2c8b-3481080d1dcf@gmail.com>
<CA+t6e1ktyE9kv=GfAUgHDmvvdj=TUx5n=V=sO8ok4pQNVbxamg@mail.gmail.com>
Not looking for errors, but redirecting it to a file for posterity.
Were/are the rules being created, but just slowly?
On 5/22/19 11:02 AM, Mariel Cherkassky wrote:
> Basically if I didnt redirect stderr i should be redirected to the screen
> ? I didnt see any errors, I used -v (verbose) when I run the restore, and
> I see that it restores the rules (but a lot of them..)
>
> בתאריך יום ד׳, 22 במאי 2019 ב-19:01 מאת Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@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.
>
--
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: <7b2dfc1b-bd47-d800-4465-2614a07ffd12@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