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:
|
1 2 3 4 5 6 7 8 9 |
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.
Resources
RELATED POSTS
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.
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 ?