Re: 8.3 / 8.2.6 restore comparison

Joshua D. Drake wrote:
> Hello,
>
> I have been testing a migration for a week now trying to get it into a
> reasonable state. This is what we have:
>
> 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.
>
> Originally I was thinking that 8.2.6 was stomping 8.3. However I am
> thinking that the reduction in the tuple header sizes for 8.3 means
> that yes I restored 38GB, it is actually *more* data than 8.2.6. Does
> that seem accurate to everyone else? If so what can we do to speed this
> up? We are certainly *not* saturating the disk (16 spindles SCSI).
>
> I am thinking the way we are going to need to do this is to have an
> extended outage and write a custom script to do a concurrent dump and
> load. (no in this case slony is not an option).
>
>

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.

cheers

andrew