Page MenuHomePhabricator

long queries
Open, Needs TriagePublic

Description

ApprovedRevs generates queries like the following:

SELECT /*  52.1.214.79 */  r.rev_user_text  FROM `revision` `r`,`page` `p`    WHERE p.page_title = 'Former_Assize_Court,_Devizes.jpg'  ORDER BY r.rev_id ASC LIMIT 1;

102GB database on system with 128GB RAM and 48CPUs, this query took 17 seconds
Serving 100 page views per minute, with ~300 concurrent users, CPU went from 100% down to <10% after disabling ApprovedRevs

# Time: 190602 12:31:10
# User@Host: wiki_app_user[wiki_app_user] @ localhost []
# Thread_id: 34081  Schema: wiki_en  QC_hit: No
# Query_time: 17.937014  Lock_time: 0.000045  Rows_sent: 1  Rows_examined: 6754801
# Rows_affected: 0

Event Timeline

freephile created this task.Jun 4 2019, 9:06 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 4 2019, 9:06 PM
freephile updated the task description. (Show Details)Jun 4 2019, 11:20 PM

Which specific ApprovedRevs version on which specific MediaWiki version is this about? What are specific steps to reproduce?

MediaWiki 1.32.1 (4012d8a)
PHP 7.1.29-1+0~20190503101539.18+stretch~1.gbp946c98 (apache2handler)
MariaDB 10.1.38-MariaDB-0+deb9u1
ICU 64.1
Elasticsearch 5.6.16
Lua 5.1.5

Approved Revs v. 251cf4db3ab99366fa281ea535e725672fa2b6c3 (git rev-parse HEAD)

This problem didn't manifest until we went into production where there is more traffic compared to our development /QA environment. Somehow the long queries start to pile up and the kernel starts switching contexts until it's spending all CPU shuffling work while waiting for the long-running queries. The ApprovedRevs queries can be found in the MySQL slow query log if the threshold is set to 5 seconds or more because these queries were all in the range of 17-30+ seconds. Although the tables in question have indexes, the problematic queries end up scanning the entire tables with millions of rows.

explain SELECT /* 52.1.214.79 */ r.rev_user_text FROM revision r,page p WHERE p.page_title = 'Geographylogo.png' ORDER BY r.rev_id ASC LIMIT 1\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: r
         type: index
possible_keys: NULL
          key: usertext_timestamp
      key_len: 811
          ref: NULL
         rows: 2908256
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: NULL
          key: name_title
      key_len: 771
          ref: NULL
         rows: 253420
        Extra: Using where; Using index; Using join buffer (flat, BNL join)

You can see the source with grep -C20 --color rev_user_text $IP/extensions/ApprovedRevs/includes/ApprovedRevs_body.php or see https://github.com/wikimedia/mediawiki-extensions-ApprovedRevs/blob/master/includes/ApprovedRevs_body.php#L356-L372