Page MenuHomePhabricator
Create Task
Maniphest T143763

Remove unused bundling DB fields
Closed, ResolvedPublic
Actions

Description

notification_bundle_base and notification_bundle_display_hash are no longer used now that T120153: Change bundling system to allow individual treatment is done.

SQL:

Where: all x1 databases that have an echo_event table (same set as echo.dblist)
Backwards compatible: yes, now that the patches are merged and deployed the software doesn't reference these fields anymore

Details

SubjectRepoBranchLines +/-
db-eqiad.php: Depool all x1 slavesoperations/mediawiki-configmaster+3 -3
db-eqiad.php: Depool all slaves in x1operations/mediawiki-configmaster+3 -3
Remove notification_bundle_display_hashmediawiki/extensions/Echomaster+8 -90
Remove notification_bundle_basemediawiki/extensions/Echomaster+14 -130
Remove unused method EchoEventMapper::fetchByUserBundleHashmediawiki/extensions/Echomaster+0 -96
Customize query in gerrit

Related Objects
Search...

Event Timeline

Catrope created this task.Aug 24 2016, 2:24 AM
Restricted Application added a project: Collaboration-Team-Triage. · View Herald TranscriptAug 24 2016, 2:24 AM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Catrope claimed this task.Aug 24 2016, 2:40 AM
gerritbot subscribed.Aug 25 2016, 11:32 PM

Change 306845 had a related patch set uploaded (by Catrope):
Remove unused method EchoEventMapper::fetchByUserBundleHash

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

gerritbot added a project: Patch-For-Review.Aug 25 2016, 11:32 PM

Change 306846 had a related patch set uploaded (by Catrope):
Remove notification_bundle_base

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

Catrope renamed this task from Remove notification_bundle_base to Remove unused bundling DB fields.Aug 25 2016, 11:53 PM
Catrope updated the task description. (Show Details)
gerritbot added a comment.Aug 25 2016, 11:55 PM

Change 306849 had a related patch set uploaded (by Catrope):
Remove notification_bundle_display_hash

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

gerritbot added a comment.Aug 26 2016, 12:18 AM

Change 306845 merged by jenkins-bot:
Remove unused method EchoEventMapper::fetchByUserBundleHash

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

Catrope mentioned this in T143963: The Great Echo Database Cleanup of 2016 (and 2019).Aug 26 2016, 12:53 AM
ReleaseTaggerBot added a project: MW-1.28-release (WMF-deploy-2016-08-30_(1.28.0-wmf.17)).Aug 26 2016, 1:00 AM
Catrope added a parent task: T143963: The Great Echo Database Cleanup of 2016 (and 2019).Aug 26 2016, 1:32 AM
• jmatazzoni moved this task from Untriaged to Hot on the Collaboration-Team-Triage board.Sep 21 2016, 7:17 PM
Catrope updated the task description. (Show Details)Mar 4 2019, 10:43 PM
Restricted Application added a project: Growth-Team. · View Herald TranscriptMar 4 2019, 10:43 PM
JTannerWMF edited projects, added Growth-Team (Sprint 0 (Growth Team)); removed Growth-Team.Mar 5 2019, 7:48 PM
JTannerWMF moved this task from Incoming to Code Review on the Growth-Team (Sprint 0 (Growth Team)) board.
gerritbot added a comment.Mar 6 2019, 2:05 PM

Change 306846 merged by jenkins-bot:
[mediawiki/extensions/Echo@master] Remove notification_bundle_base

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

gerritbot added a comment.Mar 6 2019, 7:59 PM

Change 306849 merged by jenkins-bot:
[mediawiki/extensions/Echo@master] Remove notification_bundle_display_hash

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

SBisson moved this task from Code Review to QA on the Growth-Team (Sprint 0 (Growth Team)) board.Mar 7 2019, 2:40 PM
Catrope added a project: Schema-change.Mar 14 2019, 8:46 PM
Catrope updated the task description. (Show Details)
Catrope added a project: Schema-change-in-production.Mar 14 2019, 8:50 PM
Marostegui triaged this task as Medium priority.Mar 15 2019, 6:20 AM
Marostegui moved this task from Backlog to Unclear/blocked to proceed on the Schema-change-in-production board.
Marostegui subscribed.

x1 runs row based replication, which means this may break replication if executed first on the slaves and then on the master.
We can probably put the master to serve reads (once we have the new master in place T211613: rack/setup/install db11[26-38].eqiad.wmnet) and execute it with replication.

ReleaseTaggerBot added a project: MW-1.33-notes (1.33.0-wmf.22; 2019-03-19).Mar 16 2019, 8:30 PM
Etonkovidova removed projects: Growth-Team (Sprint 0 (Growth Team)), Collaboration-Team-Triage.Mar 19 2019, 7:19 PM
Marostegui claimed this task.Apr 2 2019, 6:35 AM
Marostegui moved this task from Unclear/blocked to proceed to In progress on the Schema-change-in-production board.

I am going to do a test with these schema changes, as the table isn't big, I am going to put the master to serve reads and depool the slaves and execute it for a few small wikis with replication (they are mostly dropping an index and just 2 columns drop).
Will get the patch depooling the slaves reviewed before merging.

gerritbot added a comment.Apr 2 2019, 6:41 AM

Change 500654 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool all slaves in x1

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

gerritbot added a comment.Apr 2 2019, 8:15 AM

Change 500654 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool all slaves in x1

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

Stashbot mentioned this in T219777: DBA review of UrlShortener.Apr 2 2019, 8:16 AM

Mentioned in SAL (#wikimedia-operations) [2019-04-02T08:16:40Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Depool all slaves in x1 T219777 T143763 (duration: 00m 53s)

Stashbot added a comment.Apr 2 2019, 8:50 AM

Mentioned in SAL (#wikimedia-operations) [2019-04-02T08:50:00Z] <marostegui> Execute schema change on db1069 x1 master with replication enabled on the following small wikis: aawiki aawikibooks aawiktionary abwiki abwiktionary acewiki advisorswiki advisorywiki adywiki afwiki T143763

Stashbot added a comment.Apr 2 2019, 8:58 AM

Mentioned in SAL (#wikimedia-operations) [2019-04-02T08:58:56Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Repool all slaves in x1 T219777 T143763 (duration: 00m 53s)

Marostegui added a comment.Apr 2 2019, 9:03 AM

So, I have run both changes directly on the master with replication enabled on the following wikis:

aawiki
aawikibooks
aawiktionary
abwiki
abwiktionary
acewiki
advisorswiki
advisorywiki
adywiki
afwiki

The table now looks like this:

mysql.py -hdb1064 afwiki -e "show create table echo_notification\G"
*************************** 1. row ***************************
       Table: echo_notification
Create Table: CREATE TABLE `echo_notification` (
  `notification_event` int(10) unsigned NOT NULL,
  `notification_user` int(10) unsigned NOT NULL,
  `notification_timestamp` binary(14) NOT NULL,
  `notification_read_timestamp` binary(14) DEFAULT NULL,
  `notification_bundle_hash` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `notification_seen` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`notification_user`,`notification_event`),
  KEY `echo_user_timestamp` (`notification_user`,`notification_timestamp`),
  KEY `echo_notification_user_hash_timestamp` (`notification_user`,`notification_bundle_hash`,`notification_timestamp`),
  KEY `echo_notification_event` (`notification_event`),
  KEY `echo_notification_user_read_timestamp` (`notification_user`,`notification_read_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The change was pretty fast as the tables there were pretty small. The biggest one was 12M on disk.
I warmed up the tables before hand too.

The master has been serving all x1 traffic for around 45 minutes with no much problem

iops_db1069.png (500×1 px, 44 KB)

qps_db1069.png (500×1 px, 43 KB)

https://grafana.wikimedia.org/d/000000273/mysql?orgId=1&var-dc=eqiad%20prometheus%2Fops&var-server=db1069&var-port=9104&from=1554192789545&to=1554195687545

I think we can probably fully proceed with this one day early in the morning although there is not a big decrease in traffic during the day, maybe the best gap would be 4-6AM UTC, but even there, there is not such a big difference in traffic anyways.

Marostegui added a comment.Apr 4 2019, 5:55 AM

I will probably depool all slaves on x1 on Monday early in the morning and go ahead and alter the rest of the wikis.

gerritbot added a comment.Apr 4 2019, 6:16 AM

Change 501147 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool all x1 slaves

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

gerritbot added a comment.Apr 8 2019, 5:16 AM

Change 501147 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad.php: Depool all x1 slaves

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

Stashbot added a comment.Apr 8 2019, 5:18 AM

Mentioned in SAL (#wikimedia-operations) [2019-04-08T05:18:01Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Depool all slaves in x1 T219777 T143763 (duration: 01m 30s)

Stashbot added a comment.Apr 8 2019, 5:20 AM

Mentioned in SAL (#wikimedia-operations) [2019-04-08T05:20:02Z] <marostegui> Deploy schema change on x1 master with replication, there will be lag on x1 slaves T143763

Stashbot added a comment.Apr 8 2019, 7:02 AM

Mentioned in SAL (#wikimedia-operations) [2019-04-08T07:02:35Z] <marostegui@deploy1001> Synchronized wmf-config/db-eqiad.php: Repool all slaves in x1 T143763 (duration: 00m 58s)

Marostegui closed this task as Resolved.Apr 8 2019, 7:02 AM

This is all done!

Marostegui mentioned this in T217453: Remove etp_user from echo_target_page in production.Apr 8 2019, 7:04 AM
Marostegui mentioned this in T136427: Remove event_page_namespace and event_page_title.Apr 10 2019, 10:18 AM
Reedy subscribed.Jan 13 2020, 9:01 PM

@Marostegui https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/Echo/+/563543/ has just been made and merged...

I think there might be some wikis in prod created since you dropped this that have the echo_notification_user_hash_timestamp index, as it wasn't dropped from the main SQL file we use to create new wiki tables for Echo

Can you check? Do you want a new task filing?

Marostegui added a comment.Jan 14 2020, 6:13 AM

Thanks @Reedy
That index is existing on all the wikis as that index wasn't included on the original files of this task and hence it wasn't dropped.
https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/Echo/+/306846/10/db_patches/patch-drop-notification_bundle_base.sql
https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/Echo/+/306849/9/db_patches/patch-drop-notification_bundle_display_hash.sql

Let's create a new task for it if you don't mind, as it is a "big" change that will affect some hundreds wikis :-)

Reedy mentioned this in T242749: Drop `echo_notification_user_hash_timestamp` index where it exists.Jan 14 2020, 1:41 PM
Umherirrender mentioned this in T262033: labswiki: Field 'notification_bundle_display_hash' doesn't have a default value (10.64.0.98).Feb 28 2021, 8:59 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL