Page MenuHomePhabricator

Optimize flaggedtemplates tables in production
Open, MediumPublic

Description

The following wikis went for a clean up, and need to get their flaggedtemplates tables optimized as we can reclaim lots of disk space, for instance in s6:

1host=db1168
2Before:
3-rw-rw---- 1 mysql mysql 168G Aug 30 09:03 flaggedtemplates.ibd
4
5After:
6root@db1168:/srv/sqldata/ruwiki# ls -lh flaggedtemplates.ibd
7-rw-rw---- 1 mysql mysql 84G Aug 30 09:37 flaggedtemplates.ibd

See T289249#7311985 to check which wikis can be optimized:

eqiad (masters are done):

  • idwiki (s2)
  • trwiki (s2)
  • plwiki (s2)
  • eowiki (s2)
  • fiwiki (s2)
  • cewiki (s3)
  • plwiktionary (s3)
  • dewiktionary (s3)
  • ruwikinews (s3)
  • ruwiktionary (s3)
  • ruwikisource (s3)
  • kawiki (s3)
  • vecwiki (s3)
  • bewiki (s3)
  • mkwiki (s3)
  • dewiki (s5)
  • ruwiki (s6)
  • arwiki (s7)
  • huwiki (s7)
  • ukwiki (s7)

codfw:

  • idwiki (s2)
  • trwiki (s2)
  • plwiki (s2)
  • eowiki (s2)
  • fiwiki (s2)
  • cewiki (s3)
  • plwiktionary (s3)
  • dewiktionary (s3)
  • ruwikinews (s3)
  • ruwiktionary (s3)
  • ruwikisource (s3)
  • kawiki (s3)
  • vecwiki (s3)
  • bewiki (s3)
  • mkwiki (s3)
  • dewiki (s5)
  • ruwiki (s6)
  • arwiki (s7)
  • huwiki (s7)
  • ukwiki (s7)

Event Timeline

We should at least try to optimize the eqiad tables and then once we've switched back to codfw, do those.
So far, the only wikis that can be done are the ones listed as done at: T289249#7311985

  • huwiki
  • ruwiki
  • cewiki
  • plwiki
  • plwiktionary

Mentioned in SAL (#wikimedia-operations) [2021-08-31T07:44:43Z] <marostegui> Optimize ruwiki.flaggedtemplates T290057

plwiktionary is also done (when I was asleep)

Mentioned in SAL (#wikimedia-operations) [2021-08-31T08:05:09Z] <marostegui> Optimize plwiktionary.flaggedtemplates T290057

Mentioned in SAL (#wikimedia-operations) [2021-08-31T08:18:13Z] <marostegui> Optimize cewiki.flaggedtemplates T290057

Mentioned in SAL (#wikimedia-operations) [2021-08-31T08:39:40Z] <marostegui> Optimize plwiki.flaggedtemplates T290057

Mentioned in SAL (#wikimedia-operations) [2021-08-31T10:14:51Z] <marostegui> Optimize huwiki.flaggedtemplates T290057

arwiki and idwiki are done, started dewiktionary and ukwiki

Mentioned in SAL (#wikimedia-operations) [2021-09-01T04:23:00Z] <marostegui> Optimize arwiki.flaggedtemplates T290057

Started arwiki, this is the status before the optimize:

root@db1136:/srv/sqldata/arwiki# ls -lh flaggedtemplates.ibd
-rw-rw---- 1 mysql mysql 177G Sep  1 04:21 flaggedtemplates.ibd
Restricted Application added a subscriber: Base. · View Herald TranscriptWed, Sep 1, 4:40 AM

Mentioned in SAL (#wikimedia-operations) [2021-09-01T04:41:11Z] <marostegui> Optimize idwiki.flaggedtemplates T290057

Started arwiki, this is the status before the optimize:

root@db1136:/srv/sqldata/arwiki# ls -lh flaggedtemplates.ibd
-rw-rw---- 1 mysql mysql 177G Sep  1 04:21 flaggedtemplates.ibd
root@db1136.eqiad.wmnet[arwiki]> optimize table flaggedtemplates;
+-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| arwiki.flaggedtemplates | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| arwiki.flaggedtemplates | optimize | status   | OK                                                                |
+-------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (40 min 43.880 sec)
root@db1136:/srv/sqldata/arwiki# ls -lh flaggedtemplates.ibd
-rw-rw---- 1 mysql mysql 118G Sep  1 05:15 flaggedtemplates.ibd
Marostegui renamed this task from Optimize flaggedtemplates tables in production. to Optimize flaggedtemplates tables in production.Fri, Sep 3, 11:46 AM
Marostegui updated the task description. (Show Details)

great! I will get it done today!

Mentioned in SAL (#wikimedia-operations) [2021-09-06T06:23:57Z] <marostegui> Optimize table dewiki.flaggedtemplates in eqiad T290057

dewiki size before the optimize:

-rw-rw---- 1 mysql mysql 106G Sep  6 06:24 flaggedtemplates.ibd

Mentioned in SAL (#wikimedia-operations) [2021-09-06T06:26:47Z] <marostegui> Optimize table bewiki.flaggedtemplates in eqiad T290057

Mentioned in SAL (#wikimedia-operations) [2021-09-06T06:28:15Z] <marostegui> Optimize table mkwiki.flaggedtemplates in eqiad T290057

dewiki size before the optimize:

-rw-rw---- 1 mysql mysql 106G Sep  6 06:24 flaggedtemplates.ibd

And after:

-rw-rw---- 1 mysql mysql 53G Sep  6 07:08 flaggedtemplates.ibd

Mentioned in SAL (#wikimedia-operations) [2021-09-07T05:14:55Z] <marostegui> Optimize kawiki.flaggedtemplates in eqiad T290057

Mentioned in SAL (#wikimedia-operations) [2021-09-07T05:15:20Z] <marostegui> Optimize vecwiki.flaggedtemplates in eqiad T290057

Mentioned in SAL (#wikimedia-operations) [2021-09-07T05:15:26Z] <marostegui> Optimize eowiki.flaggedtemplates in eqiad T290057

This is affected by https://jira.mariadb.org/browse/MDEV-26618 so I have double checked ruwiki and it is definitely affected, so it needs to be re-done for all eqiad slaves as it wasn't replicated

s6 slave:

root@db1180:~# ls -lh  /srv/sqldata/ruwiki/flaggedtemplates.ibd
-rw-rw---- 1 mysql mysql 168G Sep 16 09:49 /srv/sqldata/ruwiki/flaggedtemplates.ibd

s6 master:

root@db1173:~# ls -lh /srv/sqldata/ruwiki/flaggedtemplates.ibd
-rw-rw---- 1 mysql mysql 85G Sep 16 09:50 /srv/sqldata/ruwiki/flaggedtemplates.ibd

I am assuming that the rest of the wikis are also affected, at least:

  1. this is a change that can be done online (in most cases)
  2. the eqiad masters are done
  3. codfw can be done entirely by using alter table instead (which seems to get replicated just fine)

Mentioned in SAL (#wikimedia-operations) [2021-09-16T12:08:34Z] <marostegui> Deploy schema change on s2 codfw (lag will show up) T290057

s2 eqiad:

  • dbstore1007
  • db1182
  • db1170
  • db1162
  • db1156
  • db1155
  • db1146
  • db1129
  • db1105
  • db1102
  • clouddb1021
  • clouddb1018
  • clouddb1014

s3 eqiad:

  • dbstore1007
  • db1179
  • db1175
  • db1166
  • db1154
  • db1123
  • db1112
  • db1102
  • clouddb1021
  • clouddb1017
  • clouddb1013

s5 eqiad

  • dbstore1003
  • db1161
  • db1154
  • db1150
  • db1144
  • db1113
  • db1110
  • db1100
  • db1096
  • clouddb1021
  • clouddb1020
  • clouddb1016

s6 eqiad

  • dbstore1005
  • db1180
  • db1168
  • db1165
  • db1155
  • db1140
  • db1131
  • db1113
  • db1098
  • db1096
  • clouddb1021
  • clouddb1019
  • clouddb1015

s7 eqiad

  • dbstore1003
  • db1181
  • db1174
  • db1171
  • db1170
  • db1158
  • db1155
  • db1127
  • db1101
  • db1098
  • clouddb1021
  • clouddb1018
  • clouddb1014