Description:
The IO statistics in schema_table_statistics are powered by data from the file_summary_by_instance performance schema table, and this table simply tracks IO counters on a per-file basis.
However, partitions are stored in different files with a suffix (eg. t#p#p9.ibd) and there is no attempt to parse out the suffix and group all partitions under the same table name.
How to repeat:
Partitioned tables:
mysql> CREATE TABLE t (id INT) ENGINE=INNODB PARTITION BY HASH(id) PARTITIONS 10;
Query OK, 0 rows affected (1.54 sec)
mysql> insert into t values (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from sys.schema_table_statistics where table_name = 't';
+--------------+------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+---------+-----------------+-------------------+----------+------------------+------------------+-----------------+
| table_schema | table_name | total_latency | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read | io_read_latency | io_write_requests | io_write | io_write_latency | io_misc_requests | io_misc_latency |
+--------------+------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+---------+-----------------+-------------------+----------+------------------+------------------+-----------------+
| test | t | 781.59 us | 0 | 0 ps | 3 | 781.59 us | 0 | 0 ps | 0 | 0 ps | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------------+------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+---------+-----------------+-------------------+----------+------------------+------------------+-----------------+
1 row in set (0.04 sec)
Non-partitioned case that works correctly:
mysql> CREATE TABLE t (id INT) ENGINE=INNODB ;
Query OK, 0 rows affected (0.23 sec)
mysql> insert into t values (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from sys.schema_table_statistics where table_name = 't';
+--------------+------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
| table_schema | table_name | total_latency | rows_fetched | fetch_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency | io_read_requests | io_read | io_read_latency | io_write_requests | io_write | io_write_latency | io_misc_requests | io_misc_latency |
+--------------+------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
| test | t | 627.94 us | 0 | 0 ps | 3 | 627.94 us | 0 | 0 ps | 0 | 0 ps | 0 | 0 bytes | 0 ps | 8 | 224.00 KiB | 266.20 us | 7 | 77.46 ms |
+--------------+------------+---------------+--------------+---------------+---------------+----------------+--------------+----------------+--------------+----------------+------------------+------------+-----------------+-------------------+------------+------------------+------------------+-----------------+
1 row in set (0.04 sec)
Suggested fix:
Fix sys schema to parse/aggregate the filenames of partitioned tables.
Alternatively, just track io at a higher layer, since not all storage engines will necessarily have a mapping from table to file (eg. myisam, ndb, etc.)