]]>
]]>

Patch: innodb_check_fragmentation

This patch adds several session-scoped status variables to the MySQL server. You can use the variables to compute the fragmentation of the InnoDB data scanned in the last query executed by your connection. You can compute the fragmentation for both table scans and index scans.

The variables are reset at the beginning of each query, so they are not cumulative over the life of your connection; instead, they measure only the most recent query. They are meant to be useful for simple queries that read from a single table or index. They measure leaf pages only.

This patch hasn't been ported to 5.1 and 5.5 series yet, it's only available in the 5.0 branch.

Variables Provided

The patch provides the following variables:

Innodb_scan_pages_contiguous

TypeStatus variable
ScopeSession
DynamicNo

Counts the number of leaf pages that were read contiguously during the last query. It does not count the first page, so it will be 0 for single-page scans.

Innodb_scan_pages_jumpy

TypeStatus variable
ScopeSession
DynamicNo

Counts the number of leaf pages that were not read contiguously during the last query. It does not count the first page, so it will be 0 for single-page scans. Because it measures leaf pages only and “branch pages” must be inserted for scans on large tables, it will always be nonzero for large tables.

Innodb_scan_data_in_pages

TypeStatus variable
ScopeSession
DynamicNo

Counts the bytes used by records in the leaf pages that were scanned during the last query. To make the implementation more efficient, it does not count the last page in the scan.

Innodb_scan_garbage_in_pages

TypeStatus variable
ScopeSession
DynamicNo

Counts the bytes occupied by garbage (not used by records) in the leaf pages that were scanned during the last query. To make the implementation more efficient, it does not count the last page in the scan.

Examples

The following query will scan the fkey_stock_2 index on the table:

mysql> explain select count(s_w_id) from stock;
+----+-------------+-------+-------+---------------+--------------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+--------+-------------+
|  1 | SIMPLE      | stock | index | NULL          | fkey_stock_2 | 4       | NULL | 300345 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+--------+-------------+

Let us execute the query and inspect the variables.

mysql> select count(s_w_id) from stock;
+---------------+
| count(s_w_id) |
+---------------+
|        300000 |
+---------------+

mysql> show status like 'Innodb_scan%';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_scan_pages_contiguous  | 3       |
| Innodb_scan_pages_jumpy       | 279     |
| Innodb_scan_data_in_pages     | 3287328 |
| Innodb_scan_garbage_in_pages  | 574827  |
+-------------------------------+---------+

The index scan was only about 1% contiguous, which is what we would expect for a B-Tree index that is not built in index order. Insertions into the index have also caused a lot of garbage space in the pages: about 15% of the total space is used for garbage. This is also typical for random insertions into a B-Tree, because random insertions cause many page splits.

The following query will scan the table, not the index:

mysql> explain select count(s_order_cnt) from stock;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | stock | ALL  | NULL          | NULL | NULL    | NULL | 300345 |       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

Again, we'll execute the query and inspect the status variables.

mysql> select count(s_order_cnt) from stock;
+--------------------+
| count(s_order_cnt) |
+--------------------+
|             300000 |
+--------------------+

mysql> show status like 'Innodb_scan%';
+-------------------------------+----------+
| Variable_name                 | Value    |
+-------------------------------+----------+
| Innodb_scan_pages_contiguous  | 6229     |
| Innodb_scan_pages_jumpy       | 27       |
| Innodb_scan_data_in_pages     | 94493024 |
| Innodb_scan_garbage_in_pages  | 7583     |
+-------------------------------+----------+

In contrast to the index scan, we see that the table is not very fragmented. The table scan's page accesses are more than 99% contiguous, and the pages scanned contain much less than 1% garbage space. This is typical for data that is loaded in primary key order, which does not cause page splits in the primary key.

Patch Information

Author/Origin Percona
Bugs fixed
Dependencies
 
patches/innodb_check_fragmentation.txt · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>