CODEWITHSUNDEEP
mysqlruby-on-railsperformance
Noel Frostpaw
Noel Frostpaw

Reputation: 3999

MySQL performance in Ruby on Rails

I'm currently experiencing some performance issues with MySQL and trying to come up with a solution. I've already added some indexes to various tables and it seems to have shaven off several hundreds of ms from the query length, but I was wondering if the following could be optimized:

The code responsible for this is pretty big to post below, but in general:

I know it's a bit tricky to do this without the models, but the post has a limit on the amount of characters. Most of the queries in the log look like this:

  Language Load (0.0ms)
  SELECT `languages`.* FROM `languages` WHERE `languages`.`code` = 'en' LIMIT 1   Skill Load (1.0ms)  SELECT `skills`.* FROM `skills` INNER JOIN `occupation_skills` ON `skills`.id = `occupation_skills`.skill_id WHERE ((`occupation_skills`.occupation_id = 156))

  Concept Load (1.0ms)
  SELECT `concepts`.* FROM `concepts` WHERE `concepts`.`id` = 10 LIMIT 1 ConceptLabel Load (1.0ms)  SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1

  Label Load (1.0ms)
  SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1

Some of these come directly from the cache like so:

  CACHE (0.0ms)
  SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1

  CACHE (0.0ms)
  SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1

  CACHE (0.0ms)
  SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1   CACHE (0.0ms)  SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1

  CACHE (0.0ms)
  SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1

  CACHE (0.0ms)
  SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1

The heaviest query amongst them is

Label Load (56.0ms)
SELECT `labels`.* FROM `labels` WHERE (`labels`.`id` IN (9909,9888,9855,9822,9900,9867,9834,9912,9891,9879,9846,9813,9870,9858,9825,9903,9882,9837,9804,9894,9861,9849,9816,9873,9840,9828,9796,9906,9885,9852,9807,9897,9864,9831,9819,9876,9843,9810))

Yet the output still takes too long to load to my liking:

Rendered static/categorize.html.haml within layouts/application (515.1ms)
Completed 200 OK in 1651ms (Views: 424.0ms | ActiveRecord: 188.0ms)

Is there something else missing because, last time I checked 188 + 424ms != 1651ms... When running performance tests it takes up to 8 seconds according JMeter to receive a proper response...

Upvotes: 4

Views: 352

Answers (1)

Michael De Silva
Michael De Silva

Reputation: 3818

Consider eager loading your associations (if you aren't already) to try and optimise your queries further. If not, you might want to consider utilising an intermediary store such as Redis and perform your queries against that, and possibly utilise Resque to 'sync up' whenever required.

Remember Redis is a NoSQL datastore and runs completely in RAM, so it's quick to say the least.

Upvotes: 2

Related Questions