Page MenuHomePhabricator

Drop titlekey table from all wmf databases
Closed, ResolvedPublic

Description

TitleKey extension has long since been undeployed--it's behavior was taken over by CirrusSearch. The table is titlekey and it can be safely dropped from all databases.

Shards to drop it from:

  • s1
  • s2
  • s3
  • s4
  • s5
  • s6
  • s7

Event Timeline

demon created this task.May 10 2017, 3:43 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 10 2017, 3:43 PM

Thank you for ticket. For any chance you won't have a way to know on which wikis it was deployed to, even if it is as vague as "all.dblist" 2 years ago?

demon added a comment.May 10 2017, 3:46 PM

all.dblist as of about 2 years ago sounds right, to be honest. The more accurate answer is "all wikis that weren't born with Cirrus already as default"

Umherirrender renamed this task from Drop titlekey table from all databases to Drop titlekey table from all wmf databases.May 10 2017, 6:46 PM

Removing blocked tag, nothing is blocked on this.

demon added a comment.EditedMay 22 2017, 10:52 PM

Here's the fully accurate list: all.dblist when TitleKey was removed.

If anything, it's slightly over inclusive--new wikis were already defaulting with Cirrus. But anything added after that could not possibly have had TitleKey.

demon added a comment.May 23 2017, 6:25 PM

Also, there's no need for any backups here, and I can confirm nothing is still using this data. Safe to just drop outright.

When we say we take backups, what manuel means is that we temporarily move the tables (but only for a limited amount of time). It is not as much as we do not trust that they are not in use, as much as we potentially dropping things that we shouldn't by accident. So we keep a copy from some time, then drop them all together. You will be surprised how many times people realize something they need is gone until it is too late.

Marostegui moved this task from Triage to Backlog on the DBA board.May 24 2017, 10:17 AM

Current status of this unused table:

s1:

root@db1052:/srv/sqldata/enwiki# find . -name titlekey.ibd | xargs ls -lh
-rw-rw---- 1 mysql mysql 4.8G Nov 19  2014 ./titlekey.ibd

s2:

root@db1054:/srv/sqldata# find . -name titlekey.ibd | xargs ls -lh
-rw-rw---- 1 mysql mysql  76M Oct 31  2014 ./bgwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 160M Oct 27  2014 ./bgwiktionary/titlekey.ibd
-rw-rw---- 1 mysql mysql 124M Oct 31  2014 ./cswiki/titlekey.ibd
-rw-rw---- 1 mysql mysql  25M Oct 31  2014 ./enwikiquote/titlekey.ibd
-rw-rw---- 1 mysql mysql 556M Oct 31  2014 ./enwiktionary/titlekey.ibd
-rw-rw---- 1 mysql mysql  72M Oct 31  2014 ./eowiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 144M Oct 31  2014 ./fiwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 196M Oct 31  2014 ./idwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 544M Oct 31  2014 ./itwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 496M Oct 31  2014 ./nlwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 160M Oct 31  2014 ./nowiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 332M Oct 31  2014 ./plwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 524M Oct 31  2014 ./ptwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 652M Oct 31  2014 ./svwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 112M Oct 31  2014 ./thwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 176M Oct 31  2014 ./trwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 492M Nov 11  2014 ./zhwiki/titlekey.ibd

s3 is too massive, but this is an example:

root@db1075:/srv/sqldata# find . -name titlekey.ibd | xargs ls -lh | tail -n10
-rw-rw---- 1 mysql mysql 672K Nov 18  2015 ./zhwikibooks/titlekey.ibd
-rw-rw---- 1 mysql mysql 4.0M Nov 18  2015 ./zhwikinews/titlekey.ibd
-rw-rw---- 1 mysql mysql 528K Nov 18  2015 ./zhwikiquote/titlekey.ibd
-rw-rw---- 1 mysql mysql  16M Nov 18  2015 ./zhwikisource/titlekey.ibd
-rw-rw---- 1 mysql mysql 112K Nov 18  2015 ./zhwikivoyage/titlekey.ibd
-rw-rw---- 1 mysql mysql  92M Nov 18  2015 ./zhwiktionary/titlekey.ibd
-rw-rw---- 1 mysql mysql 8.0M Nov 18  2015 ./zh_yuewiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 112K Nov 18  2015 ./zuwikibooks/titlekey.ibd
-rw-rw---- 1 mysql mysql 320K Nov 18  2015 ./zuwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 208K Nov 18  2015 ./zuwiktionary/titlekey.ibd

s4:

root@db1068:/srv/sqldata# find . -name titlekey.ibd | xargs ls -lh
-rw-rw---- 1 mysql mysql 5.6G Apr  5 11:48 ./commonswiki/titlekey.ibd

s5:

root@db1063:/srv/sqldata# find . -name titlekey.ibd | xargs ls -lh
-rw-rw---- 1 mysql mysql  728M Nov 12  2014 ./dewiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 1008M Oct 31  2014 ./wikidatawiki/titlekey.ibd

s6:

root@db1061:/srv/sqldata# find . -name titlekey.ibd | xargs ls -lh
-rw-rw---- 1 mysql mysql 1012M Nov  5  2014 ./frwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql  428M Oct 31  2014 ./jawiki/titlekey.ibd
-rw-rw---- 1 mysql mysql  804M Oct 31  2014 ./ruwiki/titlekey.ibd

s7:

root@db1062:/srv/sqldata#  find . -name titlekey.ibd | xargs ls -lh
-rw-rw---- 1 mysql mysql 252M Oct 31  2014 ./arwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 168M Oct 31  2014 ./cawiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 720M Oct 31  2014 ./eswiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 436M Oct 31  2014 ./fawiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 404M Oct 31  2014 ./frwiktionary/titlekey.ibd
-rw-rw---- 1 mysql mysql 132M Oct 31  2014 ./hewiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 152M Oct 31  2014 ./huwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 148M Oct 31  2014 ./kowiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 592M Oct 31  2014 ./metawiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 168M Oct 31  2014 ./rowiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 316M Oct 31  2014 ./ukwiki/titlekey.ibd
-rw-rw---- 1 mysql mysql 392M Oct 31  2014 ./viwiki/titlekey.ibd

I have backup'ed all the tables under:

dbstore1001:/srv/tmp/T164949
root@dbstore1001:/srv/tmp/T164949# du -sh .
3.9G	.

Mentioned in SAL (#wikimedia-operations) [2017-06-14T09:46:54Z] <marostegui> Rename table titlekey before dropping it on enwiki - db1089 - T164949

Marostegui added a comment.EditedJun 14 2017, 9:48 AM

Before dropping the tables, I have renamed it on db1089 on enwiki to see if any issues arise:

root@db1089[enwiki]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

root@db1089[enwiki]> rename table titlekey to T164949_titlekey;
Query OK, 0 rows affected (0.00 sec)

root@db1089[enwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1089     |
+------------+
1 row in set (0.00 sec)

root@db1089[enwiki]> show tables like 'T%';
+-----------------------+
| Tables_in_enwiki (T%) |
+-----------------------+
| T164949_titlekey      |
+-----------------------+
1 row in set (0.01 sec)

I will leave it like that for a few days.

If this change needs to be reverted:

set session sql_log_bin=0; rename table T164949_titlekey to titlekey;
Marostegui moved this task from Backlog to In progress on the DBA board.
demon added a comment.Jun 14 2017, 5:37 PM

Awesome progress, thanks!

Mentioned in SAL (#wikimedia-operations) [2017-06-19T08:16:41Z] <marostegui> Drop table titlekey on s6 - T164949

Marostegui updated the task description. (Show Details)Jun 19 2017, 8:20 AM

Mentioned in SAL (#wikimedia-operations) [2017-06-19T08:35:22Z] <marostegui> Drop table title key from s2 - T164949

Marostegui updated the task description. (Show Details)Jun 19 2017, 8:40 AM

Mentioned in SAL (#wikimedia-operations) [2017-06-20T07:45:20Z] <marostegui> Drop table titlekey from s5 - T164949

Marostegui updated the task description. (Show Details)Jun 20 2017, 7:50 AM

Mentioned in SAL (#wikimedia-operations) [2017-06-20T08:06:00Z] <marostegui> Drop table titlekey from s7 - https://phabricator.wikimedia.org/T164949

Marostegui updated the task description. (Show Details)Jun 20 2017, 8:07 AM

Mentioned in SAL (#wikimedia-operations) [2017-06-20T08:15:27Z] <marostegui> Drop table titlekey from s4 - T164949

Marostegui updated the task description. (Show Details)Jun 20 2017, 8:19 AM

Mentioned in SAL (#wikimedia-operations) [2017-06-20T08:22:49Z] <marostegui> Drop table titlekey from s3 - T164949

Marostegui updated the task description. (Show Details)Jun 20 2017, 8:45 AM

Mentioned in SAL (#wikimedia-operations) [2017-06-20T08:46:04Z] <marostegui> Drop table titlekey from s1 - T164949

Marostegui closed this task as Resolved.Jun 20 2017, 8:51 AM
Marostegui updated the task description. (Show Details)