Page MenuHomePhabricator

Add index on event_page_id on echo_event table
Closed, ResolvedPublic

Description

We'll need this for https://gerrit.wikimedia.org/r/302480 and it may also help for ApiEchoUnreadNotificationPages.php (although that query is fast already).

SQL: https://gerrit.wikimedia.org/r/#/c/306862/1/db_patches/patch-add-event_page_id-index.sql
Where: all x1 databases that have an echo_event table (same set as echo.dblist)
Back compat: yes, because this is an index creation

Details

Related Gerrit Patches:
mediawiki/extensions/Echo : masterAdd index on event_page_id

Event Timeline

Catrope created this task.Aug 26 2016, 12:38 AM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Change 306862 had a related patch set uploaded (by Catrope):
Add index on event_page_id

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

Catrope updated the task description. (Show Details)Aug 26 2016, 1:36 AM

Change 306862 merged by jenkins-bot:
Add index on event_page_id

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

The patch has been merged, but the index still needs to be added in production. I'm collecting this change and others at T143963: The Great Echo Database Cleanup of 2016 (and 2019) before sending them to the DBAs.

Paladox added a subscriber: Paladox.Sep 4 2016, 1:29 PM

This has now been deployed to production

No it hasn't. Database changes are not deployed automatically to production.

mysql:research@s3-analytics-slave [mediawikiwiki]> SHOW CREATE TABLE echo_event;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| echo_event | CREATE TABLE `echo_event` (
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_type` varbinary(64) NOT NULL,
  `event_variant` varbinary(64) DEFAULT NULL,
  `event_agent_id` int(10) unsigned DEFAULT NULL,
  `event_agent_ip` varbinary(39) DEFAULT NULL,
  `event_page_namespace` int(10) unsigned DEFAULT NULL,
  `event_page_title` varbinary(255) DEFAULT NULL,
  `event_extra` blob,
  `event_page_id` int(10) unsigned DEFAULT NULL,
  `event_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`event_id`),
  KEY `event_type` (`event_type`)
) ENGINE=TokuDB AUTO_INCREMENT=461932 DEFAULT CHARSET=binary `compression`='tokudb_zlib' |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql:research@x1-analytics-slave [enwiki]> SHOW CREATE TABLE echo_event;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| echo_event | CREATE TABLE `echo_event` (
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_type` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `event_variant` varchar(64) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `event_agent_id` int(10) unsigned DEFAULT NULL,
  `event_agent_ip` varchar(39) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `event_page_namespace` int(10) unsigned DEFAULT NULL,
  `event_page_title` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `event_extra` blob,
  `event_page_id` int(10) unsigned DEFAULT NULL,
  `event_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`event_id`),
  KEY `event_type` (`event_type`)
) ENGINE=InnoDB AUTO_INCREMENT=87349129 DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql:research@x1-analytics-slave [enwiki]>
Restricted Application added a project: Growth-Team. · View Herald TranscriptMar 2 2019, 1:02 AM
Catrope updated the task description. (Show Details)Mar 4 2019, 9:03 PM
JTannerWMF moved this task from Inbox to Blocked on the Growth-Team board.Mar 5 2019, 7:53 PM
Marostegui triaged this task as Medium priority.Mar 15 2019, 6:20 AM
Marostegui moved this task from Backlog to In progress on the Blocked-on-schema-change board.
Marostegui added a comment.EditedApr 1 2019, 7:16 AM

x1 hosts

  • dbstore2002
  • dbstore1005
  • dbstore1001
  • db2096
  • db2069
  • db2034
  • db2033
  • db1120
  • db1064
  • db1069

x1 is running ROW based, as this is only changing the indexes and not touching, it should not affect replication - I will test it though just to be safe.

Marostegui renamed this task from Add index on event_page_id to Add index on event_page_id on echo_event table.Apr 1 2019, 7:58 AM

Mentioned in SAL (#wikimedia-operations) [2019-04-01T08:09:05Z] <marostegui> Deploy testing schema change on enwiki.echo_event on db2033 and upgrade mysql - T143961

x1 is running ROW based, as this is only changing the indexes and not touching, it should not affect replication - I will test it though just to be safe.

Confirmed, it is fine.

Marostegui closed this task as Resolved.Apr 2 2019, 6:50 AM

All done