Sub-optimal transaction on Special:PageTranslation
Open, LowPublicPRODUCTION ERROR
Actions

Assigned To

None

Authored By

	Nikerabbit
	Oct 7 2021, 6:46 AM

Description

Error

mwversion: 1.38.0-wmf.2
reqId: 2141917c-207e-45fb-b219-576fddc78b93

message

Sub-optimal transaction on DB(s) [db1136 (metawiki) (TRX#21cace)]: 
0	0.000845	query-m: DELETE FROM `translate_sections` WHERE trs_page = N [TRX#21cace]
1	0.001191	query-m: INSERT INTO `translate_sections` (trs_page,trs_key,trs_text,trs_order) VALUES (N,'X',N) [TRX#21cace]
2	0.000565	query-m: REPLACE INTO `translate_metadata` (tmd_group,tmd_key,tmd_value) VALUES ('X',N) [TRX#21cace]
3	0.000461	query-m: REPLACE INTO `translate_metadata` (tmd_group,tmd_key,tmd_value) VALUES ('X') [TRX#21cace]
4	0.000455	query-m: DELETE FROM `revtag` WHERE rt_page = N AND rt_type = 'X' AND rt_revision = N [TRX#21cace]
5	0.000722	query-m: INSERT INTO `revtag` (rt_page,rt_type,rt_revision) VALUES (N,'X',N) [TRX#21cace]
6	3.268627	...delay...
7	0.000971	query-m: DELETE FROM `translate_metadata` WHERE tmd_group = 'X' [TRX#21cace]
8	0.000771	query-m: DELETE FROM `translate_metadata` WHERE tmd_group = 'X' [TRX#21cace]
9	0.000725	query-m: DELETE FROM `translate_metadata` WHERE tmd_group = 'X' [TRX#21cace]
10	0.001136	query-m: INSERT INTO `logging` (log_type,log_action,log_timestamp,log_actor,log_namespace,log_title,log_page,log_params,log_comment_id) VALUES ('X',N) [TRX#21cace]

Impact

Minor user impact as this is not a common action. Plus usual issues with long-running transactions.

Notes

Slowness comes from re-building the list of message groups. It should be checked if it can be postponed to happen after the writes are done.

Details

Request URL: https://meta.wikimedia.org/wiki/Special:PageTranslation

Related Objects

Mentioned In: T141961: Improve Special:PageTranslation list
T314104: Improve usability of "Sub-optimal transaction" warning in DBPerformance channel
T204026: DBPerformance warning "Query returned XXXX rows: query: SELECT * FROM `translate_metadata`"

Event Timeline

Nikerabbit created this task.Oct 7 2021, 6:46 AM

Nikerabbit triaged this task as Low priority.

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 7 2021, 6:46 AM

Nikerabbit mentioned this in T204026: DBPerformance warning "Query returned XXXX rows: query: SELECT * FROM `translate_metadata`".Oct 7 2021, 6:49 AM

For the record, in case it wasn't clear or for other people, the import bit of the transaction log is this line:

6	3.268627	...delay...

Which indicates a 3.2s gap of time spend in PHP between two writes queries that are holding back the same transaction. Such logic should generally be re-ordered so that either all the compute happens before the first write, or factored out to e.g. happen after the transaction if it isn't needed for the writes, or split off into its own deferred update. The deferred update will have its own transaction. You can use a pre-send deferred update if you still want the write to be part of the user latency (and thus result in error page if it fails).

Krinkle moved this task from Untriaged to Oct 2021 on the Wikimedia-production-error board.Oct 7 2021, 7:23 PM

Krinkle moved this task from Oct 2021 to Older on the Wikimedia-production-error board.

Pginer-WMF moved this task from Backlog to maintenance and operational issues on the MediaWiki-extensions-Translate board.Mar 28 2022, 11:44 AM

Krinkle mentioned this in T314104: Improve usability of "Sub-optimal transaction" warning in DBPerformance channel.Jul 29 2022, 1:11 AM

From query message:translate_sections on the "mediawiki" Logstash dashboard, last 30 days. Issue is still seen, as reported by the DBPerformance channel.

View single document

POST commons.wikimedia.org/wiki/Special:PageTranslation
reqId: b1adeb22-e024-44e9-90de-b351ac20be1a

0	0.001665	query-m: INSERT INTO `revision` (rev_page,rev_parent_id,rev_minor_edit,rev_timestamp,rev_deleted,rev_len,rev_sha1,rev_actor) VALUES (N,...,N,'X',N)
1	0.000305	query-m: INSERT INTO `revision_comment_temp` (revcomment_rev,revcomment_comment_id) VALUES (N,...,N)
2	0.002902	query-m: INSERT INTO `blobs_cluster2N` (blob_text) VALUES ('X')
3	0.000315	query-m: INSERT INTO `text` (old_text,old_flags) VALUES ('X')
4	0.000315	query-m: INSERT INTO `content` (content_size,content_sha1,content_model,content_address) VALUES (N,'X')
5	0.000292	query-m: INSERT INTO `slots` (slot_revision_id,slot_role_id,slot_content_id,slot_origin) VALUES (N,...,N)
6	0.000486	query-m: UPDATE `page` SET page_latest = N,page_touched = 'X' WHERE page_id = N
7	0.001596	query-m: DELETE FROM `translate_sections` WHERE trs_page = N
8	0.019828	query-m: INSERT INTO `translate_sections` (trs_page,trs_key,trs_text,trs_order) VALUES (N,'X',N)
9	0.000365	query-m: REPLACE INTO `translate_metadata` (tmd_group,tmd_key,tmd_value) VALUES ('X',N)
10	0.000315	query-m: REPLACE INTO `translate_metadata` (tmd_group,tmd_key,tmd_value) VALUES ('X')
11	0.000350	query-m: DELETE FROM `revtag` WHERE rt_page = N AND rt_type = 'X'
12	8.976770	query-m: INSERT INTO `revtag` (rt_page,rt_type,rt_revision) VALUES (N,'X',N)
13	0.000494	query-m: DELETE FROM `translate_metadata` WHERE tmd_group = 'X'
14	0.000302	query-m: DELETE FROM `translate_metadata` WHERE tmd_group = 'X'
15	0.000252	query-m: DELETE FROM `translate_metadata` WHERE tmd_group = 'X'
16	0.000537	query-m: INSERT INTO `logging` (log_type,log_action,log_timestamp,log_actor,log_namespace,log_title,log_page,log_params,log_comment_id) VALUES ('X',N)

Krinkle moved this task from Limbo to Perf recommendation on the Performance-Team (Radar) board.Jul 29 2022, 1:22 AM

Krinkle edited projects, added Wikimedia-Performance-recommendation; removed Performance-Team (Radar).Aug 18 2023, 8:42 PM

abi_ mentioned this in T141961: Improve Special:PageTranslation list.Apr 3 2025, 2:14 PM