Thread: Re: pg_restore takes more time on creation of rules

Re: pg_restore takes more time on creation of rules

From
Ron
Date:
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‬‏>:‬
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.

Re: pg_restore takes more time on creation of rules

From
Mariel Cherkassky
Date:
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‬‏>:‬
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‬‏>:‬
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.

Re: pg_restore takes more time on creation of rules

From
Ron
Date:

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‬‏>:‬
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‬‏>:‬
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.

Re: pg_restore takes more time on creation of rules

From
Mariel Cherkassky
Date:
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@gmail.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@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‬‏>:‬
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.