public inbox for [email protected]
help / color / mirror / Atom feedpg_restore seems very slow
4+ messages / 3 participants
[nested] [flat]
* pg_restore seems very slow
@ 2016-06-15 22:00 Adrian Myers <[email protected]>
2016-06-15 22:08 ` Re: pg_restore seems very slow David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Myers @ 2016-06-15 22:00 UTC (permalink / raw)
To: pgsql-performance
This is my first post to the mailing list, so I apologize for any etiquette
issues.
I have a few databases that I am trying to move from one system to
another. Both systems are running Windows 7 and Postgres 8.4, and they are
pretty powerful machines (40-core Xeon workstations with decent hardware
across the board). While the DBs vary in size, I'm working right now with
one that is roughly 50 tables and probably 75M rows, and is about 300MB on
disk when exported via pg_dump.
I am exporting and restoring using these commands (on separate sytems):
pg_dump -F c mydb > mydb.dump
pg_restore -C -j 10 mydb.dump
The dump process runs in about a minute and seems fine. The restore process
has already been running for around 7 hours.
Yesterday, I tried restoring a larger DB that is roughly triple the
dimensions listed above, and it ran for over 16 hours without completing.
I followed the advice given at
http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html and
set the conf settings as directed and restarted the server.
You can see in the command line that I am trying to use the -j parameter
for parallelism, but I don't see much evidence of that in Task Manager. CPU
load is consistently 1 or 2% and only a couple cores seem to be doing
anything, there certainly aren't 10 cpu-bound cores. I'm not sure where to
look for pg_restore's disk I/O, but there is an entry for pg_restore in
Task Manager/Processes which shows almost no I/O Read Bytes and 0 I/O Write
Bytes. Since that's just the parent process that might make sense but I
don't see much activity elsewhere either.
Is there something simple that I am missing here? Does the -j flag not work
in 8.4 and I should use --jobs? It just seems like none of the CPU or RAM
usage I'd expect from this process are evident, it's taking many times
longer than I would expect, and I don't know how to verify if the things
I'm trying are working or not.
Any insight would be appreciated!
Thanks,
Adrian
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore seems very slow
2016-06-15 22:00 pg_restore seems very slow Adrian Myers <[email protected]>
@ 2016-06-15 22:08 ` David G. Johnston <[email protected]>
2016-06-15 23:41 ` Re: pg_restore seems very slow Adrian Myers <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2016-06-15 22:08 UTC (permalink / raw)
To: Adrian Myers <[email protected]>; +Cc: pgsql-performance
On Wed, Jun 15, 2016 at 6:00 PM, Adrian Myers <[email protected]>
wrote:
> This is my first post to the mailing list, so I apologize for any
> etiquette issues.
>
> I have a few databases that I am trying to move from one system to
> another. Both systems are running Windows 7 and Postgres 8.4, and they are
> pretty powerful machines (40-core Xeon workstations with decent hardware
> across the board). While the DBs vary in size, I'm working right now with
> one that is roughly 50 tables and probably 75M rows, and is about 300MB on
> disk when exported via pg_dump.
>
> I am exporting and restoring using these commands (on separate sytems):
> pg_dump -F c mydb > mydb.dump
> pg_restore -C -j 10 mydb.dump
>
> The dump process runs in about a minute and seems fine. The restore
> process has already been running for around 7 hours.
>
> Yesterday, I tried restoring a larger DB that is roughly triple the
> dimensions listed above, and it ran for over 16 hours without completing.
>
> I followed the advice given at
> http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html and
> set the conf settings as directed and restarted the server.
>
> You can see in the command line that I am trying to use the -j parameter
> for parallelism, but I don't see much evidence of that in Task Manager. CPU
> load is consistently 1 or 2% and only a couple cores seem to be doing
> anything, there certainly aren't 10 cpu-bound cores. I'm not sure where to
> look for pg_restore's disk I/O, but there is an entry for pg_restore in
> Task Manager/Processes which shows almost no I/O Read Bytes and 0 I/O Write
> Bytes. Since that's just the parent process that might make sense but I
> don't see much activity elsewhere either.
>
> Is there something simple that I am missing here? Does the -j flag not
> work in 8.4 and I should use --jobs? It just seems like none of the CPU or
> RAM usage I'd expect from this process are evident, it's taking many times
> longer than I would expect, and I don't know how to verify if the things
> I'm trying are working or not.
>
> Any insight would be appreciated!
>
>
Did any databases restore properly?
Are there any message in logs or on the terminal? You should add the
"--verbose" option to your pg_restore command to help provoke this.
-C can be problematic at times. Consider manually ensuring the desired
target database exists and is setup correctly (matches the original) and
then do a non-create restoration to it specifically.
-j should work fine in 8.4 (according to the docs)
You need to get to a point where you are seeing feedback from the
pg_restore process. Once you get it telling you what it is doing (or
trying to do) then diagnosing can begin.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore seems very slow
2016-06-15 22:00 pg_restore seems very slow Adrian Myers <[email protected]>
2016-06-15 22:08 ` Re: pg_restore seems very slow David G. Johnston <[email protected]>
@ 2016-06-15 23:41 ` Adrian Myers <[email protected]>
2016-06-16 01:43 ` Re: pg_restore seems very slow Adam Scott <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Myers @ 2016-06-15 23:41 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-performance
Hi David,
Thank you for your reply. Yes, there is quite a lot of feedback in the
terminal. I can see a small flurry of table operations followed by hours of
table contents being printed, presumably as they are inserted. I didn't use
the --verbose option, but it seems to be echoing everything it is doing.
I haven't seen any errors, and I was able to restore a couple very small
tables successfully, so it seems like the process is valid. The problem is
that pg_restore is running for extremely long periods of time on even
modestly large tables and I can't tell if the optimizations I am trying,
such as the -j concurrency option, are having any effect.
Thanks,
Adrian
On Wed, Jun 15, 2016 at 6:08 PM, David G. Johnston <
[email protected]> wrote:
> On Wed, Jun 15, 2016 at 6:00 PM, Adrian Myers <[email protected]>
> wrote:
>
>> This is my first post to the mailing list, so I apologize for any
>> etiquette issues.
>>
>> I have a few databases that I am trying to move from one system to
>> another. Both systems are running Windows 7 and Postgres 8.4, and they are
>> pretty powerful machines (40-core Xeon workstations with decent hardware
>> across the board). While the DBs vary in size, I'm working right now with
>> one that is roughly 50 tables and probably 75M rows, and is about 300MB on
>> disk when exported via pg_dump.
>>
>> I am exporting and restoring using these commands (on separate sytems):
>> pg_dump -F c mydb > mydb.dump
>> pg_restore -C -j 10 mydb.dump
>>
>> The dump process runs in about a minute and seems fine. The restore
>> process has already been running for around 7 hours.
>>
>> Yesterday, I tried restoring a larger DB that is roughly triple the
>> dimensions listed above, and it ran for over 16 hours without completing.
>>
>> I followed the advice given at
>> http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html and
>> set the conf settings as directed and restarted the server.
>>
>> You can see in the command line that I am trying to use the -j parameter
>> for parallelism, but I don't see much evidence of that in Task Manager. CPU
>> load is consistently 1 or 2% and only a couple cores seem to be doing
>> anything, there certainly aren't 10 cpu-bound cores. I'm not sure where to
>> look for pg_restore's disk I/O, but there is an entry for pg_restore in
>> Task Manager/Processes which shows almost no I/O Read Bytes and 0 I/O Write
>> Bytes. Since that's just the parent process that might make sense but I
>> don't see much activity elsewhere either.
>>
>> Is there something simple that I am missing here? Does the -j flag not
>> work in 8.4 and I should use --jobs? It just seems like none of the CPU or
>> RAM usage I'd expect from this process are evident, it's taking many times
>> longer than I would expect, and I don't know how to verify if the things
>> I'm trying are working or not.
>>
>> Any insight would be appreciated!
>>
>>
> Did any databases restore properly?
>
> Are there any message in logs or on the terminal? You should add the
> "--verbose" option to your pg_restore command to help provoke this.
>
> -C can be problematic at times. Consider manually ensuring the desired
> target database exists and is setup correctly (matches the original) and
> then do a non-create restoration to it specifically.
>
> -j should work fine in 8.4 (according to the docs)
>
> You need to get to a point where you are seeing feedback from the
> pg_restore process. Once you get it telling you what it is doing (or
> trying to do) then diagnosing can begin.
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore seems very slow
2016-06-15 22:00 pg_restore seems very slow Adrian Myers <[email protected]>
2016-06-15 22:08 ` Re: pg_restore seems very slow David G. Johnston <[email protected]>
2016-06-15 23:41 ` Re: pg_restore seems very slow Adrian Myers <[email protected]>
@ 2016-06-16 01:43 ` Adam Scott <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Adam Scott @ 2016-06-16 01:43 UTC (permalink / raw)
To: Adrian Myers <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-performance
The "simple" case may be anti-virus or firewall blocking feeding into the
database. Be sure to check windows system logs for any unusual messages.
Check the postgres log (usually in PGDATA/pg_logs)
For seeing disk I/O on Win7 check out
http://www.digitalcitizen.life/how-use-resource-monitor-windows-7
Try also to restore without any -j or --jobs to see if you get more
activity on CPU or disk.
Can you view any data in the tables to at least know it's loading?
Thanks,
Adam C. Scott
On Wed, Jun 15, 2016 at 4:41 PM, Adrian Myers <[email protected]>
wrote:
> Hi David,
>
> Thank you for your reply. Yes, there is quite a lot of feedback in the
> terminal. I can see a small flurry of table operations followed by hours of
> table contents being printed, presumably as they are inserted. I didn't use
> the --verbose option, but it seems to be echoing everything it is doing.
>
> I haven't seen any errors, and I was able to restore a couple very small
> tables successfully, so it seems like the process is valid. The problem is
> that pg_restore is running for extremely long periods of time on even
> modestly large tables and I can't tell if the optimizations I am trying,
> such as the -j concurrency option, are having any effect.
>
> Thanks,
> Adrian
>
> On Wed, Jun 15, 2016 at 6:08 PM, David G. Johnston <
> [email protected]> wrote:
>
>> On Wed, Jun 15, 2016 at 6:00 PM, Adrian Myers <[email protected]>
>> wrote:
>>
>>> This is my first post to the mailing list, so I apologize for any
>>> etiquette issues.
>>>
>>> I have a few databases that I am trying to move from one system to
>>> another. Both systems are running Windows 7 and Postgres 8.4, and they are
>>> pretty powerful machines (40-core Xeon workstations with decent hardware
>>> across the board). While the DBs vary in size, I'm working right now with
>>> one that is roughly 50 tables and probably 75M rows, and is about 300MB on
>>> disk when exported via pg_dump.
>>>
>>> I am exporting and restoring using these commands (on separate sytems):
>>> pg_dump -F c mydb > mydb.dump
>>> pg_restore -C -j 10 mydb.dump
>>>
>>> The dump process runs in about a minute and seems fine. The restore
>>> process has already been running for around 7 hours.
>>>
>>> Yesterday, I tried restoring a larger DB that is roughly triple the
>>> dimensions listed above, and it ran for over 16 hours without completing.
>>>
>>> I followed the advice given at
>>> http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html
>>> and set the conf settings as directed and restarted the server.
>>>
>>> You can see in the command line that I am trying to use the -j parameter
>>> for parallelism, but I don't see much evidence of that in Task Manager. CPU
>>> load is consistently 1 or 2% and only a couple cores seem to be doing
>>> anything, there certainly aren't 10 cpu-bound cores. I'm not sure where to
>>> look for pg_restore's disk I/O, but there is an entry for pg_restore in
>>> Task Manager/Processes which shows almost no I/O Read Bytes and 0 I/O Write
>>> Bytes. Since that's just the parent process that might make sense but I
>>> don't see much activity elsewhere either.
>>>
>>> Is there something simple that I am missing here? Does the -j flag not
>>> work in 8.4 and I should use --jobs? It just seems like none of the CPU or
>>> RAM usage I'd expect from this process are evident, it's taking many times
>>> longer than I would expect, and I don't know how to verify if the things
>>> I'm trying are working or not.
>>>
>>> Any insight would be appreciated!
>>>
>>>
>> Did any databases restore properly?
>>
>> Are there any message in logs or on the terminal? You should add the
>> "--verbose" option to your pg_restore command to help provoke this.
>>
>> -C can be problematic at times. Consider manually ensuring the desired
>> target database exists and is setup correctly (matches the original) and
>> then do a non-create restoration to it specifically.
>>
>> -j should work fine in 8.4 (according to the docs)
>>
>> You need to get to a point where you are seeing feedback from the
>> pg_restore process. Once you get it telling you what it is doing (or
>> trying to do) then diagnosing can begin.
>>
>> David J.
>>
>>
>>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-06-16 01:43 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-15 22:00 pg_restore seems very slow Adrian Myers <[email protected]>
2016-06-15 22:08 ` David G. Johnston <[email protected]>
2016-06-15 23:41 ` Adrian Myers <[email protected]>
2016-06-16 01:43 ` Adam Scott <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox