Bug #71755 Provide per partition summary information in PERFORMANCE_SCHEMA
Submitted: 18 Feb 2014 8:09 Modified: 2 May 2014 5:00
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version:5.7+ OS:Any
Assigned to: Marc Alff CPU Architecture:Any
Tags: performance_schema

[18 Feb 2014 8:09] Valeriy Kravchuk
Description:
Even in MySQL 5.7.3 I see "io_waits" summary tables for tables and indexes, but NOT for partitions:

| table_io_waits_summary_by_index_usage                |
| table_io_waits_summary_by_table                      |
| table_lock_waits_summary_by_table                    |

Last time I checked table_io_waits_summary_by_table has details aggregated per table, while for partitioned tables it may be important to identify "hot" or "unused" partitions easily. Please, add this kind of instrumentation (or explain how to get per partition statistics on I/O waits and number of operations if I missed this somehow).

How to repeat:
mysql> CREATE TABLE employees (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     hired DATE NOT NULL DEFAULT '1970-01-01',
    ->     separated DATE NOT NULL DEFAULT '9999-12-31',
    ->     job_code INT NOT NULL,
    ->     store_id INT NOT NULL
    -> )
    -> PARTITION BY RANGE (store_id) (
    ->     PARTITION p0 VALUES LESS THAN (6),
    ->     PARTITION p1 VALUES LESS THAN (11),
    ->     PARTITION p2 VALUES LESS THAN (16),
    ->     PARTITION p3 VALUES LESS THAN (21)
    -> );
Query OK, 0 rows affected (3.81 sec)

mysql> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01',
  `separated` date NOT NULL DEFAULT '9999-12-31',
  `job_code` int(11) NOT NULL,
  `store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (store_id)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (21) ENGINE = InnoDB) */
1 row in set (0.11 sec)

mysql> insert into employees (id, store_id, fname, lname, job_code) values (1, 1
, 'John', 'Smith', 1), (2, 12, 'John', 'Doe', 1), (3, 20, 'Valerii', 'Kravchuk',
 2);
Query OK, 3 rows affected (0.48 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc performance_schema.table_io_waits_summary_by_table;
+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| OBJECT_TYPE      | varchar(64)         | YES  |     | NULL    |       |
| OBJECT_SCHEMA    | varchar(64)         | YES  |     | NULL    |       |
| OBJECT_NAME      | varchar(64)         | YES  |     | NULL    |       |
| COUNT_STAR       | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_READ       | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_WRITE      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_FETCH      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_INSERT     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_UPDATE     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_DELETE     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
+------------------+---------------------+------+-----+---------+-------+
38 rows in set (0.11 sec)

Many useful counters above, now add some activity against table:

mysql> select * from employees where store_id=1;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | John  | Smith | 1970-01-01 | 9999-12-31 |        1 |        1 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.06 sec)

mysql> explain partitions select * from employees where store_id=1;
+----+-------------+-----------+------------+------+---------------+------+-----
----+------+------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_
len | ref  | rows | Extra       |
+----+-------------+-----------+------------+------+---------------+------+-----
----+------+------+-------------+
|  1 | SIMPLE      | employees | p0         | ALL  | NULL          | NULL | NULL
    | NULL |    2 | Using where |
+----+-------------+-----------+------------+------+---------------+------+-----
----+------+------+-------------+
1 row in set (0.00 sec)

and try to find out what partition was mostly accessed. Seems no way in this table:

mysql> select object_name, count_star, count_insert, count_fetch, sum_timer_wait

    -> from performance_schema.table_io_waits_summary_by_table
    -> where object_schema = 'test';
+-------------+------------+--------------+-------------+----------------+
| object_name | count_star | count_insert | count_fetch | sum_timer_wait |
+-------------+------------+--------------+-------------+----------------+
| employees   |          5 |            3 |           2 |   460306612304 |
+-------------+------------+--------------+-------------+----------------+
1 row in set (0.02 sec)

while in reality we have 4 separate .ibd files:

employees.frm
employees.par
employees#p#p0.ibd
employees#p#p1.ibd
employees#p#p2.ibd
employees#p#p3.ibd

and surely some way to track per partition activity van be invented/implemented.

Suggested fix:
Add table_io_waits_summary_by_partition table or some other table similar to PARTITIONS table in the INFORMATION_SCHEMA, but with counters to register operations and waits for each partition (and subpartition).
[2 May 2014 5:00] Erlend Dahl
Thank you for the feature request.