long query time on brightbox

Would there be any special reason why a query on brightbox is taking 120ms and on my computer it takes 10ms?

I’m doing a load on a model including just another one with will_paginate (latest version), on Rails 2.2.2 on a table of about 6k rows.

Any help on this will be greatly appreciated.

Ernesto

Hi Ernesto

Have you checked to make sure that using :include in your finder is not instantiating a crazy number of objects from records brought back from the query? It’s probably that the ‘database time’ you’re seeing in your log is not reporting just how long it took the query to run but rather how long it took to query and load all the objects.

I had a find that was using :include => nested two levels deep, it was only bringing back 10 objects on the first level but when I profiled the action the include actually brought back nearly 600 objects which all had to be instantiated. I modified the query to use an active record named_scope which I used to make sure the scope of the includes was tightened and brought the number of objects way down and improved the performance of the find

You might also copy and paste the query from the Rails log and run it with an SQL EXPLAIN to see the query plan and make sure the query is using the right indexes.

Hi David,

I followed your tips, and I’m still puzzled at this issue. In any case, would it be reasonable that the very same code takes 10 times longer on the server than my computer?

on local:
Ad Load Including Associations (13.8ms)
SQL (16.5ms) SELECT count(DISTINCT `ads`.id) AS count_all FROM `ads` LEFT OUTER JOIN `users` ON `users`.id = `ads`.user_id WHERE (users.status = 1 AND ads.status = 1 AND ads.country_id = 1 AND ads.created_at >= ‘2008-10-09 18:29:06’)

brightbox:
Ad Load Including Associations (57.1ms)
SQL (142.8ms) SELECT count(DISTINCT `ads`.id) AS count_all FROM `ads` LEFT OUTER JOIN `users` ON `users`.id = `ads`.user_id WHERE (users.status = 1 AND ads.status = 1 AND ads.country_id = 1 AND ads.created_at >= ‘2008-10-09 18:29:06’)

The load including associations has to use “where” and “filesort”, I guess it needs filesort because I’m sorting by date, the second query is just a count and that one just uses “where”, that query I believe is the one will_paginate uses to know how many pages it has to link to, etc. and since that one doesn’t return associations or anything else I can’t seem to understand why it takes like 10 times more on brightbox than in my computer.

The association I’m making is fairly simple, I have a listings table and a users table, there’s only 1 join going in there.

Could it be that my brightbox is sick or the database server hammered?

Bests,

Ernesto

Hi Ernesto,

“In any case, would it be reasonable that the very same code takes 10 times longer on the server than my computer?”

Actually yes this is perfectly reasonable. You’d be surprised how many support queries I’ve dealt with in my time (I’ve worked for two Ruby on Rails hosting companies) where people are able to run a query fine on their 2.2 ghz dual core Macbook Pro with 4gb of RAM and then are baffled when the same inefficient query won’t run as fast on their much lower powered virtual machine. Unfortunately all I can suggest is you walk through debugging this and try to ignore the fact that it seems to run quickly on your local machine.

Try using Ruby-prof and bleak_house to analyse the memory usage of the intensive action and which methods are being called and how often. This can give you a good idea of whether you action is doing something that might take orders of magnitude longer on a much slower box, such as using REXML or doing lots of Regex’s.

Also remember that if you are on our smaller Brightbox plans then it may simply be that your box is not powerful enough for what you are trying to achieve without serious optimisation.

I think Filesort is normally a sign that you haven’t got a good index for a query – but don’t quote me on that as I am not a database expert myself.

I’m fairly confident that what you are experiencing it nothing to do with the stability of your particular Brightbox itself and I can assure you that our database cluster is very carefully monitored for load and is currently under utilised.

Hi Ernesto,

Just a note that the “just a count” query may not be as cheap as you think it is. Unless you’re using MyISAM tables (and I hope you’re not, as they don’t support transactions), COUNT in MySQL takes O(n) time (where n is the number of rows left after the restrictions in the WHERE clause have been applied). In fact, if your local database has MyISAM tables and your Brightbox one has InnoDB, that could well explain the speed discrepancy.

My SQL-fu isn’t strong so this next part is mostly speculation, but you might want to check that your LEFT OUTER JOIN isn’t producing some massive intermediate result set. 6k rows might not be a lot, but assuming you have roughly as many ads as users, 6k times 6k is a decent number of rows (which is what I believe an INNER JOIN would produce). Particularly combined with a non-indexed WHERE clause, that could hurt.

Hope that helps,
—
Sam

Hi Sam, David,

I had InnoDB on my tables before I noticed the time difference, and tried with MyISAM just to see how it performed.

I’m going to take some time to analyze this better and switch back to InnoDB.

Thanks for your help and I’ll let you know about my findings or any performance gain I manage to achieve.

Hey guys here’s an update on my problems:

I cleaned up a bunch of code, revisited my mem_caches and also optimized the queries with the help of the explain command and added some missing indexes and fixed some bad ones. Now it’s working real fast.

There’s an expensive condition on one query that selects by date (2 months ago till now), what I’m thinking to speed this one up is to make a cron job hourly to run a check on that table and flag all the items that are past 2 months with a special id on an indexed column. The data I’m displaying doesn’t need to be precise to the minute, so I think it’s a good solution.

Thank you all for the great advice.

Bests,

Ernesto