MySQL Temp Files – How to Remove Temp Files After MySQL Copy Temp Table Filled Disk

MySQLmysql5.5

What is the safest way to clean up?

MySQL server 5.5.62-0 on Debian 8 with no replication.

I made a mistake and created a new column on a 26GB table. SHOW PROCESSLIST showed MySQL was copying the data to a tmp table at 100% CPU.

+-----------+------+-----------+--------+---------+------+-------------------+------------------+
| Id        | User | Host      | db     | Command | Time | State             | Info             |
+-----------+------+-----------+--------+---------+------+-------------------+------------------+
| 145904211 | root | localhost | huge   | Query   |  160 | copy to tmp table | ALTER TABLE ...  |
| 145905739 | root | localhost | NULL   | Query   |    0 | NULL              | show processlist |
+-----------+------+-----------+--------+---------+------+-------------------+------------------+

A few minutes later the main partition was full and CPU dropped to 0. I used systemctl stop mysql hoping it would clean up temporary files. The service wouldn't restart either.

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root        79G   75G 1000K 100% /

$ sudo systemctl start mysql
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

I shut down the VPS and expanded the disk. The server restarted fine and I was able to start the MySQL process and connect to it. Everything seems to be operating.

However disk usage has not reduced since the incident 20 minutes ago.

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       158G   75G   75G  50% /

Will housekeeping kick in or should I manually clean up the mess I made? What is the safest way to do that?

Best Answer

First, from the mysql commandline tool:

kill 145904211;

That should clean things up. If not, the look around for files starting something like #sql.... One of them will be huge with a timestamp of when the ALTER was running. Simply delete it.

For safety's sake an ALTER, at least in 5.5 days, worked like this:

  1. Create a new, empty, table like the existing table.
  2. Change the schema (add a column, in your case)
  3. Copy all the data from the existing table to the new one. (The slow part)
  4. Do some table renames.
  5. Drop the old table.

You are probably hung in the middle of step 3.

The only risky time is in step 4, which is very fast. Before that, the old table is still alive and well. After that, the new table has replaced it.

Related Topic