Should SQLite users be setting barrier=1?

[Ric Wheeler]

On 07/13/2010 12:56 PM, Dan Kennedy wrote:
>
>> If you do not use barriers, your storage device could very well lose
>> data if it loses power. There is no easy answer, you need to
>> understand the type and configuration of your storage.
>>
>> For a local SAS/S-ATA drive, you should have barriers enabled when the
>> write cache is enabled (check that with hdparm for example on S-ATA).
>> Note that you could also be safe by disabling the write cache and
>> leaving barriers off as well.
>>
>> If you have a non-volatile write cache (for example on an external,
>> enterprise class array), you can safely mount without barriers.
>>
>> Regards,
>>
>> Ric
>
>
>
> Hi Ric,
>
> Thanks very much for the quick response (and Eric, thanks
> as well).
>
> Richard put a paragraph with a link to your answer in
> our documentation here:
>
> http://www.sqlite.org/draft/lockingv3.html#ext3-barrier-problem
>
> Please let us know if this misrepresents the situation.
> Or if there is something else we should add to clarify
> it.
>
> Thanks again,
> Dan.
>
>

I would suggest that the drives are designed to cache data - this is not a 
"defective" controller per se. Think of it like lossy vs lossless audio encoding 
- by design, you get more (performance) but pay a price in data integrity.

The traditional, easy answer was always "disable the write cache" on SAS or 
S-ATA drives, but the barriers do allow you to get back most of the performance.

One other note is that you should be very careful not to use fsync() too much. 
Best to use it intentionally (for example, in your commit phase) than to 
sprinkle it in too often. I know that database people understand this, think of 
the fsync() as our file system level commit since it costs a lot to do, but 
carries data integrity promises with it :-)


ric