Remove index hints for slow queries

RESOLVED FIXED in Q1 2011

Status

addons.mozilla.org Graveyard
Public Pages
P3
normal
RESOLVED FIXED
7 years ago
2 years ago

People

(Reporter: kumar, Assigned: jbalogh)

Tracking

unspecified
Q1 2011

Details

The base filter for many browse pages adds .with_index() to queries.  This bypasses mysql's own query path optimizer and causes unnecessary full table scans for many queries.  Here is a log of all the slow, index-hinted queries we've tracked in production: khan:~kmcmillan/wrong-mysql-index-hints.log (265 unique queries)

The select statements can be used to track down the culprit view in Django and remove the index hints.  Or remove all index hints?  Maybe some queries need them, I'm not sure.

Possible culprit views, known to have slow queries:
https://addons.mozilla.org/en-US/firefox/extensions/ and
https://addons.mozilla.org/en-US/firefox/extensions/web-development/ and
https://addons.mozilla.org/en-US/firefox/personas/ and
https://addons.mozilla.org/en-US/firefox/featured/


To find out if removing the hint worked, mysql's explain extended should help.  Here is an example of one slow query before and after removing the hint.  Note the personas join (the 3rd one), which does a full table scan.  After removing the hint a better index is used and no more than 6 rows are scanned per join in the entire query.


mysql> explain extended SELECT (1) AS `_app`, `addons`.`id`, `addons`.`created`, `addons`.`modified`, `addons`.`guid`, `addons`.`slug`, `addons`.`name`, `addons`.`defaultlocale`, `addons`.`addontype_id`, `addons`.`status`, `addons`.`higheststatus`, `addons`.`icontype`, `addons`.`homepage`, `addons`.`supportemail`, `addons`.`supporturl`, `addons`.`description`, `addons`.`summary`, `addons`.`developercomments`, `addons`.`eula`, `addons`.`privacypolicy`, `addons`.`the_reason`, `addons`.`the_future`, `addons`.`averagerating`, `addons`.`bayesianrating`, `addons`.`totalreviews`, `addons`.`weeklydownloads`, `addons`.`totaldownloads`, `addons`.`average_daily_downloads`, `addons`.`average_daily_users`, `addons`.`sharecount`, `addons`.`last_updated`, `addons`.`ts_slowness`, `addons`.`inactive`, `addons`.`trusted`, `addons`.`viewsource`, `addons`.`publicstats`, `addons`.`prerelease`, `addons`.`adminreview`, `addons`.`admin_review_type`, `addons`.`sitespecific`, `addons`.`externalsoftware`, `addons`.`binary`, `addons`.`dev_agreement`, `addons`.`nominationdate`, `addons`.`target_locale`, `addons`.`locale_disambiguation`, `addons`.`wants_contributions`, `addons`.`paypal_id`, `addons`.`charity_id`, `addons`.`suggested_amount`, `addons`.`total_contributions`, `addons`.`annoying`, `addons`.`enable_thankyou`, `addons`.`thankyou_note`, `addons`.`get_satisfaction_company`, `addons`.`get_satisfaction_product`, `addons`.`current_version` FROM `addons`  INNER JOIN `addons_categories`  ON (`addons`.`id` = `addons_categories`.`addon_id`) INNER JOIN `personas` USE INDEX (`personas_movers_idx`) ON (`addons`.`id` = `personas`.`addon_id`) WHERE ("hu"="hu" AND "hu"="hu" AND ((`addons`.`addontype_id` = 9  OR `addons`.`current_version` IS NOT NULL) AND `addons`.`inactive` = 0  AND `addons`.`status` IN (4)) AND `addons`.`addontype_id` = 9  AND `addons_categories`.`category_id` = 120  AND `addons`.`id` IN (81016, 20605, 33317, 23105, 216764, 82974))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: addons_categories
         type: range
possible_keys: addon_id,category_addon_idx
          key: addon_id
      key_len: 8
          ref: NULL
         rows: 6
     filtered: 83.33
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: addons
         type: eq_ref
possible_keys: PRIMARY,addontype_id,status,inactive,current_version,type_status_inactive_idx
          key: PRIMARY
      key_len: 4
          ref: zamboni.addons_categories.addon_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: personas
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 213959
     filtered: 100.00
        Extra: Using where; Using join buffer
3 rows in set, 1 warning (0.02 sec)

mysql> explain extended SELECT (1) AS `_app`, `addons`.`id`, `addons`.`created`, `addons`.`modified`, `addons`.`guid`, `addons`.`slug`, `addons`.`name`, `addons`.`defaultlocale`, `addons`.`addontype_id`, `addons`.`status`, `addons`.`higheststatus`, `addons`.`icontype`, `addons`.`homepage`, `addons`.`supportemail`, `addons`.`supporturl`, `addons`.`description`, `addons`.`summary`, `addons`.`developercomments`, `addons`.`eula`, `addons`.`privacypolicy`, `addons`.`the_reason`, `addons`.`the_future`, `addons`.`averagerating`, `addons`.`bayesianrating`, `addons`.`totalreviews`, `addons`.`weeklydownloads`, `addons`.`totaldownloads`, `addons`.`average_daily_downloads`, `addons`.`average_daily_users`, `addons`.`sharecount`, `addons`.`last_updated`, `addons`.`ts_slowness`, `addons`.`inactive`, `addons`.`trusted`, `addons`.`viewsource`, `addons`.`publicstats`, `addons`.`prerelease`, `addons`.`adminreview`, `addons`.`admin_review_type`, `addons`.`sitespecific`, `addons`.`externalsoftware`, `addons`.`binary`, `addons`.`dev_agreement`, `addons`.`nominationdate`, `addons`.`target_locale`, `addons`.`locale_disambiguation`, `addons`.`wants_contributions`, `addons`.`paypal_id`, `addons`.`charity_id`, `addons`.`suggested_amount`, `addons`.`total_contributions`, `addons`.`annoying`, `addons`.`enable_thankyou`, `addons`.`thankyou_note`, `addons`.`get_satisfaction_company`, `addons`.`get_satisfaction_product`, `addons`.`current_version` FROM `addons`  INNER JOIN `addons_categories`  ON (`addons`.`id` = `addons_categories`.`addon_id`) INNER JOIN `personas`  ON (`addons`.`id` = `personas`.`addon_id`) WHERE ("hu"="hu" AND "hu"="hu" AND ((`addons`.`addontype_id` = 9  OR `addons`.`current_version` IS NOT NULL) AND `addons`.`inactive` = 0  AND `addons`.`status` IN (4)) AND `addons`.`addontype_id` = 9  AND `addons_categories`.`category_id` = 120  AND `addons`.`id` IN (81016, 20605, 33317, 23105, 216764, 82974))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: addons_categories
         type: range
possible_keys: addon_id,category_addon_idx
          key: addon_id
      key_len: 8
          ref: NULL
         rows: 6
     filtered: 83.33
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: addons
         type: eq_ref
possible_keys: PRIMARY,addontype_id,status,inactive,current_version,type_status_inactive_idx
          key: PRIMARY
      key_len: 4
          ref: zamboni.addons_categories.addon_id
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: personas
         type: ref
possible_keys: personas_ibfk_1
          key: personas_ibfk_1
      key_len: 4
          ref: zamboni.addons_categories.addon_id
         rows: 1
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.00 sec)
Priority: -- → P3
Target Milestone: --- → Q1 2011
(Assignee)

Comment 1

7 years ago
Our slow pages are not as slow after the push today: http://jbalogh.khan.mozilla.org/xxx/#02-24

Fixed with https://github.com/jbalogh/django-cache-machine/commit/ec5ad0 + https://github.com/jbalogh/zamboni/commit/fee94658
Assignee: nobody → jbalogh
Status: NEW → RESOLVED
Last Resolved: 7 years ago
Resolution: --- → FIXED
Product: addons.mozilla.org → addons.mozilla.org Graveyard
You need to log in before you can comment on or make changes to this bug.