*bump* Anyone?
-Vern ---------------------------------------------------------------- Vern Jensen Software Engineer Artificial Intelligence in Medicine (AIM) Program jens...@cshs.org<mailto:jens...@cshs.org> Office: 310-423-8148 :: Fax: 310-423-0173 On Aug 4, 2014, at 12:02 PM, Jensen, Vern <vern.jen...@cshs.org<mailto:vern.jen...@cshs.org>> wrote: 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 sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
