From: Frederick Cheung <fred@82ask.com>
Date: 2006-09-23T03:20:21+09:00
Subject: Threading and the MySQL

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

-- 
Posted via http://www.ruby-forum.com/.