Maniphest T351999

Slow CX Queries from RecentTranslationEntryPoint
Open, In Progress, HighPublic
Actions

Description

from /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/TransactionProfiler.php(525)
#0 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/TransactionProfiler.php(334): Wikimedia\Rdbms\TransactionProfiler->reportExpectationViolated(string, Wikimedia\Rdbms\GeneralizedSql, double, string, string)
#1 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/TransactionManager.php(615): Wikimedia\Rdbms\TransactionProfiler->recordQueryCompletion(Wikimedia\Rdbms\GeneralizedSql, double, boolean, integer, string, string)
#2 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/Database.php(859): Wikimedia\Rdbms\TransactionManager->recordQueryCompletion(Wikimedia\Rdbms\GeneralizedSql, double, boolean, integer, string)
#3 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/Database.php(749): Wikimedia\Rdbms\Database->attemptQuery(Wikimedia\Rdbms\Query, string, boolean)
#4 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/Database.php(670): Wikimedia\Rdbms\Database->executeQuery(Wikimedia\Rdbms\Query, string, integer)
#5 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/Database.php(1383): Wikimedia\Rdbms\Database->query(Wikimedia\Rdbms\Query, string)
#6 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/Database.php(1392): Wikimedia\Rdbms\Database->select(array, array, array, string, array, array)
#7 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/DBConnRef.php(119): Wikimedia\Rdbms\Database->selectRow(array, array, array, string, array, array)
#8 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/database/DBConnRef.php(369): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#9 /srv/mediawiki/php-1.42.0-wmf.5/includes/libs/rdbms/querybuilder/SelectQueryBuilder.php(756): Wikimedia\Rdbms\DBConnRef->selectRow(array, array, array, string, array, array)
#10 /srv/mediawiki/php-1.42.0-wmf.5/extensions/ContentTranslation/includes/Store/TranslationStore.php(142): Wikimedia\Rdbms\SelectQueryBuilder->fetchRow()
#11 /srv/mediawiki/php-1.42.0-wmf.5/extensions/ContentTranslation/includes/HookHandler/RecentTranslationEntrypointRegistrationHandler.php(92): ContentTranslation\Store\TranslationStore->findByPublishedTitle(string, string)
#12 /srv/mediawiki/php-1.42.0-wmf.5/includes/HookContainer/HookContainer.php(161): ContentTranslation\HookHandler\RecentTranslationEntrypointRegistrationHandler->onBeforePageDisplay(MediaWiki\Output\OutputPage, MediaWiki\Minerva\Skins\SkinMinerva)
#13 /srv/mediawiki/php-1.42.0-wmf.5/includes/HookContainer/HookRunner.php(941): MediaWiki\HookContainer\HookContainer->run(string, array, array)
#14 /srv/mediawiki/php-1.42.0-wmf.5/includes/Output/OutputPage.php(3025): MediaWiki\HookContainer\HookRunner->onBeforePageDisplay(MediaWiki\Output\OutputPage, MediaWiki\Minerva\Skins\SkinMinerva)
#15 /srv/mediawiki/php-1.42.0-wmf.5/includes/MediaWiki.php(978): MediaWiki\Output\OutputPage->output(boolean)
#16 /srv/mediawiki/php-1.42.0-wmf.5/includes/MediaWiki.php(613): MediaWiki->main()
#17 /srv/mediawiki/php-1.42.0-wmf.5/index.php(50): MediaWiki->run()
#18 /srv/mediawiki/php-1.42.0-wmf.5/index.php(46): wfIndexMain()
#19 /srv/mediawiki/w/index.php(3): require(string)
#20 {main}

explain SELECT  *  FROM `cx_translations`    WHERE translation_target_language = 'XXX' AND translation_target_title = 'XXX' AND (translation_status = 'published' OR (translation_target_url IS NOT NULL))  LIMIT 1 ;
+------+-------------+-----------------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-----------------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | cx_translations | ALL  | NULL          | NULL | NULL    | NULL | 2067285 | Using where |
+------+-------------+-----------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.001 sec)

These queries are frequently taking over 5 seconds.

Logstash example: https://logstash.wikimedia.org/app/discover#/doc/logstash-*/logstash-mediawiki-1-7.0.0-1-2023.11.27?id=m0PoD4wBySCoT0gdQa_7

Details

Other Assignee: ngkountas

	Subject	Repo	Branch	Lines +/-
	CX: Add new index to speed up query in "findByPublishedTitle" method	mediawiki/extensions/ContentTranslation	master	+359 -2

Customize query in gerrit

Event Timeline

Nikerabbit created this task.Nov 27 2023, 9:09 AM

Restricted Application added subscribers: Masumrezarock100, Aklapper. · View Herald TranscriptNov 27 2023, 9:09 AM

Nikerabbit triaged this task as High priority.Nov 27 2023, 9:09 AM

Nikerabbit moved this task from Quarter Backlog to Priority: Translation on the Language-Team (Language-2023-October-December) board.

Nikerabbit updated the task description. (Show Details)Nov 27 2023, 9:11 AM

KartikMistry subscribed.Nov 27 2023, 9:12 AM

Change 979138 had a related patch set uploaded (by Nik Gkountas; author: Nik Gkountas):

[mediawiki/extensions/ContentTranslation@master] CX: Add new index to speed up query in "findByPublishedTitle" method

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

gerritbot added a project: Patch-For-Review.Nov 30 2023, 5:40 PM

ngkountas changed the task status from Open to In Progress.Nov 30 2023, 5:58 PM

ngkountas claimed this task.

ngkountas moved this task from Priority: Translation to In Review on the Language-Team (Language-2023-October-December) board.

ngkountas moved this task from In Review to In Progress on the Language-Team (Language-2023-October-December) board.

Nikerabbit moved this task from Needs Triage to Enhancements on the ContentTranslation board.Dec 4 2023, 11:14 AM

ngkountas moved this task from In Progress to In Review on the Language-Team (Language-2023-October-December) board.Dec 13 2023, 8:17 AM

Pginer-WMF edited projects, added Language-Team (Language-2024-January-March); removed Language-Team (Language-2023-October-December).Jan 8 2024, 10:36 AM

Pginer-WMF moved this task from Quarter Backlog to In Review on the Language-Team (Language-2024-January-March) board.

Change 979138 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] CX: Add new index to speed up query in "findByPublishedTitle" method

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

Maintenance_bot removed a project: Patch-For-Review.Jan 9 2024, 1:30 PM

ReleaseTaggerBot added a project: MW-1.42-notes (1.42.0-wmf.14; 2024-01-16).Jan 9 2024, 2:00 PM

Per my understanding we need to manually apply the schema change in production environments. There are no backwards/forwards compatibility issues, as it is just an index to speed up queries.

Doess not appear to be a production error (definition, but a diagnostic warning in a non-error channel (in this case, DBPerformance).

Nikerabbit moved this task from Check after deployment to Priority: Translation on the Language-Team (Language-2024-January-March) board.Feb 15 2024, 2:48 PM

Pginer-WMF edited projects, added Language-Team (Language-2024-April-June); removed Language-Team (Language-2024-January-March).Apr 2 2024, 3:28 PM

Pginer-WMF moved this task from Quarter Backlog to Priority: Translation on the Language-Team (Language-2024-April-June) board.

Nikerabbit removed KartikMistry as the assignee of this task.Mon, May 13, 12:23 PM

Slow CX Queries from RecentTranslationEntryPointOpen, In Progress, HighPublicActions

Description

Details

Event Timeline

Slow CX Queries from RecentTranslationEntryPoint
Open, In Progress, HighPublic
Actions