{"action":"created","issue":{"url":"https://api.github.com/repos/jruby/jruby/issues/3398","labels_url":"https://api.github.com/repos/jruby/jruby/issues/3398/labels{/name}","comments_url":"https://api.github.com/repos/jruby/jruby/issues/3398/comments","events_url":"https://api.github.com/repos/jruby/jruby/issues/3398/events","html_url":"https://github.com/jruby/jruby/issues/3398","id":111715377,"number":3398,"title":"Performance bug with JDBC/sqlite driver under multi-threaded conditions","user":{"login":"chuckremes","id":43569,"avatar_url":"https://avatars.githubusercontent.com/u/43569?v=3","gravatar_id":"","url":"https://api.github.com/users/chuckremes","html_url":"https://github.com/chuckremes","followers_url":"https://api.github.com/users/chuckremes/followers","following_url":"https://api.github.com/users/chuckremes/following{/other_user}","gists_url":"https://api.github.com/users/chuckremes/gists{/gist_id}","starred_url":"https://api.github.com/users/chuckremes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chuckremes/subscriptions","organizations_url":"https://api.github.com/users/chuckremes/orgs","repos_url":"https://api.github.com/users/chuckremes/repos","events_url":"https://api.github.com/users/chuckremes/events{/privacy}","received_events_url":"https://api.github.com/users/chuckremes/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2015-10-15T21:51:59Z","updated_at":"2015-10-16T02:56:46Z","closed_at":null,"body":"When writing data in a single thread to a single database, performance is excellent. As soon as there is 2+ threads using the database driver, performance falls off a cliff. \r\n\r\nNotes:\r\n* The reproduction has 4 threads each writing to a separate database and database file. There is *no contention* for the file itself between threads.\r\n* Reducing the number of threads improves performance. Increasing worsens it.\r\n* I recommend running with DEBUG_PRINT=true at least once. It will show the inserts/sec in the low 100s but as the data is exhausted and the threads exit you'll see performance rise to the many 1000s.\r\n\r\n```\r\nrequire 'sequel'\r\nrequire 'csv'\r\nrequire 'benchmark'\r\n\r\nDb_string = if RUBY_PLATFORM =~ /java/\r\n  \"jdbc:sqlite:/tmp/\"\r\nelse\r\n  \"sqlite:///tmp/\"\r\nend\r\n\r\ndef elapsed_time(&blk)\r\n  start = Time.now\r\n  blk.call\r\n  elapsed = Time.now - start\r\nend\r\n\r\nclass DBHandle\r\n  def initialize\r\n    @connections = {}\r\n  end\r\n  \r\n  def setup_db_connection(key)\r\n    connection_string = \"#{Db_string}#{key}.db\"\r\n  \r\n    db = Sequel.connect(connection_string, :single_threaded => true)\r\n    db.create_table? :options do\r\n      primary_key :id\r\n  \r\n      String :underlying_symbol, :size => 15\r\n      Float :underlying_price\r\n      String :exchange, :fixed => true, :size => 1\r\n      String :option_symbol, :size => 20\r\n      String :option_ext, :size => 5\r\n      String :type, :fixed => true, :size => 1\r\n      Date :expiration\r\n      Date :trade_date\r\n      Float :strike\r\n      Float :last\r\n      Float :bid\r\n      Float :ask\r\n      Integer :volume\r\n      Integer :open_interest\r\n      Float :implied_vol\r\n      Float :delta\r\n      Float :gamma\r\n      Float :theta\r\n      Float :vega\r\n      String :aka, :size => 15\r\n  \r\n      index :trade_date\r\n    end    \r\n    \r\n    db\r\n  end\r\n  \r\n  def setup_db_thread(key)\r\n    db_queue = Queue.new\r\n    thread = Thread.new do\r\n      work(key, db_queue)\r\n    end\r\n    \r\n    return({:queue => db_queue, :thread => thread})\r\n  end\r\n  \r\n  def work(key, queue)\r\n    headers = [\r\n        :underlying_symbol, :underlying_price, :exchange, :option_symbol, :option_ext,\r\n        :type, :expiration, :trade_date, :strike, :last, :bid, :ask, :volume,\r\n        :open_interest, :implied_vol, :delta, :gamma, :theta, :vega, :aka\r\n      ]\r\n\r\n    loop do\r\n      index, rows = queue.pop\r\n      break if rows == \"done\"\r\n      inserts = 0\r\n      elapsed = 0\r\n    \r\n      unless @connections.has_key?(index)\r\n        @connections[index] = setup_db_connection(index)\r\n      end\r\n    \r\n      db = @connections[index]\r\n  \r\n      inserts += rows.size\r\n  \r\n      elapsed += elapsed_time do\r\n        db[:options].import(headers, rows, :commit_every => 500)\r\n      end\r\n  \r\n      unless elapsed.zero?\r\n        persec = \"%.2f\" % (inserts / elapsed)\r\n        puts \"[child] db-conn Inserted [#{inserts}] rows at a rate of [#{persec}] rows per second.\" if DEBUG_PRINT\r\n      end\r\n    end # loop\r\n\r\n    puts \"[child] db-conn Database connection exiting...\" if DEBUG_PRINT\r\n  end\r\nend # class DBHandle\r\n\r\nhandles = []\r\nconnection = DBHandle.new\r\nhandles << connection.setup_db_thread(0)\r\nhandles << connection.setup_db_thread(1)\r\nhandles << connection.setup_db_thread(2)\r\nhandles << connection.setup_db_thread(3)\r\n\r\nrows = []\r\nall_rows = []\r\nfile = File.open(\"slow.csv\")\r\nio = CSV.new(file, :converters => :all)\r\n\r\ncount = 0\r\nwhile row = io.gets\r\n  rows << row\r\n  count += 1\r\n  \r\n  if count > rand(500)\r\n    all_rows << rows\r\n    count = 0\r\n    rows = []\r\n  end\r\nend\r\n\r\n\r\nDEBUG_PRINT = false\r\n\r\nBenchmark.bmbm(22) do |x|\r\n  x.report('insert rows') do\r\n    all_rows.each do |rows|\r\n      index = rand(4)\r\n      handles[index][:queue] << [index, rows]\r\n    end\r\n    \r\n    handles.each { |hsh| hsh[:queue] << [nil, \"done\"] }\r\n    handles.each { |hsh| hsh[:thread].join }\r\n  end\r\nend\r\n\r\nio.close\r\n```\r\n\r\nThe datafile to use is here:\r\nhttps://www.dropbox.com/s/sawaxksmlkep674/slow.zip?dl=0\r\n\r\n\r\nBenchmarks:\r\njruby 9.0.2.0-SNAPSHOT (2.2.2) 2015-10-13 f2242d0 Java HotSpot(TM) 64-Bit Server VM 25.60-b23 on 1.8.0_60-b27 +jit [darwin-x86_64]\r\n```\r\nruby --server slow.rb \r\nRehearsal ----------------------------------------------------------\r\ninsert rows             13.910000  25.840000  39.750000 ( 23.120468)\r\n------------------------------------------------ total: 39.750000sec\r\n\r\n                             user     system      total        real\r\ninsert rows              0.010000   0.000000   0.010000 (  0.002492)\r\n```\r\nruby 2.2.3p173 (2015-08-18 revision 51636) [x86_64-darwin14]\r\n```\r\nruby slow.rb \r\nRehearsal ----------------------------------------------------------\r\ninsert rows              1.360000   0.940000   2.300000 (  2.721511)\r\n------------------------------------------------- total: 2.300000sec\r\n\r\n                             user     system      total        real\r\ninsert rows              0.000000   0.000000   0.000000 (  0.000299)\r\n```"},"comment":{"url":"https://api.github.com/repos/jruby/jruby/issues/comments/148584024","html_url":"https://github.com/jruby/jruby/issues/3398#issuecomment-148584024","issue_url":"https://api.github.com/repos/jruby/jruby/issues/3398","id":148584024,"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},"created_at":"2015-10-16T02:56:46Z","updated_at":"2015-10-16T02:56:46Z","body":"I have pushed a copy of my build of sqlite-jdbc here: https://www.dropbox.com/s/qpa910t65d8jwxt/sqlite-jdbc-3.8.11.3-SNAPSHOT.jar?dl=0\r\n\r\nI just copied it over the jar in the current jdbc-sqlite3 gem. Keep in mind this makes all connections lockless and thread-unsafe. We'll need a more robust option long term."}}