Page MenuHomePhabricator

Full table scans on oldimage table
Closed, ResolvedPublic

Description

There are several queries that are doing full scans on the oldimage table, creating a big spike on our commons database slaves: https://grafana.wikimedia.org/d/000000273/mysql?panelId=3&fullscreen&orgId=1&var-dc=eqiad%20prometheus%2Fops&var-server=db1084&var-port=9104&kiosk&refresh=10s&from=1544617859606&to=1544628659606

root@db1084.eqiad.wmnet[sys]> select  query,first_seen,last_seen,total_latency,exec_count,rows_examined,rows_sent from x$statements_with_full_table_scans where first_seen like '2018-12-12%' and query not like '%statements_with_full_table_scans%'\G
*************************** 1. row ***************************
        query: SELECT `oi_timestamp` AS `img_timestamp` , `oi_name` AS `img_name` , `oi_name` AS `thumb` , `oi_size` AS `img_size` , ? AS `top` , COALESCE ( `comment_oi_description` . `comment_text` , `oi_description` ) AS `oi_description_text` , `comment_oi_description` . `comment_data` AS `oi_description_data` , `comment_oi_description` . `comment_id` AS `oi_description_cid` , ? AS `description_field` , `oi_user` AS `img_user` , `oi_user_text` AS `img_user_text` , ? AS `img_actor` FROM `oldimage` LEFT JOIN `comment` `comment_oi_description` ON ( ( `comment_oi_description` . `comment_id` = `oi_description_id` ) ) WHERE ( ( `oi_user` = ? ) ) AND `oi_deleted` = ? ORDER BY `oi_timestamp` DESC LIMIT ?
   first_seen: 2018-12-12 14:15:53
    last_seen: 2018-12-12 14:28:56
total_latency: 130627767982000
   exec_count: 25
rows_examined: 110432980
    rows_sent: 877
*************************** 2. row ***************************
        query: SELECT `oi_timestamp` AS `img_timestamp` , `oi_name` AS `img_name` , `oi_name` AS `thumb` , `oi_size` AS `img_size` , ? AS `top` , COALESCE ( `comment_oi_description` . `comment_text` , `oi_description` ) AS `oi_description_text` , `comment_oi_description` . `comment_data` AS `oi_description_data` , `comment_oi_description` . `comment_id` AS `oi_description_cid` , ? AS `description_field` , `oi_user` AS `img_user` , `oi_user_text` AS `img_user_text` , ? AS `img_actor` FROM `oldimage` LEFT JOIN `comment` `comment_oi_description` ON ( ( `comment_oi_description` . `comment_id` = `oi_description_id` ) ) WHERE ( ( `oi_user` = ? ) ) AND `oi_deleted` = ? AND ( `oi_timestamp` >= ? ) ORDER BY `oi_timestamp` LIMIT ?
   first_seen: 2018-12-12 14:22:36
    last_seen: 2018-12-12 14:28:07
total_latency: 27711608943000
   exec_count: 7
rows_examined: 30920774
    rows_sent: 3
*************************** 3. row ***************************
        query: SELECT `oi_timestamp` AS `img_timestamp` , `oi_name` AS `img_name` , `oi_name` AS `thumb` , `oi_size` AS `img_size` , ? AS `top` , COALESCE ( `comment_oi_description` . `comment_text` , `oi_description` ) AS `oi_description_text` , `comment_oi_description` . `comment_data` AS `oi_description_data` , `comment_oi_description` . `comment_id` AS `oi_description_cid` , ? AS `description_field` , `oi_user` AS `img_user` , `oi_user_text` AS `img_user_text` , ? AS `img_actor` FROM `oldimage` LEFT JOIN `comment` `comment_oi_description` ON ( ( `comment_oi_description` . `comment_id` = `oi_description_id` ) ) WHERE ( ( `oi_user` = ? ) ) AND `oi_deleted` = ? AND ( `oi_timestamp` < ? ) ORDER BY `oi_timestamp` DESC LIMIT ?
   first_seen: 2018-12-12 14:22:40
    last_seen: 2018-12-12 14:28:11
total_latency: 26374348551000
   exec_count: 7
rows_examined: 30921366
    rows_sent: 299
*************************** 4. row ***************************
        query: SELECT `oi_timestamp` AS `img_timestamp` , `oi_name` AS `img_name` , `oi_name` AS `thumb` , `oi_size` AS `img_size` , ? AS `top` , COALESCE ( `comment_oi_description` . `comment_text` , `oi_description` ) AS `oi_description_text` , `comment_oi_description` . `comment_data` AS `oi_description_data` , `comment_oi_description` . `comment_id` AS `oi_description_cid` , ? AS `description_field` , `oi_user` AS `img_user` , `oi_user_text` AS `img_user_text` , ? AS `img_actor` FROM `oldimage` LEFT JOIN `comment` `comment_oi_description` ON ( ( `comment_oi_description` . `comment_id` = `oi_description_id` ) ) WHERE ( ( `oi_user` = ? ) ) AND `oi_deleted` = ? AND ( `oi_timestamp` <= ? ) ORDER BY `oi_timestamp` DESC LIMIT ?
   first_seen: 2018-12-12 14:27:52
    last_seen: 2018-12-12 14:28:02
total_latency: 7693955058000
   exec_count: 2
rows_examined: 8834514
    rows_sent: 2
*************************** 5. row ***************************
        query: SELECT `oi_timestamp` AS `img_timestamp` , `oi_name` AS `img_name` , `oi_name` AS `thumb` , `oi_size` AS `img_size` , ? AS `top` , COALESCE ( `comment_oi_description` . `comment_text` , `oi_description` ) AS `oi_description_text` , `comment_oi_description` . `comment_data` AS `oi_description_data` , `comment_oi_description` . `comment_id` AS `oi_description_cid` , ? AS `description_field` , `oi_user` AS `img_user` , `oi_user_text` AS `img_user_text` , ? AS `img_actor` FROM `oldimage` LEFT JOIN `comment` `comment_oi_description` ON ( ( `comment_oi_description` . `comment_id` = `oi_description_id` ) ) WHERE ( ( `oi_user` = ? ) ) AND `oi_deleted` = ? AND ( `oi_timestamp` > ? ) ORDER BY `oi_timestamp` LIMIT ?
   first_seen: 2018-12-12 14:27:56
    last_seen: 2018-12-12 14:28:05
total_latency: 7299142771000
   exec_count: 2
rows_examined: 8834714
    rows_sent: 102
*************************** 6. row ***************************
        query: SELECT `oi_timestamp` AS `img_timestamp` , `oi_name` AS `img_name` , `oi_name` AS `thumb` , `oi_size` AS `img_size` , ? AS `top` , COALESCE ( `comment_oi_description` . `comment_text` , `oi_description` ) AS `oi_description_text` , `comment_oi_description` . `comment_data` AS `oi_description_data` , `comment_oi_description` . `comment_id` AS `oi_description_cid` , ? AS `description_field` , `oi_user` AS `img_user` , `oi_user_text` AS `img_user_text` , ? AS `img_actor` FROM `oldimage` LEFT JOIN `comment` `comment_oi_description` ON ( ( `comment_oi_description` . `comment_id` = `oi_description_id` ) ) WHERE ( ( `oi_user` = ? ) ) AND `oi_deleted` = ? ORDER BY `oi_timestamp` LIMIT ?
   first_seen: 2018-12-12 14:27:39
    last_seen: 2018-12-12 14:27:39
total_latency: 3750216586000
   exec_count: 1
rows_examined: 4417357
    rows_sent: 51

Event Timeline

Restricted Application changed the subtype of this task from "Release" to "Task". · View Herald TranscriptDec 12 2018, 3:31 PM
zeljkofilipin raised the priority of this task from Normal to Unbreak Now!.Dec 12 2018, 3:34 PM

Train blockers are UBN.

Restricted Application added a subscriber: TerraCodes. · View Herald TranscriptDec 12 2018, 3:34 PM

Change 479227 had a related patch set uploaded (by Anomie; owner: Anomie):
[mediawiki/core@master] ImageListPager: Don't query by oi_user

https://gerrit.wikimedia.org/r/479227

Change 479229 had a related patch set uploaded (by Ladsgroup; owner: Anomie):
[mediawiki/core@wmf/1.33.0-wmf.8] ImageListPager: Don't query by oi_user

https://gerrit.wikimedia.org/r/479229

Change 479227 merged by jenkins-bot:
[mediawiki/core@master] ImageListPager: Don't query by oi_user

https://gerrit.wikimedia.org/r/479227

Change 479229 merged by jenkins-bot:
[mediawiki/core@wmf/1.33.0-wmf.8] ImageListPager: Don't query by oi_user

https://gerrit.wikimedia.org/r/479229

Mentioned in SAL (#wikimedia-operations) [2018-12-12T16:17:47Z] <ladsgroup@deploy1001> Synchronized php-1.33.0-wmf.8/includes/specials/pagers/ImageListPager.php: T211774 (duration: 00m 52s)

Mentioned in SAL (#wikimedia-operations) [2018-12-12T16:19:21Z] <ladsgroup@deploy1001> Synchronized php-1.33.0-wmf.8/includes/specials/pagers/ImageListPager.php: T211774 (duration: 00m 52s)

Ladsgroup closed this task as Resolved.Dec 12 2018, 5:11 PM