Обсуждение: [pg 7.1.rc2] pg_restore and large tables
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
--- 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
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 >
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)