MySQL – Fix Disk Full of .mad and .mai Files

myisamMySQL

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.

Best Answer

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.

UPDATE 2012-02-03 17:06 EDT

Since you were able to recover everything NOW DO THIS:

mysqldump -u... -p... -A -d --routines --triggers > C:\MySQLSchema.sql

This will give all table structures in the MySQL Instance.

Mysql – How to speed up a loading data into MySQL from a zipped archive

Please keep in mind the behavior a mysqldump

It dumps all databases in alphabetical order
It dumps all tables in each database in alphabetical order
It loads each tables like this
- Drops the table
- Creates the Table
- Table is Loaded
  1. Disables Keys
  2. Locks the Table for Writes
  3. Multiple Exteneded INSERTs
  4. Unlock the Table
  5. Enable Keys
- When the keys of table are disabled, only PRIMARY KEYs and UNIQUE KEYs are reloaded. When keys are enabled, then all non-unique keys are built linearly.

I would recommend you add this to /etc/my.cnf setting bulk_insert_buffer_size as follows

[mysqld]
bulk_insert_buffer_size=256M
key_buffer_size=1G

because the default is 8M. You do not need to restart mysql. Just run this SQL command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

Give it a Try !!!

Best Answer

Related Solutions

MySQL Table Structure – How to Recover from FRM Files

UPDATE 2012-02-03 17:06 EDT

Mysql – How to speed up a loading data into MySQL from a zipped archive

Related Question