MySQL – OPTIMIZE TABLE vs. myisamchk

myisamMySQLmysql-5.1

I have a very large table (~50GB) and periodically rows are purged
from it and I want to run OPTIMIZE TABLE to recover the space. But I
do not have enough space to run it. If I do run it the server hangs
and must be killed and restarted and the table is damaged and must be
repaired. I do this with myisamchk.

At the MySQL documentation I read this:

To coalesce fragmented rows and eliminate wasted space that results
from deleting or updating rows, run myisamchk in recovery mode:

shell> myisamchk -r tbl_name

You can optimize a table in the same way by using the OPTIMIZE TABLE
SQL statement. OPTIMIZE TABLE does a table repair and a key
analysis, and also sorts the index tree so that key lookups are
faster. There is also no possibility of unwanted interaction between a
utility and the server, because the server does all the work when you
use OPTIMIZE TABLE.

Does this mean that myisamchk -r -a -S does the same thing as OPTIMIZE TABLE?

If they do do the same thing, why does myisamchk work but OPTIMIZE
TABLE run out of space?

Best Answer

They do the same thing, but there is a difference and a grave danger.

Here is the difference:

  • OPTIMIZE TABLE is done in the mysql client or in a mysql session.
  • myisamchk is a utility, not a client. Thus, there are no table locking safeguards. Therefore, you should never run myisamchk -r -a -S in a live system. Otherwise, you can quickly (in fact, instantaneously) corrupt a MyISAM table.

You could move the MyISAM table to another folder and run myisamchk against the table files without interference from mysqld or any client connections thereof.

Other difference

  • mysqld will create temp tables
  • myisamchk can figure out if it needs to make a temp table or do things in place.

More info

Doing OPTIMIZE TABLE will does a complete copy of the table. It does this under the hood

ALTER TABLE mytable ENGINE=MyISAM;
ANALYZE TABLE mytable;

Doing OPTIMIZE TABLE could make the temp table in /tmp and not in place like myisamchk

Please remember that tmpdir is mapped somewhere. That's where space may give out.

Related Question