Page MenuHomePhabricator
Create Task
Maniphest T351999

Slow CX Queries from RecentTranslationEntryPoint
Closed, ResolvedPublic
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
Related Changes in Gerrit:
SubjectAuthorRepoBranchLines +/-
CX: Add new index to speed up query in "findByPublishedTitle" methodNik Gkountasmediawiki/extensions/ContentTranslationmaster+359 -2
Customize query in gerrit

Related Objects
Search...

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
gerritbot added a comment.Nov 30 2023, 5:40 PM

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.
gerritbot added a comment.Jan 9 2024, 1:18 PM

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
Nikerabbit reassigned this task from ngkountas to KartikMistry.Jan 10 2024, 11:21 AM
Nikerabbit moved this task from In Review to Check after deployment on the Language-Team (Language-2024-January-March) board.
Nikerabbit updated Other Assignee, added: ngkountas.
Nikerabbit added a subscriber: ngkountas.

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.

Krinkle moved this task from Untriaged to Nov 2023 on the Wikimedia-production-error board.Feb 8 2024, 4:26 PM
Krinkle edited projects, added: Wikimedia-Slow-DB-Query; removed: Wikimedia-production-error.
Krinkle subscribed.

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.May 13 2024, 12:23 PM
Pginer-WMF edited projects, added: LPL Essential (LPL Essential 2024 Jul-Oct); removed: Language-Team (Language-2024-April-June).Jul 3 2024, 9:07 AM
Lucas_Werkmeister_WMDE mentioned this in T383052: Full table scan query on wikishared.Jan 6 2025, 1:49 PM
Ladsgroup subscribed.Jan 6 2025, 2:06 PM

Hi, Because this index was not added to production (not following the schema change process) it was causing stress on x1 and could have easily brought down all of production. For more details see T383052: Full table scan query on wikishared. Please next time follow the schema change process. Thank you.

Marostegui added a subtask: T383052: Full table scan query on wikishared.Jan 6 2025, 2:19 PM
Lucas_Werkmeister_WMDE subscribed.Jan 6 2025, 2:19 PM
In T351999#9449771, @Nikerabbit wrote:

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.

This was now done in T383052, which probably means this task can be closed?

Marostegui closed this task as Resolved.Jan 6 2025, 2:20 PM
Marostegui claimed this task.
Marostegui added a project: DBA.
Marostegui subscribed.

This has been fixed as we fixed T383052: Full table scan query on wikishared

Nikerabbit moved this task from Sprint Backlog to Done on the LPL Essential (LPL Essential 2024 Jul-Oct) board.Jul 1 2025, 11:54 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits