public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Adrian Myers <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: pg_restore seems very slow
Date: Wed, 15 Jun 2016 18:08:52 -0400
Message-ID: <CAKFQuwarUz7fEgBvpVFyDFFjOvBdDVQ36JSfufqKLn7PSzi7Rg@mail.gmail.com> (raw)
In-Reply-To: <CALAd6+1L3w=Zd7OqY79iYjqxEP8YfuwOmT+_sGZN5oxbFXqoFg@mail.gmail.com>
References: <CALAd6+1L3w=Zd7OqY79iYjqxEP8YfuwOmT+_sGZN5oxbFXqoFg@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-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.
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pg_restore seems very slow
In-Reply-To: <CAKFQuwarUz7fEgBvpVFyDFFjOvBdDVQ36JSfufqKLn7PSzi7Rg@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox