Page MenuHomePhabricator
Create Task
Maniphest T392839

CX tables miss a lot of important indexes causing partial outages
Closed, ResolvedPublic8 Estimated Story Points
Actions

Description

See T390510: Fatal DBUnexpectedError: "Database servers in extension1 are overloaded"

First {T391153}

Then T390510#10772985 (CREATE INDEX translation_started_by_last_updated_timestamp ON cx_translations (translation_started_by, translation_last_updated_timestamp))

Then: T390510#10772917 (cx_suggestions (cxs_source_language, cxs_title))

Then: T390510#10772940 (cxs_source_language_title ON cx_suggestions (cxs_source_language, cxs_title)) This has been scanning 3M rows every time a user made a query. T390510#10772985

Now it's much better but:

  • Please add the indexes I added to the code. And we should add them to testwiki and other places
  • Please audit your code for missing indexes. I have found three so far but according to graphs, there might be more.
  • Please make sure new issues like this doesn't get introduced again (by doing queries on conditions that don't have indexes)

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
cx_translations: Add index to make purging of unpublished draft fastermediawiki/extensions/ContentTranslationmaster+410 -0
cx_translations: Add index to make fetching language trends fastermediawiki/extensions/ContentTranslationmaster+396 -0
cx_lists: Add index to make user suggestion fetching fastermediawiki/extensions/ContentTranslationmaster+204 -0
cx_suggestions: Add index to make deletion by title and language fastermediawiki/extensions/ContentTranslationmaster+148 -1
cx_translations: Add index to fetch by translator & update time quickermediawiki/extensions/ContentTranslationmaster+382 -0
dump-corpora: apply vslow query groupmediawiki/extensions/ContentTranslationmaster+4 -3
ApiQueryPublishedTranslations: Make `from` and `to` mandatorymediawiki/extensions/ContentTranslationwmf/1.44.0-wmf.28+2 -0
ApiQueryPublishedTranslations: Make `from` and `to` mandatorymediawiki/extensions/ContentTranslationwmf/1.44.0-wmf.27+2 -0
ApiQueryPublishedTranslations: Make `from` and `to` mandatorymediawiki/extensions/ContentTranslationmaster+2 -0
Reapply "Remove links to Special:ContentTranslationStats from dashboards"mediawiki/extensions/ContentTranslationmaster+0 -19
CX3 Build 1.0.0+20250505mediawiki/extensions/ContentTranslationmaster+3 K -3 K
Remove links to Special:ContentTranslationStats from dashboardsmediawiki/extensions/ContentTranslationwmf/1.44.0-wmf.27+0 -19
Remove links to Special:ContentTranslationStats from dashboardsmediawiki/extensions/ContentTranslationmaster+0 -19
Disable APIs used in Special:ContentTranslationStatsmediawiki/extensions/ContentTranslationmaster+0 -7
Disable APIs used in Special:ContentTranslationStatsmediawiki/extensions/ContentTranslationwmf/1.44.0-wmf.27+0 -7
Disable Special:ContentTranslationStats pagemediawiki/extensions/ContentTranslationwmf/1.44.0-wmf.27+0 -6
Disable Special:ContentTranslationStats pagemediawiki/extensions/ContentTranslationmaster+0 -6
Show related patches Customize query in gerrit

Related Objects

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Nikerabbit changed the task status from Open to In Progress.May 5 2025, 7:28 AM
Nikerabbit moved this task from Backlog to In Progress on the LPL Essential (LPL Essential 2025 Apr-Jun: CX) board.
Nikerabbit assigned this task to abi_.May 5 2025, 8:29 AM
Nikerabbit set the point value for this task to 8.
gerritbot added a comment.May 5 2025, 9:57 AM

Change #1141849 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] Disbale Special:ContentTranslationStats page

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

gerritbot added a project: Patch-For-Review.May 5 2025, 9:57 AM

Change #1141850 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] Disable ApiQueryContentTranslationStats

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

gerritbot added a comment.May 5 2025, 10:18 AM

Change #1141853 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] Remove links to Special:ContentTranslationStats from dashboards

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

Nikerabbit added a comment.May 5 2025, 11:01 AM

We are disabling our stats page and some related API modules given the queries that were mentioned above. We have looked at those queries before and there is no simple fix for them and a new dashboard was already in the plans, but not ready yet.

We have started working on the query audit in an internal document.

abi_ added a comment.May 5 2025, 11:20 AM
In T392839#10790303, @Daimona wrote:

More queries causing full table scans from T390510#10790296:

SELECT  translation_target_language AS `language`,COUNT(DISTINCT translation_started_by) AS `translators`  FROM `cx_translations`    WHERE ((translation_status = 'published' OR translation_target_url IS NOT NULL))  GROUP BY translation_target_language ;

SELECT  translation_source_language AS `language`,COUNT(DISTINCT translation_started_by) AS `translators`  FROM `cx_translations`    WHERE ((translation_status = 'published' OR translation_target_url IS NOT NULL))  GROUP BY translation_source_language ;

SELECT  translation_source_language AS `sourceLanguage`,translation_target_language AS `targetLanguage`,(CASE WHEN (translation_status = 'published' OR translation_target_url IS NOT NULL) THEN 'published' ELSE 'draft' END) AS `status`,COUNT(*) AS `count`,COUNT(DISTINCT translation_started_by) AS `translators`  FROM `cx_translations`    WHERE translation_status IN ('draft','published')   GROUP BY translation_source_language,translation_target_language,status ;

SELECT  MAX(translation_last_updated_timestamp) AS `date`,COUNT(translation_id) AS `count`  FROM `cx_translations`    WHERE ((translation_status = 'published' OR translation_target_url IS NOT NULL))  GROUP BY YEARWEEK(translation_last_updated_timestamp, 3) ;

These queries are used in SpecialContentTranslationStats page, and we've now disabled that page. It was slow, and timed out most of the times.

For the last query:

SELECT  MAX(translation_last_updated_timestamp) AS `date`,COUNT(translation_id) AS `count`  FROM `cx_translations`    WHERE ((translation_status = 'published' OR translation_target_url IS NOT NULL))  GROUP BY YEARWEEK(translation_last_updated_timestamp, 3) ;

There is another caller from ApiQueryTranslatorStats but that ends up using an index (cx_translation_languages) due to there WHERE condition:

SELECT 
    MAX(translation_last_updated_timestamp) AS date,
    COUNT(translation_id) AS count
FROM 
    cx_translations
WHERE 
    (translation_status = 'draft' AND translation_target_url IS NULL)
    AND translation_source_language = :source
    AND translation_target_language = :target
    AND translation_last_update_by = :translatorId
GROUP BY 
    YEARWEEK(translation_last_updated_timestamp, 3)
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEcx_translationsrefcx_translation_languagescx_translation_languages78const,const,const1Using index condition; Using where; Using temporary; Using filesort
gerritbot added a comment.May 5 2025, 11:26 AM

Change #1141866 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] Disable Special:ContentTranslationStats page

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

gerritbot added a comment.May 5 2025, 11:26 AM

Change #1141867 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] Disable APIs used in Special:ContentTranslationStats

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

gerritbot added a comment.May 5 2025, 11:27 AM

Change #1141868 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] Remove links to Special:ContentTranslationStats from dashboards

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

gerritbot added a comment.May 5 2025, 11:54 AM

Change #1141849 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] Disable Special:ContentTranslationStats page

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

gerritbot added a comment.May 5 2025, 12:14 PM

Change #1141850 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] Disable APIs used in Special:ContentTranslationStats

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

gerritbot added a comment.May 5 2025, 12:14 PM

Change #1141853 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] Remove links to Special:ContentTranslationStats from dashboards

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

ReleaseTaggerBot added a project: MW-1.44-notes (1.44.0-wmf.28; 2025-05-06).May 5 2025, 1:00 PM
gerritbot added a comment.May 5 2025, 1:05 PM

Change #1141866 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] Disable Special:ContentTranslationStats page

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

Stashbot added a comment.May 5 2025, 1:06 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T13:06:12Z] <kartik@deploy1003> Started scap sync-world: Backport for [[gerrit:1141866|Disable Special:ContentTranslationStats page (T392839 T325790)]]

Stashbot mentioned this in T325790: Special:ContentTranslationStats is slow and getting crowded.May 5 2025, 1:06 PM
Stashbot added a comment.May 5 2025, 1:10 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T13:10:55Z] <kartik@deploy1003> kartik, abi: Backport for [[gerrit:1141866|Disable Special:ContentTranslationStats page (T392839 T325790)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Stashbot added a comment.May 5 2025, 1:21 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T13:21:42Z] <kartik@deploy1003> Finished scap sync-world: Backport for [[gerrit:1141866|Disable Special:ContentTranslationStats page (T392839 T325790)]] (duration: 15m 29s)

gerritbot added a comment.May 5 2025, 1:34 PM

Change #1141867 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] Disable APIs used in Special:ContentTranslationStats

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

Stashbot added a comment.May 5 2025, 1:34 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T13:34:32Z] <kartik@deploy1003> Started scap sync-world: Backport for [[gerrit:1141867|Disable APIs used in Special:ContentTranslationStats (T392839)]]

Stashbot added a comment.May 5 2025, 1:39 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T13:39:02Z] <kartik@deploy1003> kartik, abi: Backport for [[gerrit:1141867|Disable APIs used in Special:ContentTranslationStats (T392839)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Stashbot added a comment.May 5 2025, 1:47 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T13:47:56Z] <kartik@deploy1003> Finished scap sync-world: Backport for [[gerrit:1141867|Disable APIs used in Special:ContentTranslationStats (T392839)]] (duration: 13m 23s)

gerritbot added a comment.May 5 2025, 1:52 PM

Change #1141868 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] Remove links to Special:ContentTranslationStats from dashboards

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

Stashbot added a comment.May 5 2025, 1:52 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T13:52:38Z] <kartik@deploy1003> Started scap sync-world: Backport for [[gerrit:1141868|Remove links to Special:ContentTranslationStats from dashboards (T392839)]]

ReleaseTaggerBot edited projects, added MW-1.44-notes (1.44.0-wmf.27; 2025-04-29); removed MW-1.44-notes (1.44.0-wmf.28; 2025-05-06).May 5 2025, 2:00 PM
Stashbot added a comment.May 5 2025, 2:10 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-05T14:10:53Z] <kartik@deploy1003> kartik, abi: Backport for [[gerrit:1141868|Remove links to Special:ContentTranslationStats from dashboards (T392839)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Maintenance_bot removed a project: Patch-For-Review.May 5 2025, 2:30 PM
gerritbot added a comment.May 5 2025, 5:24 PM

Change #1141940 had a related patch set uploaded (by Eamedina; author: Eamedina):

[mediawiki/extensions/ContentTranslation@master] CX3 Build 1.0.0+20250505

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

gerritbot added a project: Patch-For-Review.May 5 2025, 5:24 PM
gerritbot added a comment.May 5 2025, 6:03 PM

Change #1141940 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] CX3 Build 1.0.0+20250505

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

gerritbot added a comment.May 5 2025, 6:18 PM

Change #1141948 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] Reapply "Remove links to Special:ContentTranslationStats from dashboards"

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

ReleaseTaggerBot edited projects, added MW-1.44-notes (1.44.0-wmf.28; 2025-05-06); removed MW-1.44-notes (1.44.0-wmf.27; 2025-04-29).May 5 2025, 7:00 PM
gerritbot added a comment.May 6 2025, 4:20 AM

Change #1141948 abandoned by Abijeet Patro:

[mediawiki/extensions/ContentTranslation@master] Reapply "Remove links to Special:ContentTranslationStats from dashboards"

Reason:

not needed.

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

Maintenance_bot removed a project: Patch-For-Review.May 6 2025, 4:30 AM
Nikerabbit updated the task description. (Show Details)May 6 2025, 11:47 AM
gerritbot added a comment.May 6 2025, 2:15 PM

Change #1142612 had a related patch set uploaded (by Nikerabbit; author: Nikerabbit):

[mediawiki/extensions/ContentTranslation@master] ApiQueryPublishedTranslations: Make `from` and `to` mandatory

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

gerritbot added a project: Patch-For-Review.May 6 2025, 2:15 PM
abi_ added a comment.EditedMay 6 2025, 2:36 PM

Following indexes have been added on production and need to be added to our code:

CREATE INDEX cx_translators_translation_id ON cx_translators (translator_translation_id);
  • Done in T391153
CREATE INDEX translation_started_by_last_updated_timestamp ON cx_translations (translation_started_by, translation_last_updated_timestamp);
CREATE INDEX cxs_source_language_title ON cx_suggestions (cxs_source_language, cxs_title);
CREATE INDEX cxl_owner ON cx_lists (cxl_owner);
CREATE INDEX translation_last_update_by_last_updated_timestamp ON cx_translations (translation_last_update_by, translation_last_updated_timestamp);
CREATE INDEX cx_translations_last_updated_timestamp ON cx_translations ( translation_last_updated_timestamp );
mark subscribed.May 6 2025, 5:01 PM
gerritbot added a comment.May 7 2025, 9:39 AM

Change #1143024 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] cx_translator: Add index fetch by translator and update time faster

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

gerritbot added a comment.May 7 2025, 10:44 AM

Change #1143050 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] cx_suggestions: Add index to make deletion by title and language faster

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

gerritbot added a comment.May 7 2025, 10:46 AM

Change #1143051 had a related patch set uploaded (by Nikerabbit; author: Nikerabbit):

[mediawiki/extensions/ContentTranslation@master] dump-corpora: apply vslow query group

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

gerritbot added a comment.May 7 2025, 11:30 AM

Change #1143062 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] cx_lists: Add index to make user suggestion fetching faster

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

gerritbot added a comment.May 7 2025, 12:06 PM

Change #1143070 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] cx_translations: Add index to make fetching language trends faster

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

Nikerabbit added a comment.May 7 2025, 12:40 PM

Query audit is complete. Working to mitigate remaining issues and adding remaining indexes to the code.

Stashbot added a comment.May 7 2025, 12:58 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-07T12:58:04Z] <Amir1> [wikishared]> CREATE INDEX translation_last_updated_timestamp ON cx_translations (translation_last_updated_timestamp); (T392839)

Ladsgroup mentioned this in T393513: Fatal exception of type "DBUnexpectedError: Database servers in extension1 are overloaded." affecting page views.May 7 2025, 1:58 PM
gerritbot added a comment.May 7 2025, 4:13 PM

Change #1142612 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] ApiQueryPublishedTranslations: Make `from` and `to` mandatory

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

ReleaseTaggerBot added a project: MW-1.45-notes (1.45.0-wmf.1; 2025-05-13).May 7 2025, 5:00 PM
gerritbot added a comment.May 7 2025, 5:13 PM

Change #1143138 had a related patch set uploaded (by Sbisson; author: Nikerabbit):

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] ApiQueryPublishedTranslations: Make `from` and `to` mandatory

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

Ladsgroup added a comment.May 7 2025, 5:29 PM

(I think you need wmf.28 too, just in case it's forgotten, if not then ignore my message)

gerritbot added a comment.May 7 2025, 5:32 PM

Change #1143142 had a related patch set uploaded (by Sbisson; author: Nikerabbit):

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.28] ApiQueryPublishedTranslations: Make `from` and `to` mandatory

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

gerritbot added a comment.May 7 2025, 9:18 PM

Change #1143138 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.27] ApiQueryPublishedTranslations: Make `from` and `to` mandatory

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

gerritbot added a comment.May 7 2025, 9:18 PM

Change #1143142 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@wmf/1.44.0-wmf.28] ApiQueryPublishedTranslations: Make `from` and `to` mandatory

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

Stashbot added a comment.May 7 2025, 9:19 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-07T21:19:20Z] <ladsgroup@deploy1003> Started scap sync-world: Backport for [[gerrit:1143138|ApiQueryPublishedTranslations: Make from and to mandatory (T392839)]], [[gerrit:1143142|ApiQueryPublishedTranslations: Make from and to mandatory (T392839)]]

Stashbot added a comment.May 7 2025, 9:26 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-07T21:26:54Z] <ladsgroup@deploy1003> ladsgroup, sbisson: Backport for [[gerrit:1143138|ApiQueryPublishedTranslations: Make from and to mandatory (T392839)]], [[gerrit:1143142|ApiQueryPublishedTranslations: Make from and to mandatory (T392839)]] synced to the testservers (https://wikitech.wikimedia.org/wiki/Mwdebug)

Stashbot added a comment.May 7 2025, 9:33 PM

Mentioned in SAL (#wikimedia-operations) [2025-05-07T21:33:33Z] <ladsgroup@deploy1003> Finished scap sync-world: Backport for [[gerrit:1143138|ApiQueryPublishedTranslations: Make from and to mandatory (T392839)]], [[gerrit:1143142|ApiQueryPublishedTranslations: Make from and to mandatory (T392839)]] (duration: 14m 12s)

ReleaseTaggerBot edited projects, added MW-1.44-notes (1.44.0-wmf.27; 2025-04-29); removed MW-1.44-notes (1.44.0-wmf.28; 2025-05-06).May 7 2025, 10:00 PM
Nikerabbit lowered the priority of this task from Unbreak Now! to High.May 8 2025, 8:41 AM
Nikerabbit moved this task from Needs Triage to Bugs on the ContentTranslation board.
Nikerabbit mentioned this in T393705: Remove CXStats related code.May 8 2025, 1:32 PM
gerritbot added a comment.May 8 2025, 2:08 PM

Change #1143051 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] dump-corpora: apply vslow query group

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

gerritbot added a comment.May 12 2025, 8:30 AM

Change #1143024 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] cx_translations: Add index to fetch by translator & update time quicker

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

gerritbot added a comment.May 12 2025, 12:55 PM

Change #1144564 had a related patch set uploaded (by Abijeet Patro; author: Abijeet Patro):

[mediawiki/extensions/ContentTranslation@master] cx_translations: Add index to make purging of unpublished draft faster

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

awight subscribed.May 14 2025, 6:13 AM

Great to see that the slowness is being addressed! +1 to everything done already, and although I think the special page was valuable, it was very undependable.

Unfortunately for me, I've just published research based on these public statistics and now I need to find another way to make the data available in the interest of transparency and making my work reproduceable. For my use case I would be happy to do a one-off query and post the results somewhere like figshare, unless there are other ideas? I doubt and legal review of the data release can be done within my timeframe (1 week) so perhaps I'll just use my locally stored output from the public API before it was disabled.

KCVelaga_WMF subscribed.May 15 2025, 4:07 PM

@awight I am currently working on developing a public dashboard currently, it will live on the public Superset instance. You can read more details of the task here: T365813

All the stats that were being presented on the Special:CXStats page will be updated daily (T394431). The dashboard itself might not be the fully ready until mid-June. However, the datasets will be publicly published to https://analytics.wikimedia.org/published/datasets/ towards the end of next week (or early the week after if there is any delay with code reviews etc.) The data will be aggregated to daily granularity. I am not sure if the timing will work for you, but wanted to share anyway as an update.

awight added a comment.May 16 2025, 9:45 AM
In T392839#10827236, @KCVelaga_WMF wrote:

@awight I am currently working on developing a public dashboard currently, it will live on the public Superset instance. You can read more details of the task here: T365813

All the stats that were being presented on the Special:CXStats page will be updated daily (T394431). The dashboard itself might not be the fully ready until mid-June. However, the datasets will be publicly published to https://analytics.wikimedia.org/published/datasets/ towards the end of next week (or early the week after if there is any delay with code reviews etc.) The data will be aggregated to daily granularity. I am not sure if the timing will work for you, but wanted to share anyway as an update.

Fantastic news, thank you for sharing! IMHO the proposed stats are good for ordinary per-language monitoring, and it's excellent to see that there will still be aggregation by both source and target language. Happily for me, it also covers my main use case (for background: extended abstract of research into translation flows). It would also be great to see flows grouped by (source, target) language pairs but this might be a specialized need... unless you can imagine it being useful to know eg. what the major flows to or from a given language are?

KCVelaga_WMF added a comment.EditedMay 16 2025, 3:01 PM

It would also be great to see flows grouped by (source, target) language pairs but this might be a specialized need... unless you can imagine it being useful to know eg. what the major flows to or from a given language are?

Yes, the task actually mentions only the metrics, but not many details on the filters. But the base datasets that will be published, will have the following dimensions/data:

cx_published_translations_daily

  • snapshot_date
  • translation_date
  • translation_source_language
  • translation_target_language
  • published_translation_count
  • translator_count

cx_draft_translations_daily

  • snapshot_date
  • translation_last_updated_date
  • translation_source_language
  • translation_target_language
  • translation_new_draft_count
  • translation_expansion_draft_count
KCVelaga_WMF added a comment.EditedMay 16 2025, 3:04 PM

Also, maybe it is better to continue the conversation on these metrics and the dashboard on the task for that: T365813 - as the scope of this task is different :)

Nikerabbit moved this task from Bugs to Operational issues on the ContentTranslation board.May 21 2025, 11:37 AM
gerritbot added a comment.May 21 2025, 4:55 PM

Change #1143050 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] cx_suggestions: Add index to make deletion by title and language faster

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

ReleaseTaggerBot edited projects, added MW-1.45-notes (1.45.0-wmf.3; 2025-05-27); removed MW-1.45-notes (1.45.0-wmf.1; 2025-05-13).May 21 2025, 5:00 PM
gerritbot added a comment.May 21 2025, 6:40 PM

Change #1143062 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] cx_lists: Add index to make user suggestion fetching faster

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

gerritbot added a comment.May 21 2025, 7:41 PM

Change #1143070 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] cx_translations: Add index to make fetching language trends faster

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

gerritbot added a comment.May 21 2025, 8:40 PM

Change #1144564 merged by jenkins-bot:

[mediawiki/extensions/ContentTranslation@master] cx_translations: Add index to make purging of unpublished draft faster

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

Maintenance_bot removed a project: Patch-For-Review.May 21 2025, 9:31 PM
abi_ moved this task from In Progress to Pending Deployment on the LPL Essential (LPL Essential 2025 Apr-Jun: CX) board.May 22 2025, 8:52 AM

All patches are merged. We need to ensure that the indexes are created on testwiki.

Ladsgroup added a comment.May 22 2025, 11:56 AM

Is it only testwiki? The rest are on wikishared? like no test2wiki or anything like that?

Nikerabbit added a comment.May 22 2025, 4:03 PM

Per https://gerrit.wikimedia.org/g/operations/mediawiki-config/+/d3c4b68d43483df0a0eb92b0ceb67c92eecb5508/wmf-config/InitialiseSettings.php#8185 it should only be Wikipedias on wikishared and testwiki. Anything else would be unexpected.

Ladsgroup added a comment.May 23 2025, 10:06 AM

I did this in test wiki. I think that's enough?

cumin2024@db1223.eqiad.wmnet[testwiki]> CREATE INDEX translation_started_by_last_updated_timestamp ON cx_translations (translation_started_by, translation_last_updated_timestamp);
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

cumin2024@db1223.eqiad.wmnet[testwiki]> CREATE INDEX cxs_source_language_title ON cx_suggestions (cxs_source_language, cxs_title);
Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

cumin2024@db1223.eqiad.wmnet[testwiki]> CREATE INDEX cx_lists_owner ON /*_*/cx_lists (cxl_owner);
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

cumin2024@db1223.eqiad.wmnet[testwiki]> CREATE INDEX cx_translations_last_update_by_last_updated_timestamp ON /*_*/cx_translations (
    ->   translation_last_update_by, translation_last_updated_timestamp
    -> );
Query OK, 0 rows affected (0.010 sec)
Records: 0  Duplicates: 0  Warnings: 0

cumin2024@db1223.eqiad.wmnet[testwiki]> CREATE INDEX cx_translations_last_updated_timestamp ON /*_*/cx_translations (
    ->   translation_last_updated_timestamp
    -> );
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0
abi_ closed this task as Resolved.May 26 2025, 9:43 AM
abi_ moved this task from Pending Deployment to Done on the LPL Essential (LPL Essential 2025 Apr-Jun: CX) board.

I can confirm that the indexes are created on testwiki.

abi_ added a subscriber: ngkountas.EditedMay 27 2025, 9:56 AM

Other tasks identified as part of this work:

Nikerabbit added a comment.May 28 2025, 6:22 AM

Review offset handling in ApiQueryContentTranslation.php

I think ApiQueryContentTranslation.php is fine. The one I was thinking of is ApiQueryPublishedTranslations.php and that code might be removed by T395319: CX: Remove "cxpublishedtranslations" Action API endpoint.

abi_ mentioned this in T395487: Evaluate queries/indexes used in dump-corpora.php script.May 28 2025, 4:18 PM
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