CodeRevisionListView::getRevCount is creating slow queries on mediawiki.org
Closed, DeclinedPublic
Actions

Assigned To

None

Authored By

	jcrespo
	Mar 12 2019, 9:35 AM

Description

SELECT /* CodeRevisionListView::getRevCount <ip> */ COUNT( DISTINCT cr_id ) AS rev_count FROM `code_rev` LEFT JOIN `code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '1' LIMIT 1

Full list at P8182, creating 1052 20+ second queries on mediawikiwiki (s3).

The extension should be fixed to avoid creating long running queries or (it says here it should no longer be used on WMF: https://www.mediawiki.org/wiki/Extension:CodeReview ), fully deprecated from WMF infrastructure and undeployed.

Related Objects
Search...

Status	Assigned	Task
Declined	None	T218079 CodeRevisionListView::getRevCount is creating slow queries on mediawiki.org
Resolved	Jdforrester-WMF	T116948 Undeploy CodeReview
Open	Legoktm	T205361 Make an HTML dump of the output of the CodeReview extension on MediaWiki.org
Resolved	Dzahn	T243056 Set up static-codereview.wikimedia.org to host static HTML dump of CodeReview
Resolved	ArielGlenn	T243055 Publish SQL dumps of CodeReview tables

Event Timeline

jcrespo created this task.Mar 12 2019, 9:35 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 12 2019, 9:35 AM

jcrespo added a subtask: T116948: Undeploy CodeReview.Mar 12 2019, 9:48 AM

Dzahn mentioned this in T205361: Make an HTML dump of the output of the CodeReview extension on MediaWiki.org.Mar 12 2019, 9:49 AM

jcrespo mentioned this in T205482: CodeReview extension: Code stewardship review.Mar 12 2019, 9:52 AM

jcrespo updated the task description. (Show Details)

That SQL query has been in the code and deployed since at least 2011. The reason is most definitely web crawler browsing through some url of https://www.mediawiki.org/wiki/Special:Code . I can not tell though which exact path is being hit :(

Going through EXPLAIN shows the query is rather straightforward using indices and going through 62k rows:

10.64.16.191(mediawikiwiki)> EXPLAIN SELECT /* CodeRevisionListView::getRevCount XXXX */ COUNT( DISTINCT cr_id ) AS rev_count FROM `code_rev` LEFT JOIN `code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '1' LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: code_rev
         type: ref
possible_keys: PRIMARY,cr_repo_id,cr_repo_author,cr_repo_status_author
          key: cr_repo_id
      key_len: 4
          ref: const
         rows: 61976
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: code_comment
         type: ref
possible_keys: cc_repo_id,cc_repo_time,cc_author
          key: cc_repo_id
      key_len: 8
          ref: mediawikiwiki.code_rev.cr_repo_id,mediawikiwiki.code_rev.cr_id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

Running it in production:

)> SELECT /* CodeRevisionListView::getRevCount XXXX */ COUNT( DISTINCT cr_id ) AS rev_count FROM `code_rev` LEFT JOIN `code_comment` ON ((cc_repo_id = cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '1' LIMIT 1 \G
*************************** 1. row ***************************
rev_count: 115792
1 row in set (0.29 sec)

Could it be that too many have/are being done at the same time and some kind of lock happens on the database that causes them to wait? Note the query is done on the replica (or should).

Just removing our team project as we probably won't work on fixing this issue specifically. Rather we want to work on removing this extension from production.

Yes sorry, your team tag was added when I wasn't aware of T116948

Krinkle triaged this task as Low priority.Apr 3 2019, 2:13 AM

Krinkle moved this task from Untriaged to Usage problem on the Wikimedia-Rdbms board.Apr 3 2019, 2:48 AM

Jdforrester-WMF changed the status of subtask T116948: Undeploy CodeReview from Open to Stalled.Apr 8 2019, 4:30 PM

hashar removed a subscriber: hashar.Apr 9 2019, 9:54 AM

Krinkle edited projects, added Wikimedia-database-issue; removed Wikimedia-Rdbms.Jul 18 2019, 8:04 PM

Krinkle edited projects, added Slow-DB-Query; removed Wikimedia-database-issue.Apr 9 2022, 7:01 PM

Legoktm changed the status of subtask T116948: Undeploy CodeReview from Stalled to Open.May 20 2022, 8:49 PM

Extension dropped from production. Problem still likely exists, but we won't be fixing it.

Bugreporter mentioned this in T309052: Archive the CodeReview extension.May 24 2022, 10:57 AM

CodeRevisionListView::getRevCount is creating slow queries on mediawiki.orgClosed, DeclinedPublicActions

Description

Related ObjectsSearch...

Event Timeline

CodeRevisionListView::getRevCount is creating slow queries on mediawiki.org
Closed, DeclinedPublic
Actions

Related Objects
Search...