You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@apr.apache.org by Bojan Smojver <bo...@rexursive.com> on 2008/07/30 09:15:30 UTC

SQLite3 tuning

After running some tests with worker MPM against SQLite3 database, it
turns out that performance can be improved by doing linear backoff in
the wait loop (although SQLite is still quite bad when it comes to
highly concurrent apps). Patch is attached.

Comments?

-- 
Bojan

Re: SQLite3 tuning

Posted by Bojan Smojver <bo...@rexursive.com>.
On Thu, 2008-07-31 at 07:30 +1000, Bojan Smojver wrote:

> I may just rework it with that instead.

On second thought, it may not be that simple, given the DBD mutex we
have in place...

-- 
Bojan


Re: SQLite3 tuning

Posted by Bojan Smojver <bo...@rexursive.com>.
On Thu, 2008-07-31 at 07:30 +1000, Bojan Smojver wrote:
> I could get about 40 times as many pages out.

Eh, not true, actually. Never mind - please ignore me :-(

-- 
Bojan


Re: SQLite3 tuning

Posted by Bojan Smojver <bo...@rexursive.com>.
On Wed, 2008-07-30 at 06:53 -0400, Tom Donovan wrote:

> Calling sqlite3_busy_timeout() in dbd_sqlite3_open might be better than using the
>    do { ... apr_sleep() ... }
> loop in dbd_sqlite3_select_internal.

Yeah, true. I may just rework it with that instead. Thanks for the hint!

> Nevertheless, I also see that SQLite is quite slow for concurrent updates; despite being very fast 
> for reads.

Also true, but with the current 100 ms sleep, it is not just slow - it
is dead slow. At one point I thought Apache hung or something during my
testing, but it was just sleeping for a long time in all of its threads.
Once I switched from the original behaviour to linear backoff, I could
get about 40 times as many pages out. Worth fixing.

-- 
Bojan


Re: SQLite3 tuning

Posted by Tim Bray <Ti...@Sun.COM>.
On Jul 30, 2008, at 3:53 AM, Tom Donovan wrote:

> Nevertheless, I also see that SQLite is quite slow for concurrent  
> updates; despite being very fast for reads.

SQLite is truly great software, but I totally wouldn't recommend it  
for high-concurrency apps.  -T


Re: SQLite3 tuning

Posted by Tom Donovan <do...@bellatlantic.net>.
Bojan Smojver wrote:
> After running some tests with worker MPM against SQLite3 database, it
> turns out that performance can be improved by doing linear backoff in
> the wait loop (although SQLite is still quite bad when it comes to
> highly concurrent apps). Patch is attached.
> 
> Comments?
> 
This is similar to what SQLite3 does internally if you call sqlite3_busy_timeout() after sqlite3_open().

See the sqliteDefaultBusyCallback() function in the SQLite3 source file main.c.
ref: http://www.sqlite.org/c3ref/busy_timeout.html

Calling sqlite3_busy_timeout() in dbd_sqlite3_open might be better than using the
   do { ... apr_sleep() ... }
loop in dbd_sqlite3_select_internal.

The sqlite3_busy_timeout() function has been in SQLite since version 3.0, so no worries about 
someone having an older version.

Nevertheless, I also see that SQLite is quite slow for concurrent updates; despite being very fast 
for reads.

-tom-