pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore takes more time on creation of rules
8+ messages / 3 participants
[nested] [flat]
* pg_restore takes more time on creation of rules
@ 2019-05-22 15:26 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mariel Cherkassky @ 2019-05-22 15:26 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org; pgsql-admin@lists.postgresql.org
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.
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).
I know that it is an old version, just trying to help..
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_restore takes more time on creation of rules
@ 2019-05-22 15:41 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tomas Vondra @ 2019-05-22 15:41 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_restore takes more time on creation of rules
@ 2019-05-22 15:44 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 2 replies; 8+ messages in thread
From: Mariel Cherkassky @ 2019-05-22 15:44 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; +Cc: pgsql-performance@lists.postgresql.org; pgsql-admin@lists.postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_restore takes more time on creation of rules
@ 2019-05-22 16:00 Ron <ronljohnsonjr@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ron @ 2019-05-22 16:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_restore takes more time on creation of rules
@ 2019-05-22 16:02 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Mariel Cherkassky @ 2019-05-22 16:02 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_restore takes more time on creation of rules
@ 2019-05-22 16:27 Ron <ronljohnsonjr@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ron @ 2019-05-22 16:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_restore takes more time on creation of rules
@ 2019-05-22 16:44 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Mariel Cherkassky @ 2019-05-22 16:44 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_restore takes more time on creation of rules
@ 2019-05-22 17:00 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tomas Vondra @ 2019-05-22 17:00 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org; pgsql-admin@lists.postgresql.org
On Wed, May 22, 2019 at 06:44:29PM +0300, Mariel Cherkassky wrote:
>By rules I mean DB rules (simillar to triggers but different)
>
I very much doubt such high number of rules was expected during the
design (especially if it's on a single table), so perhaps there's an
O(N^2) piece of code somewhere. I suggest you do a bit of profiling, for
example using perf [1], which would show where the time is spent.
[1] https://wiki.postgresql.org/wiki/Profiling_with_perf
And please stop top-posting, it makes it much harder to follow the
discussion.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2019-05-22 17:00 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-22 15:26 pg_restore takes more time on creation of rules Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-05-22 15:41 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-05-22 15:44 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-05-22 16:00 ` Ron <ronljohnsonjr@gmail.com>
2019-05-22 16:02 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-05-22 16:27 ` Ron <ronljohnsonjr@gmail.com>
2019-05-22 16:44 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-05-22 17:00 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
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