Re: [SQLite] Use of db:exec cause 5 second delay

[Gilles Ganault <gilles.ganault@...>]

On Wed, 29 Jan 2014 20:48:21 -0500, David Favro <lua@meta-dynamic.com>
wrote:
>Ext4 has little or nothing to do with it [1], but sqlite and flash storage 
>has a lot.
>
>If you need your database to be preserved across power loss, don't put it on 
>a tmpfs; if you don't, you might want to consider a different database.
>
>As I mentioned before, sqlite syncs to disk after every transaction. 
>Writing to cheap flash (especially cheap SD cards) is _very_ slow.
>
>Typically, when using sqlite the best idea is to wrap operations that are 
>all executed at once into transactions (this is often desirable for other 
>reasons as well, such as atomicity and serializability); you did 4 
>operations and when you wrapped it you got a reduction by about a factor of 
>4, etc.  I only showed you the first exec() wrapped in the xaction because 
>that's what you complained was slow.  You can also put the next SELECT 
>inside the transaction and you can generate your mg.write()s without delay. 
>  If the DB is accessed by only one process you may be able to create some 
>long-running transactions but generally this is not a good idea.
>
>In your case, you can disable the sync of the write-ahead log by executing 
>this commands after you open your database [2]:
>PRAGMA synchronous = OFF;
>This should eliminate the delay, at the cost of leaving you open to lost 
>data if e.g. your appliance loses power after a write.  But that's basically 
>what you are asking for anyhow: the only way to guarantee that the data is 
>physically written is to physically write it, and your storage is slow for 
>writes.
>
>-- David
>
>[1]: the filesystem's metadata journal (which may or may not be present on 
>either an ext3 or ext4 FS, but is not on ext2) might make the delay slightly 
>longer, but even if you removed it you would likely not notice the difference.
>
>[2]: http://www.sqlite.org/pragma.html#pragma_synchronous

Thanks much David.

More information about ext4:
http://stackoverflow.com/questions/13891920/