Re: slow update of index during insert/copy

On Mon, Sep 1, 2008 at 12:41 PM, Gregory Stark <stark(at)enterprisedb(dot)com>wrote:

> "Scott Carey" <scott(at)richrelevance(dot)com> writes:
>
> > 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.
>
> This could be read the wrong way. With a BBU it's not that you can run the
> drives in write-back mode safely. It's that you can cache in the BBU
> safely.
> The drives still need to have their write caches off (ie, in write-through
> mode).
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
> training!
>

Actually, the drive level write-cache does not have to be disabled, the
controller just has to issue a drive write-cache-flush and use write
barriers appropriately. They are only a problem if the controller assumes
that data that it sent to the drive has gotten to the platters without
checking up on it or issuing a cache flush command to validate that things
are on the platter. The controller, if its any good, should handle this
downstream configuration or document that it does not. What is appropriate
will vary, see documentation.
Drive write caches are 100% safe when used appropriately. This is true with
or without RAID, but in the case of a non-RAID or software raid setup the
file system and OS have to do the right thing. It true that many
combinations of file system + OS (Linux LVM, for just one example) don't
necessarily do the right thing, and some RAID controllers may also behave
badly. The safe thing is to turn off drive write back caches if in doubt,
and the performance degradation caused by disabling it will be less for a
good hardware RAID card with a large cache than in other cases.

Likewise, the safe thing is not to bother with write-back cache on the raid
controller as well -- it protects against power failure but NOT various
hardware failures or raid card death. I've seen the latter, where upon
power loss and restore, the raid card was broken, and thus it could not
flush the data it had in RAM (assuming it was still there) to disk.
Luckily, after getting another card and loading up the db, there was no
corruption and we went on our way. Never, ever assume that your raid array
+ BBU are fail-safe. All that stuff makes failure a lot less likely, but
not 0.