Re: [sqlite] sqlite ext3 settings

[Simon Slavin]

On 10 Nov 2016, at 10:03am, Dan Kennedy  wrote:

> It seems to be that if your drive has write-caching enabled, you need 
> barrier=1 to avoid risking corruption. Or you can get away with barrier=0 if 
> write-caching is disabled.

To supplement Dan's answer, having write-caching enabled is always a problem 
with ACID storage, unless it's defeated by other settings.  Because the thing 
which looks at the cache and writes it to disk is not limited to writing writes 
in order.  So you cannot guarantee that your changes are written to disk in the 
order they were made.

Illustration: Suppose your program modifies the contents of these sectors in 
order:

A B C D B E

When it gets to the second 'B' the mechanism which caches writes notices that 
it already has data for B and just replaces that data in the cache.  The first 
write to B never happens.  Instead, even if it happens to write all the other 
sectors in order it actually writes one of these two sequences, depending on 
how it's programmed:

A B C D E
A C D B E

In both sequences the first the change to sector C gets written before the 
first change to sector B.  And power could fail, or the disk subsystem could 
glitch, between them.  If SQLite recovers a file after a crash it goes some way 
towards detecting this but it's impossible to completely compensate for it.

You can ask why write-caching does this thing.  Well, it has two advantages.  
One is that it uses less memory (store just one copy of sector B in memory even 
if it's part of a frequently-changed log file).  The other is that it saves 
time (write all the changes to sector B in one operation).  And those two 
things are the main reasons people would turn write-caching on in the first 
place.

Some storage drivers have an EIOW (Enforce In Order Writes) 
directive/switch/patch.  Or an equivalent, like the 'barrier=1' switch when you 
mount the drive, that Dan mentioned, but one of the things that does is ensure 
that write-caching is not fully used.

> the possibility of losing data due to power loss

A useful way to proceed if you care about that, is to look at each setting 
which looks like it speeds things up and ask yourself "If it was okay to have 
this on all the time, why would they have provided the other option ?".  It's 
not nice, but it does help you consider the aspects.  And it can slow down 
operation considerably.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite ext3 settings

[Dan Kennedy]

On 11/10/2016 06:25 AM, Andrii Motsok wrote:

Hi,


I have sqlite database on top of ext3 (on ssd).


Does

   1) ext3 parameters (data=writeback|ordered,barrier=0|1, etc)

   2) drive write-caching setting (hdparm -W) value

   3) ???


There's a thread here:

  https://www.redhat.com/archives/ext3-users/2010-July/msg0.html

It seems to be that if your drive has write-caching enabled, you need 
barrier=1 to avoid risking corruption. Or you can get away with 
barrier=0 if write-caching is disabled.


Also:

  http://www.sqlite.org/draft/lockingv3.html#ext3-barrier-problem

Dan.





have impact on the possibility of losing data due to power loss? If yes which 
parameters should be used to achieve maximum safety against loosing of the data 
due to power loss during writing to WAL database?

   3) Are there any other settings (system, sqlite, etc) which could increase 
protection against data corruption after power lost or system crash?


Regards,

Andrii

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite ext3 settings

[Andrii Motsok]

Hi,


I have sqlite database on top of ext3 (on ssd).


Does

  1) ext3 parameters (data=writeback|ordered,barrier=0|1, etc)

  2) drive write-caching setting (hdparm -W) value

  3) ???

have impact on the possibility of losing data due to power loss? If yes which 
parameters should be used to achieve maximum safety against loosing of the data 
due to power loss during writing to WAL database?

  3) Are there any other settings (system, sqlite, etc) which could increase 
protection against data corruption after power lost or system crash?


Regards,

Andrii

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users