pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chad Thompson <chad@weblinkservices.com>
To: Andrew Sullivan <andrew@libertyrms.info>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: >24 hour restore
Date: Wed, 28 May 2003 11:59:49 -0600
Message-ID: <02aa01c32542$ef61f730$32021aac@chad> (raw)
References: <027901c32530$273839c0$32021aac@chad>
	<20030528162455.GG6637@libertyrms.info>



> On Wed, May 28, 2003 at 09:12:23AM -0600, Chad Thompson wrote:
> >
> > It started off quick, but it got to the first table w/ any real data in
it
> > (only about 30k records) and acted like it was frozen.  I left it
running
> > all night, it finished that table and started on others but it hasnt
even
> > gotten to the big tables (2 @ about 9 million records).  At this pace it
> > will take several days to finish the restore.
>
> This makes me think you have a trigger problem.  You don't say what
> version you're running, but my guess is that you need to disable all
> your triggers, and remove all your indices, before you start loading
> the data.  Re-enable them afterwards.
>
> By building the schema first, then loading the data, you're spending
> cycles running triggers &c.
>

This was my first thought.  After about an hour of running, I stopped the
process, edited the schema file to remove all the foreign keys and triggers.
I then started it again.  So there SHOULD be no triggers right now.

UPDATE:  I stopped the restore, before it was stopped, top showed postmaster
using 17% CPU.  After stopping I noticed that it DID fill my largest table
(1.16 M tuples) over night.  So I am editing the dump file to continue where
it left off. ( vi is the only thing that is not choking on the 2.4 gig file)
That is good news because that means it wont take 7-10 days to import, just
1-2.

As for version (oops) my old version was 7.3.1 and I am moving to 7.3.2

Any other ideas?

TIA
Chad

Oh, a bit off topic... I remember that I wanted to move the WAL files off of
the raid but forgot to do it on start up.  Can I do that now that the system
is setup?  Where would I find docs to tell me about that?




view thread (6+ messages)  latest in thread

Message-ID: <02aa01c32542$ef61f730$32021aac@chad>
Permalink:  ../02aa01c32542$ef61f730$32021aac@chad/
Also on:    postgresql.org/message-id/02aa01c32542$ef61f730$32021aac@chad

 · 

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: chad@weblinkservices.com, andrew@libertyrms.info
  Subject: Re: >24 hour restore
  In-Reply-To: <02aa01c32542$ef61f730$32021aac@chad>
  References: <027901c32530$273839c0$32021aac@chad> <20030528162455.GG6637@libertyrms.info> <02aa01c32542$ef61f730$32021aac@chad>

* 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