Re: 8.3 / 8.2.6 restore comparison

On Thu, 7 Feb 2008 11:54:07 -0500 (EST)
Greg Smith <gsmith(at)gregsmith(dot)com> wrote:

> On Wed, 6 Feb 2008, Joshua D. Drake wrote:
>
> > 8.2.6 after 2 hours has restored 41GB.
>
> I've been doing a long bulk import job recently (COPY) on a box with
> more spindles than yours (but with a dumb controller) and I too am
> stuck at that speed; I calculate a consistant 19.6GB/hour.

Bingo! O.k. that is ~ I am pushing. So I am not alone in this world.

> The
> actual disk I/O is very low as that works out to only 5.7MB/s of
> progress. Mine was bottlenecked by capacity of a single CPU (4X
> Opteron system). I think this is one of those barriers it's hard to
> crack without a caching controller, for reasons I haven't figured out
> completely yet.

O.k. we have 8X but it is also Opteron.

>
> > 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.
>
> If you look at the -performance list this week I've been yelping
> about this issue and trying to figure out how to setup a useful
> multi-CPU loader for cases like these.

I would think that for the custom format this would be reasonably
simple. It is certainly something we need to figure out. The idea that
PostgreSQL can only restore 20G an hour on a box that can actually
write 120+ Megs a second (random) is pathetic.

I know Luke has mentioned some issues in the past as well around CPU
boundness with an upper limit of 300M/s (IIRC) but even that doesn't
equate to what is going on here as we are not getting anywhere near
that.

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