Closed Bug 1022650 Opened 11 years ago Closed 11 years ago

Theme related queries are slow

Categories

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

Product:
addons.mozilla.org Graveyard
Component:
Code Quality
Platform:
x86
macOS
Type:
defect
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED WONTFIX

People

(Reporter: jason, Unassigned)

Details

At times of heavy db load theme related queries take very long to return, sometimes over 100 seconds. Most of the time they take over 1 second to execute. This became a noticeable issue since we upgraded to Django 1.6. What can we do improve performance on these pages? Can we cache this better? Below is the count of queries I saw on one of the db slaves when executing 'show full processlist' during high db load [1]. 4 SELECT `waffle_flag`.`id`, `waffle_flag`.`name`, `waffle_flag`.`everyone`, `waffle_flag`.`percent`, `waffle_flag`.`testing`, `waffle_flag`.`superusers`, `waffle_flag`.`staff`, `waffle_flag`.`authenticated`, `waffle_flag`.`languages`, `waffle_flag`.`rollout`, `waffle_flag`.`note`, `waffle_flag`.`created`, `waffle_flag`.`modified` FROM `waffle_flag` WHERE `waffle_flag`.`name` = 'collect-timings' 5 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("it"="it") AND NOT (`addons`.`status` = 11 ) AND ("it"="it") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`popularity` DESC LIMIT 6 6 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("ru"="ru") AND NOT (`addons`.`status` = 11 ) AND ("ru"="ru") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`movers` DESC LIMIT 15 7 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("de"="de") AND NOT (`addons`.`status` = 11 ) AND ("de"="de") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`movers` DESC LIMIT 15 8 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("fr"="fr") AND NOT (`addons`.`status` = 11 ) AND ("fr"="fr") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`movers` DESC LIMIT 15 9 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("de"="de") AND NOT (`addons`.`status` = 11 ) AND ("de"="de") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`popularity` DESC LIMIT 6 9 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("fr"="fr") AND NOT (`addons`.`status` = 11 ) AND ("fr"="fr") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`popularity` DESC LIMIT 6 9 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("ru"="ru") AND NOT (`addons`.`status` = 11 ) AND ("ru"="ru") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`popularity` DESC LIMIT 6 40 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("en-us"="en-us") AND NOT (`addons`.`status` = 11 ) AND ("en-us"="en-us") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`movers` DESC LIMIT 15 117 SELECT (1) AS `_app`, `addons`.`id` FROM `addons` LEFT OUTER JOIN `personas` ON ( `addons`.`id` = `personas`.`addon_id` ) WHERE (("en-us"="en-us") AND NOT (`addons`.`status` = 11 ) AND ("en-us"="en-us") AND NOT (`addons`.`status` = 11 ) AND `addons`.`current_version` IS NOT NULL AND `addons`.`inactive` = 0 AND `addons`.`status` IN (4) AND `addons`.`addontype_id` = 9 AND NOT (`addons`.`id` IN (9449, 199424, 258053, 261458, 263347, 316928, 324462, 326593, 328076, 337547, 339842, 346226, 354399, 361313, 361551, 365177, 366659, 371268, 429014, 450340))) ORDER BY `personas`.`popularity` DESC LIMIT 6 [1] https://gist.github.com/jasonthomas/7dd404aea10213d23003
Thanks for filing this. Due to resource constraints we are closing bugs which we won't realistically be able to fix. If you have a patch that applies to this bug please reopen. For more info see http://micropipes.com/blog/2014/09/24/the-great-add-on-bug-triage/
Status: NEW → RESOLVED
Closed: 11 years ago
Resolution: --- → WONTFIX
Product: addons.mozilla.org → addons.mozilla.org Graveyard
You need to log in before you can comment on or make changes to this bug.