Relevant Tickets:
Result:
Timebox:
In tendril and in the past 72 hours I found these two queries that might be affected:
Tavg= 375
SELECT ips_site_id AS sit, COUNT(*) AS ct FROM page_props, wb_items_per_site, page WHERE pp_sortkey = 0 AND pp_propname="wb-claims" AND pp_page = page_id AND CONCAT("Q", ips_item_id) = page_title AND page_namespace = 0 GROUP BY ips_site_id ORDER BY ct DESC
Tavg=271
SELECT page_title FROM page WHERE page_namespace = 0 AND page_title NOT IN (SELECT term_full_entity_id FROM wb_terms) AND page_title NOT IN (SELECT CONCAT("Q", ips_item_id) FROM wb_items_per_site) AND page_is_redirect = 0 LIMIT 1I will also send a notice to users asking them if it's affecting their performance.
The first query can be rewritten to use self join instead of subqueries that usually are more expensive:
mysql:wikiadmin@db1087 [wikidatawiki]> explain SELECT ips_item_id, count(*) AS cnt FROM wb_items_per_site WHERE CONCAT("Q", ips_item_id) IN (SELECT page_title FROM page JOIN pagelinks ON page_id=pl_from WHERE pl_title="Q937857") AND ips_item_id NOT IN (SELECT ips_item_id FROM wb_items_per_site WHERE ips_site_id="enwiki") GROUP BY ips_item_id ORDER BY cnt DESC LIMIT 100;
+------+--------------+-------------------+-------+--------------------------------------+-----------------------+---------+---------------------------+----------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+-------------------+-------+--------------------------------------+-----------------------+---------+---------------------------+----------+-----------------------------------------------------------+
| 1 | PRIMARY | wb_items_per_site | index | NULL | wb_ips_item_id | 4 | NULL | 57997491 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | page | index | PRIMARY | name_title | 261 | NULL | 41222664 | Using where; Using index |
| 1 | PRIMARY | pagelinks | ref | PRIMARY | PRIMARY | 4 | wikidatawiki.page.page_id | 7 | Using where; Using index; FirstMatch(wb_items_per_site) |
| 3 | MATERIALIZED | wb_items_per_site | ref | wb_ips_item_site_page,wb_ips_item_id | wb_ips_item_site_page | 34 | const | 11774980 | Using index condition |
+------+--------------+-------------------+-------+--------------------------------------+-----------------------+---------+---------------------------+----------+-----------------------------------------------------------+
4 rows in set (0.00 sec)
mysql:wikiadmin@db1087 [wikidatawiki]> explain SELECT ips1.ips_item_id, count(*) AS cnt FROM wb_items_per_site as ips1 left join wb_items_per_site as ips2 on ips1.ips_item_id = ips2.ips_item_id and ips2.ips_site_id="enwiki" WHERE CONCAT("Q", ips1.ips_item_id) IN (SELECT page_title FROM page JOIN pagelinks ON page_id=pl_from WHERE pl_title="Q937857") AND ips2.ips_item_id is null GROUP BY ips1.ips_item_id ORDER BY cnt DESC LIMIT 100;
+------+-------------+-----------+------------+--------------------------------------+----------------------+---------+-------------------------------+----------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------------+--------------------------------------+----------------------+---------+-------------------------------+----------+-------------------------------------------------------------------+
| 1 | PRIMARY | ips1 | index | NULL | wb_ips_item_id | 4 | NULL | 57997495 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | page | hash_index | PRIMARY | #hash#$hj:name_title | 257:261 | func | 41222669 | Using where; Start temporary; Using join buffer (flat, BNLH join) |
| 1 | PRIMARY | pagelinks | ref | PRIMARY | PRIMARY | 4 | wikidatawiki.page.page_id | 7 | Using where; Using index; End temporary |
| 1 | PRIMARY | ips2 | ref | wb_ips_item_site_page,wb_ips_item_id | wb_ips_item_id | 4 | wikidatawiki.ips1.ips_item_id | 1 | Using where; Not exists |
+------+-------------+-----------+------------+--------------------------------------+----------------------+---------+-------------------------------+----------+-------------------------------------------------------------------+
4 rows in set (0.01 sec)But both of them take a very long time to finish.
My suggestion: T114904#3769427
Overall, I don't think is there anything we can do for now.