Page MenuHomePhabricator
Create Task
Maniphest T362123

Retool transcode table with a status column
Open, Needs TriagePublic
Actions

Description

The transcode table in TimedMediaHandler doesn't currently store the status in a straightforward way, which makes the table awkward to query in bulk at the scale of Wikimedia Commons.

Instead of implicitly storing the state of a transcode as the null/present state of various timestamp columns, add a column with suitable state constants and suitable indexes for pulling bulk info and most-recently-added N items to each state.

  • add column: transcode_status INT (eg 0 - 'missing', 1 - 'queued', 2 - 'active', 3 - 'failed')
  • add column: transcode_touched VARBINARY(14)
  • add index: `transcode_status_touched (transcode_status,transcode_touched)
  • update code to set transcode_state and transcode_touched on each update [switchable or carefully deployed]
  • update special:transcode_statistics to use the new fields [switchable or carefully deployed]
  • maintenance script to fill out the fields on existing rows after deployment
  • prep for deployment of the db update

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
[DNM] Cleanup table fieldsmediawiki/extensions/TimedMediaHandlermaster+28 -35
Update WebVideoTrancode classes to rely on touched and state fieldsmediawiki/extensions/TimedMediaHandlermaster+25 -49
Use the state and touched fields where possiblemediawiki/extensions/TimedMediaHandlermaster+19 -36
WIP transcode table updatesmediawiki/extensions/TimedMediaHandlermaster+462 -23
Start writing to transcode status columnsmediawiki/extensions/TimedMediaHandlermaster+62 -6
Add status columns to transcode tablemediawiki/extensions/TimedMediaHandlermaster+348 -1
Customize query in gerrit

Related Objects
Search...

Event Timeline

bvibber created this task.Apr 8 2024, 11:17 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 8 2024, 11:17 PM
bvibber added parent tasks: T350816: Special:Transcode_statistics is very slow to load on Commons, cuts off lots of info, Restricted Task, T112931: Improve readability, reliability of Special:TimedMediaHandler reports, T43867: Show active transcodes counts on Special:TimedMediaHandler.Apr 8 2024, 11:18 PM
Reedy subscribed.Apr 9 2024, 9:03 AM

xref T119173: RFC: Discourage use of MySQL's ENUM type

Ladsgroup subscribed.Apr 9 2024, 9:15 AM

Yup, we shouldn't use ENUM for schema anymore, just use code constants (missing=0, queued=1, etc.)

TheDJ subscribed.Apr 9 2024, 1:14 PM

@bvibber if we are going to run db updates anyways. Perhaps we should add a column for the file size of the derivs to the table as well ? Those are currently unknown and there's been a desire in the past to get better insight into that information. T57942: Expose file sizes of transcoded assets in API

bvibber added a comment.Apr 9 2024, 1:30 PM

Agreed let's do those together...

bvibber updated the task description. (Show Details)Apr 9 2024, 4:17 PM
In T362123#9700047, @Ladsgroup wrote:

Yup, we shouldn't use ENUM for schema anymore, just use code constants (missing=0, queued=1, etc.)

You're right, enum is asking for trouble next time a state gets added. ;) Updated task description.

bvibber mentioned this in T185997: Transcode logging should also log the server on which the transcode process ran.Apr 9 2024, 5:50 PM
Reedy renamed this task from Retool transcode table with a status enum column to Retool transcode table with a status column.Apr 9 2024, 7:44 PM
gerritbot added a comment.Apr 9 2024, 8:49 PM

Change #1018347 had a related patch set uploaded (by Bvibber; author: Bvibber):

[mediawiki/extensions/TimedMediaHandler@master] WIP transcode table updates

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

gerritbot added a project: Patch-For-Review.Apr 9 2024, 8:49 PM
A_smart_kitten subscribed.Jan 31 2025, 2:43 PM
bvibber mentioned this in T398363: Refactor requeueTranscodes to be controllable from Special:TimedMediaHandler.Jul 1 2025, 7:59 PM
bvibber moved this task from Backlog to Major maintenance on the TimedMediaHandler-Transcode board.Jul 2 2025, 7:34 PM
bvibber mentioned this in T358266: TimedMediaHandler: Improve video quality on older iPhones (MPEG-4 Visual).Oct 24 2025, 8:19 PM
Zabe subscribed.Jan 1 2026, 10:09 PM
gerritbot added a comment.Jan 2 2026, 5:55 PM

Change #1222601 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/TimedMediaHandler@master] Add status columns to transcode table

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

gerritbot added a comment.Jan 3 2026, 1:13 PM

Change #1222601 merged by jenkins-bot:

[mediawiki/extensions/TimedMediaHandler@master] Add status columns to transcode table

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

Zabe created subtask T413688: Add status columns to transcode table in wmf production.Jan 3 2026, 2:30 PM
FCeratto-WMF changed the status of subtask T413688: Add status columns to transcode table in wmf production from Open to In Progress.Jan 5 2026, 9:53 AM
gerritbot added a comment.Jan 6 2026, 12:25 AM

Change #1223284 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/TimedMediaHandler@master] Start writing to transcode status columns

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

FCeratto-WMF closed subtask T413688: Add status columns to transcode table in wmf production as Resolved.Jan 12 2026, 11:42 AM
gerritbot added a comment.Jan 13 2026, 1:34 AM

Change #1223284 merged by jenkins-bot:

[mediawiki/extensions/TimedMediaHandler@master] Start writing to transcode status columns

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

TheDJ added a comment.EditedJan 13 2026, 10:26 AM

We never really talked about what we want to do with the old columns, to get rid of them, or keep them. One use for them is still to calculate how long something has been on the queue, or how long it took to transcode. The first one we can probably calculate when the job starts and print to console, the latter one is actually in the interface (and good feedback to users), so we would need to store that in a column if we were to remove the old timestamps.

I realized this while making some queries on quarry, where I calculate those durations based on the old timestamps
Transcodes by last successful: https://quarry.wmcloud.org/query/100961
Transcodes in progress: https://quarry.wmcloud.org/query/100963
Transcodes in queue total: https://quarry.wmcloud.org/query/100966
Transcodes in queue by key: https://quarry.wmcloud.org/query/100964
Last 500 transcode errors: https://quarry.wmcloud.org/query/83666

Note that the in progress values contain some items that are 'stuck', because their failure wasn't properly registered: T414348
And that the in queue contains items that will never be processed because the transcode key was disabled T414414, or because the file no longer exists.

TheDJ added a parent task: T414419: Transcode table should refer to file id.Jan 13 2026, 10:38 AM
TheDJ mentioned this in T414348: Some ogg vorbis files fail transcode silently and have duration of 0.Jan 14 2026, 11:25 AM
TheDJ created subtask T415064: Backfill new status and touched columns.Jan 20 2026, 1:33 PM
gerritbot added a comment.Jan 21 2026, 5:07 PM

Change #1229613 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/TimedMediaHandler@master] Add script for populating transcode state columns

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

gerritbot added a comment.Jan 21 2026, 5:12 PM

Change #1229613 had a related patch set uploaded (by Zabe; author: Zabe):

[mediawiki/extensions/TimedMediaHandler@master] Add script for populating transcode state columns

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

gerritbot added a comment.Feb 17 2026, 7:23 PM

Change #1018347 abandoned by TheDJ:

[mediawiki/extensions/TimedMediaHandler@master] WIP transcode table updates

Reason:

done via I12e4bc72e0a6163948bf19d9e30bef12534ea043 and I3bc0e2c9047ffed7aee1aff8150d6519c902ea99

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

Zabe closed subtask T415064: Backfill new status and touched columns as Resolved.Mar 6 2026, 11:13 AM
Ladsgroup added a comment.Mar 17 2026, 11:06 AM

Okay that now it's fully populated. What's the next step here? I'm sorry. I don't remember all the details of this.

Zabe added a comment.Mar 17 2026, 11:25 AM

@TheDJ wrote a patch https://gerrit.wikimedia.org/r/c/mediawiki/extensions/TimedMediaHandler/+/1239536 starting to read from the new columns.

Ladsgroup added a comment.Mar 17 2026, 11:43 AM

Thanks!

TheDJ added a comment.Mar 17 2026, 12:18 PM

Other places that need converting to make use of the new fields are:

  • TranscodeStatusTable
  • checkTimeSinceLastReset of ApiTranscodeReset
TheDJ added a comment.Mar 26 2026, 9:19 AM

https://commons.wikimedia.org/wiki/Special:Transcode_statistics should be using the new column now.

We:

  • should check if with the new queries for those pages, there's index improvements we should/have to make ?
  • we can probably drop the OLD index over the 4 timestamps ?
gerritbot added a comment.Mar 26 2026, 8:11 PM

Change #1261563 had a related patch set uploaded (by TheDJ; author: TheDJ):

[mediawiki/extensions/TimedMediaHandler@master] Use the state and touched fields where possible

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

gerritbot added a comment.Mar 26 2026, 8:58 PM

Change #1261594 had a related patch set uploaded (by TheDJ; author: TheDJ):

[mediawiki/extensions/TimedMediaHandler@master] Update WebVideoTrancode classes to rely on touched and state fields

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

gerritbot added a comment.Mar 26 2026, 9:12 PM

Change #1261603 had a related patch set uploaded (by TheDJ; author: TheDJ):

[mediawiki/extensions/TimedMediaHandler@master] [DNM] Cleanup table fields

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

gerritbot added a comment.Mar 27 2026, 11:20 AM

Change #1261563 merged by jenkins-bot:

[mediawiki/extensions/TimedMediaHandler@master] Use the state and touched fields where possible

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

ReleaseTaggerBot added a project: MW-1.46-notes (1.46.0-wmf.22; 2026-03-31).Mar 27 2026, 12:01 PM
gerritbot added a comment.Apr 4 2026, 12:58 PM

Change #1261594 merged by jenkins-bot:

[mediawiki/extensions/TimedMediaHandler@master] Update WebVideoTrancode classes to rely on touched and state fields

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

ReleaseTaggerBot edited projects, added: MW-1.46-notes (1.46.0-wmf.23; 2026-04-07); removed: MW-1.46-notes (1.46.0-wmf.22; 2026-03-31).Apr 4 2026, 1:00 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