# Threading and the MySQL

**URL:** <https://www.ruby-forum.com/t/threading-and-the-mysql/69663>\
**Category:** Ruby\
**Created:** [September 22, 2006, 8:20pm UTC](https://www.ruby-forum.com/t/threading-and-the-mysql/69663 "2006-09-22T20:20:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![fcheung](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/90db22/32.png) [@fcheung](https://www.ruby-forum.com/u/fcheung)\
**Post date:** [September 22, 2006, 8:20pm UTC](https://www.ruby-forum.com/t/threading-and-the-mysql/69663/1 "2006-09-22T20:20:18Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![fcheung](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/90db22/32.png) [@fcheung](https://www.ruby-forum.com/u/fcheung)\
**Post date:** [September 22, 2006, 10:34pm UTC](https://www.ruby-forum.com/t/threading-and-the-mysql/69663/2 "2006-09-22T22:34:28Z")

</div>

On 9/22/06, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 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

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.

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.

jeremy

---

<div class="post-metadata">

**Author:** ![fcheung](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/90db22/32.png) [@fcheung](https://www.ruby-forum.com/u/fcheung)\
**Post date:** [September 22, 2006, 11:56pm UTC](https://www.ruby-forum.com/t/threading-and-the-mysql/69663/3 "2006-09-22T23:56:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![fcheung](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/90db22/32.png) [@fcheung](https://www.ruby-forum.com/u/fcheung)\
**Post date:** [September 23, 2006, 11:21pm UTC](https://www.ruby-forum.com/t/threading-and-the-mysql/69663/4 "2006-09-23T23:21:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![fcheung](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/90db22/32.png) [@fcheung](https://www.ruby-forum.com/u/fcheung)\
**Post date:** [September 23, 2006, 10:52pm UTC](https://www.ruby-forum.com/t/threading-and-the-mysql/69663/5 "2006-09-23T22:52:01Z")

</div>

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

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