InnoDB Page Fragmentation Counters

InnoDB Page Fragmentation Counters

InnoDB page fragmentation is caused by random insertion or deletion from a secondary index. This means that the physical ordering of the index pages on the disk is not same as the index ordering of the records on the pages. As a consequence this means that some pages take a lot more space and that queries which require a full table scan can take a long time to finish.

To provide more information about the InnoDB page fragmentation Percona Server for MySQL now provides the following counters as status variables: Innodb_scan_pages_contiguous, Innodb_scan_pages_disjointed, Innodb_scan_data_size, Innodb_scan_deleted_recs_size, and Innodb_scan_pages_total_seek_distance.

Version Specific Information

  • 8.0.12-1: The feature was ported from Percona Server for MySQL 5.7

Status Variables

variable Innodb_scan_pages_contiguous
Variable Type:Numeric
Scope:Session

This variable shows the number of contiguous page reads inside a query.

variable Innodb_scan_pages_disjointed
Variable Type:Numeric
Scope:Session

This variable shows the number of disjointed page reads inside a query.

variable Innodb_scan_data_size
Variable Type:Numeric
Scope:Session

This variable shows the size of data in all InnoDB pages read inside a query (in bytes) - calculated as the sum of page_get_data_size(page) for every page scanned.

variable Innodb_scan_deleted_recs_size
Variable Type:Numeric
Scope:Session

This variable shows the size of deleted records (marked as deleted in page_delete_rec_list_end()) in all InnoDB pages read inside a query (in bytes) - calculated as the sum of page_header_get_field(page, PAGE_GARBAGE) for every page scanned.

variable Innodb_scan_pages_total_seek_distance
Variable Type:Numeric
Scope:Session

This variable shows the total seek distance when moving between pages.

Download PDF Manual

Table Of Contents

This Page

Percona Server Series

Contact Us

For free technical help, visit the Percona Community Forum.
To report bugs or submit feature requests, open a JIRA ticket.
For paid support and managed or professional services, contact Percona Sales.