MRI Ruby + MySQL + Threads == Stop the world... JRuby doesn't

Posted by aaron
on Wednesday, August 27

As we have been internally discussing how to scale our databases from 10's of millions of rows to 100's of millions, database sharding came up.

Depending on your data model and your application, sharding data into tables by some natural key is great if any given request uses only one shard. FiveRun's DataFabric seems to help with that. Its obviously best to shard the data in the most used way, but occasionally you'll need to write broadcast queries across shards. It'd be even better if those broadcast queries were executed concurrently. Well, apparently, its not that simple in MRI Ruby.

The mysql (2.7) gem stop's the world while executing a query. A sloppy but reproducible test is here. This is not a mutex lock on the re-use of a single connection object. Eddie also reached out to the Sequel maintainer, who agrees it is likely due "to the fact that the C drivers don't release the interpreter lock while they wait for a response from the server." JRuby, or more accurately JDBC, acts as expected. We even tested DataMapper's DataObjects::MySQL, as it appears they've re-implemented the mysql gem. Unfortunately it suffers from the same stop-the-world issue. 

$ ruby mysql_locking_test.rb 
Loading MRI MySQL
Serial: 4.00969815254211s
Multi-threaded: 4.00785183906555s

$ data_mapper=true ruby mysql_locking_test.rb 
Loading DataMapper
Serial: 4.01330804824829s
Multi-threaded: 4.01132893562317s

$ jruby mysql_locking_test.rb 
Loading JDBC-MySQL
Serial: 4.2802369594573975s
Multi-threaded: 1.0499329566955566s

Only in JRuby was the multi-threaded != sequential time, as expected.

Potentially unrelated, no one has touched the mysql gem in 3 years?!?! 

$ gem spec -v 2.7 mysql | grep date
date: 2005-10-09 00:00:00 +00:00

So even Ruby ORM frameworks (Sequel, DataMapper) that say they're thread-safe, are not concurrent on MRI... at least for mysql. For folks not using Rails, which already has a mutex lock higher in the stack, this must be a performance issue. For example, Merb + DataMapper + MySQL. If there is a 2s SQL query, all threads in that process stop for 2s.

Can others verify? "select sleep(2) from dual;" is a great way to test for this.

UPDATE: Multiple have asked, so to clarify. The sample code here creates a new connection PER thread. The mysql docs states: "Two threads can't send a query to the MySQL server at the same time on the same connection", but the test is creating a new connection object per thread, so that should not apply.

UPDATE 2: A couple folks have mentioned asynch drivers. One for mysql and one for postgres, but I believe they're based on NeverBlock, which is Ruby 1.9 only. That sounds like awesome progress. What's the realistic ETA for folks running 1.9 in a production environment? At least until Christmas for a 1.9 official release?

UPDATE 3: Looks like lots is happening here. There now is a mysql driver that supports async/threaded operations on 1.8! See the NeverBlock MySQL project. Looking forward to testing this in a production environment.