You can optimize a table without interrupting service by using pt-online-schema-change:
$ pt-online-schema-change --alter="ENGINE=InnoDB" --execute D=sakila,t=actor
Also, MySQL 5.6.17 supports OPTIMIZE as an online DDL change, allowing concurrent DML.
I'll assume you are using innodb_file_per_table for this answer.
There is more than one meaning to "InnoDB fragmentation":
.ibd file is fragmented, and is very large whereas the dataset is small- Index pages are fragmented in that there are too many pages to contain little data, in which case they could be merged.
Please consider this post I wrote a while back: it shows how after purging many rows from a large table, the data file is fragmented (i.e. it is very large in the filesystem -- it's a known issue these files never reduce in size). And yet the indexes were not fragmented by the end of deletion: this is because InnoDB properly merges pages as they become empty(er).
The OPTIMIZE command indeed does not apply on InnoDB. What it does is rebuild the table (exactly like an ALTER). See this:
mysql [localhost] {msandbox} (test) > create table t(id int) engine=innodb;
mysql [localhost] {msandbox} (test) > optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status | OK |
+--------+----------+----------+-------------------------------------------------------------------+
As for DATA_FREE: I suggest that you simply ignore this variable. To be honest, I've been working with InnoDB tables for 10 years, and have never found this value to be very consistent with anything.
And now it's time for the real discussion: what exactly are you trying to achieve? Unless your database is completely stale, there will always be some fragmentation. It is natural to the process of adding, removing and updating rows in your table.
Fragmentation is not that evil: free space can get reclaimed by new data. If you tables are not very large, then just forget about the whole thing. For very large tables, you might gain some disk space by optimizing the table. But ask yourself: how soon would the table reach same fragmentation? An hour? A day? A week? IMHO in all these cases it is pointless to optimize the table.
Nevertheless, if a large table is massively purged of data, which is not expected to return, I'm all for optimizing it. Say you realize you have some redundant data which consists some 30% of your table size. Sure, it would be great to have that disk space back.
Bottom line: only consider these issues with very large tables; only if you have issues with disk space.
Best Answer
Once you delete some rows from a table its data file could become fragmented and that table becomes unoptimized in terms of MySQL. If you really do frequently delete rows from those tables, you should not worry because this is the expected behavior. You don't need to do anything else.
OPTIMIZE TABLEis usually used to reclaim the unused space and to defragment the data file. Also you should know that MySQL locks the table during the timeOPTIMIZE TABLEis running, which means that no one can run queries against that table and has to wait.