Joshua D. Drake wrote:
On Thu, 07 Feb 2008 09:47:08 -0500
Andrew Dunstan <[EMAIL PROTECTED]> wrote:
Restore file 220G
8.2.6 and 8.3.0 are configured identically:
shared_buffers = 8000MB
work_mem = 32MB
maintenance_work_mem = 512MB
fsync = off
full_page_writes = off
checkpoint_segments = 300
synchronous_commit = off (8.3)
wal_writer_delay = off (8.3)
autovacuum = off
8.2.6 after 2 hours has restored 41GB.
8.3.0 after 2.5 hours had restored 38GB.
I just tested a ~110GB load. On our modest backup server, 8.2
yesterday did the data load (i.e. the COPY steps) in 1h57m. Today,
8.3 on identical data and settings took 1h42m. Relation size is down
by about 10% too, which is very nice, and probably accounts for the
load time improvement.
Ergghh o.k. I am definitely missing something in the environment. By
your numbers I should be well over 100GB restored at 2.5 hours. I am
not. I am only 38GB in.
What type of IO do you have on that machine? What type of CPU and RAM?
2Ghz Xeon dual core
16 Gb RAM
HW RAID0 data store - not sure how many spindles
I didn't touch any of the 8.3-only config stuff. I have work_mem set a
lot higher than you do, though - not sure if that makes any difference
to a bulk load.
This is not a very heavy duty box.
Note: a full restore takes much longer than this - it is almost all
taken up building indexes. I will be testing that over the weekend,
probably.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
