I have been investigating the performance of the sqlite-jdbc driver, a JNI-based library that wraps sqlite3 and presents it as a Java JDBC connection.
The original bug report is here: https://github.com/jruby/jruby/issues/3398 My bug report to sqlite-jdbc is here: https://github.com/xerial/sqlite-jdbc/issues/59 Basically, the performance bug consists of a Ruby (JRuby) script opening connections to *separate* database files and writing large amounts of data. The performance of JRuby using the sqlite-jdbc driver was as much as 30x slower than the equivalent execution on CRuby (MRI). After some investigation, I realized that threading was not improving performance; four threads performing these tasks each performed at 1/4 the speed of a single thread. For some reason they were blocking each other. On a hunch, I modified sqlite-jdbc to recompile with SQLITE_THREADSAFE=0, single thread mode. Performance improved to 2x *better* than CRuby. I'm confused, however, why CRuby does not have this performance impact. As far as I can tell, it is using the built-in sqlite3 on OS X, which most resources seem to say is compiled with SQLITE_THREADSAFE=2 (multi-threaded). I tried that mode with sqlite-jdbc and performance returned to the dismal 30x-slower numbers. Is there something in sqlite causing threadsafe logic to block threads accessing independent databases? A global lock of some kind? Something that can be compiled out? Something Apple does differently in their build of OS X? I have to admit I'm a little stumped. - Charlie
