Re: Optimize table and fragmentation
Old wives' tales. Fragmentation is rarely a real problem. Don't be sucked in by the tools that recommend OPTIMIZE TABLE.
More details...
MyISAM tables...
When a row is deleted, that leaves a hole (free spot) in the middle of the .MYD file. When a row is inserted it tries first to fill in any holes, only then it will append to the file. If the row won't completely fit in one hole, it may have to 'continue' in another hole; this is "fragmentation of rows". UPDATEs also can mess with the record if the length of the row changes. Only when you have a _lot_ of churn like this, will the table become so fragmented that you will noticeably benefit from OPTIMIZE. (I have observed this once, in a table that had not been optimized in two years.)
InnoDB tables...
The "data" of an InnoDB table is organized in a BTree based on the PRIMARY KEY. (All secondary InnoDB indexes and all MyISAM indexes are BTree-structured.) BTrees are inherently stable. InnoDB will merge two adjacent blocks (16KB for InnoDB) when beneficial, so blocks don't get very 'empty'. Even so, a block is likely to be between half full and full. To look at it another way, a table could be spread across twice as many blocks as necessary. This degree of fragmentation is _not_ worth worrying about unless you are really tight on space. (If you are really tight on disk space, you don't have room to defragment -- Oops!) BTrees, under chrun, gravitate toward 69% full.
Fields of SHOW TABLE STATUS, and the equivalent in information_schema, provide Data_length, Index_length, and Free.
For MyISAM, the Free is space inside the MYD that could be recovered via OPTIMIZE. (Don't bother with OPTIMIZE if Free is less than 10% of Data.)
For tiny InnoDB tables, Free might be zero or close to it. For larger InnoDB tables, Free is often 4, 5, 6, or 7MB. The 4-7MB is preallocated space that cannot be recouped. When Free is more than 7MB, it implies that some 'extents' have been freed up. Extents will be reused as needed. (This is not really "fragmentation".) There can also be more 'free' space in the 'Data', but there is no metric showing that.
PARTITIONs (and MERGE tables) can be accurately thought of as being a collection of separate tables -- each with Data+Index+Free. The SHOW TABLE STATUS for a PARTITIONed table will sum up all the 4-7MB Free pieces, giving you a frighteningly large value.
OPTIMIZE does multiple things:
* squeeze out free space.
* (for MyISAM) put fragmented rows back together
* give space back to the OS (except for file_per_table=0)
This last item is a different reason to use OPTIMIZE. However, it has to copy the entire table and indexes over before any space is freed up.
MyISAM will (potentially) get a smaller .MYD and .MYI file.
InnoDB, with file_per_table will (potentially) get a smaller .ibd file.
InnoDB, without file_per_table will _not_ return any space to the OS.
65MB is 0.1% of 60GB -- NOT worth worrying about!