Re: Idea for improving speed of pg_restore - Mailing list pgsql-general

From scott.marlowe
Subject Re: Idea for improving speed of pg_restore
Date
Msg-id Pine.LNX.4.33.0309161440410.5612-100000@css120.ihs.com
Whole thread Raw
In response to Re: Idea for improving speed of pg_restore  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Idea for improving speed of pg_restore
Re: Idea for improving speed of pg_restore
List pgsql-general
On Tue, 16 Sep 2003, Tom Lane wrote:

> Christopher Browne <cbbrowne@acm.org> writes:
> > Restoring a database involves, for each table:
> >  1. Reading table data from the source file;
> >  2. Writing data to the database file for the table;
> >  3. After that, reading the database file data, and
> >  4. Writing the sorted bits to the index file.
> >  5. Along with all of this, HEFTY amounts of updates to WAL.
>
> An idea that Marc and Jan and I were kicking around last night was to
> offer a GUC option to disable writes to WAL.  During initial data load,
> you might as well go back to initdb if you have any failure, so why
> bother with full ACID compliance?  I'm not sure if the performance
> benefit would be great enough to make it worth equipping the system
> with such a large-caliber foot-gun, but it's something to think about.
>
> I tend to agree with your doubts about parallelizing index builds,
> but there may be scenarios where it's a win; it'd depend on your
> relative CPU and disk horsepower.  (Consider fast disk and multiple
> not-so-fast CPUs; serial index builds can only use one of the CPUs.)
> Question is, is it a big enough win for enough people to make it worth
> supporting?

I'd say anything that improves postgresql's performance on medium to large
iron is worth at least trying.  I imagine a 12 disk RAID5 with battery
backed RAID would likely be able to use parallelized index builds and even
loads.

Not so sure on whether the foot gun is a good idea.  We already have .22
calibre foot gun (fsync) that makes for pretty big improvements in load
speed, and we see people all the time on General and Performance running
production servers with it turned off.  You know as well as I do the
second we make WAL optional, some people are gonna start running
production servers with it.


pgsql-general by date:

Previous
From: "scott.marlowe"
Date:
Subject: Re: char o varchar
Next
From: Andrew Rawnsley
Date:
Subject: Re: State of Beta 2