Defragment db1044
Closed, ResolvedPublic

Description

db1044 (which is depooled already and it is the master of db1095) is reaching around 90% of /srv/ usage
In order to avoid it paging during the holidays period let's try to defragment some of the big tables across all the wikis to give us back some space.

I will start with pagelinks which gave good results on db1015 (T153739)

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 21 2016, 7:54 AM

Mentioned in SAL (#wikimedia-operations) [2016-12-21T08:01:40Z] <marostegui> Running optimize table on db1044 for the pagelinks tables as we urgently need some space back on that host - T153826

Thanks! - I thought just about defragmenting a couple of tables across the board to give us enough space just for the holidays given that we cannot depool slaves during this week.
But this is indeed the long/medium solution until they get decommissioned.

The big ones (bigger than 1G) finished and now running it over the smaller ones.

root@db1044:~# df -hT /srv/
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sda6      xfs   1.7T  1.5T  229G  87% /srv

This should be enough for the holidays:

root@db1044:/srv/sqldata# df -hT /srv/
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sda6      xfs   1.7T  1.4T  252G  85% /srv
marostegui@db1044:~$ df -hT /srv/
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sda6      xfs   1.7T  1.5T  147G  92% /srv

Going to run a full optimize over this host to avoid it paging during the summit and then we can decide what to do with it (this is the temporary master for db1095 as of today)

Going to start with this (not with cebwiki/templatelinks yet, as there is not currently enough disk space to alter that table):

root@db1044:/srv/sqldata# find -type f -exec du -Sh {} + | sort -rh | head -n 5
207G	./cebwiki/templatelinks.ibd
63G	./enwikivoyage/text.ibd
21G	./shwiki/logging.ibd
18G	./cebwiki/externallinks.ibd
17G	./shwiki/pagelinks.ibd

Mentioned in SAL (#wikimedia-operations) [2017-01-03T07:54:00Z] <marostegui> Run optimize table on a few large tables - db1044 - T153826

I am compressing now the biggest tables on db1044 so we can get space.
Reminder, this server is currently acting as master for db1095 (sanitarium2)

I am leaving a few of the biggest tables on the host getting compressed over the night. The server is depooled anyways (I silenced it anyways on Icinga just in case).

root@db1044:/srv/sqldata# find -type f -exec du -Sh {} + | sort -rh | head -n 10
208G	./cebwiki/templatelinks.ibd
63G	./enwikivoyage/text.ibd
18G	./cebwiki/externallinks.ibd
17G	./shwiki/pagelinks.ibd
14G	./srwiki/pagelinks.ibd
14G	./shwiki/revision.ibd
9.7G	./shwiki/logging.ibd
8.8G	./cebwiki/pagelinks.ibd
8.2G	./loginwiki/logging.ibd
8.1G	./plwiktionary/flaggedtemplates.ibd

Note: cebwiki/templatelinks is excluded on this first loop.

Mentioned in SAL (#wikimedia-operations) [2017-01-04T07:24:15Z] <marostegui> Compressing more tables on db1044 - T153826

Mentioned in SAL (#wikimedia-operations) [2017-01-05T07:09:11Z] <marostegui> Compressing pagelinks tables across all the wikis - db1044 - T153826

Mentioned in SAL (#wikimedia-operations) [2017-01-16T10:35:37Z] <marostegui> Compressing templatelinks tables on db1044 (depooled) - T153826

I have started to compress templatelinks tables, including this one:

223G	./cebwiki/templatelinks.ibd

I have started to compress templatelinks tables, including this one:

223G	./cebwiki/templatelinks.ibd

This was a nice compression:

root@db1044:~# ls -lh /srv/sqldata/cebwiki/templatelinks.ibd
-rw-rw---- 1 mysql mysql 59G Jan 17 06:45 /srv/sqldata/cebwiki/templatelinks.ibd

The following tables have been compressed across all the wikis:

revision
templatelinks
pagelinks

This is the situation with this host now:

root@db1044:/srv/sqldata# df -hT /srv/
Filesystem     Type  Size  Used Avail Use% Mounted on
/dev/sda6      xfs   1.7T  1.1T  599G  65% /srv

Going to close this ticket for now

Marostegui closed this task as Resolved.Jan 18 2017, 8:22 AM
Marostegui triaged this task as High priority.

Mentioned in SAL (#wikimedia-operations) [2017-01-19T07:18:16Z] <marostegui> Compressing enwikivoyage.text and shwiki.logging tables on db1044 - T153826