If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql
is on the same mount and /root
.
Here is what you can do to start investigating: Go to the OS and do this:
cd /var/lib/mysql
df -h .
If the root partition is 100% despite dropping 4GB,
- your datadir is not
var/lib/mysql
- another table consumed up the space quickly
- something else clogged the root partition
Who are the usual suspects ?
- Binary Logs : You cannot run
PURGE BINARY LOGS
with a 100% full disk. You will have to locate the folder with the binary logs, delete the oldest ones, and mysqld will get moving.
- Error Log : Just a text file. Suppose your error log is
/var/log/mysqld.log
. You run echo -n > /var/log/mysqld.log
and the space from that file is reclaimed. I don't use rm
since mysqld has an open file handle to the error log.
- Slow Log : Locate the Slow Log and do the same thing as you did with the Error Log
- Temp Tables : These are usually created in
/tmp
. If you see .MYD
file in /tmp
with old timestamps, you can manually delete them.
Whatever it is, find the offending files and delete/truncate them because mysqld
will not crash on Error 28 (Out of Disk Space)
. The mysqld
will pause and wait patiently for diskspace to be available (See my post "Site Offline" MySQL server failing to start and stop).
This is a good question. You have several solutions but your table is quite big so none will be without pain :)
You have three solutions to "shrink" InnoDB tables:
1. OPTIMIZE TABLE
You can use OPTIMIZE TABLE
as you mentionned it but you should care about the innodb_file_per_table
variable :
mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
Let me explain:
The OPTIMIZE TABLE
with InnoDB tables, locks the table, copies the data in a new clean table (that's why the result is smaller), drops the original table and renames the new table with the original name. That why you should make sure to have twice the space of the original table available on your disk (You'll probably need less, since the optimized table will be smaller, but it's better to be safe than sorry).
innodb_file_per_table = ON
: In this mode, all tables have their own data file. The OPTIMIZE
statement will then create a new data file with optimized space usage. When the operation is finished, MySQL will drop the original one and replace it with the optimized version (so at the end the 700GB -- probably less because it will be optimized -- of data generated during the operation will be released)
innodb_file_per_table = OFF
: In this mode, all data is contained in one data file: ibdata. This mode has a big drawback since it cannot be optimized. So during the OPTIMIZE
process, your new table will be created (near 700GB), but even after the drop and renaming operation (and the end of OPTIMIZE
phase) your ibdata will not released the ~700GB, so you wanted to free some data, instead you have 700GB more, cool isn't it?
2. ALTER TABLE
You can also use an ALTER TABLE
statement, the ALTER TABLE
will work in the same way as OPTIMIZE TABLE
. You can just use:
ALTER TABLE myTable ENGINE=InnoDB;
3. ALTER TABLE (ONLINE)
The problem of OPTIMIZE
and ALTER TABLE
is, that it locks the table during operation. You can use the Percona tool : pt-online-schema-change (from Percona Toolkit : link
). pt-online-schema... provide mechanisms to optimize the table, while keeping the original table available for read and writes. I use this tool in production for ALTER
statements on big tables and it's pretty cool.
Note that any FOREIGN KEY
s referencing your table might complicate things, since locks might lead to locks on other tables and so on. To check this, simply query:
mysql> SELECT COUNT(*) FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE REFERENCED_TABLE_NAME = "myTable";
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.04 sec)
Here is how I use pt-online-schema-change:
pt-online-schema-change --alter "ENGINE=InnoDB" D=myBase,t=myTable --user --ask-pass
Note that my note on innodb_file_per_table
is true also for this solution.
4. mysqldump
The last solution is to recreate all databases from a dump. It takes forever, but it's extremely efficient. Note that this is the only solution to optimize your ibdata file, if innodb_file_per_table
is OFF
Max.
Best Answer
DISKSPACE FOR EVERYTHING INNODB
DISKSPACE FOR DATABASE mydb BY TABLE
DISKSPACE FOR INDIVIDUAL TABLE (mydb.mytable)
These queries will tell you how much space you need for any table, database, and engine.
SUGGESTION #1
If you would like to simulate
OPTIMIZE TABLE
just to see diskspace usage, you run the following steps onmydb.mytable
Doing this can give an idea who big the
.ibd
file will be after runningOPTIMIZE TABLE
.SUGGESTION #2
Please do not convert the table to MyISAM. It will be building two files (
.MYD
and.MYI
). Then, you try to converting to InnoDB again. It is essentially the same as doing#SUGGESTION #1
. MyISAM is also a little more prone to corruption with no recovery processes in place.EPILOGUE
By all means, you can proceed with pt-online-schema-change if your data is live. My answer simply gives you queries to estimate sizes of the tables and approximate temp table sizes for
OPTIMIZE TABLE
.If you are doing this during off hours, try my post Recover the disk space after deleting rows from table
GIVE IT A TRY !!!