Hello jeremy. Thank you for your comment.
I'll use sequel and sqlite3 with max_connections 1 until this issue resolve. And narrow down the problem space. -- Hiroyuki Sato. 2015年4月4日土曜日 15時43分05秒 UTC+9 Jeremy Evans: > > 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.
