Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	hoo
	Nov 22 2016, 5:58 PM

Description

The queries produced in Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds is slow.

It runs queries like:

SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */  page_id,page_title  FROM `page`    WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  AND page_is_redirect = '0'  ORDER BY page_id ASC LIMIT 100

(takes >1s on db1082)

… rewriting the queries as follows makes it way faster for me:

SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */  page_id,page_title,page_is_redirect FROM `page`    WHERE (page_id > 3128073) AND page_namespace IN ('0','120') HAVING page_is_redirect = 0 ORDER BY page_id ASC LIMIT 100;

(takes <0.00s on db1082)

Details

	Subject	Repo	Branch	Lines +/-
	Use the "redirect" table in SqlEntityIdPager	mediawiki/extensions/Wikibase	wmf/1.29.0-wmf.3	+63 -10
	Use the "redirect" table in SqlEntityIdPager	mediawiki/extensions/Wikibase	master	+63 -10

Customize query in gerrit

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Resolved		hoo	T151787 No wikidata JSON dump for 2 weeks
		Resolved		hoo	T151356 Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow

Event Timeline

hoo created this task.Nov 22 2016, 5:58 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 22 2016, 5:58 PM

EXPLAINs:

mysql:wikiadmin@db1082 [wikidatawiki]> EXPLAIN SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */  page_id,page_title  FROM `page`    WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  AND page_is_redirect = '0'  ORDER BY page_id ASC LIMIT 100;                                                                                                                                                                                                                                                                
+------+-------------+-------+-------+------------------------------------------------+---------+---------+------+------+-------------+                                                                                                                                        
| id   | select_type | table | type  | possible_keys                                  | key     | key_len | ref  | rows | Extra       |                                                                                                                                        
+------+-------------+-------+-------+------------------------------------------------+---------+---------+------+------+-------------+                                                                                                                                        
|    1 | SIMPLE      | page  | index | PRIMARY,name_title,page_redirect_namespace_len | PRIMARY | 4       | NULL |  199 | Using where |                                                                                                                                        
+------+-------------+-------+-------+------------------------------------------------+---------+---------+------+------+-------------+                                                                                                                                        
1 row in set (0.00 sec)

mysql:wikiadmin@db1082 [wikidatawiki]> EXPLAIN SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */  page_id,page_title,page_is_redirect FROM `page`    WHERE (page_id > 3128073) AND page_namespace IN ('0','120') HAVING page_is_redirect = 0 ORDER BY page_id ASC LIMIT 100;
+------+-------------+-------+-------+--------------------+---------+---------+------+----------+-------------+                                                                                                                                                                
| id   | select_type | table | type  | possible_keys      | key     | key_len | ref  | rows     | Extra       |                                                                                                                                                                
+------+-------------+-------+-------+--------------------+---------+---------+------+----------+-------------+                                                                                                                                                                
|    1 | SIMPLE      | page  | range | PRIMARY,name_title | PRIMARY | 4       | NULL | 12879905 | Using where |                                                                                                                                                                
+------+-------------+-------+-------+--------------------+---------+---------+------+----------+-------------+                                                                                                                                                                
1 row in set (0.00 sec)

Handler variables:

mysql:wikiadmin@db1082 [wikidatawiki]>  SHOW SESSION STATUS like 'Hand%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 0       |
| Handler_icp_attempts       | 0       |
| Handler_icp_match          | 0       |
| Handler_mrr_init           | 0       |
| Handler_mrr_key_refills    | 0       |
| Handler_mrr_rowid_refills  | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 0       |
| Handler_read_last          | 0       |
| Handler_read_next          | 3057066 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_deleted   | 0       |
| Handler_read_rnd_next      | 115     |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_tmp_update         | 0       |
| Handler_tmp_write          | 113     |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+
25 rows in set (0.00 sec)

mysql:wikiadmin@db1082 [wikidatawiki]>  SHOW SESSION STATUS like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 102   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 115   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 113   |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
25 rows in set (0.00 sec)

I have some ideas, this is a very interesting edge case, but I will give you a better answer tomorrow- I have some pending schema changes today.

This is the goal, but we will try to achieve this without the force index, depending on how much I can change the original query:

MariaDB MARIADB db1082 wikidatawiki > EXPLAIN SELECT page_id, page_title FROM `page` FORCE INDEX(PRIMARY) WHERE (page_id > 3128073) AND page_namespac
e IN ('0','120') and page_is_redirect = '0' ORDER BY page_id ASC LIMIT 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: page
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 12880021
        Extra: Using where
1 row in set (0.01 sec)

MariaDB MARIADB db1082 wikidatawiki > SELECT page_id, page_title FROM `page` FORCE INDEX(PRIMARY) WHERE (page_id > 3128073) AND page_namespace IN ('0
','120') and page_is_redirect = '0' ORDER BY page_id ASC LIMIT 100;
100 rows in set (0.01 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 102   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
25 rows in set (0.00 sec)

In T151356#2815197, @jcrespo wrote:

This is the goal, but we will try to achieve this without the force index, depending on how much I can change the original query:

MediaWiki's database abstraction is quite flexible, so most things should just work.

The problem is not how to do the force, that can be added directly, the problem is that force index is a poor workaround, if the index changes in the future or the row distribution, we may be forcing a bad query plan.

hoo triaged this task as High priority.Nov 23 2016, 10:23 AM

So we have the choice of forcing a bad query plan ourselves, or leaving it to Maria to pick a bad query plan...

So we have the choice of forcing a bad query plan ourselves, or leaving it to Maria to pick a bad query plan...

No, why is this a binary solution? we maybe can rewrite the query to do what we want and it changes if the environement changes. What we do not want to do at all is your original horrible HAVING hack that is probably expliting a bug.

jcrespo moved this task from Backlog to In progress on the DBA board.Nov 23 2016, 2:24 PM

If we have to fail back to index hinting, this should be preferred -ignore rather than force:

MariaDB [wikidatawiki]> FLUSH STATUS; pager cat > /dev/null; SELECT page_id,page_title  FROM `page` IGNORE INDEX(page_redirect_namespace_len) WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  AND page_is_redirect = '0'  ORDER BY page_id ASC LIMIT 100; nopager; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.00 sec)

PAGER set to 'cat > /dev/null'
100 rows in set (0.00 sec)

PAGER set to stdout
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 102   |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
26 rows in set (0.00 sec)

I am trying now with histograms to see if we can avoid changing the query in the first place.

The histograms are still not enough to convince T151356#2817971 to not optimize the page_is_redirect condition. My advice is to rewrite the query into:

MariaDB [wikidatawiki]> FLUSH STATUS; pager cat > /dev/null; SELECT page_id,page_title,page_is_redirect  FROM `page` WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  ORDER BY page_id ASC LIMIT 100; nopager; SHOW STATUS like 'Hand%';Query OK, 0 rows affected (0.00 sec)

PAGER set to 'cat > /dev/null'
100 rows in set (0.00 sec)

PAGER set to stdout
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 99    |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
26 rows in set (0.00 sec)

And discard the redirects at application side (assuming they are small, which should be mostly true) or write the query like this:

MariaDB [wikidatawiki]> FLUSH STATUS; pager cat > /dev/null; SELECT page_id,page_title  FROM `page` IGNORE INDEX(page_redirect_namespace_len) WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  AND page_is_redirect = '0'  ORDER BY page_id ASC LIMIT 100; nopager; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.00 sec)

PAGER set to 'cat > /dev/null'
100 rows in set (0.00 sec)

PAGER set to stdout
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 102   |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
26 rows in set (0.00 sec)

We could maybe partition the table to hold the redirects on a separate partition but that may create problems for other queries (requires a PRIMARY key change).

jcrespo moved this task from In progress to Done on the DBA board.Nov 23 2016, 3:30 PM

There already is a separate table with redirect information (redirect), the page_is_redirect field is mostly for convenience these days, I guess.

We could rewrite the query as follows:

SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */  page_id,page_title  FROM `page` LEFT JOIN redirect ON rd_from = page_id WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  AND rd_from IS NULL ORDER BY page_id ASC LIMIT 100;

mysql:wikiadmin@db1082 [wikidatawiki]> EXPLAIN SELECT /* Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds datasets@snapsh... */  page_id,page_title  FROM `page` LEFT JOIN redirect ON rd_from = page_id WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  AND rd_from IS NULL ORDER BY page_id ASC LIMIT 100;                                                                                                                                                                                                                                   
+------+-------------+----------+--------+--------------------+---------+---------+---------------------------+----------+--------------------------------------+                                                                                                              
| id   | select_type | table    | type   | possible_keys      | key     | key_len | ref                       | rows     | Extra                                |                                                                                                              
+------+-------------+----------+--------+--------------------+---------+---------+---------------------------+----------+--------------------------------------+                                                                                                              
|    1 | SIMPLE      | page     | range  | PRIMARY,name_title | PRIMARY | 4       | NULL                      | 12836589 | Using where                          |                                                                                                              
|    1 | SIMPLE      | redirect | eq_ref | PRIMARY            | PRIMARY | 4       | wikidatawiki.page.page_id |        1 | Using where; Using index; Not exists |                                                                                                              
+------+-------------+----------+--------+--------------------+---------+---------+---------------------------+----------+--------------------------------------+                                                                                                              
2 rows in set (0.00 sec)

mysql:wikiadmin@db1082 [wikidatawiki]> SHOW SESSION STATUS like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 104   |
| Handler_read_last          | 0     |
| Handler_read_next          | 102   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 115   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 113   |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
25 rows in set (0.00 sec)

Would that work for you @jcrespo?

I am ok with that, but on my version, it doesn't create a temporary table (maybe you were mixing 2 different executions?):

MariaDB [wikidatawiki]> FLUSH STATUS; pager cat > /dev/null; SELECT page_id,page_title  FROM `page` LEFT JOIN redirect ON rd_from = page_id WHERE (page_id > 3128073) AND page_namespace IN ('0','120')  AND rd_from IS NULL ORDER BY page_id ASC LIMIT 100; nopager; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.00 sec)

PAGER set to 'cat > /dev/null'
100 rows in set (0.00 sec)

PAGER set to stdout
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 104   |
| Handler_read_last          | 0     |
| Handler_read_next          | 102   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
25 rows in set (0.00 sec)

Mentioned in SAL (#wikimedia-operations) [2016-11-24T11:41:26Z] <hoo> Killed the Wikidata JSON dump creation on snapshot1007: Wont succeed before Monday, due to T151356

• Jonas subscribed.Nov 27 2016, 10:47 PM

Change 323817 had a related patch set uploaded (by Hoo man):
Use the "redirect" table in SqlEntityIdPager

https://gerrit.wikimedia.org/r/323817

gerritbot added a project: Patch-For-Review.Nov 28 2016, 12:12 PM

Mentioned in SAL (#wikimedia-operations) [2016-11-28T12:17:19Z] <hoo> Killed the Wikidata json dumpers on snapshot1007 due to T151356. Will be restarted once a fix has been deployed.

Change 323871 had a related patch set uploaded (by Hoo man):
Use the "redirect" table in SqlEntityIdPager

https://gerrit.wikimedia.org/r/323871

hoo added a parent task: T151787: No wikidata JSON dump for 2 weeks.Nov 28 2016, 6:57 PM

Change 323817 merged by jenkins-bot:
Use the "redirect" table in SqlEntityIdPager

https://gerrit.wikimedia.org/r/323817

Change 323871 merged by jenkins-bot:
Use the "redirect" table in SqlEntityIdPager

https://gerrit.wikimedia.org/r/323871

hoo closed this task as Resolved.Nov 28 2016, 7:29 PM

hoo removed a project: Patch-For-Review.

Mentioned in SAL (#wikimedia-operations) [2016-11-28T19:50:12Z] <thcipriani@tin> Synchronized php-1.29.0-wmf.3/extensions/Wikidata: SWAT: [[gerrit:323880|Update Wikibase: Use the "redirect" table in SqlEntityIdPager]] T151356 (duration: 02m 11s)

Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slowClosed, ResolvedPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

Wikibase\Repo\Store\Sql\SqlEntityIdPager::fetchIds query slow
Closed, ResolvedPublic
Actions

Related Objects
Search...