615241 - [slow query] Fix enormous collections query

Status: RESOLVED WORKSFORME

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

Description

[Wil Clouser [:clouserw]]

Seeing this in the logs on the slaves:

# Time: 100416 15:17:04
# User@Host: remora[remora] @  [10.8.70.200]
# Query_time: 47  Lock_time: 0  Rows_sent: 10  Rows_examined: 21630
SELECT `Collection`.`id` FROM `collections` AS `Collection`   WHERE `Collection`.`id` IN (2, 3, 4, 5, 6, 7,
........ (many pages of numbers here) ........
64029, 64030, 64034, 13626, 30473, 30478, 34445)   ORDER BY `Collection`.`weekly_subscribers` DESC  LIMIT 10;

I'm not 100% sure this is in zamboni, but if someone has a good idea on where to look it won't hurt to spend a few minutes on it.

Comment 1

[Jeff Balogh (:jbalogh)]

That's remora.  Django doesn't alias or capitalize table names.  We should find what remora collection url is still accessible and kill it.

Comment 2

[Jeff Balogh (:jbalogh)]

I expect that this is from something like http://jbalogh.khan.mozilla.org/amo/site/en-US/firefox/collections/addon/1865/ but that's a 404 in zamboni: https://addons.mozilla.org/en-US/firefox/collections/addon/1865/

Comment 3

[Jeff Balogh (:jbalogh)]

I only see this impossible remora query on one box in phoenix, and as far as I know we run out of tm* boxes.

oremj: any idea how queries would get to phoenix, particularly one that's on a remora page that should be hidden by zamboni?

(khan) $ cd /data/backup-drop/amo01/mysql-slow                                 
(khan) $ grep 'SELECT `Collection`.`id` FROM `collections` AS `Collection`' tm*
(khan) $ grep -c 'SELECT `Collection`.`id` FROM `collections` AS `Collection`' tp*
tp-amo01-slave01.phx.mozilla.com-20101129-063403-slow.log:20
tp-amo01-slave01.phx.mozilla.com-20101129-063924-slow.log:0
tp-amo01-slave01.phx.mozilla.com-20101130-054501-slow.log:0
tp-amo01-slave01.phx.mozilla.com-20101201-054502-slow.log:0
tp-amo01-slave01.phx.mozilla.com-20101202-054501-slow.log:0
tp-amo01-slave02.phx.mozilla.com-20101129-063403-slow.log:0
tp-amo01-slave02.phx.mozilla.com-20101129-063924-slow.log:0
tp-amo01-slave02.phx.mozilla.com-20101130-054501-slow.log:0
tp-amo01-slave02.phx.mozilla.com-20101201-054502-slow.log:0
tp-amo01-slave02.phx.mozilla.com-20101202-054501-slow.log:0
tp-amo01-slave03.phx.mozilla.com-20101129-063403-slow.log:0
tp-amo01-slave03.phx.mozilla.com-20101129-063924-slow.log:0
tp-amo01-slave03.phx.mozilla.com-20101130-054501-slow.log:0
tp-amo01-slave03.phx.mozilla.com-20101201-054502-slow.log:0
tp-amo01-slave03.phx.mozilla.com-20101202-054501-slow.log:0