July 24, 2014

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:

That output was generated from the stats output that Vadim showed on an earlier blog post about xtrabackup’s analysis capabilities.

About Baron Schwartz

Baron is the lead author of High Performance MySQL.
He is a former Percona employee.

Comments

  1. Baron Schwartz says:

    FREE_PAGES is the difference between the total pages in the index, and the pages that xtrabackup reports to be used. I’m not sure that this is really correct — maybe header pages etc aren’t accounted correctly? PCT_FULL is the total bytes of data, divided by the number of pages containing data, times the page size, times 100.

  2. Peter Zaitsev says:

    Baron,

    Just to make it clear does FREE_PAGES lists how many pages are free and PCT_FULL is full ratio of remaining pages, it is not the percent of used pages in the index, right ?

  3. Baron,

    Just to make it clear does FREE_PAGES lists how many pages are free and PCT_FULL is full ratio of remaining pages, it is not the percent of used pages in the index, right ?

  4. FREE_PAGES is the difference between the total pages in the index, and the pages that xtrabackup reports to be used. I’m not sure that this is really correct — maybe header pages etc aren’t accounted correctly? PCT_FULL is the total bytes of data, divided by the number of pages containing data, times the page size, times 100.

Speak Your Mind

*