There is a user comment on the OPTIMIZE TABLE documentation page that had the same situation as you. His solution was to copy the table somewhere else, optimize, then copy back. This required offline time.

Unfortunately, my solution would also require offline time as well, for 70GB worth of data. Something like this:

• mysqldump table from another machine with enough space
• DROP TABLE foo
• Load table from the mysqldump file: mysql -hXXX.XX.XX.XXX -u... -p... < foo.sql

The downtime will be from the DROP TABLE step until the load table from a remote host of 70GB of data can be completed.

One potential bright spot is that new INSERTS will re-use the space created from DELETES:

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

But this is what causes fragmentation to begin with. So you will need to make sure your tablespace is optimized frequently to prevent this in the future.

I just want to throw out there that to run an optimize query, you're always going to need at least the database size + the size of the table you're optimizing available on disk.

As an example, this means for a database size of 30GB and the table your optimizing is 28GB, you will need at minimum a harddrive that can fit 58GB of data space. This is because MySQL rewrites a new temporary file and then replaces the old one.

I say at least, because there's always other things going on. You'll need space for other programs, MySQL might be creating on-disk temporary tables during the operation, etc.

I think I have a plausible explanation you find very intriguing.

When you loaded the MyISAM table, only one index got loaded into the .MYI file in a lopsided manner.

Which index would that be ? THE PRIMARY KEY. Why ?

Indexes usually use BTREEs. They are designed to collect keys into a single BTREE node and balance the BTREE internally until the BTREE is full. On the insert into the full node, the full tree node is split and the keys inside are divided.

Most often a BTREE node will split is when you load the keys in order. For proof, look at the worst case BTREE --- the balanced binary tree.

I have several posts in the DBA STackExchange where I mention how a balanced binary only has node with one key. I also mention how a leaf pages must rebalance about 45% of the time when inserting a new key. Here are those posts:

• Aug 03, 2011 : When should I rebuild indexes?
• Jun 28, 2012 : Benefits of BTREE in MySQL
• Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
• Nov 26, 2012 : Does the Int type primary key insert order affect query performance in MySQL?
• Jun 04, 2013 : Why would increase in innodb_buffer_pool_size slow down MySQL?

My working theory is that random inserts into the table may actually make the .MYI smaller by prevent tree node splits as much as possible.

In order to make this happen, you will have to something quite unusual. Ready for this ?

ALTER TABLE generic_dummy_table_name ORDER BY datetime;

or

ALTER TABLE generic_dummy_table_name ORDER BY user,datetime;

That right, you can reorder the physical rows of the table. This may make the indexes a different size by inducing BTREE splits on another index.

Try reordering the table. Then, mysqldump it and reload it. You may find that the .MYI may get smaller or bigger. You cannot really predict it.

Next time, try reloading the MyISAM table with a very large bulk insert buffer.

Just run

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 1024;

then connect to mysql and reload the mysqldump.

GIVE IT A TRY !!!