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