Page MenuHomePhabricator

Spike: Investigate Long running queries from pltools unlikely to finish
Closed, ResolvedPublic

Description

Relevant Tickets:

Result:

  • understand if we have to have full entity IDs in the wb_items_per_site table to support toolforge and friends

Timebox:

  • 4 hours

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 7 2017, 2:15 PM
Ladsgroup claimed this task.Nov 7 2017, 3:40 PM
Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptNov 7 2017, 3:40 PM

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 1

I will also send a notice to users asking them if it's affecting their performance.

Pasleim added a subscriber: Pasleim.Nov 8 2017, 6:05 AM

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.

WMDE-leszek closed this task as Resolved.Nov 21 2017, 1:14 PM