Pretty-formatted index fragmentation with xtrabackup

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.