Problem with threading mysql queries in ruby

ruby-talk
1

I’m having a problem with threads and the mysql module. I’ve trolled the
archives and haven’t found anything similar.

It seems that once a thread containing a mysql query gets some cpu time
it won’t let go until the query is finished. Some testing with strace on
linux and truss on solaris seems to confirm this- once the query is
submitted to the server, read() is called and nothing else happens until
it returns.

I’ve attached a small test script which demonstrates the problem I’m
having.

So does the mysql module just not play nice with threads, or is there
something I’m doing wrong here? Any suggestions for alternative ways of
getting periodic feedback on the status of a long mysql query?

Any help or suggestions would be greatly appreciated.

mysqlthread.rb (379 Bytes)

···

–
Stan Hiller
Systems Administrator
LocalNet Corp
shiller@localnet.com

2

It seems that once a thread containing a mysql query gets some cpu time
it won’t let go until the query is finished.

In the database work I’ve done, the client locks up while the query is
running, so you’ve got to fork before that call (and I don’t know if it
can be fixed with Ruby and the mysql module, but threading doesn’t seem to
be enough with the different ways I tried-- fork does work, though)… so
I wouldn’t expect anything back from that process until the query is done.
So goes life.

So does the mysql module just not play nice with threads, or is there
something I’m doing wrong here? Any suggestions for alternative ways of
getting periodic feedback on the status of a long mysql query?

I can’t think of anything you’re doing wrong. Short of logging into MySQL
as an admin and checking the running processes, you can’t really ask a
query how it’s doing. But here’s one possible solution as a way to get you
started: (and I’ve probably got this backwards, you may want to fork the
query itself and have it send the results back via socket or temp file)

#!/usr/bin/ruby -w require 'mysql' @dbConn = Mysql.new("host", "user", "pass", "datbase")

def threadTest()
timer = fork {
while (1)
puts "ticktock = " + Time.now.to_s
sleep 1
end
}
puts "Timer PID is " + timer.to_s

call = Thread.new {
puts “sleeping”
sleep 3
puts “done sleeping”
@connRes = @dbConn.query(“select * from table;”)
puts “query finished”
sleep 2
puts “all done”
}

while (1)
break if call.status == false
end
call.join

puts “cleaning up timer”
system(“kill #{timer}”)
puts “timer cleaned. waiting.”
Process.wait

puts “done waiting, exit threadTest”
end

threadTest()

ichimunki@greyhound:~/ruby-misc$ ruby mysql-threads.rb Timer PID is 7641 sleeping ticktock = Fri Oct 25 19:53:56 CDT 2002 ticktock = Fri Oct 25 19:53:57 CDT 2002 ticktock = Fri Oct 25 19:53:58 CDT 2002 done sleeping ticktock = Fri Oct 25 19:53:59 CDT 2002 ticktock = Fri Oct 25 19:54:00 CDT 2002 ticktock = Fri Oct 25 19:54:01 CDT 2002 ticktock = Fri Oct 25 19:54:02 CDT 2002 ticktock = Fri Oct 25 19:54:03 CDT 2002 ticktock = Fri Oct 25 19:54:04 CDT 2002 ticktock = Fri Oct 25 19:54:05 CDT 2002 ticktock = Fri Oct 25 19:54:06 CDT 2002 query finished ticktock = Fri Oct 25 19:54:07 CDT 2002 ticktock = Fri Oct 25 19:54:08 CDT 2002 all done cleaning up timer timer cleaned. waiting. done waiting, exit threadTest ichimunki@greyhound:~/ruby-misc$

-michael

Michael C. Libby x@ichimunki.com http://www.ichimunki.com/ http://www.ichimunki.com/public_key.txt
···

On Friday 25 October 2002 16:48, stan wrote: