On Tue, Aug 23, 2011 at 09:28:35AM +0200, Sébastien Escudier wrote:
> Hi,
>
> I am currently using sqlite on an ext3 file system with ordered mode.
> But we have serious performance issues when sqlite calls fsync,
> especially on RAID devices.
>
> We noticed that disabling fsync in sqlite OR removing ordered mode
> solves this performance issue.
>
> But I couldn't find if there is a risk of corrupted database in one of
> these solutions.
>
> in http://www.sqlite.org/howtocorrupt.html 3.2, I read that disabling
> sync is risky if the system reorder writes.
> So can I assume that :
> - I can safely disable sqlite syncs in ext3 ordered mode ?
No. For appending data, ordered mode provides you with the guarantee that
the file will either have the appended data in it's entirety or not at all.
For in place updates, all bets are off, data must be fsync'd.
> - I can safely store my databases on an axt3 filesystem without ordered
> mode if I keep sqlite syncs ?
Yes.
I found that I can increase SQLite performance on ext3 by using data=journal.
This way, all synchronous writes become a big sequential write to the journal,
at which point it is considered safely written. You do increase the volume
of data written, but the in place writes to the files can be deferred, removing
latency. I found I could double the number of transactions SQLite could
write using this. Details of my non-scientific test here:
http://www.osnews.com/thread?184137
Another option you might want to investigate is using WAL mode. Similar to
the data=journal mode above, data is written sequentially to a Write Ahead
Log, and copied from there to it's final destination at a later time.
Cheers,
Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
