CODEWITHSUNDEEP
mysqlperformanceactiverecordruby-on-rails-3.2mysql2
user1659357
user1659357

Reputation: 31

rails activerecord, mysql and mysql2 performance degradation?

I have only recently upgraded from Rails 2.3.5 to Rails 3.2.7 and noticed some performance degradation in some of my queries. I am aware of Rails 3 ActiveRecord being slower in some cases than Rails 2.3.5, but the benchmarks I have surprised me and I just want to make sure I am not missing anything.

I ran the following query, which is very popular in my application, as a benchmark

SELECT SQL_NO_CACHE table_name.* FROM table_name WHERE ((string_col = 'value') AND (int_col1 BETWEEN 5 AND 30)) ORDER BY int_col2 DESC LIMIT 1000

I checked:

Results

Rails 3.2.7

rails 3.2.7, mysql adapter, "select_all": avg. 0.0148 seconds
rails 3.2.7, mysql adapter, "find_by_sql" avg. 0.0555 seconds

rails 3.2.7, mysql2 adapter, "select_all": avg. 0.045 seconds
rails 3.2.7, mysql2 adapter, "find_by_sql" avg. 0.088 seconds

Rails 2.3.5

rails 2.3.5, mysql adapter "select_all": avg. 0.013 seconds
rails 2.3.5, mysql adapter "find_by_sql": avg. 0.0177 seconds

Although my original code is using ActiveRecord query api, I used hardcoded sql for the benchmark and also verified that calling mysql directly from the bash command line is stable and the above numbers result from rails/mysql adapter and not the db.

Question

Are these differences reasonable?
The diff between "find_by_sql" and "select_all" is much bigger in Rails 3.2.7 than in Rails 2.3.5.
And why is mysql2 slower than mysql?

Upvotes: 3

Views: 957

Answers (1)

ScottJShea
ScottJShea

Reputation: 7111

I found this blog post which discusses how connection.select_all uses a lower level database call eliminating some of the higher libraries used by find_by_sql.

Most calls in the standard ActiveRecord API return ActiveRecord “model” objects. However, there might be cases when you want to bypass the overhead of creating these full ActiveRecord objects, or maybe you want to query data that doesn’t have a corresponding ActiveRecord class. The connection adapter’s low-level query methods let you write your own SQL, and return “plain old data” as raw result tables.

In regards to mysql v mysql2 the mysql2 gem converts the returns into the more complex Ruby types used in Ruby itself rather than strings or nil. However if you need those, streaming or asynchronous querying the mysql2 gem is faster.

Upvotes: 1

Related Questions