6

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?

Guillermo
  • 63
  • 1
  • 1
  • 3
  • It is not safe to remove those files - and as a general rule, any file - from MySQL data directories. Now assuming /var/tmp is not your data directory (by default: /var/lib/mysql), it could be an option, bearing in mind they'ld come back eventually. Better options would include expanding your filesystem, migrating to some larger server, or figuring out how to shard your dataset, ... Good luck. – SYN May 15 '19 at 15:59
  • What were you in the middle of doing? Perhaps an `ALTER TABLE`? And let's see `SHOW CREATE TABLE`. (A .MAD file is data for Aria; #sql... is a temp table.) – Rick James May 16 '19 at 01:34
  • It was a select but with a couple of join in large tables. – Guillermo May 17 '19 at 11:46
  • Kill the query and it will clean-up the temporary file for you. – Christopher Schultz Sep 30 '22 at 14:02

2 Answers2

4

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?

John Mahowald
  • 32,050
  • 2
  • 19
  • 34
  • When this happened to me, the queries sat there waiting to be able to write more data to the temp file. Using `SHOW PROCESSLIST` to identify the long-running, dead queries, I was able to get things back up and running by simply doing a `KILL QUERY [id]` for each of them. After a short interval, the queries disappeared from the process list and the files disappeared from my *tmpdir*. That allowed everything else to resume where it left off. No need to restart the server process or anything like that. – Christopher Schultz Dec 11 '20 at 01:22
3

That file is a temporary table that MariaDB wrote to disk because it was too large to keep in memory. If you've stopped the database, then the query which caused the table to be created is no longer running, so it is safe to delete the file. But you also need to investigate why that file was created and fix the underlying problem, or it will happen again.

Michael Hampton
  • 244,070
  • 43
  • 506
  • 972