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