Re: 8.3 / 8.2.6 restore comparison

Joshua D. Drake wrote:
> On Thu, 07 Feb 2008 09:47:08 -0500
> Andrew Dunstan <andrew@dunslane.net> 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