Re: slow update of index during insert/copy

On Raid Controllers and Dev machines:

For a dev machine the battery backup is NOT needed.

Battery back up makes a _production_ system faster: In production, data
integrity is everything, and write-back caching is dangerous without a
battery back up.

So:
Without BBU: Write-through cache = data safe in power failure; Write back
cache = not safe in power failure.
With BBU : Both modes are safe on power loss.

Write-back is a lot faster for the WAL log in particular.

For a development box, just enable write-back caching regardless of the
battery back up situation. As long as its not your only copy of critical
data you just want to improve performance for the dev box. Just make sure
whatever data on that array can be replaced without condern if you're in the
middle of writing to that data when power fails.
-----------

On JDBC and COPY:
Thanks for the info on the patch to support it -- however the versions
posted there are rather old, and the 8.3 version is not even the same as the
8 month old current release -- its 3 releases prior and 8 months older than
that. There are quite a few bugfixes between 8.3 - v600 and v603:
http://jdbc.postgresql.org/changes.html and that concerns me. Is there a
patched version of the latest driver? Or will that have to be undertaken by
the user -- I worry about a conflict due to one of the changes since v600
listed.

On the performance impact of using COPY instead of INSERT : out of
curiosity, were you comparing COPY against raw row-by-row inserts (slow) or
JDBC batch inserts (faster) or multi-row inserts: INSERT into X (a,b,c)
values (1,2,3) , (4,5,6) , (7,8,9 ) , (10,11,12) ....
?
Copy should be faster than all of these, but I would not expect 5x faster
for the latter two.

On Mon, Sep 1, 2008 at 10:51 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Mon, Sep 1, 2008 at 5:29 AM, Thomas Finneid
> <tfinneid(at)student(dot)matnat(dot)uio(dot)no> wrote:
>
> > It does have a sata raid controller, but not have the battery pack,
> because
> > its a develmachine and not a production machine, I thought it was not
> > needed. But if you are saying the battery pack enables a cache which
> enables
> > faster disk writes I will consider it.
> > Its the first time I have worked with a raid controller, so I suspect I
> have
> > to read up on the features to understand how to utilise it best.
>
> The model of the controller will have a large impact on performance as
> well. The latest fastest RAID controllers have dual core 1.2GHz CPUs
> on them, where some slower ones still in produciton are using 333MHz
> single core CPUs. The quality of the firmware, the linux driver (or
> windows, or bsd) all have a large impact on the performance of a raid
> controller.
>
> Definitely look into the battery backing unit. But if it's a $250
> card, then it might not be enough.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>