DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=g/BmOtldR1mvbaDGv/F8wPEL6YfuvvEvjDEq+zGrUeIKKJXReNI9geE2UoftfdiQfOgO2bAQYeCp5wQe6boguKll4bB9qoXzfcgPh/7FMP50vfpGQrXxwjP3e1Ywnja4VcpYBdT2mRwQw0oTcg2GVAtjEmKB9k68t51cdDMp5nU=
Message-ID: <53b425b00604131826s2872bd49id2ac1ab99a5fade4@mail.gmail.com>
Date: Thu, 13 Apr 2006 18:26:00 -0700
From: "patrick keshishian" <pkeshish@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: pg 7.4.x - pg_restore impossibly slow
Cc: pgsql-performance@postgresql.org
In-Reply-To: <9585.1144903611@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <53b425b00604121826x213fe2fdmc793e22026469923@mail.gmail.com>
	<9585.1144903611@sss.pgh.pa.us>

Hi Tom, et.al.,

So I changed the following settings in postgresql.conf file and
restarted PostgreSQL and then proceeded with pg_restore:

# new changes for this test-run
log_statement =3D true
sort_mem =3D 10240 # default 1024
vacuum_mem =3D 20480 # default 8192
# from before
checkpoint_segments =3D 10
log_pid =3D true
log_timestamp =3D true

With these settings and running:

pg_restore -vaOd dbname dbname.DUMP


Things seem to progress better. The first of the large
tables got COPY'ed within 1 hr 40 min:

start: 2006-04-13 11:44:19
finish: 2006-04-13 13:25:36

I ended up ctrl-C'ing out of the pg_restore as the second
large table was taking over 3 hours and the last PostgreSQL
log entry was from over 2.5hrs ago, with message:

2006-04-13 14:09:29 [3049] LOG:  recycled transaction log file
"000000060000006B"

Time for something different. Before attempting the same
procedure with fsync off, I ran the following sequence of
commands:

$ dropdb dbname
$ createdb dbname
$ pg_restore -vsOd dbname dbname.DUMP
$ date > db.restore ; pg_restore -vcOd dbname \
    dbname.DUMP ; date >> db.restore
$ cat db.restore
Thu Apr 13 18:02:51 PDT 2006
Thu Apr 13 18:17:16 PDT 2006

That's just over 14 minutes!

Ideas?

Is this because the -c option drops all foreign keys and
so the restore goes faster?  Should this be the preferred,
recommended and documented method to run pg_restore?
Any drawbacks to this method?

Thanks,
--patrick


On 4/12/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "patrick keshishian" <pkeshish@gmail.com> writes:
> > My dev box is much slower hardware than the customer's
> > server.  Even with that difference I expected to be able to
> > pg_restore the database within one day.  But no.
>
> Seems a bit odd.  Can you narrow down more closely which step of the
> restore is taking the time?  (Try enabling log_statements.)
>
> One thought is that kicking up work_mem and vacuum_mem is likely to
> help for some steps (esp. CREATE INDEX and foreign-key checking).
> And be sure you've done the usual tuning for write-intensive activity,
> such as bumping up checkpoint_segments.  Turning off fsync wouldn't
> be a bad idea either.
>
>                         regards, tom lane