MariaDB – How to Fix MAD File Taking All Space on Hard Drive

mariadb

My disk is full, an .MAD file in tmpdir takes up all the space on the file system (50G out of 110G)

from log:

[Warning] mysqld: Disk is full writing '/var/tmp/#sql_b6f_0.MAD' (Errcode: 28). Waiting for someone to free space

I execute the service mysqld stop and it works but it didn't remove the file.
I execute the service mysqld start and fail because of the free sapace available.

It's safe to remove by hand the file?
How can I set a max size for mad files to avoid this problem?

Best Answer

Multiply the maximum number of rows your DBA thinks is practical for your joins by 10 and set that as max_join_size.

Set tmp_disk_table_size so that your file system does not exceed 90% full.

Tell whomever wrote the query in question about the limits you put in place to preserve availability. Have a cost benefit discussion of writing more efficient queries versus throwing memory and storage at the problem.

And further tune your temporary tables now that you know what they are called. From Stack Overflow: How can I limit the size of temporary tables?

Related Topic