The xtrabackup compiled C binary (as distinct from XtraBackup, which is the combination of the C binary and the Perl script) has support for printing out stats on InnoDB tables and indexes. This can be useful to examine whether you’d benefit from “defragmenting” your MySQL database with OPTIMIZE TABLE, although I have not determined firm guidelines for when that will actually help. I’ve written a small Perl script that formats the stats output nicely to give an overview of fragmentation.
It’s an initial draft, and if you find issues with it I would like to know so I can fix them. The script is embedded in the documentation page and can be downloaded by clicking on the header at the top of the code listing. The output looks like this:
TABLE INDEX TOT_PAGES FREE_PAGES PCT_FULL
art.link_out104 832383 38561 86.8%
art.link_out104 PRIMARY 498304 49 91.9%
art.link_out104 domain_id 49600 6230 76.9%
art.link_out104 domain_id_2 26495 3339 89.1%
art.link_out104 from_message_id 28160 142 96.3%
art.link_out104 from_site_id 38848 4874 79.4%
art.link_out104 revert_domain 153984 19276 71.4%
art.link_out104 site_message 36992 4651 83.4%
That output was generated from the stats output that Vadim showed on an earlier blog post about xtrabackup’s analysis capabilities.