{"action":"created","issue":{"url":"https://api.github.com/repos/xerial/sqlite-jdbc/issues/59","labels_url":"https://api.github.com/repos/xerial/sqlite-jdbc/issues/59/labels{/name}","comments_url":"https://api.github.com/repos/xerial/sqlite-jdbc/issues/59/comments","events_url":"https://api.github.com/repos/xerial/sqlite-jdbc/issues/59/events","html_url":"https://github.com/xerial/sqlite-jdbc/issues/59","id":111773583,"number":59,"title":"Improve perf by compiling with no locking and allowing users to configure it","user":{"login":"headius","id":10135,"avatar_url":"https://avatars.githubusercontent.com/u/10135?v=3","gravatar_id":"","url":"https://api.github.com/users/headius","html_url":"https://github.com/headius","followers_url":"https://api.github.com/users/headius/followers","following_url":"https://api.github.com/users/headius/following{/other_user}","gists_url":"https://api.github.com/users/headius/gists{/gist_id}","starred_url":"https://api.github.com/users/headius/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/headius/subscriptions","organizations_url":"https://api.github.com/users/headius/orgs","repos_url":"https://api.github.com/users/headius/repos","events_url":"https://api.github.com/users/headius/events{/privacy}","received_events_url":"https://api.github.com/users/headius/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"xerial","id":57538,"avatar_url":"https://avatars.githubusercontent.com/u/57538?v=3","gravatar_id":"","url":"https://api.github.com/users/xerial","html_url":"https://github.com/xerial","followers_url":"https://api.github.com/users/xerial/followers","following_url":"https://api.github.com/users/xerial/following{/other_user}","gists_url":"https://api.github.com/users/xerial/gists{/gist_id}","starred_url":"https://api.github.com/users/xerial/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xerial/subscriptions","organizations_url":"https://api.github.com/users/xerial/orgs","repos_url":"https://api.github.com/users/xerial/repos","events_url":"https://api.github.com/users/xerial/events{/privacy}","received_events_url":"https://api.github.com/users/xerial/received_events","type":"User","site_admin":false},"milestone":null,"comments":13,"created_at":"2015-10-16T07:03:42Z","updated_at":"2015-10-26T18:14:02Z","closed_at":null,"body":"We were investigating a concurrent performance issue with JRuby at https://github.com/jruby/jruby/issues/3398 and after a few hours of investigation figured out that sqlite-jdbc is compiling sqlite with the default configuration of always threadsafe. Unfortunately it seems like that default mode does not treat separate database connections (to separate databases) as their own mutexes. As a result, parallel performance did not scale properly even when inserting into completely separate databases.\r\n\r\nThe fix, which is trivial, was to compile sqlite with SQLITE_THREADSAFE=0.\r\n\r\n```diff\r\ndiff --git a/Makefile b/Makefile\r\nindex de1f1d3..31cd2d5 100644\r\n--- a/Makefile\r\n+++ b/Makefile\r\n@@ -62,6 +62,7 @@ $(SQLITE_OUT)/sqlite3.o : $(SQLITE_UNPACKED)\r\n            -DSQLITE_ENABLE_FTS3_PARENTHESIS \\\r\n            -DSQLITE_ENABLE_RTREE \\\r\n            -DSQLITE_ENABLE_STAT2 \\\r\n+           -DSQLITE_THREADSAFE=0 \\\r\n            $(SQLITE_FLAGS) \\\r\n            $(SQLITE_OUT)/sqlite3.c\r\n \r\n```\r\n\r\nObviously this is not sufficient for the library. The full fix would be to build sqlite this way but when opening connections default to a thread-safe setup. As it turns out, sqlite can upgrade connections to be more threadsafe, but it can't downgrade them. The current configuration sets them by default to serialize all accesses.\r\n\r\nThe full doco for this is here: https://www.sqlite.org/threadsafe.html\r\n\r\nBasically, sqlite-jdbc needs to configure the library to be thread-unsafe as a minimum, and then by default specify thread-safety when opening the connection. Users can then specify configuration parameters to open the connection as thread-unsafe (single thread access) giving them better concurrency if inserting into isolated databases.\r\n\r\nI don't have a full patch for sqlite-jdbc, but I wanted to start the dialog now. We have a large number of JRuby users hitting sqlite via your library in heavily concurrent applications, and this would give them a better chance of having good scaling characteristics."},"comment":{"url":"https://api.github.com/repos/xerial/sqlite-jdbc/issues/comments/151233343","html_url":"https://github.com/xerial/sqlite-jdbc/issues/59#issuecomment-151233343","issue_url":"https://api.github.com/repos/xerial/sqlite-jdbc/issues/59","id":151233343,"user":{"login":"xerial","id":57538,"avatar_url":"https://avatars.githubusercontent.com/u/57538?v=3","gravatar_id":"","url":"https://api.github.com/users/xerial","html_url":"https://github.com/xerial","followers_url":"https://api.github.com/users/xerial/followers","following_url":"https://api.github.com/users/xerial/following{/other_user}","gists_url":"https://api.github.com/users/xerial/gists{/gist_id}","starred_url":"https://api.github.com/users/xerial/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/xerial/subscriptions","organizations_url":"https://api.github.com/users/xerial/orgs","repos_url":"https://api.github.com/users/xerial/repos","events_url":"https://api.github.com/users/xerial/events{/privacy}","received_events_url":"https://api.github.com/users/xerial/received_events","type":"User","site_admin":false},"created_at":"2015-10-26T18:14:02Z","updated_at":"2015-10-26T18:14:02Z","body":"OK. I'll tweak packaging scripts so that we can prepare two types of binaries (normal jar and single-thread one). "}}