Page MenuHomePhabricator

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

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

Event Timeline

Nikerabbit created this task.

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).

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

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)