From: patrick keshishian <pkeshish@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg 7.4.x - pg_restore impossibly slow
Date: Thu, 13 Apr 2006 18:26:00 -0700
Message-ID: <53b425b00604131826s2872bd49id2ac1ab99a5fade4@mail.gmail.com> (raw)
In-Reply-To: <9585.1144903611@sss.pgh.pa.us>
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 = true
sort_mem = 10240 # default 1024
vacuum_mem = 20480 # default 8192
# from before
checkpoint_segments = 10
log_pid = true
log_timestamp = 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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pkeshish@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: pg 7.4.x - pg_restore impossibly slow
In-Reply-To: <53b425b00604131826s2872bd49id2ac1ab99a5fade4@mail.gmail.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