Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bDMLl-0006oC-29 for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 01:44:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bDMLj-0003UJ-VC for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jun 2016 01:44:04 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bDMLh-0003Tp-Nz for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 01:44:02 +0000 Received: from mail-yw0-x22c.google.com ([2607:f8b0:4002:c05::22c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bDMLZ-0003gX-Bq for pgsql-performance@postgresql.org; Thu, 16 Jun 2016 01:44:00 +0000 Received: by mail-yw0-x22c.google.com with SMTP id c72so30251329ywb.1 for ; Wed, 15 Jun 2016 18:43:53 -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=ZeGHrFGP4LnXUe5VPzDcv2foeOqWyl106crHYSMXxQM=; b=SVbkLvoB8B6uc/uPi6HdQVqZP/+C3WVlo42hZVR3b4+wBxDNFdv/c/w5xfQze89HDG BBTYbrl/kUKsbDnOlsN7KZxqsqdAs/NBL4rFRWFABruHsBCD11pN48GYYUELRPDJ80Nx MiQNRrNHuDTPoKXAUgf8V2QsPMOG7wPoJIhVyp2vDpZUO9FMOg/sFjYASXQqaMB4J5Q8 0X6FOpEN7mW/C3GYEJO+TpcAHmsS2jhv6pqlHxn59/V27DHJ0FEJPQBLbpBlTO87s5Wy iqp2ERLA/UXaO3plCrl6nqIhrBywyOgH3GkQun3K5yMJfkWxbqJ4JH9J8vrUj4m2Z3Mn Zc1w== 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=ZeGHrFGP4LnXUe5VPzDcv2foeOqWyl106crHYSMXxQM=; b=GYPH9oKeMtm3s92cu68IdLQLTv88gTdReNXUKcw0K7ZkyA2I3g39jyJ+k/D9cLWuSI hgaNc83yXlkRLRH1AtqlbtQCpeX92cVnhF1dH+xt2ZdWlyvWgoiq1gBoLcdJqhRkZmZh UUC79IHE49YMNTm78zk8ZOTxdWcdUT14UdaSBzNKxMD3httB9S0JYHeSBoOKzW0wc1TI Htknqbce4+obJDjpmtK/ajhfKStHCadHqRt55PiEN4kPkO8oMIUvTtfLExo37KpEKflz CuY2nOyeu2OZF5qtAuSK4sLNgibgzgOgdHcyvT05dPQ0Qtq/Kif+R5cS+/0EjCZqoV+f uOuw== X-Gm-Message-State: ALyK8tJP2l6vVx2xuFl6KjL5yIYNRdJan3gKUHcCeDRJSzpU4JXdRoyglqjN4r83YS3Sim4FF4HBLf0a20g4gw== X-Received: by 10.13.196.132 with SMTP id g126mr1268635ywd.95.1466041432044; Wed, 15 Jun 2016 18:43:52 -0700 (PDT) MIME-Version: 1.0 Received: by 10.13.228.132 with HTTP; Wed, 15 Jun 2016 18:43:51 -0700 (PDT) In-Reply-To: References: From: Adam Scott Date: Wed, 15 Jun 2016 18:43:51 -0700 Message-ID: Subject: Re: pg_restore seems very slow To: Adrian Myers Cc: "David G. Johnston" , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a114d7ed4c87b3105355b5e24 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 --001a114d7ed4c87b3105355b5e24 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 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 u= se > 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 i= s > 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 < > david.g.johnston@gmail.com> wrote: > >> 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= are >>> pretty powerful machines (40-core Xeon workstations with decent hardwar= e >>> across the board). While the DBs vary in size, I'm working right now wi= th >>> 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 completin= g. >>> >>> 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 paramete= r >>> 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 W= rite >>> 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 ti= mes >>> longer than I would expect, and I don't know how to verify if the thing= s >>> 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 a= dd 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 >> >> > --001a114d7ed4c87b3105355b5e24 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
The "simple" case = may be anti-virus or firewall blocking feeding into the database.=C2=A0 Be = sure to check windows system logs for any unusual messages.

Check the postgres log (usually in PGDATA/pg_logs)
Try also to=C2=A0 resto= re 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 <= hadrianmyers@gmail.com> wrote:
Hi David,

Thank you for your reply.= Yes, there is quite a lot of feedback in the terminal. I can see a small f= lurry of table operations followed by hours of table contents being printed= , presumably as they are inserted. I didn't use the --verbose option, b= ut it seems to be echoing everything it is doing.

= I haven't seen any errors, and I was able to restore a couple very smal= l tables successfully, so it seems like the process is valid. The problem i= s that pg_restore is running for extremely long periods of time on even mod= estly large tables and I can't tell if the optimizations I am trying, s= uch as the -j concurrency option, are having any effect.

Thanks,
Adrian

On Wed, J= un 15, 2016 at 6:08 PM, David G. Johnston <david.g.johnston@gmail= .com> wrote:
On Wed, Jun 15, 20= 16 at 6:00 PM, Adrian Myers <hadrianmyers@gmail.com> wrote:
This is my first post to the mailing list, so I apologize for any etiquet= te issues.

I have a few databases that I am trying to mo= ve from one system to another.=C2=A0 Both systems are running Windows 7 and= Postgres 8.4, and they are pretty powerful machines (40-core Xeon workstat= ions with decent hardware across the board). While the DBs vary in size, I&= #39;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 sepa= rate sytems):
pg_dump -F c mydb = > mydb.dump
pg_restore= -C -j 10 mydb.dump
<= br>
The dump process runs= in about a minute and seems fine. The restore process has already been run= ning for around 7 hours.
=
Yesterday, I tried r= estoring a larger DB that is roughly triple the dimensions listed above, an= d it ran for over 16 hours without completing.

I followed the advice given at http://www.databas= esoup.com/2014/09/settings-for-fast-pgrestore.html and set the conf set= tings as directed and restarted the server.

Y= ou 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. CP= U load is consistently 1 or 2% and only a couple cores seem to be doing any= thing, there certainly aren't 10 cpu-bound cores. I'm not sure wher= e to look for pg_restore's disk I/O, but there is an entry for pg_resto= re 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 sens= e 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 CP= U or RAM usage I'd expect from this process are evident, it's takin= g 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!


=E2=80=8BDid any dat= abases restore 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_re= store command to help provoke this.

<= /div>
-C can be problematic at times.=C2=A0 Con= sider manually ensuring the desired target database exists and is setup cor= rectly (matches the original) and then do a non-create restoration to it sp= ecifically.

-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 diagno= sing can begin.

= =E2=80=8BDavid J.
=E2=80=8B



--001a114d7ed4c87b3105355b5e24--