Skip site navigation (1) Skip section navigation (2)

Re: >24 hour restore

From: "Chad Thompson" <chad(at)weblinkservices(dot)com>
To: "Andrew Sullivan" <andrew(at)libertyrms(dot)info>,"pgsql-performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: >24 hour restore
Date: 2003-05-28 17:59:49
Message-ID: 02aa01c32542$ef61f730$32021aac@chad (view raw or flat)
Thread:
Lists: pgsql-performance

> 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?


In response to

Responses

pgsql-performance by date

Next:From: Dave TennyDate: 2003-05-28 18:08:02
Subject: Re: IN list processing performance (yet again)
Previous:From: Dave TennyDate: 2003-05-28 17:58:14
Subject: Re: IN list processing performance (yet again)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group