Thread: [pg 7.1.rc2] pg_restore and large tables

[pg 7.1.rc2] pg_restore and large tables

From
ow
Date:
Hi,

Trying to restore a table that has about 80 million records. The database was
dumped and restored according to the following procedure:

1) dump the db, data only
time /usr/local/pgsql/bin/pg_dump -abf ./pgsql.7.4.rc1.pgdump.Z --format=c
--compress=6 -U postgres testdb

2) create db schema from a separate file, including table structures,
constraints, indexes
3) edit restore order to satisfy the constraints
4) restore the db
time /usr/local/pgsql/bin/pg_restore -d testdb -U postgres -a
./pgsql.7.4.rc1.pgdump.Z -L ./restoreOrder.txt --verbose

pg_restore has been running for 14 hours now and it does not appear that
there's any end in sight. Meanwhile, postmaster is slowly eating away at the
memory, it now has 46% of all available memory with about 900MB on swap. HD
activity is non-stopping.

In retrospective, I guess, the table with 80M records could've been created
without indexes (it has 3, pk & ak constraints and fk index) to speed up the
pg_restore ... but then I'm not sure if creating the indexes afterwards
would've been much faster. Anything I'm doing wrong?

Thanks










__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: [pg 7.4.rc2] pg_restore and large tables

From
ow
Date:
--- ow <oneway_111@yahoo.com> wrote:
>
> Hi,
>
> Trying to restore a table that has about 80 million records. The database was
[...]

I meant pg7.4.rc2, of course. Thanks













__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: [pg 7.1.rc2] pg_restore and large tables

From
Shane Wright
Date:
Hi,

I have found, on 7.3.4, a _massive_ performance difference on restoring
without indices - on a 25million row table from 8 hours down to <1
hour!

I've found the best way is to do this... (there may be a script
somewhere that automates this)

- do a --schema-only restore to create the tables

- manually drop the indices using psql

- do a --data-only restore, also using --disable-triggers

- manually recreate the indices.


IIRC, it also helps to turn off fsync

Hope that helps,

Shane

On 12 Nov 2003, at 16:55, ow wrote:

>
> Hi,
>
> Trying to restore a table that has about 80 million records. The
> database was
> dumped and restored according to the following procedure:
>
> 1) dump the db, data only
> time /usr/local/pgsql/bin/pg_dump -abf ./pgsql.7.4.rc1.pgdump.Z
> --format=c
> --compress=6 -U postgres testdb
>
> 2) create db schema from a separate file, including table structures,
> constraints, indexes
> 3) edit restore order to satisfy the constraints
> 4) restore the db
> time /usr/local/pgsql/bin/pg_restore -d testdb -U postgres -a
> ./pgsql.7.4.rc1.pgdump.Z -L ./restoreOrder.txt --verbose
>
> pg_restore has been running for 14 hours now and it does not appear
> that
> there's any end in sight. Meanwhile, postmaster is slowly eating away
> at the
> memory, it now has 46% of all available memory with about 900MB on
> swap. HD
> activity is non-stopping.
>
> In retrospective, I guess, the table with 80M records could've been
> created
> without indexes (it has 3, pk & ak constraints and fk index) to speed
> up the
> pg_restore ... but then I'm not sure if creating the indexes afterwards
> would've been much faster. Anything I'm doing wrong?
>
> Thanks
>
>
>
>
>
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard
> http://antispam.yahoo.com/whatsnewfree
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: [pg 7.1.rc2] pg_restore and large tables

From
Christopher Browne
Date:
me@shanewright.co.uk (Shane Wright) writes:
> I have found, on 7.3.4, a _massive_ performance difference on
> restoring without indices - on a 25million row table from 8 hours down
> to <1 hour!
>
> I've found the best way is to do this... (there may be a script
> somewhere that automates this)
>
> - do a --schema-only restore to create the tables
>
> - manually drop the indices using psql
>
> - do a --data-only restore, also using --disable-triggers
>
> - manually recreate the indices.
>
> IIRC, it also helps to turn off fsync

None of this should come as any great surprise...  All but the "turn
off fsync" part is described in the documentation tree thus:

   <http://www.postgresql.org/docs/7.2/interactive/populate.html>
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)