0

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.

0

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.

| improve this answer | |
  • It seems when installing Joomla 'something' chooses MyIsam by default. I am currently looking into that. I will contact owners to see ff we can change them. It's just not an easy or quick solution. i would really like to find the user causing this. – JeFFeR Dec 9 '19 at 19:06
  • It may be set in my.cnf -- default_storage_engine. – Rick James Dec 9 '19 at 19:15
  • That is a very good tip tbh. I checked this and it commented. So it chooses the defaut option. This is problably MyIsam. I will test this today to be sure. It's not solving the existing problem but prevents more problems in the future. Great tip, thanks alott! – JeFFeR Dec 10 '19 at 12:23
  • @JeFFeR - Existing tables can be converted to InnoDB by using `ALTER TABLE ... ENGINE=InnoDB'. More notes on the conversion: mysql.rjweb.org/doc.php/myisam2innodb – Rick James Dec 10 '19 at 18:25
  • This sounds great but is it really this simple or may i expect lots of errors, broken databases / tables etc? I haven't done ths before and ofcourse will try 1 first before doing all of them but at this point i am unsure what to expect when i do this. It would be great if you (or someone else ofcourse) can tell me what errors or issues i can expect. Thanks so much already :) – JeFFeR Dec 10 '19 at 22:28
0

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%';
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.