Brad Nicholson wrote:
> On 8/10/2010 12:21 PM, Greg Smith wrote:
>> Scott Carey wrote:
>>> Also, the amount of data at risk in a power loss varies between
>>> drives. For Intel's drives, its a small chunk of data ( < 256K).
>>> For some other drives, the cache can be over 30MB of outstanding
>>> writes.
>>> For some workloads this is acceptable
>>
>> No, it isn't ever acceptable. You can expect the type of data loss
>> you get when a cache fails to honor write flush calls results in
>> catastrophic database corruption. It's not "I lost the last few
>> seconds"; it's "the database is corrupted and won't start" after a
>> crash. This is why we pound on this topic on this list. A SSD that
>> fails to honor flush requests is completely worthless for anything
>> other than toy databases. You can expect significant work to recover
>> any portion of your data after the first unexpected power loss under
>> heavy write load in this environment, during which you're down. We
>> do database corruption recovery at 2ndQuadrant; while I can't talk
>> about the details of some recent incidents, I am not speaking
>> theoretically when I warn about this.
>
> What about putting indexes on them? If the drive fails and drops
> writes on those, they could be rebuilt - assuming your system can
> function without the index(es) temporarily.
You could put indices on them but as noted by Scott, he's SPOT ON.
ANY disk that says "write is complete" when it really is not is entirely
unsuitable for ANY real database use. It is simply a matter of time
before you have a failure of some sort that results in catastrophic data
loss. If you're LUCKY the database won't start and you know you're in
trouble. If you're UNLUCKY the database DOES start but there is
undetected and unrecoverable data corruption somewhere inside the data
tables, which you WILL discover at the most-inopportune moment (like
when you desperately NEED that business record for some reason.) You
cannot put either the tables or the logs on such a drive without running
the risk of a data corruption problem that WILL lose data and MAY be
catastrophic, depending on exactly what fails when.
While it is possible to recover that which is not damaged from a
database that has corruption like this it simply is not possible to
recover data that never made it to the disk - no matter what you do -
and the time and effort expended (not to mention money if you have to
bring in someone with specialized skills you do not possess) that result
from such "decisions" when things go wrong are extreme.
Don't do it.
-- Karl