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.

Comments

Leave a response

  1. Jared – August 06, 2008 @ 01:31 AM
    According to http://oldmoe.blogspot.com/2008/08/case-for-nonblocking-ruby-stack.html, "calling C extensions blocks the whole Ruby interpreter". Could this be the cause? Isn't there a pure-ruby MySQL driver? Is it thread safe?
  2. oldmoe – August 07, 2008 @ 12:53 AM
    Unless the C extension permits the Ruby interpreter to schedule its threads it will block. Even the pure Ruby driver will block unless it handles the connection in an explicit non-blocking manner. PostgreSQL is another story, since it has native async interfaces, parallel query execution in Ruby is doable via the exec_async method which behaves the same as exec, but does the query in an async manner, giving way for the Ruby interpreter to schedule threads. oldmoe
  3. mormon – August 07, 2008 @ 01:24 AM
    Yeah you got it--the mysql gem 'stops the world' while it twitters its thumbs and waits for each query to return. For mysql currently the only way around this is to use an asynchronous mysql library [asymy] [with or without fibers]. Or hack into the mysql binaries themselves so that they release the global lock (and use 1.9). Or fork before executing a query. Or use multiple processes, etc. Or you might be able to use the pure ruby mysql adapter in conjunction with revactor and ruby 1.9 [though the pure ruby adapter is really old, I'm told]. Or use Jruby :) So yeah it's currently basically a limitation in Ruby that some people are trying to overcome. Note also that if you want an updated version of the mysql gem you can download it from http://www.tmtm.org/en/mysql/ruby/ -R
  4. Fred – August 19, 2008 @ 06:00 AM
    You should use ruby-mysql 2.7.5
  5. Fred – August 19, 2008 @ 06:01 AM
    You should try benchmarking ruby-mysql 2.7.5, but it still wont solve your concurrency problem,
  6. Thomas – August 19, 2008 @ 02:33 PM
    Ruby 1.9 allows extensions to release the global interpreter lock while they're performing blocking operations, allowing other threads to run. See rb_thread_blocking_region().
  7. Ilya Grigorik – August 19, 2008 @ 10:36 PM
    @Thomas: Ruby 1.8 can do that as well. There are some async client libraries out there, problem is, none of them are production ready. On the GIL lock.. Ruby is not an exception, Python and most other interpreted languages have the same problem.
  8. Lourens Naude – August 26, 2008 @ 08:13 PM
    Post just been invalidated ... http://github.com/oldmoe/mysqlplus
  9. Lourens Naude – August 26, 2008 @ 08:13 PM
    Post just been invalidated ... http://github.com/oldmoe/mysqlplus
  10. roger – August 27, 2008 @ 10:42 AM
    For the record, the ruby MRI postgres drivers do not 'stop the world' [and I believe that the oldmoe drivers listed also don't]. -=R
  11. roger – August 27, 2008 @ 02:26 PM
    Appears that MRI works with 1.8.6 if you do the following: compile the oldmoe drivers, require them instead of the gem, replace line 49: res = conn.query(sql_statement) with conn.send_query(sql_statement) socket = IO.new(conn.socket) result = select([socket], nil, nil, nil) res = conn.get_result => Multi-threaded: 1.0046272277832s
  12. Justin Marney – August 28, 2008 @ 11:48 AM
    I was curious on how the postgres benchmarks actually looked/worked so I added the ability to generate them to the test. http://pastie.org/261782 jmarney:Desktop $ postgres=true ruby db_locking_test.rb Loading MRI Postgres Serial: Testing 4 iterations thread: #<thread:0x35700> executing: select pg_sleep(1) thread: #<thread:0x35700> executing: select pg_sleep(1) thread: #<thread:0x35700> executing: select pg_sleep(1) thread: #<thread:0x35700> executing: select pg_sleep(1) Serial: 4.02708697319031s Multi-threaded: Testing 4 iterations thread: #<thread:0x5229e8> executing: select pg_sleep(1) thread: #<thread:0x522740> executing: select pg_sleep(1) thread: #<thread:0x5224e8> executing: select pg_sleep(1) thread: #<thread:0x5221f0> executing: select pg_sleep(1) Multi-threaded: 4.03514003753662s jmarney:Desktop $ postgres=true async=true ruby db_locking_test.rb Loading MRI Postgres Serial: Testing 4 iterations thread: #<thread:0x35700> executing: select pg_sleep(1) thread: #<thread:0x35700> executing: select pg_sleep(1) thread: #<thread:0x35700> executing: select pg_sleep(1) thread: #<thread:0x35700> executing: select pg_sleep(1) Serial: 4.02873086929321s Multi-threaded: Testing 4 iterations thread: #<thread:0x522998> executing: select pg_sleep(1) thread: #<thread:0x5226f0> executing: select pg_sleep(1) thread: #<thread:0x522448> executing: select pg_sleep(1) thread: #<thread:0x5221f0> executing: select pg_sleep(1) Multi-threaded: 1.02273917198181s As oldmoe pointed out you have to use the async versions to get async benefits of postgres.
  13. roger – September 02, 2008 @ 03:17 PM
    Fascinatingly, it appears that the pure ruby mysql driver is also ruby thread friendly. Go figure.
  14. Bmore – October 16, 2008 @ 11:51 PM
    have you guys tried working with a more open mongo table or mongo database like 10gen or something similar to the massive table architecture in google's app engine? http://www.10gen.com/