SQLite optimization and async I/O

[Olivier Guilyardi]

Hello,

In the last release of my app, I added an SQLite database, to deal with file
cache and audio metadata. So I'm now bundling my own SQLite library, v3.7.6.2.

But, on some devices, and especially the Samsung S GT-I9000 Froyo, writing to
the database can be very slow sometimes. It seems to come from the SDCard I/O
latency. It doesn't really freeze but it's close. I'm storing the database on
the SDCard because, in some cases, users need to reinstall the app, and the data
mustn't vanish.

So I found this documentation about the SQLite Asynchronous I/O Module:
http://www.sqlite.org/asyncvfs.html

It is supposed to delay writes so that they return immediately, by relying on a
background thread. But I can't find much feedback about it on the web. It seems
to be quite rarely used, so I'm a bit worried. Plus, this background thread is a
bit dangerous, it needs to be handled cautiously and would certainly require a
Service. That said, if it provides massive optimization then I may go for it.

But what would you recommend to deal with such SQLite latencies on Android?
Any experience with this async I/O module?

Thanks in advance

--
Olivier

[Dianne Hackborn]

I would suggest just doing all of your SQLite access from a background thread that is not going to block your UI.  In fact you should seriously consider doing all disk access off the main UI thread, since disk access can block for a not insignificant amount of time and cause UI glitches.

--
You received this message because you are subscribed to the Google Groups "android-ndk" group.
To post to this group, send email to andro...@googlegroups.com.
To unsubscribe from this group, send email to android-ndk...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/android-ndk?hl=en.

--
Dianne Hackborn
Android framework engineer
hac...@android.com

Note: please don't send private questions to me, as I don't have time to provide private support, and so won't reply to such e-mails.  All such questions should be posted on public forums, where I and others can see and answer them.

[Olivier Guilyardi]

Thank you Dianne for this advice. I'm actually writing very few values (per-file
display properties) from the UI thread, when the activity gets paused, and was
maybe naively thinking this would be ok. But it does produce bad glitches.

Actually, it seems like you recommend to perform both read and write in the
background, so this SQLite async I/O module is maybe not ideal, since it may
still block when reading. That said, reads are much faster than writes AFAICS.

But I will consider refactoring some parts, to maybe perform all read and write
operations in the background.

Thanks

Olivier

> <mailto:andro...@googlegroups.com>.

> To unsubscribe from this group, send email to
> android-ndk...@googlegroups.com

> <mailto:android-ndk%2Bunsu...@googlegroups.com>.

> For more options, visit this group at
> http://groups.google.com/group/android-ndk?hl=en.
>
>
>
>
> --
> Dianne Hackborn
> Android framework engineer

> hac...@android.com <mailto:hac...@android.com>

>
> Note: please don't send private questions to me, as I don't have time to
> provide private support, and so won't reply to such e-mails. All such
> questions should be posted on public forums, where I and others can see
> and answer them.
>

[muri...@gmail.com]

Check the compilation flags you used for the Sqlite. Should run extremely fast. I have a database with over 1.5 million records and is instant. Also check the schema and if you write on multiple tables or you do multiple inserts embed the operations in a transaction.
If the data is small, backup the db online and restore after the reinstall.

Mike
Sent using BlackBerry® from Orange

[Olivier Guilyardi]

To clarify: on many devices, it runs very fast, but on some specific device,
especially the Samsung S, the first write can take several seconds. Subsequent
writes take just a few ms. I'm talking about writing a single value to table
with less than 500 records.

Here are the flags that I use for amalgated sqlite3.c v3.7.6.2:

LOCAL_CFLAGS := -DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 \
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 \
-DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DHAVE_USLEEP=1 \
-DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 \
-DHAVE_STRERROR_R=1 -DSQLITE_THREADSAFE=1 -Dfdatasync=fsync

What you think?

A question: are you storing the database in internal storage? I sense that this
may run much faster.

About backing up the db online, this isn't an option. My app doesn't require any
internet access permission. Plus, in the past, some users seem to have
encountered corrupted installations or so, after upgrading, etc.. In these
cases, a reinstall was needed, and there's no way to perform an automatic online
backup. Plus I have a small directory on the SDCard for mmap files of a few MB,
so I use this directory for storing the database. That makes reinstalling safe.

Olivier

[Dianne Hackborn]

When we are talking about running on the UI thread, SQLite isn't the concern, just plain disk access is the concern.

If you are touching storage, *especially* writing to it, from the main UI thread then I can guarantee you that you will see glitches during animations like flings through a list view.  Not all the time, but they will happen.  There are just no strong guarantees for how fast access to storage may happen -- if there is contention with other processes you may get blocked, if the flash storage needs to do some garbage collection you may get blocked, etc.

[Olivier Guilyardi]

I see your point Dianne. I will try and follow this advice.

While we're talking about disk access, I asked another question in the past
which is left unanswered. I have a background thread which does some long and
intensive disk access, and I've seen that this can badly affect overall system
responsiveness.

I tried setpriority() with no success, unless I use a very low priority which
makes the job takes a huge time to complete. So I'm calling usleep() after every
buffer or so. That helps but it's not perfect.

Sorry this is getting a little off-topic, but would it be ok to try and lower
disk I/O priority with ionice or some corresponding syscalls?

Olivier

> > <mailto:andro...@googlegroups.com

> <mailto:andro...@googlegroups.com>>.
> > To unsubscribe from this group, send email to
> > android-ndk...@googlegroups.com
> <mailto:android-ndk%2Bunsu...@googlegroups.com>

> > <mailto:android-ndk%2Bunsu...@googlegroups.com
> <mailto:android-ndk%252Buns...@googlegroups.com>>.

> > For more options, visit this group at
> > http://groups.google.com/group/android-ndk?hl=en.
> >
> >
> >
> >
> > --
> > Dianne Hackborn
> > Android framework engineer
> > hac...@android.com <mailto:hac...@android.com>

> <mailto:hac...@android.com <mailto:hac...@android.com>>

> >
> > Note: please don't send private questions to me, as I don't have
> time to
> > provide private support, and so won't reply to such e-mails. All such
> > questions should be posted on public forums, where I and others
> can see
> > and answer them.
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "android-ndk" group.

[Dianne Hackborn]

Most flash fs drivers I believe don't do IO prioritization at this point.

[Olivier Guilyardi]

I see. Thanks for this precision.

There is a subtlety that I forgot to mention. The overall system responsiveness
is badly affected when reading continuously a long file in the background, from
C code, using read(). (This is read in small buffers for analysis purposes only,
there's no big memory buffer being allocated).

But, in my recent tests, I've seen that when copying large files using Java
FileChannel, there's no such problem. The system runs smoothly while the copy is
being performed.

Do you have an idea of what's going on here? What's so smart about Java and/or
FileChannel regarding file I/O?

Olivier

> > <mailto:muri...@gmail.com <mailto:muri...@gmail.com>>> wrote:
> >
> > Check the compilation flags you used for the Sqlite. Should run
> > extremely fast. I have a database with over 1.5 million
> records and
> > is instant. Also check the schema and if you write on multiple
> > tables or you do multiple inserts embed the operations in a
> transaction.
> > If the data is small, backup the db online and restore after the
> > reinstall.
> >
> > Mike
> > Sent using BlackBerry� from Orange
> >
> > -----Original Message-----
> > From: Olivier Guilyardi <li...@samalyse.com
> <mailto:li...@samalyse.com> <mailto:li...@samalyse.com
> <mailto:li...@samalyse.com>>>
> > Sender: andro...@googlegroups.com
> <mailto:andro...@googlegroups.com>
> > <mailto:andro...@googlegroups.com
> <mailto:andro...@googlegroups.com>>
> > Date: Tue, 21 Jun 2011 14:07:46
> > To: <andro...@googlegroups.com
> <mailto:andro...@googlegroups.com>

> <mailto:andro...@googlegroups.com

> <mailto:andro...@googlegroups.com>>>
> > Reply-To: andro...@googlegroups.com
> <mailto:andro...@googlegroups.com>

> > <mailto:andro...@googlegroups.com

> > > <mailto:android-ndk%2Bunsu...@googlegroups.com
> <mailto:android-ndk%252Buns...@googlegroups.com>
> > <mailto:android-ndk%252Buns...@googlegroups.com
> <mailto:android-ndk%25252Bun...@googlegroups.com>>>.

[Tim Mensch]

On 6/22/2011 6:48 AM, Olivier Guilyardi wrote:
> Do you have an idea of what's going on here? What's so smart about Java and/or
> FileChannel regarding file I/O?

In the discussion about memory mapped files, it was brought up that
Android uses memory mapped file I/O all over the place. You can get a
lot higher read speed by using that, for two reasons that I know of --
it prevents an extra copy of the data, and the reads happen at
kernel-level priority instead of user-level, and ... I can't remember
why that's supposed to help, but I think it is. :)

Tim

[Olivier Guilyardi]

IIRC, I tried with mmap and it didn't help. And my problem isn't speed here,
it's overall system responsiveness when doing intensive disk I/O. But it's
getting off-topic here, here's my original (unanswered) post:
http://groups.google.com/group/android-ndk/msg/3fd1e5b4d8322528

--
Olivier

[Dianne Hackborn]

What priority are the two threads running at?  If you are doing background work you should make sure to set your thread to background priority.  You also should do this using the android.os.Process API since that takes care of adjusting cgroups or whatever mechanism may be used to better control scheduling.

Other information that would be useful -- how long the two versions take to complete, how much CPU the two versions use while running.

And of course you can go and look at the code of FileChannel to see how that is implemented (I don't know off-hand).

--
You received this message because you are subscribed to the Google Groups "android-ndk" group.
To post to this group, send email to andro...@googlegroups.com.
To unsubscribe from this group, send email to android-ndk...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/android-ndk?hl=en.

--
Dianne Hackborn
Android framework engineer
hac...@android.com

[Olivier Guilyardi]

Hello Dianne,

Some of your questions are answered in my original post named "Background thread
I/O priority" at:
http://groups.google.com/group/android-ndk/msg/3fd1e5b4d8322528

If you don't mind, I'm quoting you and adding some precisions in this other
thread, because it's getting very off-topic here.

Olivier

> <mailto:andro...@googlegroups.com>.

> To unsubscribe from this group, send email to
> android-ndk...@googlegroups.com

> <mailto:android-ndk%2Bunsu...@googlegroups.com>.

> For more options, visit this group at
> http://groups.google.com/group/android-ndk?hl=en.
>
>
>
>
> --
> Dianne Hackborn
> Android framework engineer

> hac...@android.com <mailto:hac...@android.com>

>
> Note: please don't send private questions to me, as I don't have time to
> provide private support, and so won't reply to such e-mails. All such
> questions should be posted on public forums, where I and others can see
> and answer them.
>

[Olivier Guilyardi]

For the records, I'm now pretty sure that huge write delays are caused by
fsync() calls performed by SQLite. So, I have found a workaround, which is a bit
dangerous, but I can live with it for now I think.

"PRAGMA synchronous = <OFF|NORMAL|FULL> " is used to control how SQLite calls
fsync().

By default it's FULL, which means a lot of fsync().

My workaround consists in turning this PRAGMA to OFF at very rare occasions, in
particular when writing a couple values to the database from onPause(). That
makes it much faster.

There's a small risk for the database to get corrupted, but it's minimized by 1.
the fact that I leave it to FULL by default and use OFF very rarely, and 2. I
have another process which necessarily writes to the database quite soon after
the activity pauses and use the FULL synchronous mode, so fsync() is quite
rapidly called.

It's not super clean, but it seems to work fine. Making certain writes
asynchronous is pretty problematic at the moment so I'm quite happy with this
workaround.

Olivier

>> Sent using BlackBerryŽ from Orange