Optimization Tips: Ruby on Rails and MySQL

24 Feb 2008 in MySQL, Programming, Ruby on Rails by Garrick

Stop.

I’ve uncovered these tips after (at least) the 3rd refactoring effort of some fairly simple, straight-forward Rails code. Rails is great for getting ideas prototyped super fast. These tips will slow down development and make apps less portable. Continue reading only if you’re running a live app in production and not happy with how resource-intensive it is.

My approach to this round of optimization was watch the Load calculations in the development log and optimize transactions with a Load greater than 0.0009.

• First, a rule of thumb: Development boxes are faster than production boxes. If it’s acceptably fast locally, then it’ll probably be a turtle in production.
• Use Model.find_by_sql or Model.count_by_sql whenever possible.
  Slow: Person.find_by_name('JoeyJoeJoe')
  Fast: Person.find_by_sql("SELECT person.* WHERE person.name = 'JoeyJoeJoe'")
• Don’t put keys, IDs, or other numbers within quotes in your Finds
  Slow: WHERE id = "1234"
  Fast: WHERE id = 1234
• Only request the specific database column/model attribute you want
  Slow: Person.find_by_name('JoeyJoeJoe').height
  Fast: Person.find_by_sql("SELECT person.height WHERE person.name = 'JoeyJoeJoe'"). Put indexes on all these columns/attributes.
• Use connection.insert, connection.update, connection.delete for database transactions performed on an array of models or transactions that don’t need the overhead of a model.
• Slow: ...WHERE table_1.id = table_2.table1_id...
  Fast: ...table_1 JOIN table_2 ON table1.id = table_2.table1_id....
• Many tiny database transactions are faster than 1 big one
  Slow: stuff = Stuff.find_by_sql("SELECT everything.* FROM everything JOIN (box_1, box_2) ON (everything.id = box_1.everything_id box_2.box1_id = box_1.id")
  Fast: boxes = Boxes.find_by_sql("SELECT box_1.everything_id FROM box_1 JOIN box_2 ON box_2.box1_id = box_1.id")
  followed by
  boxes.each do |box_1|
stuff = Stuff.find_by_sql("SELECT everything.* FROM everything WHERE id = #{box_1.everything_id}
end
• If you’re running the InnoDB datastore (vs MyISAM) try cranking up your innodb_buffer_pool_size. I say start by doubling it. Seriously.
• Again, if you’re running InnoDB and doing a anything more involved than the simplest ORDER BY, try cranking up your sort_buffer_size and read_rnd_buffer_size to something in the double-digit M range.
• Also, if you’re comparing datastore engines, MyISAM has full text search, InnoDB doesn’t. So you’ll need a clever work around. There are a number of them.

Comments (2)

Add a Comment

Related Entries

• MySQL on OS X Reinstall Reminders
• How To Install MacPorts, Apache2, Rails, MySql, Mongrel, and Subversion on an Intel Mac
• Ruby on Rails is Agile Web Development
• Rails Cheap, MySQL Expensive
• Everything Else You Need to Run a Web App