Page MenuHomePhabricator

Add 'tmp1' index that is in WMF production to the Wikibase install SQL files
Closed, ResolvedPublic3 Story Points

Description

As a WMF DBA I want the production SQL tables to match what the developers work on to avoid unexpected issues.
As a Wikibase developer I want the SQL in code to match what is used in production in order to avoid accidently breaking production.

Description

At some point in 2018 we thoughts the index "tmp1" that exists in WMF production for wikidata on the wb_terms table was not used.
While removing the index we had a rather large outage, probably to do with some usage of the index, and thus the removal was reverted.
In order to being our code SQL files in line with WMF production this index should be re added to our code.

T197854 discusses renaming the index, but this process would take weeks, and as our end game is to totally remove the table there is little point in spending many weeks of DBA time doing the rename.
If we do find that we actually want to still use the table in the future we can re-re evaluate this

Impact & Priority

The tmp1 index caused a massive outage, we want to avoid doing that in the future, although I don't see if forgetting about it in the near term.
This task is small and should be easy to complete so lets get it done and avoid it sitting here for a year, at which point we may have forgotten about the hardships that it caused.

Task

Add the SQL code for the tmp1 index on wb_terms to the Wikibase code base

Acceptance criteria

  • On Wikibase install the wb_terms table should exist with the tmp1 index.
  • On Wikibase update the tmp1 index should be added to wb_terms if it did not already exist.
  • The tmp1 index should match the index that is used in WMF production.
  • A comment next to the index should be added pointing to this ticket so it can easily be found if a developer looks at the code in 2 years time.

SQL Details

As of 20/08/2018

MariaDB [wikidatawiki]> describe wb_terms;
+---------------------+---------------------+------+-----+---------+----------------+
| Field               | Type                | Null | Key | Default | Extra          |
+---------------------+---------------------+------+-----+---------+----------------+
| term_row_id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| term_entity_id      | int(10) unsigned    | NO   | MUL | NULL    |                |
| term_full_entity_id | varbinary(32)       | YES  | MUL | NULL    |                |
| term_entity_type    | varbinary(32)       | NO   |     | NULL    |                |
| term_language       | varbinary(32)       | NO   | MUL | NULL    |                |
| term_type           | varbinary(32)       | NO   |     | NULL    |                |
| term_text           | varbinary(255)      | NO   | MUL | NULL    |                |
| term_search_key     | varbinary(255)      | NO   | MUL | NULL    |                |
| term_weight         | float unsigned      | NO   |     | 0       |                |
+---------------------+---------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
MariaDB [wikidatawiki]> SHOW INDEX FROM wb_terms;
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wb_terms |          0 | PRIMARY             |            1 | term_row_id         | A         |  1682161570 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | wb_terms_entity_id  |            1 | term_entity_id      | A         |    98950680 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | wb_terms_text       |            1 | term_text           | A         |   240308795 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | wb_terms_search_key |            1 | term_search_key     | A         |    64698521 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | term_full_entity    |            1 | term_full_entity_id | A         |   112144104 |     NULL | NULL   | YES  | BTREE      |         |               |
| wb_terms |          1 | term_search_full    |            1 | term_language       | A         |     7067905 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | term_search_full    |            2 | term_full_entity_id | A         |  1682161570 |     NULL | NULL   | YES  | BTREE      |         |               |
| wb_terms |          1 | term_search_full    |            3 | term_type           | A         |  1682161570 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | term_search_full    |            4 | term_search_key     | A         |  1682161570 |       16 | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | tmp1                |            1 | term_language       | A         |     6184417 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | tmp1                |            2 | term_type           | A         |     6979923 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | tmp1                |            3 | term_entity_type    | A         |     8048619 |     NULL | NULL   |      | BTREE      |         |               |
| wb_terms |          1 | tmp1                |            4 | term_search_key     | A         |   560720523 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 rows in set (0.00 sec)

The create statement should be as simple as:

CREATE INDEX `tmp1` ON wb_terms (`term_language`,`term_type`,`term_entity_type`,`term_search_key`) COMMENT 'term search index (in use)';

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 20 2018, 9:39 AM
Addshore renamed this task from Add 'tmp1' index that is in WMF production to the wikibase install SQL files to Add 'tmp1' index that is in WMF production to the Wikibase install SQL files.Aug 20 2018, 9:51 AM
Addshore updated the task description. (Show Details)
Addshore updated the task description. (Show Details)
Addshore moved this task from incoming to ready to go on the Wikidata board.Aug 20 2018, 9:53 AM
Addshore moved this task from Backlog to Ready on the wikidata-tech-focus board.

We could also try to add a Comment for the index in the SQL itself.

Jonas updated the task description. (Show Details)Sep 14 2018, 9:44 AM
Jonas added a subscriber: Jonas.

I added a comment to the create command.

Jenkins failed, because sqlite is not able to handle COMMENT statements - should I remove it (pro: we would use this statement the very first time in our sql-files) or split it this in 2 seperate files?

Change 461618 had a related patch set uploaded (by Addshore; owner: Matthias Geisler):
[mediawiki/extensions/Wikibase@master] Add 'tmp1' index to the Wikibase install SQL files

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

Jenkins failed, because sqlite is not able to handle COMMENT statements - should I remove it (pro: we would use this statement the very first time in our sql-files) or split it this in 2 seperate files?

Personally I think having the comment in the SQL file itself will be enough, I don't think we need to add the comment to the actual table.
So I would be pro removing that bit.

Addshore removed Matthias_Geisler_WMDE as the assignee of this task.Oct 9 2018, 10:03 AM
Addshore assigned this task to WMDE-leszek.
Addshore added a subscriber: Matthias_Geisler_WMDE.

Change 463302 had a related patch set uploaded (by WMDE-leszek; owner: Matthias Geisler):
[mediawiki/extensions/Wikibase@master] Adjust wb_terms SQL indexes to match WMF production

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

Change 463302 abandoned by WMDE-leszek:
Adjust wb_terms SQL indexes to match WMF production

Reason:
squashed into I152ca2233afbdc7a9c95b27eb5e649f61e901ede

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

Change 463302 restored by WMDE-leszek:
Adjust wb_terms SQL indexes to match WMF production

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

Change 466896 had a related patch set uploaded (by WMDE-leszek; owner: WMDE-leszek):
[mediawiki/extensions/Wikibase@master] Added tmp1 SQL index on wb_terms table

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

Change 466896 merged by jenkins-bot:
[mediawiki/extensions/Wikibase@master] Added tmp1 SQL index on wb_terms table

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

Change 463302 abandoned by WMDE-leszek:
Adjust wb_terms SQL indexes to match WMF production

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