Pretty-formatted index fragmentation with xtrabackup

March 16, 2011

Author

Baron Schwartz

MySQL

Percona Software

Share this Post:

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:

<br>          TABLE           INDEX TOT_PAGES FREE_PAGES   PCT_FULL<br>art.link_out104                    832383      38561      86.8%<br>art.link_out104         PRIMARY    498304         49      91.9%<br>art.link_out104       domain_id     49600       6230      76.9%<br>art.link_out104     domain_id_2     26495       3339      89.1%<br>art.link_out104 from_message_id     28160        142      96.3%<br>art.link_out104    from_site_id     38848       4874      79.4%<br>art.link_out104   revert_domain    153984      19276      71.4%<br>art.link_out104    site_message     36992       4651      83.4%

1

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.