Thread: pg 7.4.x - pg_restore impossibly slow

pg 7.4.x - pg_restore impossibly slow

From
"patrick keshishian"
Date:
Greetings,

I have 395M pg_dump from a PostgreSQL 7.4.2 database.
This dump is from one of our customer's servers.  There is
a web-based administration UI which has been reported to
be extremely slow and unusable.

To see what's going on with their data I have grabbed a
copy of their nightly pg_dump output and attempting to
restore it on my development box, running PostgreSQL
7.4.12.

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. After
leaving pg_restore running for about 2 days, I ctrl-C'ed
out of it (see copy/paste below along with other info).

I must say, that data was being restored, as I could do
select count(*) on tables which had their data restored and
I would get valid counts back.

The database contains 34 tables. The pg_restore seems to
restore the first 13 tables pretty quickly, but they do not have
many records. The largest amongst them with ~ 17,000 rows.

Then restore gets stuck on a table with 2,175,050 rows.
Following this table another table exists with 2,160,616
rows.

One thing worth mentioning is that the PostgreSQL package
that got deployed lacked compression, as in:

$ pg_dump -Fc dbname > dbname.DUMP
pg_dump: [archiver] WARNING: requested compression not available in
this installation -- archive will be uncompressed


Any suggestions as to what may be the problem here?
I doubt that the minor version mis-match is what's causing
this problem. (I am try this test on another machine with the
same version of PostgreSQL installed on it, and right now,
it is stuck on the first of the two huge tables, and it has
already been going for more than 2 hrs).

I'm open to any ideas and/or suggestions (within reason) :)

Best regards,
--patrick


me@devbox:/tmp$ date
Mon Apr 10 15:13:19 PDT 2006
me@devbox:/tmp$ pg_restore -ad dbname customer_db.DUMP ; date
^C
me@devbox:/tmp$ date
Wed Apr 12 10:40:19 PDT 2006

me@devbox:/tmp$ uname -a
Linux devbox 2.4.31 #6 Sun Jun 5 19:04:47 PDT 2005 i686 unknown
unknown GNU/Linux
me@devbox:/tmp$ cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 8
model name      : Pentium III (Coppermine)
stepping        : 6
cpu MHz         : 731.477
cache size      : 256 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 mmx fxsr sse
bogomips        : 1461.45

me@devbox:/tmp/$ cat /proc/meminfo
        total:    used:    free:  shared: buffers:  cached:
Mem:  527499264 523030528  4468736        0 10301440 384454656
Swap: 1579204608   552960 1578651648
MemTotal:       515136 kB
MemFree:          4364 kB
MemShared:           0 kB
Buffers:         10060 kB
Cached:         374984 kB
SwapCached:        460 kB
Active:          79004 kB
Inactive:       306560 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:       515136 kB
LowFree:          4364 kB
SwapTotal:     1542192 kB
SwapFree:      1541652 kB


postgresql.conf changes on devbox:
checkpoint_segments = 10
log_pid = true
log_timestamp = true

The checkpoint_segments was changed to 10 after
seeing many "HINT"s in PostgreSQL log file about it.
Doesn't seem to have affected pg_restore performance.

Re: pg 7.4.x - pg_restore impossibly slow

From
Tom Lane
Date:
"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

Re: pg 7.4.x - pg_restore impossibly slow

From
"patrick keshishian"
Date:
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

Re: pg 7.4.x - pg_restore impossibly slow

From
Tom Lane
Date:
"patrick keshishian" <pkeshish@gmail.com> writes:
> With these settings and running:
> pg_restore -vaOd dbname dbname.DUMP

If you had mentioned you were using random nondefault switches, we'd
have told you not to.  -a in particular is a horrid idea performancewise
--- a standard schema-plus-data restore goes way faster because it's
doing index builds and foreign key checks wholesale instead of
incrementally.

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

It is documented in recent versions of the documentation: see
http://www.postgresql.org/docs/8.1/static/populate.html
particularly the last section.

            regards, tom lane

Re: pg 7.4.x - pg_restore impossibly slow

From
"Jim C. Nasby"
Date:
On Thu, Apr 13, 2006 at 06:26:00PM -0700, patrick keshishian wrote:
> $ dropdb dbname
> $ createdb dbname
> $ pg_restore -vsOd dbname dbname.DUMP

That step is pointless, because the next pg_restore will create the
schema for you anyway.

> $ date > db.restore ; pg_restore -vcOd dbname \
>     dbname.DUMP ; date >> db.restore
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: pg 7.4.x - pg_restore impossibly slow

From
"patrick keshishian"
Date:
On 4/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "patrick keshishian" <pkeshish@gmail.com> writes:
> > With these settings and running:
> > pg_restore -vaOd dbname dbname.DUMP
>
> If you had mentioned you were using random nondefault switches, we'd

Random?

With all due respect, I did.

I specified the PostgreSQL version of the pg_dump source
server. I specified the version of my dev PostgreSQL server.

I provided specific information about which postgresql.conf
entries I had changed and to what specific values they were
changed to.

I pasted the _exact_ command used (including so called
"random nondefault switches") to do the dump and the
exact command used (again, with said "random nondefault
switches") to restore from the dump'ed data.

I believe I tried my best to be as thorough as possible with
my post(s).

archived at: http://archives.postgresql.org/pgsql-performance/2006-04/msg00287.php



> have told you not to.  -a in particular is a horrid idea performancewise
> --- a standard schema-plus-data restore goes way faster because it's
> doing index builds and foreign key checks wholesale instead of
> incrementally.

Duly noted. Option "-a" bad.


> > 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?
>
> It is documented in recent versions of the documentation: see
> http://www.postgresql.org/docs/8.1/static/populate.html
> particularly the last section.

As a general rule of thumb, I have always assumed,
documentation for any software, from one major version
to another, would not necessarily apply cross revisions
(e.g., version 7.4 vs 8.1).


But thanks for your time and help,
--patrick

Re: pg 7.4.x - pg_restore impossibly slow

From
"patrick keshishian"
Date:
On 4/13/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> On Thu, Apr 13, 2006 at 06:26:00PM -0700, patrick keshishian wrote:
> > $ dropdb dbname
> > $ createdb dbname
> > $ pg_restore -vsOd dbname dbname.DUMP
>
> That step is pointless, because the next pg_restore will create the
> schema for you anyway.

Yes, I noticed this with the verbose output (random
non-standard option "-v").

I was providing all information (read: exact steps taken)
which may have been relevant to my post/question, so
that, I would avoid being guilty of omitting any possibly
significant, yet random information.

Thanks for the insight,
--patrick