Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bDKRG-0001Y6-3K for pgsql-performance@arkaria.postgresql.org; Wed, 15 Jun 2016 23:41:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bDKRF-0008Rs-FJ for pgsql-performance@arkaria.postgresql.org; Wed, 15 Jun 2016 23:41:37 +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 1bDKRE-0008RN-HX for pgsql-performance@postgresql.org; Wed, 15 Jun 2016 23:41:36 +0000 Received: from mail-yw0-x230.google.com ([2607:f8b0:4002:c05::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bDKR9-0007N3-5Z for pgsql-performance@postgresql.org; Wed, 15 Jun 2016 23:41:34 +0000 Received: by mail-yw0-x230.google.com with SMTP id c72so28298373ywb.1 for ; Wed, 15 Jun 2016 16:41:30 -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=eKrLtUWoYKHQyDDj85zvHs6cYu0IVsynqf8S9mJYyLo=; b=RCdMXRkczED66cXOsYiWoGMUoVt2/x62uA9CV1dfyCz60Cw4zuylkoJZ7HzHQcxoSO CI9kv1YwE/sNEW8DGDk+8jSfZRjU+VoblOLjQiKIIbTdwShdEi6Oow60FqUZ94nfFYeA c68MUrG+oX5Ob8KZkZSET+j0rdmcKvODjvM6Y7DFgDl/k0eSwENlbe47BQMLVFLxkoNS IIk1NqGFzmgqcINFs9aHjxlXFV92qm+z3H2bt3PgNS+y9lCZFpgcf8aBTnivlJ+t/sjj gJGPToAvVrSAlWcKpGaf0XyF5TeoJuDuooaqI7sihw9y499KQ28feBl5Rn9310GJx7ch OqCA== 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=eKrLtUWoYKHQyDDj85zvHs6cYu0IVsynqf8S9mJYyLo=; b=ezPEvjjLMapxAr/RRAr5J57OS0Vveke/msjzYJFqLrMh8K3t6SWwylvitsAI5Km0XR 9jEuv+igrtXSciIOxKynffWXCrrxfIvU0Pnfba3GtssQlVI/u1rgEdXVIe+pxUfwQ+q9 fHXnPDshNBOD86f4Gc1M+GA/7oK4mTHuLLvk/Qeeh8fk1l9rLdy9hTlsIWG5F5Sx+4sr 2TfVL9GFFAaiy92svsqN7gR485v401nnPBGWBwfiHCgtlEsAzMS3QS6zpkW/0nfDV+Ub hhWd8jWN+weNiarLG236B/AVelQMC6D4crbMdBxc0E9/U+1zULTfN7HZRwTZIwX/SEBz qPcg== X-Gm-Message-State: ALyK8tJ5Q344sGsIR1BC6W7tCgJRaZYq6CT43CTxtWudtfjuxAilKLscvXjjVcj29l+Kb19pqTcuzlpWBaO/Vg== X-Received: by 10.13.218.131 with SMTP id c125mr926569ywe.310.1466034088945; Wed, 15 Jun 2016 16:41:28 -0700 (PDT) MIME-Version: 1.0 Received: by 10.37.30.135 with HTTP; Wed, 15 Jun 2016 16:41:28 -0700 (PDT) In-Reply-To: References: From: Adrian Myers Date: Wed, 15 Jun 2016 19:41:28 -0400 Message-ID: Subject: Re: pg_restore seems very slow To: "David G. Johnston" Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=94eb2c08130819aecb053559a9ba 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 --94eb2c08130819aecb053559a9ba Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 < 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 hardware >> across the board). While the DBs vary in size, I'm working right now wit= h >> 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 Wr= ite >> 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 tim= es >> 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 ad= d 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 > > --94eb2c08130819aecb053559a9ba Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi David,

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

I ha= ven't seen any errors, and I was able to restore a couple very small ta= bles successfully, so it seems like the process is valid. The problem is th= at pg_restore is running for extremely long periods of time on even modestl= y 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 <hadrianmyers@gmail.com&= gt; wrote:
This is my first pos= t 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.=C2=A0 Both systems are running Windows 7 and Postgres 8.4, and the= y are pretty powerful machines (40-core Xeon workstations with decent hardw= are across the board). While the DBs vary in size, I'm working right no= w with one that is roughly 50 tables and probably 75M rows, and is about 30= 0MB 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<= /div>
pg_restore -C -j 10 mydb.dump

<= span style=3D"font-size:12.8px">The dump process runs in about a minute and= seems fine. The restore process has already been running for around 7 hour= s.

= Yesterday, I tried restoring a larger DB t= hat is roughly triple the dimensions listed above, and it ran for over 16 h= ours without completing.
=
I followed the advic= e given at http://www.databasesoup.com/2014/09/sett= ings-for-fast-pgrestore.html and set the conf settings as directed and = restarted the server.
You can see in the comm= and line that I am trying to use the -j parameter for parallelism, but I do= n'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_resto= re's disk I/O, but there is an entry for pg_restore in Task Manager/Pro= cesses which shows almost no I/O Read Bytes and 0 I/O Write Bytes. Since th= at's just the parent process that might make sense but I don't see = much activity elsewhere either.

Is there some= thing 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 th= an 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 properl= y?

Are there any message in logs or on the terminal=E2=80=8B?=C2=A0 You shoul= d add the "--verbose" option to your pg_restore command to help p= rovoke this.

-C can be problematic at times.=C2=A0 Consider manually ensuring= the desired target database exists and is setup correctly (matches the ori= ginal) and then do a non-create restoration to it specifically.
=

-j should wor= k fine in 8.4 (according to the docs)
You need to get to a point where you ar= e seeing feedback from the pg_restore process.=C2=A0 Once you get it tellin= g you what it is doing (or trying to do) then diagnosing can begin.

=E2=80=8BDavid J.
<= div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif= ">=E2=80=8B


--94eb2c08130819aecb053559a9ba--