Re: SQLite on flash (was: [PATCH 00/16] f2fs: introduce flash-friendly file system)

[Clemens Ladisch <clemens@ladisch.de>]

(CC'd sqlite-users ML)
Theodore Ts'o wrote:
> On Tue, Oct 09, 2012 at 02:53:26PM -0500, Jooyoung Hwang wrote:
>> I'd like you to refer to the following link as well which is about
>> mobile workload pattern.
>> http://www.cs.cmu.edu/~fuyaoz/courses/15712/report.pdf
>> It's reported that in Android there are frequent issues of fsync and
>> most of them are only for small size of data.
>
> What bothers me is no one is asking the question, *why* is Android
> (and more specifically SQLite and the applications which call SQLite)
> using fsync's so often?  These aren't transaction processing systems,
> after all.

Neither were Firefox's bookmarks and history.  That one got fixed,
but it was a single application.

> So there are two questions that are worth asking here.
> (a) Is SQLite being as flash-friendly as possible,

It would be possible to use the write-ahead log instead of the default
rollback journal, but that is unfortunately not entirely compatible --
HTC once enabled WAL by default on some phones, and all apps that tried
to open a database in read-only mode broke.  If apps are aware of this,
they can enable WAL for their own DBs without problems.

There are some other configuration options, but they, too, have side
effects and thus cannot be enabled by default.

SQLite 4 (currently being developed) will use a log-structured merge
database.

> and (b) do the applications really need as many transaction boundaries
> as they are requesting of SQLite.

Most apps get the default of one transaction per statement because they
do not bother to mange transactions explicitly.


Regards,
Clemens