MIME-Version: 1.0
From: Adrian Myers <hadrianmyers@gmail.com>
Date: Wed, 15 Jun 2016 18:00:06 -0400
Message-ID: 
 <CALAd6+1L3w=Zd7OqY79iYjqxEP8YfuwOmT+_sGZN5oxbFXqoFg@mail.gmail.com>
Subject: pg_restore seems very slow
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001a114fad389b803c0535583e72
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a114fad389b803c0535583e72
Content-Type: text/plain; charset=UTF-8

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

--001a114fad389b803c0535583e72
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">This is my first post to the mailing list, so I apologize =
for any etiquette issues.<div><br></div><div>I have a few databases that I =
am trying to move from one system to another.=C2=A0 Both systems are runnin=
g Windows 7 and Postgres 8.4, and they are pretty powerful machines (40-cor=
e Xeon workstations with decent hardware across the board). While the DBs v=
ary in size, I&#39;m working right now with one that is roughly 50 tables a=
nd probably 75M rows, and is about 300MB on disk when exported via pg_dump.=
=C2=A0</div><div><br></div><div>I am exporting and restoring using these co=
mmands (on separate sytems):</div><div><span style=3D"font-size:12.8px">pg_=
dump -F c mydb &gt; mydb.dump</span></div><div><span style=3D"font-size:12.=
8px">pg_restore -C -j 10 mydb.dump</span><br></div><div><span style=3D"font=
-size:12.8px"><br></span></div><div><span style=3D"font-size:12.8px">The du=
mp process runs in about a minute and seems fine. The restore process has a=
lready been running for around 7 hours.</span></div><div><span style=3D"fon=
t-size:12.8px"><br></span></div><div><span style=3D"font-size:12.8px">Yeste=
rday, I tried restoring a larger DB that is roughly triple the dimensions l=
isted above, and it ran for over 16 hours without completing.</span></div><=
div><span style=3D"font-size:12.8px"><br></span></div><div><span style=3D"f=
ont-size:12.8px">I followed the advice given at <a href=3D"http://www.datab=
asesoup.com/2014/09/settings-for-fast-pgrestore.html">http://www.databaseso=
up.com/2014/09/settings-for-fast-pgrestore.html</a> and set the conf settin=
gs as directed and restarted the server.</span></div><div><span style=3D"fo=
nt-size:12.8px"><br></span></div><div><span style=3D"font-size:12.8px">You =
can see in the command line that I am trying to use the -j parameter for pa=
rallelism, but I don&#39;t see much evidence of that in Task Manager. CPU l=
oad is consistently 1 or 2% and only a couple cores seem to be doing anythi=
ng, there certainly aren&#39;t 10 cpu-bound cores. I&#39;m not sure where t=
o look for pg_restore&#39;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 Wr=
ite Bytes. Since that&#39;s just the parent process that might make sense b=
ut I don&#39;t see much activity elsewhere either.</span></div><div><span s=
tyle=3D"font-size:12.8px"><br></span></div><div><span style=3D"font-size:12=
.8px">Is there something simple that I am missing here? Does the -j flag no=
t work in 8.4 and I should use --jobs? It just seems like none of the CPU o=
r RAM usage I&#39;d expect from this process are evident, it&#39;s taking m=
any times longer than I would expect, and I don&#39;t know how to verify if=
 the things I&#39;m trying are working or not.</span></div><div><span style=
=3D"font-size:12.8px"><br></span></div><div><span style=3D"font-size:12.8px=
">Any insight would be appreciated!</span></div><div><span style=3D"font-si=
ze:12.8px"><br></span></div><div><span style=3D"font-size:12.8px">Thanks,</=
span></div><div><span style=3D"font-size:12.8px">Adrian</span></div></div>

--001a114fad389b803c0535583e72--