Mysql filling up disk space, but storage not even on this disk

Question

Have Mysql running on a server, with data, backups and logs each on their own disk.

For 2nd time recently the root disk has filled up to 100% which gets cleared the moment that MySQL restarts. This suggests that some large temporary files, or deleted files still locked by the mysql process.

Any idea what they could be? Strange since majority if not all of mysql file writing should be onto the other disks which aren't full.

root@ip-172-31-10-88:~# df -h
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           3.2G  1.1M  3.2G   1% /run
/dev/xvda1       30G   29G     0 100% /
tmpfs            16G     0   16G   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
/dev/xvdg       148G   82G   59G  59% /var/lib/mysql
/dev/xvdc       492G  185G  283G  40% /var/lib/automysqlbackup
/dev/xvdj1       50G  1.8G   45G   4% /var/log/mysql
tmpfs           3.2G  4.0K  3.2G   1% /run/user/999
tmpfs           3.2G  4.0K  3.2G   1% /run/user/1000

Then restarted Mysql

root@ip-172-31-10-88:~# /etc/init.d/mysql restart
Restarting mysql (via systemctl): mysql.service.

Space on disk now freed up

root@ip-172-31-10-88:~# df -h
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           3.2G  1.1M  3.2G   1% /run
/dev/xvda1       30G  4.4G   24G  16% /
tmpfs            16G     0   16G   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
/dev/xvdg       148G   82G   59G  59% /var/lib/mysql
/dev/xvdc       492G  185G  283G  40% /var/lib/automysqlbackup
/dev/xvdj1       50G  1.8G   45G   4% /var/log/mysql
tmpfs           3.2G  4.0K  3.2G   1% /run/user/999
tmpfs           3.2G  4.0K  3.2G   1% /run/user/1000

mysql Ver 15.1 Distrib 10.6.16-MariaDB, for debian-linux-gnu

Answer 1

SHOW VARIABLES LIKE '%tmpdir%';

I suspect you will see "/tmp" in some of those. This means that complex queries against big tables are probably filling up the "tmpdir". Since you have about 24G available, it will be quite a long-running SQL. If you don't know which query it is, you can probably do SHOW FULL PROCESSLIST; when it is likely to happen. Alas the SlowLog won't record it until the query finishes. But since things are apparently crashing, it won't "finish".

Once you have found the naught query, show us SHOW CREATE TABLE and the query. There may be a way to rewrite the query and/or add better indexes to avoid such excessive use of tmpdir.

Moving the appropriate tmpdir [I don't know which one] to one of your more-empty disks would be another 'solution', but that might simply delay the problem. I prefer fixing the query/indexes instead.

(dba.stackexchange.com is a better site for MySQL/MariaDB DBA questions like this.)