X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A5C98D1C515 for ; Fri, 13 Feb 2004 21:03:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 46658-01 for ; Fri, 13 Feb 2004 17:03:27 -0400 (AST) Received: from arkanoid.comvision.com (arkanoid.comvision.com [63.108.120.12]) by svr1.postgresql.org (Postfix) with ESMTP id A5B0FD1B57C for ; Fri, 13 Feb 2004 17:03:19 -0400 (AST) Received: by arkanoid.comvision.com (Postfix, from userid 98) id DD1BF179FE; Fri, 13 Feb 2004 16:25:28 -0500 (EST) Received: from [63.108.121.47] (calendar.comvision.com [63.108.121.47]) by arkanoid.comvision.com (Postfix) with ESMTP id 5D1A6179EB for ; Fri, 13 Feb 2004 16:25:27 -0500 (EST) Mime-Version: 1.0 (Apple Message framework v612) To: pgsql-performance@postgresql.org Message-Id: <103862F9-5E68-11D8-AB22-0030658FB514@comvision.com> Content-Type: multipart/alternative; boundary=Apple-Mail-5--385242844 From: Leon Out Subject: Lengthy pg_restore and high iowait? Date: Fri, 13 Feb 2004 16:03:24 -0500 X-Mailer: Apple Mail (2.612) X-Sanitizer: Comvision mail filter X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/159 X-Sequence-Number: 5683 --Apple-Mail-5--385242844 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed Hello again. I'm setting up a backup routine for my new db server. As part of my testing, I'm attempting to pg_restore a pg_dump'ed backup of my database. The database is about 4.3 GB, and the dump file is about 100 MB. I first did a schema-only restore, then started a data-only restore with --disable-triggers to get around the referential integrity issues of reloading the data. The data-only restore has been running for a couple of hours now, and I'm seeing high iowait numbers in top. 15:57:58 up 23:55, 2 users, load average: 2.04, 2.07, 2.01 60 processes: 57 sleeping, 3 running, 0 zombie, 0 stopped CPU states: cpu user nice system irq softirq iowait idle total 4.0% 0.0% 0.7% 0.0% 0.0% 43.5% 51.6% cpu00 0.0% 0.0% 0.3% 0.0% 0.0% 84.8% 14.7% cpu01 15.7% 0.0% 1.7% 0.0% 0.0% 2.7% 79.6% cpu02 0.1% 0.0% 0.7% 0.0% 0.0% 84.2% 14.7% cpu03 0.2% 0.0% 0.0% 0.0% 0.0% 2.4% 97.4% Mem: 3869544k av, 3849280k used, 20264k free, 0k shrd, 110544k buff 1297452k actv, 2298928k in_d, 57732k in_c Swap: 2040244k av, 0k used, 2040244k free 3576684k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 8366 postgres 16 0 954M 954M 952M R 4.3 25.2 21:58 1 postmaster 9282 postgres 15 0 0 0 0 RW 0.2 0.0 0:00 2 postmaster 1 root 15 0 496 496 444 S 0.0 0.0 0:05 3 init Questions: * Do these iowait numbers indicate a problem with my setup? * Does anyone have a good method for disabling indexes before a restore and restoring them afterwards? I've spent some time writing scripts to do this, but I have yet to come up with drop/recreate solution that returns my database to the same pre-drop state. Thanks in advance! Leon --Apple-Mail-5--385242844 Content-Type: text/enriched; charset="US-ASCII"; name="unnamed.txt" Content-Transfer-Encoding: 7bit Courier NewHello again. I'm setting up a backup routine for my new db server. As part of my testing, I'm attempting to pg_restore a pg_dump'ed backup of my database. The database is about 4.3 GB, and the dump file is about 100 MB. I first did a schema-only restore, then started a data-only restore with --disable-triggers to get around the referential integrity issues of reloading the data. The data-only restore has been running for a couple of hours now, and I'm seeing high iowait numbers in top. 15:57:58 up 23:55, 2 users, load average: 2.04, 2.07, 2.01 60 processes: 57 sleeping, 3 running, 0 zombie, 0 stopped CPU states: cpu user nice system irq softirq iowait idle total 4.0% 0.0% 0.7% 0.0% 0.0% 43.5% 51.6% cpu00 0.0% 0.0% 0.3% 0.0% 0.0% 84.8% 14.7% cpu01 15.7% 0.0% 1.7% 0.0% 0.0% 2.7% 79.6% cpu02 0.1% 0.0% 0.7% 0.0% 0.0% 84.2% 14.7% cpu03 0.2% 0.0% 0.0% 0.0% 0.0% 2.4% 97.4% Mem: 3869544k av, 3849280k used, 20264k free, 0k shrd, 110544k buff 1297452k actv, 2298928k in_d, 57732k in_c Swap: 2040244k av, 0k used, 2040244k free 3576684k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 8366 postgres 16 0 954M 954M 952M R 4.3 25.2 21:58 1 postmaster 9282 postgres 15 0 0 0 0 RW 0.2 0.0 0:00 2 postmaster 1 root 15 0 496 496 444 S 0.0 0.0 0:05 3 init Questions: * Do these iowait numbers indicate a problem with my setup? * Does anyone have a good method for disabling indexes before a restore and restoring them afterwards? I've spent some time writing scripts to do this, but I have yet to come up with drop/recreate solution that returns my database to the same pre-drop state. Thanks in advance! Leon --Apple-Mail-5--385242844--