825280 - Fix slow AMO queries to make the DB cluster more stable

Status: RESOLVED WONTFIX

(addons.mozilla.org Graveyard :: Code Quality, defect)

Description

[Kumar McMillan [:kumar]]

Easy win! Spend some time with mysql's explain and you'll be a hero. Hint: you can probably getting a speedup by removing USE KEY on one here.

+++ This bug was initially created as a clone of Bug #823054 +++

Looking at the slow query logs, we keep today and yesterday, so starting  121219  4:45:08 here's what we have:
[[e-mail]1 mysql]# grep Query.time mysql-slow.log.* | cut -f2 -d: | cut -f1 -d\. | sort -n | tail
 7
 9
 9
 10
 11
 12
 34
 35
 61
 62

So there are some 60 second queries...this is on addons2.stage, one of the slaves...Note that the thing they have most in common is they are examining LOTS of rows. (see Rows_examined) As opposed to, say, Lock_time.....the queries themselves are taking a while.

here's the 4 queries that are over 20 seconds:
# User@Host: addons_dev[addons_dev] @  [10.8.70.202]
# Query_time: 35.398121  Lock_time: 0.000036 Rows_sent: 1  Rows_examined: 13729345
SET timestamp=1355985339;
SELECT SUM(`download_counts`.`count`) AS `sum` FROM `download_counts` WHERE `download_counts`.`date` < '2012-12-20';

# Time: 121219 21:35:39
# User@Host: addons_dev[addons_dev] @  [10.8.70.202]
# Query_time: 34.801077  Lock_time: 0.000054 Rows_sent: 24338  Rows_examined: 13738531
use marketplace_altdev_allizom_org;
SET timestamp=1355981739;
SELECT
               addon_id, AVG(count), SUM(count)
           FROM download_counts
           USE KEY (`addon_and_count`)
           GROUP BY addon_id
           ORDER BY addon_id;
# Time: 121219 23:46:06
# User@Host: addons_stage[addons_stage] @  [10.8.70.202]
# Query_time: 61.873735  Lock_time: 0.000048 Rows_sent: 921027  Rows_examined: 7666361
use addons_allizom_org;
SET timestamp=1355989566;
SELECT `stats_addons_collections_counts`.`addon_id`, `stats_addons_collections_counts`.`collection_id`, SUM(`stats_addons_collections_counts`.`count`) AS `sum` FROM `stats_addons_collections_counts` GROUP BY `stats_addons_collections_counts`.`addon_id`, `stats_addons_collections_counts`.`collection_id` ORDER BY NULL;



# User@Host: addons_dev[addons_dev] @  [10.8.70.202]
# Query_time: 62.015101  Lock_time: 0.000047 Rows_sent: 919481  Rows_examined: 7644040
use addons_dev_allizom_org_new;
SET timestamp=1355989566;
SELECT `stats_addons_collections_counts`.`addon_id`, `stats_addons_collections_counts`.`collection_id`, SUM(`stats_addons_collections_counts`.`count`) AS `sum` FROM `stats_addons_collections_counts` GROUP BY `stats_addons_collections_counts`.`addon_id`, `stats_addons_collections_counts`.`collection_id` ORDER BY NULL;

Comment 1

[Andy McKay]

These are all stats. I hope we can move these to monolith and make that fast.

Comment 2

[Wil Clouser [:clouserw]]

All stats queries will be replaced in 2013 with monolith.  I suspect these are from the cron jobs calculating totals.  I'm wary to spend any time on this now

Comment 3

[Kumar McMillan [:kumar]]

ah, cool, if they will be replaced then no bother.