Hey all! We use SQLite (3.7.15.2) as the backend for a server that provides
floating licenses for our software. I've recently written a stress-testing
framework that starts up any number of threads, and hits the server with
multiple requests per thread.
While being stress-tested in this way, I've found that if the testing framework
is using 100 threads, a single call to sqlite3_exec() can take 8-15 seconds at
times (assuming my timeout set with sqlite3_busy_timeout is big enough to allow
this, otherwise is failed with error code 5 -- LOCKED). This is when doing DB
writes, obviously. journal_mode is set to WAL.
I assumed that by switching from a DB file on disk to one in memory, we'd see
this 'write' bottleneck nearly disappear. We don't have too many records in the
tables being written to, so the only reasonable explanation for 8-15 seconds
for some writes would be that the DB is on disk.
Yet when switching to an in-memory database (with a shared cache, since
currently the server has 30 separate threads, each with their own separate
SQLite connection), I found the performance actually got slightly *worse* than
with a disk-based version. I re-ran the tests each way several times, and
indeed, performance is worse.
I open the shared-cache in-memory DB like this:
int result = sqlite3_open_v2("file:memdb1?mode=memory&cache=shared", &_db,
flags, NULL);
I also tried using these for the in-memory version (versus WAL journaling mode):
sqlite3_exec(_db, "PRAGMA read_uncommitted=true", NULL, NULL, NULL);
sqlite3_exec(_db,"PRAGMA journal_mode=OFF",NULL,NULL,NULL);
but to no avail. Still worse performance than when the DB is on disk. Which
makes little sense to me.
I realize SQLite was not designed to be accessed concurrently by 100 separate
users, and this really is a stress-testing case that far exceeds our expected
real-world usage. But at the same time, I'd like to get this running as
optimally as possible, and it seems like 8-15 second writes is unreasonable for
an in-memory database. (Not that 8-15 seconds is the average... but even the
average is 3-5 seconds when 100 threads are stress-testing the server at once.)
-Vern
IMPORTANT WARNING: This message is intended for the use of the person or entity
to which it is addressed and may contain information that is privileged and
confidential, the disclosure of which is governed by applicable law. If the
reader of this message is not the intended recipient, or the employee or agent
responsible for delivering it to the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this information is
STRICTLY PROHIBITED. If you have received this message in error, please notify
us immediately by calling (310) 423-6428 and destroy the related message. Thank
You for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
