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; runpt-query-digest
ormysqldumpslow -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]
.