Optimize flaggedtemplates tables in production
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Marostegui
	Aug 31 2021, 7:41 AM

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:

P17106 (An Untitled Masterwork)

1	host=db1168
2	Before:
3	-rw-rw---- 1 mysql mysql 168G Aug 30 09:03 flaggedtemplates.ibd
4
5	After:
6	root@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):

codfw:

Related Objects
Search...

Status	Assigned	Task
Open	None	T31744 FlaggedRev installation (deployment) requests (tracking)
Stalled	None	T143886 Activating Flagged revisions on ar.wikinews
Stalled	None	T204354 Flagged Revisions for Vietnamese Wikipedia
Stalled	None	T205145 Deploy FlaggedRevs on bn.wikibooks
Stalled	None	T221933 Enable Flagged Revisions (for trial run purpose) at the Chinese Wikipedia
Open	None	T185664 Code stewardship review: FlaggedRevs
Resolved	Ladsgroup	T277883 Drop all low-use and unused features of FlaggedRevs to make it more maintainable
Resolved	Ladsgroup	T289249 flaggedtemplates table should not keep the whole history of all revisions
		Restricted Task
Resolved	Legoktm	T281515 June 2021 Datacenter switchover
Resolved	Marostegui	T285139 DB maintenance work to do while eqiad is passive (June 2021)
Resolved	Marostegui	T290057 Optimize flaggedtemplates tables in production

Event Timeline

Marostegui created this task.Aug 31 2021, 7:41 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 31 2021, 7:41 AM

Marostegui triaged this task as Medium priority.Aug 31 2021, 7:41 AM

Marostegui moved this task from Triage to In progress on the DBA board.

Marostegui added a parent task: T285139: DB maintenance work to do while eqiad is passive (June 2021).

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)

Edited, thanks!

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

Marostegui updated the task description. (Show Details)Aug 31 2021, 8:17 AM

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

Marostegui updated the task description. (Show Details)Aug 31 2021, 8:23 AM

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

Marostegui updated the task description. (Show Details)Aug 31 2021, 9:16 AM

Marostegui updated the task description. (Show Details)Aug 31 2021, 10:11 AM

Marostegui updated the task description. (Show Details)

Marostegui updated the task description. (Show Details)Aug 31 2021, 10:14 AM

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

Marostegui updated the task description. (Show Details)Aug 31 2021, 10:29 AM

Marostegui updated the task description. (Show Details)

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

Marostegui updated the task description. (Show Details)Sep 1 2021, 4:40 AM

Restricted Application added a subscriber: Base. · View Herald TranscriptSep 1 2021, 4:40 AM

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

Marostegui updated the task description. (Show Details)Sep 1 2021, 4:42 AM

Marostegui updated the task description. (Show Details)Sep 1 2021, 4:51 AM

In T290057#7323623, @Marostegui wrote:
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 updated the task description. (Show Details)Sep 1 2021, 5:29 AM

Marostegui renamed this task from Optimize flaggedtemplates tables in production. to Optimize flaggedtemplates tables in production.Sep 3 2021, 11:46 AM

Marostegui updated the task description. (Show Details)

Marostegui updated the task description. (Show Details)Sep 3 2021, 11:49 AM

Marostegui updated the task description. (Show Details)

Marostegui updated the task description. (Show Details)Sep 3 2021, 11:51 AM

Marostegui updated the task description. (Show Details)Sep 3 2021, 11:56 AM

Marostegui updated the task description. (Show Details)Sep 3 2021, 12:00 PM

Marostegui updated the task description. (Show Details)Sep 3 2021, 12:06 PM

Marostegui updated the task description. (Show Details)Sep 3 2021, 12:16 PM

dewiki is done.

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

Marostegui updated the task description. (Show Details)Sep 6 2021, 6:28 AM

Marostegui updated the task description. (Show Details)Sep 6 2021, 6:31 AM

In T290057#7333666, @Marostegui wrote:
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

Marostegui updated the task description. (Show Details)Sep 6 2021, 7:23 AM

https://grafana.wikimedia.org/d/000000377/host-overview?viewPanel=28&from=1630878699080&orgId=1&to=1630944052734&var-server=db1130&var-datasource=eqiad%20prometheus%2Fops&var-cluster=mysql

In T290057#7335018, @Ladsgroup wrote:

https://grafana.wikimedia.org/d/000000377/host-overview?viewPanel=28&from=1630878699080&orgId=1&to=1630944052734&var-server=db1130&var-datasource=eqiad%20prometheus%2Fops&var-cluster=mysql

Not to mention the fact that is now fits in the buffer pool :)

Marostegui updated the task description. (Show Details)Sep 7 2021, 5:11 AM

Marostegui updated the task description. (Show Details)Sep 7 2021, 5:14 AM

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

Marostegui updated the task description. (Show Details)Sep 7 2021, 5:15 AM

Marostegui updated the task description. (Show Details)Sep 7 2021, 5:33 AM

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:

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

Marostegui updated the task description. (Show Details)Sep 16 2021, 9:56 AM

Marostegui updated the task description. (Show Details)Sep 16 2021, 11:17 AM

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