Queries via do_mysql block the whole interpreter, even when executed inside a thread (making concurrent db access impossible)
Reported by Matthew W | May 29th, 2008 @ 05:47 PM
Queries via do_mysql block the whole interpreter, even when executed inside a thread.
For an example, see:
> d = DataObject::Mysql::Connection.new('user=root')
=> #<DataObject::Mysql::Connection:0x741058 ...>
> d.open
=> true
> command = d.create_command('select sleep(10)')
=> #<DataObject::Mysql::Command:0x7384d0 ...>
> Thread.new { command.execute_reader.entries }
...Here there is a long 10 second pause while the whole interpreter hangs while mysql runs my 'sleep 10' command...
=> #<Thread:0x7102a0 run>
This is against the latest (0.2.4) do_mysql gem.
Note that this makes using a connection pool quite pointless - because only one query can ever run at a time across all connections.
In addition to fixing this, one needs to make sure (IIRC) that you don't hold the Global Interpreter Lock over the portion of the C extension code that waits for the query result. In this situation, while you can fire off queries in separate threads without blocking the main interpreter, only one thread can ever actually execute a mysql command at the same time, and this makes connection pools and concurrent DB access equally pointless. See Mysql/Ruby for an example of this behaviour.
You can test for a fix, by opening 2 separate connections, firing off threads which run 'select sleep(10)' queries on both connections, and checking with 'show processlist' from the mysql console, that both queries are indeed executing concurrently.
Comments and changes to this ticket
-
Matthew W May 29th, 2008 @ 06:01 PM
btw this might help:
http://dev.mysql.com/doc/refman/...
Sorry wish I could be more use on this one but my C skillz are a bit rusty.
A C-based mysql client for Ruby which gets the concurrency aspects of it right would be really handy though, for more than just datamapper.
-
Matthew W May 29th, 2008 @ 06:47 PM
and just realised my comments about the global interpreter lock would only apply (I think) for Ruby 1.9.
Damn.
Am starting to realise why this is hard now. Without native threads, it's hard to do without an asynchronous C API.
looks promising though - apparently an async mysql client library is in the works
-
Sam Smoot June 10th, 2008 @ 10:10 PM
- State changed from “new” to “hold”
Right. So I'll put this on hold until there's a work-around.
-
concurrent_queries September 5th, 2008 @ 03:34 AM
- Tag set to “async, asynchronous, blocking, concurrency, connection, do_mysql, global, interpreter, io, lock, non-blocking, pool”
Currently working solutions
This problem doesn
t exist with JRuby & JDBC compo.NeverBlock, MySQL and MySQLPlus http://oldmoe.blogspot.com/2008/... http://github.com/oldmoe/mysqlplus/ Asynchronous query processing support Threaded access support
http://blog.hungrymachine.com/20... * "Fascinatingly, it appears that the pure ruby mysql driver is also ruby thread friendly.", Comment by Roger
My own ideas?
Is it possible to use/create native threads in c-extension? With native threads we could create new thread per query or use thread pooling. Connection pooling at c-level is also one possibility if it is needed.
-
Dan Kubb (dkubb) November 30th, 2008 @ 02:12 AM
- Assigned user changed from “Sam Smoot” to “Dirkjan Bussink”
- State changed from “hold” to “resolved”
This issue was resolved with the following commits:
http://github.com/sam/do/commit/... http://github.com/sam/do/commit/...
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create new ticket
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
Dirkjan Bussink
Rick