Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bDJ1Q-0005un-Jf for pgsql-performance@arkaria.postgresql.org; Wed, 15 Jun 2016 22:10:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bDJ1P-0004DN-Pl for pgsql-performance@arkaria.postgresql.org; Wed, 15 Jun 2016 22:10:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bDIzb-00028l-FJ for pgsql-performance@postgresql.org; Wed, 15 Jun 2016 22:08:59 +0000 Received: from mail-oi0-x235.google.com ([2607:f8b0:4003:c06::235]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bDIzW-0005LZ-Se for pgsql-performance@postgresql.org; Wed, 15 Jun 2016 22:08:58 +0000 Received: by mail-oi0-x235.google.com with SMTP id d132so41692296oig.1 for ; Wed, 15 Jun 2016 15:08:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=VOa6gdbsyujL3Aa1GxB3M9e7jWNGrRE9+65frZssF84=; b=vvmjDu0r6oY2DVyKZeajXDY0yrYxEDwt/kUMO9CjTY8JeQJCpYrcbAOv0aNbXI7Ygt /7GwIbiG38Th0oEM5S5c4o9VdS5tZBTz9hdCGchHb1xGRvzaqY4Euo7uDRfbWBbFt5J0 UlAglYUdqLlw7yHK5FlurQZJJI1k/+QF/kCVx4+dYUl1MmsCsIgnqair3cDQ3rsXR0aC llitiZIUDEPY4TQ/BD17LTxWIXN82RfphLe+XWa5KCzNmW2swr1hOkPxKuKDtYD3Ag6l CbAvIQk/dzlQkg4pndhkQZpq8wSQNWAJOe9CtpEOHSWyCI4jIVI1TBJ23Jcbc4RLPa1E M4nQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=VOa6gdbsyujL3Aa1GxB3M9e7jWNGrRE9+65frZssF84=; b=HEnVc/ZPfdDfVGxTAu1Fb6af89axziXh0gXT2rmys0vKrI0cV+OGhOVwa64T6SZEEf IGqJOo0HNhABQo6s+qfnxCvepjCd7PJOAzBl//Jvf3cZmUb/GoxC2w98uWo4t+e5Un0B 4rNeiiErEQrP4KN+35TPfKPwORtaAsOHvTn7Cz/2JKbQ+ST54jlilizB+Rbj23UWOo+C 3sk0VTVyIn1hStX4s7yZlIiy00bpmecKOe6270P+rj5LzJMq/xlE9lz09H4WR0ZLCKoL X4zA48otCoAq6k1FpjPCM37QcGYEd8Tw3YUV/qT/4D8KVMvGu/bkrj14nHNue0s/QcFM gPUw== X-Gm-Message-State: ALyK8tJbsHlYnthcT7WhhprtRubHiBXyE8pwrd4QRHK7N73u+htJOpSCWGw3PAJpm7TV7lrrO9kWtamk0PZd0A== X-Received: by 10.202.222.132 with SMTP id v126mr588454oig.82.1466028532949; Wed, 15 Jun 2016 15:08:52 -0700 (PDT) MIME-Version: 1.0 Received: by 10.157.34.104 with HTTP; Wed, 15 Jun 2016 15:08:52 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Wed, 15 Jun 2016 18:08:52 -0400 Message-ID: Subject: Re: pg_restore seems very slow To: Adrian Myers Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a113d5376efe85c0535585dd2 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a113d5376efe85c0535585dd2 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Wed, Jun 15, 2016 at 6:00 PM, Adrian Myers 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 a= re > 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 o= n > 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. C= PU > 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 t= o > 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 Wri= te > 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 o= r > RAM usage I'd expect from this process are evident, it's taking many time= s > 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! > > =E2=80=8BDid any databases restore properly? Are there any message in logs or on the terminal=E2=80=8B? 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. =E2=80=8BDavid J. =E2=80=8B --001a113d5376efe85c0535585dd2 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Wed, Ju= n 15, 2016 at 6:00 PM, Adrian Myers <hadrianmyers@gmail.com> wrote:
Thi= s is my first post to the mailing list, so I apologize for any etiquette is= sues.

I have a few databases that I am trying to move fr= om one system to another.=C2=A0 Both systems are running Windows 7 and Post= gres 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.=C2=A0
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 a= bout a minute and seems fine. The restore process has already been running = for around 7 hours.

<= /span>
Yesterday, I tried restor= ing a larger DB that is roughly triple the dimensions listed above, and it = ran for over 16 hours without completing.

I f= ollowed 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 ca= n see in the command line that I am trying to use the -j parameter for para= llelism, but I don't see much evidence of that in Task Manager. CPU loa= d 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 Writ= e 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 man= y times longer than I would expect, and I don't know how to verify if t= he things I'm trying are working or not.

Any insight would be appreciated!


=E2=80=8BDid any databases res= tore properly?

Are there any message in logs or on the terminal=E2=80=8B?=C2= =A0 You should add the "--verbose" option to your pg_restore comm= and to help provoke this.

-C can be problematic at times.=C2=A0 Consider manu= ally ensuring the desired target database exists and is setup correctly (ma= tches 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.=C2=A0 Once you = get it telling you what it is doing (or trying to do) then diagnosing can b= egin.

=E2=80=8BDa= vid J.
=E2=80=8B

--001a113d5376efe85c0535585dd2--