Hi,
We’ve noticed the following slightly odd behaviour when executing long
running queries to mysql using the native ruby gem (in some ActiveRecord
code running in backgroundrb but the problem is not connected to
activerecord) versus using the pure ruby mysql adaptor
Conside the following test program
require ‘rubygems’
require ‘mysql’
def generate_dummy_data(connection)
connection.query “delete from test”
query_string = “INSERT INTO test(id) VALUES”
1.upto(4000) do |i|
query_string << “(#{i}),”
end
query_string.chomp!(‘,’)
connection.query query_string
end
Thread.new do
while true
puts “sleeper thread checking in at #{Time.now}”
sleep(1)
end
end
connection =
Mysql.new(‘localhost’,‘test’,“”,‘test’,3306,“/tmp/mysql.sock”)
generate_dummy_data(connection)
#run a long running query
connection.query “select count(distinct(test.id)) from test inner join
test as tt” do |res|
puts “got #{res}”
end
sleep(5)
It fills the test table of the test table with some data and runs a
query that’s long running (but which produces very little actual output)
while running a separate thread that just prints the current time to the
console every second. When the native mysql gem is available, the output
looks a little like this
sleeper thread checking in at Fri Sep 22 19:07:00 BST 2006
sleeper thread checking in at Fri Sep 22 19:07:13 BST 2006
got #Mysql::Result:0x3dd818
sleeper thread checking in at Fri Sep 22 19:07:14 BST 2006
sleeper thread checking in at Fri Sep 22 19:07:15 BST 2006
All the ruby threads are blocked during execution of the query.
If however I use the pure ruby mysql adaptor then I get a line of output
every second even when the query is running, which is obviously the more
desirable solution.
I found a reference to this here:
http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/54160
However given that this was in 2002 I was hoping that perhaps there was
now a solution (or even a hint of the right direction to pursue) as we’d
very much like to keep the speed benefits of the native mysql adaptor.
I imagine that one could do something like have the actual calls to the
c mysql api happen in their own thread (with 1 thread per connection to
mysql)
Thanks for reading
Fred