| Bug #105244 | x$schema_table_statistics io statistics doesn't work with partitioned tables | ||
|---|---|---|---|
| Submitted: | 18 Oct 2021 6:12 | Modified: | 21 Oct 2021 13:13 |
| Reporter: | yi qian | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: SYS Schema | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Oct 2021 12:58]
MySQL Verification Team
Hi Mr. qian, Thank you for your bug report. However, your report is incomplete. You have not INSERTed , nor SELECTed any rows from the partitioned table. Hence, please do that and then run the script from the SYS schema. After that, let us know the results. We are waiting on your feedback.
[18 Oct 2021 13:21]
yi qian
Partitioned tables:
mysql> create table t1(id int(11) not null,name varchar(32) not null)
-> partition by range(id)
-> (
-> partition p0 values less than(10),
-> partition p1 values less than(20),
-> partition p2 values less than(30),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected, 1 warning (2.54 sec)
mysql> insert into t1 values(1,'a'), (2,'b'), (3,'c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from sys.x$schema_table_statistics where table_name like 't1%'\G
*************************** 1. row ***************************
table_schema: testdb
table_name: t1
total_latency: 2602265080
rows_fetched: 0
fetch_latency: 0
rows_inserted: 3
insert_latency: 2602265080
rows_updated: 0
update_latency: 0
rows_deleted: 0
delete_latency: 0
io_read_requests: NULL
io_read: NULL
io_read_latency: NULL
io_write_requests: NULL
io_write: NULL
io_write_latency: NULL
io_misc_requests: NULL
io_misc_latency: NULL
1 row in set (0.75 sec)
[18 Oct 2021 13:31]
MySQL Verification Team
Hi, We see a difference in behaviour, but that is how it was designed to function in the SYS schema. However, if you would like to see this functioning for the partitioned table, we can verify your report as a feature request. Please, confirm that this is OK with you.
[19 Oct 2021 3:34]
yi qian
This problem was mentioned in 8.0.19. The link is as follows: https://bugs.mysql.com/bug.php?id=98435 At that time, the problem was considered a bug. However, on the 8.0.25 version, the problem still exists.
[20 Oct 2021 14:10]
MySQL Verification Team
Hi, Since SYS would require a new view, if you wish it to be a feature request, we shall be happy to verify it .....
[21 Oct 2021 6:34]
yi qian
OK！I would like to see it.
[21 Oct 2021 13:13]
MySQL Verification Team
Thank you Mr. gian, This is now a verified feature request.
Description: The IO statistics in view x$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 and there is no attempt to parse out the suffix and group all partitions under the same table name. How to repeat: 1. Non-partitioned case that works correctly: mysql> CREATE TABLE t (id INT); Query OK, 0 rows affected (0.65 sec) mysql> insert into t values (1), (2); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from sys.schema_table_statistics where table_name = 't'\G *************************** 1. row *************************** table_schema: testdb table_name: t total_latency: 1.62 ms rows_fetched: 0 fetch_latency: 0 ps rows_inserted: 2 insert_latency: 1.62 ms rows_updated: 0 update_latency: 0 ps rows_deleted: 0 delete_latency: 0 ps io_read_requests: 0 io_read: 0 bytes io_read_latency: 0 ps io_write_requests: 8 io_write: 224.00 KiB io_write_latency: 2.02 ms io_misc_requests: 7 io_misc_latency: 1.22 ms 1 row in set (0.98 sec) 2. Partitioned tables: mysql> create table t1(id int(11) not null,name varchar(32) not null) -> partition by range(id) -> ( -> partition p0 values less than(10), -> partition p1 values less than(20), -> partition p2 values less than(30), -> partition p3 values less than maxvalue -> ); Query OK, 0 rows affected, 1 warning (2.54 sec) mysql> select * from sys.x$schema_table_statistics where table_name like 't1%'\G *************************** 1. row *************************** table_schema: testdb table_name: t1 total_latency: 0 rows_fetched: 0 fetch_latency: 0 rows_inserted: 0 insert_latency: 0 rows_updated: 0 update_latency: 0 rows_deleted: 0 delete_latency: 0 io_read_requests: NULL io_read: NULL io_read_latency: NULL io_write_requests: NULL io_write: NULL io_write_latency: NULL io_misc_requests: NULL io_misc_latency: NULL 1 row in set (0.09 sec) Suggested fix: Fix sys schema to parse/aggregate the filenames of partitioned tables.