When looking at the backup sizes (logical backup, compressed) I get:
root@dbstore1001:/srv/backups$ ls -lhaSr ... -rw-r----- 1 root root 34G Jun 14 03:16 wikidatawiki-201606131835.sql.gz -rw-r----- 1 root root 34G Jun 15 10:54 wikidatawiki-201606150205.sql.gz -rw-r----- 1 root root 38G Jun 15 03:59 dewiki-201606150205.sql.gz -rw-r----- 1 root root 39G Jun 22 03:55 dewiki-201606220205.sql.gz -rw-r----- 1 root root 39G Jun 29 03:45 dewiki-201606290205.sql.gz -rw-r----- 1 root root 62G Jun 15 05:43 commonswiki-201606150205.sql.gz -rw-r----- 1 root root 62G Jun 22 05:43 commonswiki-201606220205.sql.gz -rw-r----- 1 root root 62G Jun 29 05:27 commonswiki-201606290205.sql.gz -rw-r----- 1 root root 83G Jun 15 06:50 enwiki-201606150205.sql.gz -rw-r----- 1 root root 83G Jun 22 06:47 enwiki-201606220205.sql.gz -rw-r----- 1 root root 83G Jun 29 06:31 enwiki-201606290205.sql.gz -rw-r--r-- 1 root root 257G Jun 15 04:24 m2-otrs-20160615010002.sql.gz -rw-r--r-- 1 root root 259G Jun 22 04:46 m2-otrs-20160622010001.sql.gz -rw-r--r-- 1 root root 259G Jun 29 04:41 m2-otrs-20160629010002.sql.gz
OTRS is 259GB after compression! In comparison, a full enwiki copy is only 83GB! And it can grow 2 GB in a week.
After investigating, it seems that the main responsible tables for that are:
root@db1020:/srv/sqldata/otrs$ ls -lhaSr ... -rw-rw---- 1 mysql mysql 192M Jun 29 03:11 dynamic_field_value.ibd -rw-rw---- 1 mysql mysql 248M Jun 29 07:50 article_flag.ibd -rw-rw---- 1 mysql mysql 980M Jun 29 07:50 ticket.ibd -rw-rw---- 1 mysql mysql 4.3G Jun 29 07:50 article_search.ibd -rw-rw---- 1 mysql mysql 5.4G Jun 29 07:50 ticket_history.ibd -rw-rw---- 1 mysql mysql 9.2G Jun 29 07:50 article.ibd -rw-rw---- 1 mysql mysql 143G Jun 29 07:50 article_attachment.ibd -rw-rw---- 1 mysql mysql 151G Jun 29 07:50 article_plain.ibd
dbstore1001 is now at <5% available space, so this is creating actual issues. Also, this host is only for creating the tarballs- in the backups this may be wasting a lot of space.
Maybe a more efficient strategy can be created for OTRS structure and backups? In particular, storing attachments on the database seems like a bad idea, but we should be careful if we try to change that, so that not millions of small files are created making thing worse.
The main problem, rather than space, is that recovering a single 150GB-table from a dump may take weeks, which would make the backups rather useless.