Re: pg 7.4.x - pg_restore impossibly slow

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
> "patrick keshishian" <pkeshish@xxxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux