Page MenuHomePhabricator

Investigate indexes of wb_changes
Closed, ResolvedPublic

Description

While doing T205094: Investigate and restructure SQL directory, and use new MediaWiki abstract schema change features I realized structure of wb_changes is this:

CREATE TABLE IF NOT EXISTS /*_*/wb_changes (
  change_id                  INT unsigned        NOT NULL PRIMARY KEY AUTO_INCREMENT, -- Id of change
  change_type                VARCHAR(25)         NOT NULL, -- Type of the change
  change_time                varbinary(14)       NOT NULL, -- Time the change was made
  change_object_id           varbinary(14)       NOT NULL, -- The full id of the object (ie item, query) the change affects
  change_revision_id         INT unsigned        NOT NULL, -- The id of the revision on the repo that made the change
  change_user_id             INT unsigned        NOT NULL, -- The id of the user on the repo that made the change
  change_info                MEDIUMBLOB          NOT NULL -- Holds additional info about the change, inc diff and stuff
) /*$wgDBTableOptions*/;

CREATE INDEX /*i*/wb_changes_change_type ON /*_*/wb_changes (change_type);
CREATE INDEX /*i*/wb_changes_change_time ON /*_*/wb_changes (change_time);
CREATE INDEX /*i*/wb_changes_change_object_id ON /*_*/wb_changes (change_object_id);
CREATE INDEX /*i*/wb_changes_change_user_id ON /*_*/wb_changes (change_user_id);
CREATE INDEX /*i*/wb_changes_change_revision_id ON /*_*/wb_changes (change_revision_id);

It seems all of indexes are complete random sense of "each column will have an index" (because why not). I'm pretty sure most of these indexes are not used at all and we can drop them. wb_changes is rather small but it's a pretty heavy-write table (twice of ratio of edits) so cleaning up the indexes would make a big difference in replication (also we can turn the ones that are used to covering index for faster read/write)

I add our DBAs to check if they can confirm the hypotheses. This table only exists on s8.

Event Timeline

So a quick look (this needs to be taken with care) reveals that some of them are indeed unused:

root@db2091.codfw.wmnet[sys]> select * from schema_unused_indexes where object_name='wb_changes';
+---------------+-------------+-------------------------------+
| object_schema | object_name | index_name                    |
+---------------+-------------+-------------------------------+
| wikidatawiki  | wb_changes  | wb_changes_change_type        |
| wikidatawiki  | wb_changes  | wb_changes_change_object_id   |
| wikidatawiki  | wb_changes  | wb_changes_change_user_id     |
| wikidatawiki  | wb_changes  | wb_changes_change_revision_id |
+---------------+-------------+-------------------------------+
4 rows in set (0.054 sec)

My advise would be to drop these on an active slave and see if we see performance regressions.

This table only exists on s8.

Surely it must also exist on s4 (commonswiki, testcommonswiki) and s3 (testwikidatawiki)?

This table only exists on s8.

Surely it must also exist on s4 (commonswiki, testcommonswiki) and s3 (testwikidatawiki)?

Indeed. But they are pretty small and the writes on them are not much, I meant it's not in every db so people don't get confused when checking it.

My advise would be to drop these on an active slave and see if we see performance regressions.

Sounds good to me, do you want to do it?

Marostegui triaged this task as Medium priority.
Marostegui added a project: DBA.

Yep, I will try to pick db2082 which is the one with the less weight. We can also try to capture some of the heaviest queries and replay them manually.
Not sure if I will be able to do it today, but I will try to remove them tomorrow

Mentioned in SAL (#wikimedia-operations) [2020-09-23T05:55:31Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2084 T262856', diff saved to https://phabricator.wikimedia.org/P12741 and previous config saved to /var/cache/conftool/dbconfig/20200923-055531-marostegui.json

db2084 got those keys dropped:

root@db2084.codfw.wmnet[wikidatawiki]> show create table wb_changes;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wb_changes | CREATE TABLE `wb_changes` (
  `change_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `change_type` varbinary(25) NOT NULL,
  `change_time` varbinary(14) NOT NULL,
  `change_object_id` varbinary(14) NOT NULL,
  `change_revision_id` int(10) unsigned NOT NULL,
  `change_user_id` int(10) unsigned NOT NULL,
  `change_info` mediumblob NOT NULL,
  PRIMARY KEY (`change_id`),
  KEY `wb_changes_change_type` (`change_type`),
  KEY `wb_changes_change_time` (`change_time`),
  KEY `wb_changes_change_object_id` (`change_object_id`),
  KEY `wb_changes_change_user_id` (`change_user_id`),
  KEY `wb_changes_change_revision_id` (`change_revision_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1274872346 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.032 sec)

root@db2084.codfw.wmnet[wikidatawiki]> alter table wb_changes drop key wb_changes_change_type, drop key wb_changes_change_object_id, drop key wb_changes_change_user_id, drop key wb_changes_change_revision_id;
Query OK, 0 rows affected (0.101 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db2084.codfw.wmnet[wikidatawiki]> show create table wb_changes;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wb_changes | CREATE TABLE `wb_changes` (
  `change_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `change_type` varbinary(25) NOT NULL,
  `change_time` varbinary(14) NOT NULL,
  `change_object_id` varbinary(14) NOT NULL,
  `change_revision_id` int(10) unsigned NOT NULL,
  `change_user_id` int(10) unsigned NOT NULL,
  `change_info` mediumblob NOT NULL,
  PRIMARY KEY (`change_id`),
  KEY `wb_changes_change_time` (`change_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1274872503 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.032 sec)

I am going to slowly repool it back, we need to look for regressions there.
@Ladsgroup if you have some queries in mind, please try them there to see what's the response time.

Mentioned in SAL (#wikimedia-operations) [2020-09-23T05:58:51Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db2084 after index removal T262856', diff saved to https://phabricator.wikimedia.org/P12742 and previous config saved to /var/cache/conftool/dbconfig/20200923-055850-marostegui.json

So tendril doesn't have anything in sampled queries: https://tendril.wikimedia.org/report/sampled_queries?host=^db&user=wikiuser&schema=wik&hours=1 (maybe it's not sampling from codfw?) I looked at the code in depth and found a couple of queries. The queries I see in the performance schema of db2083 are:

SELECT `chd_site` , `chd_db` , `chd_seen` , `chd_touched` , `chd_lock` , `chd_disabled` , `change_time` FROM `wb_changes_dispatch` LEFT JOIN `wb_changes` ON ( ( `chd_seen` = `change_id` ) ) WHERE `chd_disabled` = ? ORDER BY `chd_seen` AS
UPDATE `wb_changes` SET `change_type` = ? , `change_time` = ? , `change_object_id` = ? , `change_revision_id` = ? , `change_user_id` = ? , `change_info` = ? WHERE `change_id` = ?
INSERT INTO `wb_changes` ( `change_type` , `change_time` , `change_object_id` , `change_revision_id` , `change_user_id` , `change_info` ) VALUES (...)
SELECT MIN ( `change_id` ) AS `min_id` , MAX ( `change_id` ) AS `max_id` , MIN ( `change_time` ) AS `min_time` , MAX ( `change_time` ) AS `max_time` FROM `wb_changes` LIMIT ?
DELETE FROM `wb_changes` WHERE ( `change_time` < ? )
SELECT MAX ( `change_id` ) AS `maxid` FROM `wb_changes` LIMIT ?
SELECT `change_id` , `change_type` , `change_time` , `change_object_id` , `change_revision_id` , `change_user_id` , `change_info` FROM `wb_changes` WHERE `change_id` IN (...)
SELECT `change_id` , `change_type` , `change_time` , `change_object_id` , `change_revision_id` , `change_user_id` , `change_info` FROM `wb_changes` WHERE `change_id` = ?
SELECT MIN ( `change_time` ) AS TIMESTAMP FROM `wb_changes_dispatch` , `wb_changes` WHERE `chd_disabled` = ? AND ( `chd_seen` = `change_id` ) LIMIT ?

Mostly are straightforward but I actually found a query on master in the code that's like this (found it in performance schema of db2079 too):

SELECT `change_id` , `change_type` , `change_time` , `change_object_id` , `change_revision_id` , `change_user_id` , `change_info` FROM `wb_changes` WHERE `change_revision_id` = ? LIMIT ?

(With exec count of ~12,510,000 and total latency of 3150724249736000) so I think we need to bring back the index on revision id but the rest can be safely dropped.

I can also try to live capture some on db2084.

Mostly are straightforward but I actually found a query on master in the code that's like this (found it in performance schema of db2079 too):

SELECT change_id , change_type , change_time , change_object_id , change_revision_id , change_user_id , change_info FROM wb_changes WHERE change_revision_id = ? LIMIT ?

(With exec count of ~12,510,000 and total latency of 3150724249736000) so I think we need to bring back the index on revision id but the rest can be safely dropped.

Doing that now

Mentioned in SAL (#wikimedia-operations) [2020-09-23T07:09:26Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Depool db2084 to re-add change_revision_id index T262856', diff saved to https://phabricator.wikimedia.org/P12745 and previous config saved to /var/cache/conftool/dbconfig/20200923-070926-marostegui.json

root@db2084.codfw.wmnet[wikidatawiki]> CREATE INDEX /*i*/wb_changes_change_revision_id ON /*_*/wb_changes (change_revision_id);

Query OK, 0 rows affected (10.702 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db2084.codfw.wmnet[wikidatawiki]>
root@db2084.codfw.wmnet[wikidatawiki]> show create table wb_changes;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table      | Create Table
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| wb_changes | CREATE TABLE `wb_changes` (
  `change_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `change_type` varbinary(25) NOT NULL,
  `change_time` varbinary(14) NOT NULL,
  `change_object_id` varbinary(14) NOT NULL,
  `change_revision_id` int(10) unsigned NOT NULL,
  `change_user_id` int(10) unsigned NOT NULL,
  `change_info` mediumblob NOT NULL,
  PRIMARY KEY (`change_id`),
  KEY `wb_changes_change_time` (`change_time`),
  KEY `wb_changes_change_revision_id` (`change_revision_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1274893656 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.032 sec)

Mentioned in SAL (#wikimedia-operations) [2020-09-23T07:11:29Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856', diff saved to https://phabricator.wikimedia.org/P12746 and previous config saved to /var/cache/conftool/dbconfig/20200923-071129-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-09-23T08:06:51Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856', diff saved to https://phabricator.wikimedia.org/P12747 and previous config saved to /var/cache/conftool/dbconfig/20200923-080651-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-09-23T08:32:00Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856', diff saved to https://phabricator.wikimedia.org/P12748 and previous config saved to /var/cache/conftool/dbconfig/20200923-083200-marostegui.json

@Ladsgroup db2084 has half the weight it used to, I am capturing live queries arriving to wb_changes, so far I haven't found anything strange with their query plans, or extremely slow queries showing up.

Mentioned in SAL (#wikimedia-operations) [2020-09-23T09:45:11Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Slowly repool db2084 after index changes T262856', diff saved to https://phabricator.wikimedia.org/P12750 and previous config saved to /var/cache/conftool/dbconfig/20200923-094511-marostegui.json

Mentioned in SAL (#wikimedia-operations) [2020-09-23T10:01:56Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Fully repool db2084 after index changes T262856', diff saved to https://phabricator.wikimedia.org/P12751 and previous config saved to /var/cache/conftool/dbconfig/20200923-100156-marostegui.json

db2084 is fully repooled, let's monitor its performance

@Ladsgroup after a few hours, I have not seen any significant impact on the host's slow queries for now.
The host dashboard at https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&from=now-24h&to=now&var-server=db2084&var-port=9104 also doesn't show any spikes on anything (scanned rows, or query latency).
Tendril is also not showing slow queries, I will try to get some more queries involving the wb_changes table tomorrow to manually analyze them, but so far so good.

Thank you so much! Let me know If I can be of any help.

So, I have captured a lots of queries involving wb_changes and I haven't found any single query that has a crazy query plan as a result of deleting wb_changes_change_type wb_changes_change_object_id wb_changes_change_user_id.
Also, I have checked if those reported unused indexes are consistent across all the hosts and they seem to be:

db2100.codfw.wmnet:3318
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2094.codfw.wmnet:3318
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_time
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2091.codfw.wmnet:3306
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2086.codfw.wmnet:3318
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2085.codfw.wmnet:3318
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2084.codfw.wmnet:3306
db2083.codfw.wmnet:3306
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2082.codfw.wmnet:3306
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2081.codfw.wmnet:3306
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2080.codfw.wmnet:3306
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id
wikidatawiki	wb_changes	wb_changes_change_revision_id
db2079.codfw.wmnet:3306
object_schema	object_name	index_name
wikidatawiki	wb_changes	wb_changes_change_type
wikidatawiki	wb_changes	wb_changes_change_object_id
wikidatawiki	wb_changes	wb_changes_change_user_id

Some of them do report wb_changes_change_revision_id, some others don't. But as Amir said at T262856#6486318, it might be used, so let's leave it there.
@Ladsgroup I think we can probably go ahead and create a proper schema change ticket and delete: wb_changes_change_type wb_changes_change_type wb_changes_change_user_id.
It is not a huge table, so if we see something very strange once in production on more hosts, we can probably alter it quickly.

-rw-rw---- 1 mysql mysql 2.0G Sep 24 07:28 wb_changes.ibd

Anything else left on this task then?

Change 630224 had a related patch set uploaded (by Ladsgroup; owner: Ladsgroup):
[mediawiki/extensions/Wikibase@master] Drop three unused indexes from wb_changes

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

@Ladsgroup I am going to close this - once you've got all the changes merged, let's create a normal Schema-change-in-production task.
Thank you for cleaning this up!