pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Leon Out <leon-lists@comvision.com>
To: pgsql-performance@postgresql.org
Subject: Lengthy pg_restore and high iowait?
Date: Fri, 13 Feb 2004 16:03:24 -0500
Message-ID: <103862F9-5E68-11D8-AB22-0030658FB514@comvision.com> (raw)

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

Attachments:

unnamed.txt (text/enriched, 2.0K) download <fixed><fontfamily><DEFANGED_param>Courier New</DEFANGED_param>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</fontfamily></fixed>

Message-ID: <103862F9-5E68-11D8-AB22-0030658FB514@comvision.com>
Permalink:  ../103862F9-5E68-11D8-AB22-0030658FB514@comvision.com/
Also on:    postgresql.org/message-id/103862F9-5E68-11D8-AB22-0030658FB514@comvision.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: leon-lists@comvision.com
  Subject: Re: Lengthy pg_restore and high iowait?
  In-Reply-To: <103862F9-5E68-11D8-AB22-0030658FB514@comvision.com>
  References: <103862F9-5E68-11D8-AB22-0030658FB514@comvision.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox