Page MenuHomePhabricator

DROP OAI-related tables
Closed, ResolvedPublic

Description

There seems to be a large table per wiki: updates, and an oai database on s3. No longer in use: T70867

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

Event Timeline

jcrespo created this task.Jul 5 2016, 10:13 AM
Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptJul 5 2016, 10:13 AM
jcrespo moved this task from Triage to Backlog on the DBA board.Jul 11 2016, 2:37 PM
jcrespo moved this task from Backlog to In progress on the DBA board.Aug 12 2016, 2:08 PM
jcrespo raised the priority of this task from Medium to High.Aug 18 2016, 2:22 PM
jcrespo moved this task from In progress to Next on the DBA board.
jcrespo removed jcrespo as the assignee of this task.Oct 24 2016, 11:42 AM
jcrespo moved this task from Next to Backlog on the DBA board.

While checking for tables with PK I found this ticket and took a look at this table across all the masters, as it looks like it can be dropped and it is pretty big on some shards. It indeed looks unused as per: T54921 and the files are untouched for a year now.
Probably renaming it on one slave per shard before dropping to make sure nothing reads from it first is a good approach.

s1

-rw-rw---- 1 mysql mysql 6.5G Mar 15  2016 ./enwiki/updates.ibd

s2:

-rw-rw---- 1 mysql mysql  88M Mar 15  2016 ./bgwiki/updates.ibd
-rw-rw---- 1 mysql mysql 124M Mar 14  2016 ./bgwiktionary/updates.ibd
-rw-rw---- 1 mysql mysql 164M Mar 15  2016 ./cswiki/updates.ibd
-rw-rw---- 1 mysql mysql  32M Mar 15  2016 ./enwikiquote/updates.ibd
-rw-rw---- 1 mysql mysql 700M Mar 15  2016 ./enwiktionary/updates.ibd
-rw-rw---- 1 mysql mysql  80M Mar 15  2016 ./eowiki/updates.ibd
-rw-rw---- 1 mysql mysql 180M Mar 15  2016 ./fiwiki/updates.ibd
-rw-rw---- 1 mysql mysql 268M Mar 15  2016 ./idwiki/updates.ibd
-rw-rw---- 1 mysql mysql 756M Mar 15  2016 ./itwiki/updates.ibd
-rw-rw---- 1 mysql mysql 604M Mar 15  2016 ./nlwiki/updates.ibd
-rw-rw---- 1 mysql mysql 192M Mar 15  2016 ./nowiki/updates.ibd
-rw-rw---- 1 mysql mysql 488M Mar 15  2016 ./plwiki/updates.ibd
-rw-rw---- 1 mysql mysql 668M Mar 15  2016 ./ptwiki/updates.ibd
-rw-rw---- 1 mysql mysql 808M Mar 15  2016 ./svwiki/updates.ibd
-rw-rw---- 1 mysql mysql 112M Mar 15  2016 ./thwiki/updates.ibd
-rw-rw---- 1 mysql mysql 280M Mar 15  2016 ./trwiki/updates.ibd
-rw-rw---- 1 mysql mysql 696M Mar 15  2016 ./zhwiki/updates.ibd

s3 is too massive to post, but an example:

root@db1075:/srv/sqldata# find . -name updates.ibd | xargs ls -lh | tail -n10
-rw-rw---- 1 mysql mysql   10M Mar 14  2016 ./zhwikibooks/updates.ibd
-rw-rw---- 1 mysql mysql   12M Mar 14  2016 ./zhwikinews/updates.ibd
-rw-rw---- 1 mysql mysql   10M Mar 13  2016 ./zhwikiquote/updates.ibd
-rw-rw---- 1 mysql mysql   27M Mar 14  2016 ./zhwikisource/updates.ibd
-rw-rw---- 1 mysql mysql  960K Mar 14  2016 ./zhwikivoyage/updates.ibd
-rw-rw---- 1 mysql mysql  136M Mar 14  2016 ./zhwiktionary/updates.ibd
-rw-rw---- 1 mysql mysql   22M Mar 15  2016 ./zh_yuewiki/updates.ibd
-rw-rw---- 1 mysql mysql  256K Nov 18  2015 ./zuwikibooks/updates.ibd
-rw-rw---- 1 mysql mysql  736K Mar 14  2016 ./zuwiki/updates.ibd
-rw-rw---- 1 mysql mysql  432K Mar 11  2016 ./zuwiktionary/updates.ibd

s4:

-rw-rw---- 1 mysql mysql 6.3G Mar 15  2016 updates.ibd

s5:

-rw-rw---- 1 mysql mysql 1.3G Mar 15  2016 ./dewiki/updates.ibd
-rw-rw---- 1 mysql mysql 3.0G Mar 15  2016 ./wikidatawiki/updates.ibd

s6:

-rw-rw---- 1 mysql mysql 1.4G Mar 15  2016 ./frwiki/updates.ibd
-rw-rw---- 1 mysql mysql 468M Mar 15  2016 ./jawiki/updates.ibd
-rw-rw---- 1 mysql mysql 868M Mar 15  2016 ./ruwiki/updates.ibd

s7:

root@db1041:/srv/sqldata# find . -name updates.ibd | xargs ls -lh
-rw-rw---- 1 mysql mysql 412M Mar 15  2016 ./arwiki/updates.ibd
-rw-rw---- 1 mysql mysql 184M Mar 15  2016 ./cawiki/updates.ibd
-rw-rw---- 1 mysql mysql 972M Mar 15  2016 ./eswiki/updates.ibd
-rw-rw---- 1 mysql mysql 476M Mar 15  2016 ./fawiki/updates.ibd
-rw-rw---- 1 mysql mysql 408M Mar 15  2016 ./frwiktionary/updates.ibd
-rw-rw---- 1 mysql mysql 184M Mar 15  2016 ./hewiki/updates.ibd
-rw-rw---- 1 mysql mysql 188M Mar 15  2016 ./huwiki/updates.ibd
-rw-rw---- 1 mysql mysql 204M Mar 15  2016 ./kowiki/updates.ibd
-rw-rw---- 1 mysql mysql 1.2G Mar 15  2016 ./metawiki/updates.ibd
-rw-rw---- 1 mysql mysql 244M Mar 15  2016 ./rowiki/updates.ibd
-rw-rw---- 1 mysql mysql 284M Mar 15  2016 ./ukwiki/updates.ibd
-rw-rw---- 1 mysql mysql 436M Mar 15  2016 ./viwiki/updates.ibd

I have renamed updates table on enwiki

db1089: rename table updates to T139342_updates;

Will leave it like that for a week before starting to drop it (after taking a backup).

Mentioned in SAL (#wikimedia-operations) [2017-05-30T11:48:04Z] <marostegui> Rename update table on enwiki on db1089 host - T139342

In order to get ready for the deletion next week I have backuped all the tables across the shards and placed them at:

dbstore1001:/srv/tmp/T139342

Mentioned in SAL (#wikimedia-operations) [2017-06-07T12:43:19Z] <marostegui> Drop table updates on s2 - T139342

Marostegui updated the task description. (Show Details)Jun 7 2017, 3:08 PM

Mentioned in SAL (#wikimedia-operations) [2017-06-08T07:00:05Z] <marostegui> Drop table updates on s6 - T139342

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

Mentioned in SAL (#wikimedia-operations) [2017-06-08T11:41:23Z] <marostegui> Drop table updates on s7 - T139342

Marostegui updated the task description. (Show Details)Jun 8 2017, 1:15 PM

Mentioned in SAL (#wikimedia-operations) [2017-06-12T09:12:55Z] <marostegui> Drop table updates on dewiki and wikidatawiki (s5) - T139342

Marostegui updated the task description. (Show Details)Jun 12 2017, 9:30 AM

Mentioned in SAL (#wikimedia-operations) [2017-06-12T10:59:56Z] <marostegui> Drop table updates on commonswiki (s4) - T139342

Marostegui updated the task description. (Show Details)Jun 12 2017, 11:05 AM
Marostegui moved this task from Backlog to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2017-06-13T07:47:59Z] <marostegui> Drop table updates on enwiki (s1) - T139342

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

Mentioned in SAL (#wikimedia-operations) [2017-06-14T07:59:20Z] <marostegui> Drop table updates on s3 - T139342

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

All shards done.

We have to check labsdb hosts- some views may be there pointing to updates tables or mark_as_helpful dropped tables.