Threading and the MySQL

[Frederick Cheung]

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

 

[Frederick Cheung]

All the ruby threads are blocked during execution of the query.

I was rather fearing that would be the case

See also ruby-talk:71672

The mysqlclient has no nonblocking API so you won't get the desired
behavior
until Ruby has native threads. For what it's worth, postgres offers a
nonblocking API and the native gem (and Active Record) support it.

Backgroundrb could be more robust in this respect by forking instead of
threading jobs.

That would be nice. We've only really ran into this because of
backgroundrb, what with rails being single threaded.

Fred

 

[Ezra Zygmuntowicz]

I was rather fearing that would be the case

That would be nice. We've only really ran into this because of
backgroundrb, what with rails being single threaded.

Fred

I have a new version of backgroundrb that supports multi process
workers with the same interface. Hoping to get it finished for
release in the next week or so.

-Ezra

 

[Frederick Cheung]

I have a new version of backgroundrb that supports multi process
workers with the same interface. Hoping to get it finished for
release in the next week or so.

Excellent, that sounds great (I'm a big fan of backgroundrb)

Fred