Page MenuHomePhabricator

Investigate possible optimizations writes to new term store
Closed, ResolvedPublic

Description

A follow up on T234948: New Wikibase deadlocks on Wikidata wiki since 2019-10-08T00:00:02: Wikibase\Lib\Store\Sql\Terms\{closure} Deadlock found when trying to get lock; try restarting transaction

Needs work

Event Timeline

Restricted Application added a project: Wikidata. · View Herald TranscriptOct 9 2019, 5:54 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
alaa_wmde triaged this task as Medium priority.Oct 22 2019, 12:12 PM
alaa_wmde moved this task from Needs Work to Ready to pick up on the Wikidata-Campsite board.
Addshore added subscribers: Ladsgroup, Addshore.

@Ladsgroup has investigated this somewhat and will write the results here.

Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptOct 29 2019, 1:29 PM

I turned on logging on all of queries to database on my localhost and did some editing. This is the output for the new term store:

amsa@amsa-Latitude-7480:/var/log/mediawiki$ tail debug-repo.log --lines 200 | grep DBQuery | grep -v LCStoreDB | grep wbt_ | grep -i insert
[DBQuery] Wikibase\Lib\Store\Sql\Terms\DatabasePropertyTermStore::acquireAndInsertTerms [0.001s] [Null]: SELECT  wbpt_term_in_lang_id AS `value`  FROM `wbt_property_terms`    WHERE wbpt_property_id = '21'   FOR UPDATE
[DBQuery] Wikimedia\Rdbms\Database::insert [0.001s] [Null]: INSERT  INTO `wbt_text` (wbx_text) VALUES ('fa-value'),('fa-vaeeelue')
[DBQuery] Wikimedia\Rdbms\Database::insert [0s] [Null]: INSERT  INTO `wbt_text_in_lang` (wbxl_text_id,wbxl_language) VALUES ('19','fa'),('18','fa')
[DBQuery] Wikimedia\Rdbms\Database::insert [0s] [Null]: INSERT  INTO `wbt_term_in_lang` (wbtl_text_in_lang_id,wbtl_type_id) VALUES ('19','1'),('18','2')
[DBQuery] Wikibase\Lib\Store\Sql\Terms\{closure} [0s] [Null]: INSERT  INTO `wbt_property_terms` (wbpt_property_id,wbpt_term_in_lang_id) VALUES ('21','18'),('21','19')

Looking at the queries, there's no obvious low-hanging fruit to me. We can revisit this if we run out of options.

I added one description in German, one in French, one label in German and one in French and still the number of queries is pretty optimal:

[DBQuery] Wikimedia\Rdbms\Database::insert [0.001s] [Null]: INSERT  INTO `wbt_text` (wbx_text) VALUES ('fr-value'),('de-value'),('fr-vaeeelue'),('de-vaeeelue')
[DBQuery] Wikimedia\Rdbms\Database::insert [0s] [Null]: INSERT  INTO `wbt_text_in_lang` (wbxl_text_id,wbxl_language) VALUES ('22','fr'),('20','fr'),('23','de'),('21','de')
[DBQuery] Wikimedia\Rdbms\Database::insert [0s] [Null]: INSERT  INTO `wbt_term_in_lang` (wbtl_text_in_lang_id,wbtl_type_id) VALUES ('21','1'),('23','1'),('20','2'),('22','2')
[DBQuery] Wikibase\Lib\Store\Sql\Terms\{closure} [0s] [Null]: INSERT  INTO `wbt_property_terms` (wbpt_property_id,wbpt_term_in_lang_id) VALUES ('21','20'),('21','21'),('21','22'),('21','23')

Per @Ladsgroup manual testing, there doesn't seem, number of writes per edit seem to be (near-)optimal .. marking that bit with a check.

alaa_wmde renamed this task from Investigate possible optimizations on number of writes to new term store to Investigate possible optimizations writes to new term store.Oct 30 2019, 12:46 PM
alaa_wmde updated the task description. (Show Details)

Moving back to To Do to cover the other bit (the unchecked Database Transactions documentation for other possible optimizations).

alaa_wmde removed Ladsgroup as the assignee of this task.Oct 30 2019, 12:47 PM
Addshore closed this task as Resolved.Dec 10 2019, 11:43 AM
Addshore claimed this task.

We decided that we have completed this investigation with other tasks and will close this now

Restricted Application added a project: User-Addshore. · View Herald TranscriptDec 10 2019, 11:43 AM