EmergencyEMERGENCY? Get 24/7 Help Now!

Pretty-formatted index fragmentation with xtrabackup

 | March 15, 2011 |  Posted In: MySQL, Percona Software

PREVIOUS POST
NEXT 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:

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

PREVIOUS POST
NEXT POST
Baron Schwartz

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

4 Comments

  • 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.

  • 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 ?

  • 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 ?

  • 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.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.