brianmario/mysql2 — Issue #246: Performance issue processing large results sets inside Rails 2.3 ENV compared to plain Ruby
Author: shiloa
Created: 2012-03-05 12:42:34+00:00
State: open

# The Problem

I've been having major performance issues with large result sets when I use `Mysql2::Client` querying inside Rails 2.3.14 environment. I'm using Ruby 1.9.2-p290. I ran a small benchmark to check just how significant the difference.

# My Environment

```
Rails : 2.3.14
Ruby : 1.9.2-p290
mysql2 : 0.2.18 (latest compatiable with Rails 2.3.x)
OS : Ubuntu 10.04.3 LTS, Linux 2.6.32-37-generic
Machine : Inter Core2 Quad CPU Q9400 @ 2.66GHz, 8GB RAM
```

# Benching

```ruby
#!/usr/bin/env ruby

require 'benchmark'
require 'mysql2'

class DBTest

def initialize
end

def connect
@client = Mysql2::Client.new({
:host => 'localhost',
:username => 'minda',
:password => 'sikriT',
:database => 'testdb'
})
end

def load_results
# pretty elaborate query, utilizing
# several joines and returning about
# 200,000 rows
sql = "SELECT * FROM ..."

# takes a bit to complete, but no different than running directly
# on the DB server
@results = @client.query(sql, :cast => false, :cache_rows => false)
end

def process_results
@results.each { }
end

def bench
connect
Benchmark.bmbm(10) do |x|
x.report("querying:") { load_results }
x.report("processing:") { process_results }
end
@client.close
end
end
```

```
# from normal irb
$ irb
>> load 'dbtest.rb'
=> true
>> DBTest.new.bench
Rehearsal --------------------------------------------------
querying: 0.090000 0.070000 0.160000 ( 35.768303)
processing: 3.360000 0.000000 3.360000 ( 3.370060)
----------------------------------------- total: 3.520000sec

real system total real
querying: 0.090000 0.060000 0.150000 ( 27.900330)
processing: 3.110000 0.000000 3.110000 ( 3.109986)

# same thing with rails console
$ ./script/console production
>> load 'dbtest.rb'
=> true
>> DBTest.new.bench
Rehearsal --------------------------------------------------
querying: 0.100000 0.050000 0.150000 ( 26.027604)
processing: 36.950000 0.200000 37.150000 ( 37.193124)
-----------------------------------------total: 37.300000sec

real system total real
querying: 0.070000 0.040000 0.110000 ( 26.019600)
processing: 36.980000 0.040000 37.020000 ( 37.027342)
```

I tried playing with the client configuration (`:cast`, `:cache_rows`), but this didn't change much. I might be missing something here, but it seems as though the processing takes almost 10x longer inside Rails. Can anyone else confirm this?