Disk full of mysql .mad and .mai files

Question

On a (owned) shared server sometimes mysql writes alott of .mad and .mai. This happens since i think 2 or 3 months, completely random. It may happen 3 times a day, then it does not happen for a couple of weeks and then on a monday morning the issue occurs again.

It writes alott of files quickly. The disk is full very fast. I changed the tmp folder to a different location so it could write more files. The location it writes now is 16gb and also gets full.

I found this issue on multiple locations and tried some solutions. I tried to limit tmp files that could be written with tmp_disk_table_size and tmp_table_size but this did not seem to help.

I also tried to enter internal_tmp_disk_storage_engine=MyISAM in my my.cnf but then mysql would not start. I did this because i googled for a solution to this error in error logs:

Cannot add field images in table database_name.`jospr_tags` because after adding it, the row size is 8769 which is greater than maximum allowed size (8126) for a record on index leaf page.

The problems could maybe stop if all database where changed to innodb, but i cannot do that easily. These are not my databases.

I have alerting on the server, so when the disk is almost full i get a message. When i quickly login, the disk is already full and checking the processlist in mysql does not show a query, problably because it stops because the disk is full.

[ERROR] mysqld: Disk full (/mysqltmp/#sql_3d1b_3.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

At the moment i am unsure what would help or what i can do to find any cause. All files that are written are owned by mysql. When i try to tail a file it only says mysql and when i delete a file the process stops and all files are gone.

The only solution i see at the moment is mounting an additional large disk so it can write longer so we can find the cause. However it could take weeks before this issue occurs again and i really would like to solve this quicker or more efficient.

I really hope some people have more information for me what could cause this and ofcourse any solutions.

BTW: All site using MyIsam seem to be Joomla (3.x) sites.

Answer 1

Files like #sql_* are temp files used by things like ALTER TABLE. When successfully finished they are removed. So, I conclude that and ALTER (or some other admin query) was rudely aborted (such as with a crash).

They can safely be deleted.

You 'should' switch to InnoDB.

Answer 2

To show the engines which can be used by MySQL:

show engines;

To list all tables, and the engine they use:

select 
TABLE_SCHEMA, TABLE_NAME, ENGINE 
from information_schema.tables

These settings for storage engines should also be noted:

show variables like '%storage_engine%';

Answer 3

When you run certain ALTER or CREATE commands on an InnoDB table, a temporary table is created in mysql's tmpdir during the operation