| Bug #115298 | partition table's io wait summary is not updated correct in PERFORMANCE_SCHEMA | ||
|---|---|---|---|
| Submitted: | 12 Jun 2024 10:21 | Modified: | 12 Jun 2024 13:05 |
| Reporter: | Luodan Zhang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Jun 2024 10:50]
MySQL Verification Team
Hi Mr. Zhang, Thank you for the test case. However, we can not repeat it with our 8.0.37 production binary. We always run test cases on the latest release available. For 8.0 , current release is 8.0.37. This i what we get: OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 6 0 6 0 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 0 0 0 0 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 6 0 6 0 6 0 0 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 3 3 abc 2024-06-12 00:00:00 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 6 0 6 0 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 0 0 0 0 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 6 0 6 0 6 0 0 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 id col1 col2 partition_key 6 2 bcd 2024-06-10 00:00:00 4 1 bcd 2024-06-11 00:00:00 5 2 bcd 2024-06-11 00:00:00 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 3 3 abc 2024-06-12 00:00:00 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 14 8 6 8 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 2 2 0 2 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 12 6 6 6 6 0 0 Results are simply perfect. Can't repeat.
[12 Jun 2024 11:11]
Luodan Zhang
Here are you results. We can see that after you execute the follow two sqls, the count_star is the same as before. The correct result is count_star will increase.You can test it in non-partition table to observe the different results. select * from t where id<=3; select * from t where id in (1,2); OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 6 0 6 0 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 0 0 0 0 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 6 0 6 0 6 0 0 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 3 3 abc 2024-06-12 00:00:00 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 6 0 6 0 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 0 0 0 0 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 6 0 6 0 6 0 0
[12 Jun 2024 11:40]
MySQL Verification Team
Hi, If you read our Reference Manual for the version 8.0 or higher, you will find out that COUNT_STAR provides the correct result.
[12 Jun 2024 12:13]
Luodan Zhang
Hi, Could you point it out to me. I found the explanations for count_star as bellow,but in fact I rarely found count_star instructions for partitioned table. https://dev.mysql.com/doc/refman/8.0/en/performance-schema-table-wait-summary-tables.html " COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT These columns aggregate all I/O operations. They are the same as the sum of the corresponding xxx_READ and xxx_WRITE columns. COUNT_READ, SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ These columns aggregate all read operations. They are the same as the sum of the corresponding xxx_FETCH columns. " Why do partition table behave differently from non-partition table? And why range query don't increase count_star/count_read while equal conditions increase count_star/count_read.
[12 Jun 2024 12:43]
MySQL Verification Team
Hi Mr. Zhang, Thank you for pointing out that Performance Schema for non-partitioned tables does work correctly: BJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 6 0 6 0 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 0 0 0 0 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 6 0 6 0 6 0 0 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 3 3 abc 2024-06-12 00:00:00 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 12 6 6 6 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 6 6 0 6 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 6 0 6 0 6 0 0 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 id col1 col2 partition_key 1 1 abc 2024-06-12 00:00:00 2 2 abc 2024-06-12 00:00:00 3 3 abc 2024-06-12 00:00:00 4 1 bcd 2024-06-11 00:00:00 5 2 bcd 2024-06-11 00:00:00 6 2 bcd 2024-06-10 00:00:00 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t 19 13 6 13 6 0 0 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE TABLE test t PRIMARY 7 7 0 7 0 0 0 TABLE test t idx_col2 0 0 0 0 0 0 0 TABLE test t NULL 12 6 6 6 6 0 0 This is now a verified bug for the version 8.0 and higher. Thanks a lot ......
[12 Jun 2024 12:54]
Luodan Zhang
Thanks, And could you explain the logical to me . I'm really instrested in it. Besides the cases I supported , are there any other scenarios that don't update count_star/count_read. Additionally, in what version the bug will be fixed, I'm looking forward the version.
[12 Jun 2024 12:58]
MySQL Verification Team
Hi Mr. Zhang, We truly do not know any other scenarios, because these P_S tables are rarely reported ...... Regarding bug being fixed, it will not be so soon. This is not a high severity, nor crashing bug. This is a relatively low priority bug. Furthermore, we can not provide info on the fixing of any bug. Simply, each Development team has it's own schedule for fixing bugs and it is changing at least once each week. Hence, nobody could really answer your question, regardless of the bug's severity .........
[12 Jun 2024 13:05]
Luodan Zhang
Okay,I understand. Thank you for your reply.
Description: First of all,thank you for taking the time to look at my question. The following is my problem. When a table is created as partition table,the count info such as count_read,count_star in table_io_waits_summary_by_table and table_io_waits_summary_by_index_usage can't update as expected in some cases. As I tested that may not covering all situations,the follow cases will not update count info in PS io wait summary table: 1. range query using secondary index or primary ,include where condition is col_name in ('a','b') that in more than one value While the follow situations ,the count info is updated correctly: 1. DML SQL 2. equal conditions using secondary index or primary key 3. sql no using index How to repeat: 1. Create Table CREATE TABLE `t` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID', `col1` int DEFAULT NULL, `col2` varchar(20) DEFAULT NULL, `partition_key` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`,`partition_key`), KEY `idx_col2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (to_days(`partition_key`)) (PARTITION p20240528 VALUES LESS THAN (739400) ENGINE = InnoDB, PARTITION p20240529 VALUES LESS THAN (739401) ENGINE = InnoDB, PARTITION p20240530 VALUES LESS THAN (739402) ENGINE = InnoDB, PARTITION p20240531 VALUES LESS THAN (739403) ENGINE = InnoDB, PARTITION p20240601 VALUES LESS THAN (739404) ENGINE = InnoDB, PARTITION p20240602 VALUES LESS THAN (739405) ENGINE = InnoDB, PARTITION p20240603 VALUES LESS THAN (739406) ENGINE = InnoDB, PARTITION p20240604 VALUES LESS THAN (739407) ENGINE = InnoDB, PARTITION p20240605 VALUES LESS THAN (739408) ENGINE = InnoDB, PARTITION p20240606 VALUES LESS THAN (739409) ENGINE = InnoDB, PARTITION p20240607 VALUES LESS THAN (739410) ENGINE = InnoDB, PARTITION p20240608 VALUES LESS THAN (739411) ENGINE = InnoDB, PARTITION p20240609 VALUES LESS THAN (739412) ENGINE = InnoDB, PARTITION p20240610 VALUES LESS THAN (739413) ENGINE = InnoDB, PARTITION p20240611 VALUES LESS THAN (739414) ENGINE = InnoDB, PARTITION p20240612 VALUES LESS THAN (739415) ENGINE = InnoDB, PARTITION p20240613 VALUES LESS THAN (739416) ENGINE = InnoDB, PARTITION p20240614 VALUES LESS THAN (739417) ENGINE = InnoDB, PARTITION p20240615 VALUES LESS THAN (739418) ENGINE = InnoDB, PARTITION p20240616 VALUES LESS THAN (739419) ENGINE = InnoDB, PARTITION p20240617 VALUES LESS THAN (739420) ENGINE = InnoDB, PARTITION p20240618 VALUES LESS THAN (739421) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) 2. Insert Data insert into t values(null,1,'abc','2024-06-12'),(null,2,'abc','2024-06-12'),(null,3,'abc','2024-06-12'),(null,1,'bcd','2024-06-11'),(null,2,'bcd','2024-06-11'),(null,2,'bcd','2024-06-10'); 3.SELECT SQL a.cases updatting count info correctly not as expected Range query using primary or secondary index： select * from t where id<=3; select * from t where id in (1,2); b. cases updating count info correctly equal condition: select * from t where id=1; full table scan: SELECT * FROM t; before and after executing above select sqls ,observe the count info changes using the follow sqls: Replace 'testdb' with your own db name please. select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where object_name='t' and object_schema='testdb'; select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,COUNT_STAR,COUNT_READ,COUNT_WRITE,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where object_name='t' and object_schema='testdb';