Ladsgroup added a comment.
Top queries that put more than 1% of the total load on the replicas:
Termbox:
23690016.0 (41.05%) SELECT `wbtl_id` , `wbtl_type_id` , `wbxl_language` ,
`wbx_text` , `wbit_item_id` FROM `wbt_term_in_lang` JOIN `wbt_text_in_lang` ON
( ( `wbtl_text_in_lang_id` = `wbxl_id` ) ) JOIN `wbt_text` ON ( (
`wbxl_text_id` = `wbx_id` ) ) JOIN `wbt_item_terms` ON ( (
`wbit_term_in_lang_id` = `wbtl_id` ) ) WHERE `wbit_item_id` IN (...) AND
`wbtl_type_id` = ? AND `wbxl_language` = ?
6320160.0 (10.95%) SELECT `wbtl_id` , `wbtl_type_id` , `wbxl_language` ,
`wbx_text` , `wbit_item_id` FROM `wbt_term_in_lang` JOIN `wbt_text_in_lang` ON
( ( `wbtl_text_in_lang_id` = `wbxl_id` ) ) JOIN `wbt_text` ON ( (
`wbxl_text_id` = `wbx_id` ) ) JOIN `wbt_item_terms` ON ( (
`wbit_term_in_lang_id` = `wbtl_id` ) ) WHERE `wbit_item_id` = ? AND
`wbtl_type_id` = ? AND `wbxl_language` = ?
2288736.0 (3.966%) SELECT `wbtl_id` , `wbtl_type_id` , `wbxl_language` ,
`wbx_text` , `wbit_item_id` FROM `wbt_term_in_lang` JOIN `wbt_text_in_lang` ON
( ( `wbtl_text_in_lang_id` = `wbxl_id` ) ) JOIN `wbt_text` ON ( (
`wbxl_text_id` = `wbx_id` ) ) JOIN `wbt_item_terms` ON ( (
`wbit_term_in_lang_id` = `wbtl_id` ) ) WHERE `wbit_item_id` = ? AND
`wbtl_type_id` = ? AND `wbxl_language` IN (...)
Rest of wikibase:
2736288.0 (4.741%) SELECT `ips_site_id` , `ips_site_page` , `ips_item_id`
FROM `wb_items_per_site` WHERE `ips_item_id` = ? AND `ips_site_id` = ?
Queries involving revision table:
5337792.0 (9.249%) SELECT `rev_id` , `rev_page` , `rev_timestamp` ,
`rev_minor_edit` , `rev_deleted` , `rev_len` , `rev_parent_id` , `rev_sha1` ,
`comment_rev_comment` . `comment_text` AS `rev_comment_text` ,
`comment_rev_comment` . `comment_data` AS `rev_comment_data` ,
`comment_rev_comment` . `comment_id` AS `rev_comment_cid` , `actor_rev_user` .
`actor_user` AS `rev_user` , `actor_rev_user` . `actor_name` AS `rev_user_text`
, `temp_rev_user` . `revactor_actor` AS `rev_actor` , `page_namespace` ,
`page_title` , `page_id` , `page_latest` , `page_is_redirect` , `page_len` ,
`user_name` FROM `revision` JOIN `revision_comment_temp` `temp_rev_comment` ON
( ( `temp_rev_comment` . `revcomment_rev` = `rev_id` ) ) JOIN `comment`
`comment_rev_comment` ON ( ( `comment_rev_comment` . `comment_id` =
`temp_rev_comment` . `revcomment_comment_id` ) ) JOIN `revision_actor_temp`
`temp_rev_user` ON ( ( `temp_rev_user` . `revactor_rev` = `rev_id` ) ) JOIN
`actor` `actor_rev_user` ON ( ( `actor_rev_user` . `actor_id` =
5085504.0 (8.812%) SELECT `rev_id` , `rev_timestamp` , `page_latest` ,
`page_is_redirect` , `page_title` FROM `page` INNER JOIN `revision` ON ( (
`page_latest` = `rev_id` ) ) WHERE ( `page_title` = ? AND `page_namespace` = ?
)
1920672.0 (3.328%) SELECT `page_namespace` , `page_title` , `page_id` ,
`page_latest` , `page_is_redirect` , `page_len` FROM `revision` JOIN `page` ON
( ( `page_id` = `rev_page` ) ) WHERE `rev_id` = ? LIMIT ?
Rest of core:
3577824.0 (6.199%) SELECT `slot_revision_id` , `slot_content_id` ,
`slot_origin` , `slot_role_id` , `content_size` , `content_sha1` ,
`content_address` , `content_model` FROM `slots` JOIN `content` ON ( (
`slot_content_id` = `content_id` ) ) WHERE `slot_revision_id` = ?
Misc:
2718288.0 (4.71%) SELECT `ts` FROM `heartbeat` . `heartbeat` WHERE `shard`
= ? AND `datacenter` = ? ORDER BY `ts` DESC LIMIT ?
These queries on total are responsible for 90% of the load on the replicas.
Some logical conclusions so far:
- Splitting based on rc table doesn't make sense, let's just drop that.
- 9% of the total load on the db is just figuring out what is the most recent
revision id of an entity. I assume we can cache this better (out of scope of
this ticket, just interesting)
- 56% of the total load is term store (and it's heavily cached, this is
actually 1% of the actual amount of read that reaches to the database, this is
scary).
- I'm working on to find out what's the load for term store coming from, it's
very likely client but let's wait until we can get the actual data
- wb_items_per_site is being pretty heavily, I assume in client when it's
getting sitelink of an item. To be confirmed with stastd
- 10% is Special:Contributions, that's weird. To be checked in more depth
- 6% of the load is just trying to figure out how to load a revision (so it
could connect to ES and load it)
TASK DETAIL
https://phabricator.wikimedia.org/T246415
EMAIL PREFERENCES
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
To: Ladsgroup
Cc: Marostegui, Ladsgroup, WMDE-leszek, Aklapper, Addshore, Alter-paule,
Beast1978, Un1tY, Akuckartz, Hook696, Iflorez, darthmon_wmde, Kent7301,
alaa_wmde, joker88john, CucyNoiD, Nandana, jijiki, Klaas_Z4us_V, Gaboe420,
Giuliamocci, Cpaulf30, Lahi, Gq86, Af420, Bsandipan, Pablo-WMDE,
GoranSMilovanovic, QZanden, LawExplorer, Lewizho99, Maathavan, elukey, _jensen,
rosalieper, Scott_WUaS, Jonas, Wikidata-bugs, aude, Lydia_Pintscher, Mbch331,
Jay8g
_______________________________________________
Wikidata-bugs mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs
