Bug #98435 schema_table_statistics io statistics does not work with partitioned tables
Submitted: 30 Jan 2020 4:03 Modified: 30 Jan 2020 5:40
Reporter: Manuel Ung Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[30 Jan 2020 4:03] Manuel Ung
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.)
[30 Jan 2020 5:40] MySQL Verification Team
Hello Manuel Ung,

Thank you for the bug report and feedback.

regards,
Umesh