Re: pg_restore takes more time on creation of rules

Well, it isn't taking more than 1 sec to be created, but I guess that there
are a lot of rules. Still, the DB sze is by far smaller than the other dbs.

On Wed, May 22, 2019, 7:27 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

>
> 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(at)gmail(dot)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(dot)vondra(at)2ndquadrant(dot)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.
>