Mysql – MariaDB uses all /tmp space

disk-spacemariadbMySQL

I'm running a Galera cluster with MariaDB 10.

One of my nodes reports 100% usage disk for the /tmp table.

   /dev/mapper/vg0-LV--Tmp      1,8G    1,7G  228K 100% /tmp

I can't see files in /tmp with the ls command, but lsof reports:

mysqld     1236            mysql   14u      REG              254,5           0         17 /tmp/ibrpksHg (deleted)
mysqld     1236            mysql   15u      REG              254,5           0         19 /tmp/iboJmq1H (deleted)
mysqld     1236            mysql   16u      REG              254,5           0         20 /tmp/ibJLXol9 (deleted)
mysqld     1236            mysql   17u      REG              254,5           0         21 /tmp/ibKM4Kq5 (deleted)
mysqld     1236            mysql   21u      REG              254,5           0         22 /tmp/ibFyx8KA (deleted)

If I restart the node, the space is released, but the issue comes back after some time.

I don't understand what's happening here.

Any idea?

Best Answer

SHOW VARIABLES LIKE 'tmpdir'; -- You could change that (and restart) to immediately workaround the problem. But that is not a long-term solution.

Complex queries with inadequate indexes are likely to be the cause. Set long_query_time=1 and turn on the slowlog; wait a day; run pt-query-digest or mysqldumpslow -s t to see the 'worst' queries. Focus on them.

If you don't see how to improve a query, post a question with EXPLAIN SELECT..., SHOW CREATE TABLE and a tag it [mysql] [query-optimization].