Bug #115298 partition table's io wait summary is not updated correct in PERFORMANCE_SCHEMA
Submitted: 12 Jun 10:21 Modified: 12 Jun 13:05
Reporter: Luodan Zhang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[12 Jun 10:21] Luodan Zhang
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';
[12 Jun 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 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 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 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 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 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 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 13:05] Luodan Zhang
Okay,I understand.
Thank you for your reply.