[Wikidata-bugs] [Maniphest] [Commented On] T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow

[jcrespo]

jcrespo added a comment.

This is not a problem with the servers, the query planner, or the indexing:

MariaDB  db1068 commonswiki > EXPLAIN SELECT  DISTINCT eu_entity_id  FROM `wbc_entity_usage`   WHERE eu_entity_id IN ('Q148475','Q54919','Q423048','Q2494649','Q13219454','Q131454','Q36578','Q1798125','Q2597810','Q19938912','Q193563','Q2553334','Q477675','Q623578','Q19675','Q13481868','Q384602')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wbc_entity_usage
         type: index
possible_keys: eu_entity_id
          key: eu_entity_id
      key_len: 300
          ref: NULL
         rows: 17882560
        Extra: Using where; Using index
1 row in set (0.03 sec)

MariaDB  db1068 commonswiki > SHOW CREATE TABLE wbc_entity_usage\G
*************************** 1. row ***************************
       Table: wbc_entity_usage
Create Table: CREATE TABLE `wbc_entity_usage` (
  `eu_row_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `eu_entity_id` varbinary(255) NOT NULL,
  `eu_aspect` varbinary(37) NOT NULL,
  `eu_page_id` int(11) NOT NULL,
  `eu_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`eu_row_id`),
  UNIQUE KEY `eu_entity_id` (`eu_entity_id`,`eu_aspect`,`eu_page_id`),
  KEY `eu_page_id` (`eu_page_id`,`eu_entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18752832 DEFAULT CHARSET=binary
1 row in set (0.01 sec)

MariaDB  db1070 wikidatawiki > EXPLAIN SELECT /* Wikibase\Client\Usage\Sql\EntityUsageTable::getUsedEntityIdStrings  */ DISTINCT eu_entity_id  FROM `wbc_entity_usage`   WHERE eu_entity_id IN ('Q148475','Q54919','Q423048','Q2494649','Q13219454','Q131454','Q36578','Q1798125','Q2597810','Q19938912','Q193563','Q2553334','Q477675','Q623578','Q19675','Q13481868','Q384602')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: wbc_entity_usage
         type: range
possible_keys: eu_entity_id
          key: eu_entity_id
      key_len: 257
          ref: NULL
         rows: 1323
        Extra: Using where; Using index
1 row in set (0.05 sec)

MariaDB  db1070 wikidatawiki > SHOW CREATE TABLE wbc_entity_usage\G
*************************** 1. row ***************************
       Table: wbc_entity_usage
Create Table: CREATE TABLE `wbc_entity_usage` (
  `eu_row_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `eu_entity_id` varbinary(255) NOT NULL,
  `eu_aspect` varbinary(37) NOT NULL,
  `eu_page_id` int(11) NOT NULL,
  `eu_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`eu_row_id`),
  UNIQUE KEY `eu_entity_id` (`eu_entity_id`,`eu_aspect`,`eu_page_id`),
  KEY `eu_page_id` (`eu_page_id`,`eu_entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15125849 DEFAULT CHARSET=binary
1 row in set (0.00 sec)

You are selecting in the first case 14 million rows:

MariaDB  db1068 commonswiki > SELECT count(*)  FROM `wbc_entity_usage`   WHERE eu_entity_id IN ('Q148475','Q54919','Q423048','Q2494649','Q13219454','Q131454','Q36578','Q1798125','Q2597810','Q19938912','Q193563','Q2553334','Q477675','Q623578','Q19675','Q13481868','Q384602')\G
*************************** 1. row ***************************
count(*): 13997900
1 row in set (1 min 30.30 sec)

and 1328 in the second case:

MariaDB  db1070 wikidatawiki > SELECT count(*) FROM `wbc_entity_usage`   WHERE eu_entity_id IN ('Q148475','Q54919','Q423048','Q2494649','Q13219454','Q131454','Q36578','Q1798125','Q2597810','Q19938912','Q193563','Q2553334','Q477675','Q623578','Q19675','Q13481868','Q384602');
+----------+
| count(*) |
+----------+
|     1328 |
+----------+
1 row in set (0.00 sec)

I will start killing this query when it takes more than 5 seconds until it is fixed.

TASK DETAIL

https://phabricator.wikimedia.org/T116404

EMAIL PREFERENCES

https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: jcrespo
Cc: aaron, aude, daniel, hoo, Aklapper, jcrespo, Vali.matei, Minhnv-2809, Volans, D3r1ck01, Izno, Luke081515, Wikidata-bugs, GWicke, Mbch331, Jay8g, Krenair

_______________________________________________
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs