Mysqlplus ActiveRecord Adapter

Installation

Grab mysqlplus ….

git clone git://github.com/oldmoe/mysqlplus.git cd mysqlplus rake

… and mysqlplus_adapter …

sudo gem install methodmissing-mysqlplus_adapter -s http://gems.github.com

… then update config/database.yml

  production:
    adapter:  mysqlplus
    database: myapp_production
    username: root
    password:  
    host: localhost
    pool: 10

Why Bother ?

The mysqlplus gem ( a fork of mysql-2.8.4pre ) exposes an asynchronous API that lends itself very well to decoupling the stock Mysql protocol into full duplex in a multi-threaded environment.

The ever popular mysql ruby do not schedule Threads and thus lock the whole MRI interpreter for any database I/O from within a given process.

Rails since release 2.2 support connection pooling and Thread safety ( at the framework level mind you, plugins, gems and user code aside ) through :

  config.threadsafe!

This configuration hook removes the global Dispatcher lock and yields one connection from the pool per request / response cycle.

You’ll need mysqplus and this adapter to get around the MRI lock with mysql ruby.

Configuration Options

An additional connection specification element, warmup is available that attempts to establish the pooled connections in advance.This may be useful for high traffic environments where it makes sense to setup connections when bouncing the App and not let initial incoming requests be hogged with Mysql connection overheads.

  production:
    adapter:  mysqlplus
    database: myapp_production
    username: root
    password:  
    host: localhost
    pool: 10  
    warmup: true

Deferrable Results

Deferred results simulate lazy loading in a background Thread, through another Mysql connection, other than the one the current Thread has acquired.This type of workflow assumes a decent Connection Pool size of 5 to 10 connections.

  # Immediate yield control back to the current Thread as the query is pushed to the background.
  # Yields an instance of ActiveRecord::Deferrable::Result
  #
  Post.find( :first, :defer => true )

A deferred result blocks when any method’s invoked on the result set right away.

  Post.find( :first, :defer => true ).title

This concept is quite useful in an MVC context, allowing the controller to fetch results, defer fetching them to the background and reference them in the view, allowing an undefined period / time slice during which rendering, template setup etc. may occur.

  class PostsController
   
    def index
      # No blocking, executes in the background, yields a deferrable result.
      #
      @posts = Posts.published.find(:all, :defer => true ) # Slow, push to the background 
      @visitors = Site.visitors.recent # Snappy
      # You don't want to do this. Try to not invoke methods on deferred results right away 
      # to minimize potential blocking. 
      @posts.any?  
    end

  end

Since ActiveRecord 2.1 preloading favors multiple efficient queries to cumbersome and mostly slow JOINs.Those secondary queries can easily be pushed down different connections.

  # Use 3 connections from the pool : 1 x Post, 1 x Comment and 1 x Vote 
  #
  Post.find(:all, :limit => 10, :include => [:comments, :votes], :defer => true )

Garbage Collection

There’s some experimental GC patches available – the mysql ruby gem forces GC every 20 queries, that’s a guaranteed GC cycle every 5th request for a request with a 4 query overhead.This adapter will automatically detect the presence of those patches and disable the forced GC runs.

methodmissing:mysqlplus lourens$ ruby test/gc_benchmark.rb
Rehearsal ----------------------------------------------
With GC      0.440000   0.020000   0.460000 (  0.741424)
Without GC   0.040000   0.030000   0.070000 (  0.327787)
------------------------------------- total: 0.530000sec

                 user     system      total        real
With GC      0.430000   0.030000   0.460000 (  0.725934)
Without GC   0.040000   0.010000   0.050000 (  0.311233)

References this benchmark script.

Asynchronous abilities

The adapter exposes Mysqplus’s async interface through the connection instance.

MysqlUser.connection.send_query( "SELECT * FROM mysql.user WHERE User = 'root'" ) # Returns right away, no IO wait
MysqlUser.connection.get_result # Retrieves the result from the previous async query

Be very careful with this feature :

Model.connection.send_query( "first query" )
Model.connection.send_query( "second query" )
Model.connection.get_result # Resultset of "second query"

Abuse may also lead to server side temporary tables not being properly cleaned up, although this effect is partially negated by the Connection Pool’s check in / check out feature which switches the current mysql user session back to itself, cleaning up in the process.This holds true for the request / response cycle, but doesn’t apply to background processes etc.

Stability

In (pre)-production use at a handful of sites and the test suite is designed to run against the existing ActiveRecord suite.

TODO

• Experiment with turning off query_with_result for certain queries.

• Deferred inserts / updates – dangerous INSERT DELAYED for Innodb