1.. _innodb_fragmentation_count:
2
3====================================
4InnoDB Page Fragmentation Counters
5====================================
6
7InnoDB page fragmentation is caused by random insertion or deletion from a
8secondary index. This means that the physical ordering of the index pages on
9the disk is not same as the index ordering of the records on the pages. As a
10consequence this means that some pages take a lot more space and that queries
11which require a full table scan can take a long time to finish.
12
13To provide more information about the InnoDB page fragmentation |Percona
14Server| now provides the following counters as status variables:
15:variable:`Innodb_scan_pages_contiguous`,
16:variable:`Innodb_scan_pages_disjointed`, :variable:`Innodb_scan_data_size`,
17:variable:`Innodb_scan_deleted_recs_size`, and
18:variable:`Innodb_scan_pages_total_seek_distance`.
19
20
21Version Specific Information
22============================
23
24  * :rn:`5.7.20-18`:
25    Feature Implemented
26
27Status Variables
28================
29
30.. variable:: Innodb_scan_pages_contiguous
31
32     :vartype: Numeric
33     :scope: Session
34
35This variable shows the number of contiguous page reads inside a query.
36
37.. variable:: Innodb_scan_pages_disjointed
38
39     :vartype: Numeric
40     :scope: Session
41
42This variable shows the number of disjointed page reads inside a query.
43
44.. variable:: Innodb_scan_data_size
45
46     :vartype: Numeric
47     :scope: Session
48
49This variable shows the size of data in all InnoDB pages read inside a
50query (in bytes) - calculated as the sum of ``page_get_data_size(page)`` for
51every page scanned.
52
53.. variable:: Innodb_scan_deleted_recs_size
54
55     :vartype: Numeric
56     :scope: Session
57
58This variable shows the size of deleted records (marked as ``deleted`` in
59``page_delete_rec_list_end()``) in all InnoDB pages read inside a query
60(in bytes) - calculated as the sum of ``page_header_get_field(page,
61PAGE_GARBAGE)`` for every page scanned.
62
63.. variable:: Innodb_scan_pages_total_seek_distance
64
65     :vartype: Numeric
66     :scope: Session
67
68This variable shows the total seek distance when moving between pages.
69
70Related Reading
71===============
72
73  * `InnoDB: look after fragmentation
74    <https://www.percona.com/blog/2009/11/05/innodb-look-after-fragmentation/>`_
75
76  * `Defragmenting a Table
77    <https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html>`_
78