Re: 8.3 / 8.2.6 restore comparison

On Thu, 07 Feb 2008 11:20:49 -0500
Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

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

Depends on how you look at it. Restores are traditionally CPU bound if
you have any kind of IO. If you have RAID 0 you have some IO available
to you. It bothers me that the environment I am testing which has 16
spindles (8 per volume) is garnering such miserable performance in
comparison.

In my environment we aren't able to push the I/O at all (wait times <
3%) and yet these are Opterons that we have which have great memory
bandwidth. 8.2 did use more I/O than 8.3 but I think that is just
architectural with the change to 8.3 in general.

Joshua D. Drake
--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit