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.

This patch provides the following variables:

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.

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

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.

The patch provides the following INFORMATION_SCHEMA tables:

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.

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)

This patch provides the following commands:

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