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 subscriber: Aklapper. · View Herald Transcript
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.

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

Addshore claimed this task.

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