[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.ruby

Threading and the MySQL

Frederick Cheung

9/22/2006 6:20:00 PM

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-...
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-....
3 Answers

Frederick Cheung

9/22/2006 9:57:00 PM

0

Jeremy Kemper wrote:
>
> All the ruby threads are blocked during execution of the query.
I was rather fearing that would be the case

>> 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.
>
>
> 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

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

Ezra Zygmuntowicz

9/23/2006 3:07:00 AM

0


On Sep 22, 2006, at 2:56 PM, Frederick Cheung wrote:

> Jeremy Kemper wrote:
>>
>> All the ruby threads are blocked during execution of the query.
> I was rather fearing that would be the case
>
>>> 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.
>>
>>
>> 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


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

9/23/2006 9:21:00 PM

0

Ezra Zygmuntowicz wrote:
>
> 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

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