On Friday, April 3, 2015 at 9:18:39 PM UTC-7, Hiroyuki Sato wrote: > > Hello jeremy. > > Thank you for your comment. > > I wrote test program. > It run two readers and writers. It access same table. > > Almost operation complete < 0.005 sec. > But sometime raise SQLite3::BusyException. > so It take over 5sec. (red part). > > I have no idea why this operation lock database over 5sec. > > That's why I decrease max_connection to 1. > If I changed max_connections to 1. It works good. > > -- > Hiroyuki Sato. > > > 2015年4月4日土曜日 0時28分22秒 UTC+9 Jeremy Evans: > > On Friday, April 3, 2015 at 2:49:46 AM UTC-7, Hiroyuki Sato wrote: > > Hello members. > > Question > > I'm using Sequel with Celluloid(https://celluloid.io) > Celluloid is a multithread library for Ruby. > > And SQLite db is the file database. not on-memory db. > > I got SQLite3::BusyException, so I would like to avoid this exception. > > It is possible to access sqlite db from one thread at once? > Or should I access to sqlite from one thread? > > I changed those values , It seems good. > max_connections: 1 > pool_timeout: 10 > > Should I change another parameters? > > Is there any best practice configuration for connect sqlite3 with multi > thread? > > > If you are only running a single process and you don't mind blocking > queries in other threads (basically serializing access to the database), > that should be fine. > > For anything involving multithread or multiprocess access, it may be > better to set a higher busy timeout on the underlying connection objects > via the :timeout option (the default is 5000 milliseconds). > > Thanks, > Jeremy > > > > Sequel.migration do > > change do > > create_table(:artists) do > > primary_key :id > > String :name, :null=>false > > end > > end > > end > > > > > require 'celluloid' > > require 'sequel' > > require 'sqlite3' > > require 'logger' > > > logger = Logger.new(STDERR) > > logger.level = Logger::DEBUG > > > DB = Sequel.connect(:adapter=>'sqlite', > > :database=>'/tmp/test.db') > > DB.loggers << logger > > Celluloid.logger = logger > > DB[:artists].delete > > > > > class Reader > > include Celluloid > > include Celluloid::Logger > > def initialize > > every(1){ run_sql } > > end > > def run_sql > > artists = DB[:artists] > > bfr = Time.new > > begin > > artists.where( :id => 1 ).first > > est = Time.new - bfr > > debug "--> read #{est}sec #{Actor.current}" > > rescue > > est = Time.new - bfr > > debug "--> read(#{$!}) #{est}sec #{Actor.current}" > > STDERR.puts $!.backtrace > > end > > end > > end > > > > class Writer > > include Celluloid > > include Celluloid::Logger > > def initialize > > every(1){ run_sql } > > end > > def run_sql > > artists = DB[:artists] > > name = sprintf("test%f",Time.new.to_f) > > bfr = Time.new > > begin > > DB.transaction do > > artists.insert( :name => name ) > > est = Time.new - bfr > > debug "--> write #{est}sec #{Actor.current}" > > end > > rescue > > est = Time.new - bfr > > debug "--> write(#{$!}) #{est}sec #{Actor.current}" > > STDERR.puts $!.backtrace > > end > > end > > end > > > > > > manager = Celluloid::SupervisionGroup.run! > > > > manager.pool(Writer, as: :writer, args: [], size: 2) > > manager.pool(Reader, as: :reader, args: [], size: 2) > > > > sleep > > > > > I, [2015-04-04T12:49:34.936240 #45666] INFO -- : (0.000677s) PRAGMA > foreign_keys = 1 > I, [2015-04-04T12:49:34.936358 #45666] INFO -- : (0.000028s) PRAGMA > case_sensitive_like = 1 > I, [2015-04-04T12:49:34.936914 #45666] INFO -- : (0.000485s) SELECT > sqlite_version() > I, [2015-04-04T12:49:34.938964 #45666] INFO -- : (0.001836s) DELETE FROM > `artists` WHERE (1 = 1) > I, [2015-04-04T12:49:35.944744 #45666] INFO -- : (0.000109s) BEGIN > I, [2015-04-04T12:49:35.947201 #45666] INFO -- : (0.001993s) INSERT INTO > `artists` (`name`) VALUES ('test1428119375.944484') > D, [2015-04-04T12:49:35.947593 #45666] DEBUG -- : --> write 0.002763sec > #<Writer:0x007fdc14512d20> > I, [2015-04-04T12:49:35.948436 #45666] INFO -- : (0.000281s) PRAGMA > foreign_keys = 1 > I, [2015-04-04T12:49:35.950077 #45666] INFO -- : (0.001104s) COMMIT > I, [2015-04-04T12:49:35.950211 #45666] INFO -- : (0.000039s) PRAGMA > case_sensitive_like = 1 > I, [2015-04-04T12:49:35.950584 #45666] INFO -- : (0.000125s) BEGIN > I, [2015-04-04T12:49:35.951233 #45666] INFO -- : (0.000234s) PRAGMA > foreign_keys = 1 > I, [2015-04-04T12:49:35.951347 #45666] INFO -- : (0.000047s) PRAGMA > case_sensitive_like = 1 > I, [2015-04-04T12:49:35.951766 #45666] INFO -- : (0.000309s) SELECT * > FROM `artists` WHERE (`id` = 1) LIMIT 1 > I, [2015-04-04T12:49:35.952320 #45666] INFO -- : (0.000332s) INSERT INTO > `artists` (`name`) VALUES ('test1428119375.947833') > D, [2015-04-04T12:49:35.952678 #45666] DEBUG -- : --> write 0.004531sec > #<Writer:0x007fdc1302b308> > I, [2015-04-04T12:49:35.953675 #45666] INFO -- : (0.000919s) COMMIT > I, [2015-04-04T12:49:35.953958 #45666] INFO -- : (0.000180s) SELECT * > FROM `artists` WHERE (`id` = 1) LIMIT 1 > D, [2015-04-04T12:49:35.954264 #45666] DEBUG -- : --> read 0.005512sec > #<Reader:0x007fdc14508988> > D, [2015-04-04T12:49:35.954528 #45666] DEBUG -- : --> read 0.005523sec > #<Reader:0x007fdc14502600> > I, [2015-04-04T12:49:36.947791 #45666] INFO -- : (0.000094s) BEGIN > I, [2015-04-04T12:49:36.948589 #45666] INFO -- : (0.000563s) INSERT INTO > `artists` (`name`) VALUES ('test1428119376.947594') > I, [2015-04-04T12:49:36.949251 #45666] INFO -- : (0.000052s) BEGIN > I, [2015-04-04T12:49:41.972344 #45666] INFO -- : (0.000161s) INSERT INTO > `artists` (`name`) VALUES ('test1428119381.971985') >
Notice the 5 second time difference here. If I had to guess, the sqlite3 driver does not release the GVL when running queries. I believe it is this code, but I'm not all that knowledgeable about the SQLite3 C API: https://github.com/sparklemotion/sqlite3-ruby/blob/b73fae5a4c515cbe2bbcf2bb01be4f8edd86d4b8/ext/sqlite3/statement.c#L133 Basically, it appears as though you can't use sqlite3 driver in multithreaded code if you have multiple transactions in effect where a query in one transaction depends on the other transaction being committed first. The ruby mysql driver has the same issue I believe (but not mysql2), which is why Sequel automatically tries to use mysqlplus in the mysql adapter. This is basically a guess on my part. You may want to talk with the ruby sqlite3 driver developers and see if this is the case, or if something else could be causing this. Before you do that, I suggest you could reproduce your issue using the ruby sqlite3 driver directly, without using Sequel or Celluloid, to narrow down the problem space. Or you could just live with the max_connections = 1 workaround. That's Sequel's default for an SQLite memory database. :) Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
