Defragmentation is a concept that mostly does not apply to InnoDB. It is rarely worth the effort to use
OPTIMIZE TABLE or other techniques to defrag.
There are several ways in which InnoDB fragments and/or has "free" space:
- The rows in a block not filling up the block.
- BTree blocks gravitate to 69% full. If you will insert new rows at random spots, this is sort of the limit of the intra-block waste.
- The blocks in an 'extent' not consuming the entire extent.
- Undo/redo records temporarily consuming space.
- Data and Indexes are each BTrees, with various 'free' space.
SHOW TABLE STATUS shows only part of the "free" space. Furthermore, it will usually indicate 4/5/6/7MB as free for non-small tables because of reserving space in an 'extent' for future action.
- Defragmentation provides only a tiny benefit to performance; so this is rarely a valid reason.
- If you are almost out of disk space, defragging may keep you out of trouble briefly. But if the defrag action requires copying the table/tablespace, then you may not have room to do it. Catch-22. Well, OK, MariaDB's feature avoids the copy. But will it shrink the size of the tablespace file, thereby giving space back to the OS?
- InnoDB already does some defragmentation -- by combining adjacent somewhat-empty blocks automatically (under certain circumstances).
TABLE STATUS provided)
It shows 0.9GB "data" but a huge 6.9GB "free". (I assume that
STATUS was taken after an
OPTIMIZE?) This can exist in multiple situations:
innodb_file_per_table = OFF, in which case, the "free" is how much free space is in
ibdata1, and has nothing to do with the table in question. (You say this was not your case.)
- The table has a thousand
PARTITIONs. Each partition always shows some "free" space; it could add up to 6.9GB. (One reason why
PARTITIONing may not be wise to pursue.)
- You recently
DELETEd most of the table's rows. This was probably a very slow process. It would have been faster to create a new table, copy over the rows to keep, then rename to put the table in place. And, guess what, this approach would have given you 'optimize' for free.
The defrag feature in MariaDB, according to the manual:
Note that tablespace files (including ibdata1) will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.
- It does not take extra disk space while defragmenting. This can be very important if you are too low on disk space.
- It fails to give the space back to the OS, even for file_per_table.
- It apparently does not prevent accesses to the table during the defragmenting. (Or at worst does it in quick bursts.)