]]>
]]>
This page has been moved, the new location is Data Page I/O Activity.

Patch: innodb_io_pattern

This patch adds the INNODB_IO_PATTERN table in the INFORMATION_SCHEMA. The table contains information about I/O activity for InnoDB data pages. There are also some new configuration variables to control how the server collects information about the I/O activity, and a new FLUSH statement to reset the statistics.

Variables Provided

This patch provides the following variables:

innodb_io_pattern_trace

TypeSystem variable, command-line option
ScopeGlobal
DynamicYes
Syntax1 or 0
Default0

Enables or disables the table and statistics gathering, and controls the memory allocation for the stats table. The default is 0 (disabled), meaning that no statistics are gathered and no memory is allocated. This is to ensure that the feature doesn't cause any extra load on the server unless desired. This feature uses a lot of memory.

innodb_io_pattern_trace_running

TypeSystem variable, command-line option
ScopeGlobal
DynamicYes
Syntax1 or 0
Default0

Causes the trace activity to pause (0) or run (1). The default value is 0.

innodb_io_pattern_size_limit

TypeSystem variable, command-line option
ScopeGlobal
DynamicYes
Syntaxunsigned integer
Default

This value limits the number of pages for which the system will track I/O activity. If the number of pages counted reaches or exceeds this value, no more counters will be allocated.

Tables Provided

The patch provides the following INFORMATION_SCHEMA tables:

INNODB_IO_PATTERN

This table shows the count of I/O accesses for each data page read or written. The rows will be returned in a random order, because the table is implemented internally as a hash table, and hash tables are not ordered.

Field Notes
SPACE The page's tablespace ID (determined by the datafile).
OFFSET The page's ID (unit = pages).
INDEX_ID The InnoDB index ID (determined by the table and index).
TABLE_NAME The table name, as InnoDB refers to it internally; empty unless the table's data dictionary has been loaded.
INDEX_NAME The index name, as InnoDB refers to it internally; empty unless the table's data dictionary has been loaded.
N_READ The number of read I/Os for this page.
N_WRITE The number of write I/Os for this page.

Examples:

mysql> select * from information_schema.innodb_io_pattern;
+-------+--------+------------+-----------------+-------------------+--------+---------+
| SPACE | OFFSET | INDEX_ID   | TABLE_NAME      | INDEX_NAME        | N_READ | N_WRITE |
+-------+--------+------------+-----------------+-------------------+--------+---------+
|     0 |  28225 |         46 | tpcc/stock      | PRIMARY           |      1 |       0 |
|     0 |  28252 |         46 | tpcc/stock      | PRIMARY           |      2 |       0 |
|     0 |  28248 |         46 | tpcc/stock      | PRIMARY           |      2 |       0 |
|     0 |  28268 |         46 | tpcc/stock      | PRIMARY           |      1 |       1 |
|     0 |  28280 |         46 | tpcc/stock      | PRIMARY           |      1 |       1 |
|     0 |  28279 |         46 | tpcc/stock      | PRIMARY           |      2 |       2 |
|     0 |  28278 |         46 | tpcc/stock      | PRIMARY           |      1 |       1 |
|     0 |  28273 |         46 | tpcc/stock      | PRIMARY           |      2 |       2 |
....
|     0 |   5519 |         38 | tpcc/history    | fkey_history_2    |      2 |       2 |
|     0 |   5516 |         38 | tpcc/history    | fkey_history_2    |      1 |       1 |
|     0 |   5515 |         38 | tpcc/history    | fkey_history_2    |      1 |       2 |
|     0 |   5514 |         38 | tpcc/history    | fkey_history_2    |      1 |       1 |
|     0 |   5513 |         38 | tpcc/history    | fkey_history_2    |      2 |       2 |
|     0 |   5512 |         38 | tpcc/history    | fkey_history_2    |      1 |       1 |
|     0 |   5508 |         38 | tpcc/history    | fkey_history_2    |      2 |       2 |
|     0 |   5507 |         38 | tpcc/history    | fkey_history_2    |      2 |       3 |
|     0 |   5504 |         38 | tpcc/history    | fkey_history_2    |      2 |       2 |
|     0 |   5520 |         38 | tpcc/history    | fkey_history_2    |      1 |       1 |
+-------+--------+------------+-----------------+-------------------+--------+---------+
5563 rows in set (0.05 sec)

mysql> select index_id, table_name, index_name, sum(n_read), sum(n_write) from information_schema.innodb_io_pattern group by index_id;
+------------+-----------------+-------------------+-------------+--------------+
| index_id   | table_name      | index_name        | sum(n_read) | sum(n_write) |
+------------+-----------------+-------------------+-------------+--------------+
|          0 |                 |                   |          36 |           47 |
|          1 | SYS_TABLES      | CLUST_IND         |          14 |           31 |
|          8 |                 |                   |           0 |            1 |
|         24 | tpcc/warehouse  | PRIMARY           |           0 |            3 |
|         26 |                 |                   |          12 |            0 |
|         30 | tpcc/item       | PRIMARY           |         933 |            0 |
|         32 | tpcc/district   | PRIMARY           |           1 |            3 |
|         36 | tpcc/history    | GEN_CLUST_INDEX   |          10 |            5 |
|         37 | tpcc/history    | fkey_history_1    |         154 |          149 |
|         38 | tpcc/history    | fkey_history_2    |          59 |           60 |
|         39 | tpcc/new_orders | PRIMARY           |         117 |          159 |
|         43 | tpcc/order_line | PRIMARY           |         313 |          311 |
|         44 | tpcc/order_line | fkey_order_line_2 |        1112 |         1173 |
|         46 | tpcc/stock      | PRIMARY           |        3334 |         1529 |
|         47 | tpcc/stock      | fkey_stock_2      |         414 |            0 |
|         48 | tpcc/customer   | PRIMARY           |         746 |          377 |
|         49 | tpcc/customer   | idx_customer      |         165 |            0 |
|         50 | tpcc/orders     | PRIMARY           |         157 |          166 |
|         51 | tpcc/orders     | idx_orders        |         199 |          147 |
|      65535 |                 |                   |           3 |            6 |
|   28442624 |                 |                   |           1 |            4 |
|  251527168 |                 |                   |           2 |            4 |
| 4294901760 |                 |                   |           4 |            5 |
+------------+-----------------+-------------------+-------------+--------------+
23 rows in set (0.07 sec)

mysql> FLUSH INNODB_IO_PATTERN;
Query OK, 0 rows affected (0.01 sec)

mysql> select index_id, table_name, index_name, sum(n_read), sum(n_write) from information_schema.innodb_io_pattern group by index_id;
Empty set (0.01 sec)

Commands Provided

This patch provides the following commands:

FLUSH INNODB_IO_PATTERN

This statement empties the INNODB_IO_PATTERN table and frees the extended memory for counters.

 
patches/innodb_io_pattern.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
]]> ]]>