| Bug #105486 | sys.innodb_buffer_stats_by_table doesn't work with partitioned tables | ||
|---|---|---|---|
| Submitted: | 7 Nov 2021 13:31 | Modified: | 7 Nov 2021 21:11 |
| Reporter: | yi qian | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: SYS Schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | sys.innodb_buffer_stats_by_table； sys.x$innodb_buffer_stats_by_table; partitione | ||
[7 Nov 2021 16:06]
MySQL Verification Team
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p0 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p0 */
allocated: 16.00 KiB
data: 90 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 3
1 row in set (0,02 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1%'\G
Empty set (0,03 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p0 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p0 */
allocated: 16.00 KiB
data: 90 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 3
1 row in set (0,02 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p1 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p1 */
allocated: 16.00 KiB
data: 0 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 0
1 row in set (0,03 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p2 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p2 */
allocated: 16.00 KiB
data: 0 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 0
1 row in set (0,03 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p3 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p3 */
allocated: 16.00 KiB
data: 0 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 0
1 row in set (0,03 sec)
[7 Nov 2021 16:07]
MySQL Verification Team
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p0 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p0 */
allocated: 16.00 KiB
data: 90 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 3
1 row in set (0,02 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1%'\G
Empty set (0,03 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p0 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p0 */
allocated: 16.00 KiB
data: 90 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 3
1 row in set (0,02 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p1 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p1 */
allocated: 16.00 KiB
data: 0 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 0
1 row in set (0,03 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p2 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p2 */
allocated: 16.00 KiB
data: 0 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 0
1 row in set (0,03 sec)
mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1 /* Partition p3 */'\G
*************************** 1. row ***************************
object_schema: testdb1
object_name: t1 /* Partition p3 */
allocated: 16.00 KiB
data: 0 bytes
pages: 1
pages_hashed: 0
pages_old: 0
rows_cached: 0
1 row in set (0,03 sec)
mysql> show variables like "%version%";
+--------------------------+-------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------+
| admin_tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999 |
| innodb_version | 8.0.27 |
| original_server_version | 999999 |
| protocol_version | 10 |
| replica_type_conversions | |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 8.0.27 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.11 |
+--------------------------+-------------------------------+
13 rows in set (0,01 sec)
mysql>
[7 Nov 2021 21:11]
MySQL Verification Team
Thank you for the bug report. Please see prior comments.
Description: View sys.innodb_buffer_stats_by_table makes normal statistics for non partitioned tables and doesn't work with partitioned tables. The same problem also exists on view sys.x$innodb_buffer_stats_by_table. How to repeat: 1.Prepare environment： mysql> create database testdb1; Query OK, 1 row affected (0.01 sec) mysql> use testdb1; Database changed 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> 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 2. For Non-partitioned table: mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't'\G *************************** 1. row *************************** object_schema: testdb object_name: t allocated: 16.00 KiB data: 58 bytes pages: 1 pages_hashed: 0 pages_old: 1 rows_cached: 2 1 row in set (2 min 35.52 sec) 3. For partitioned table: mysql> select * from sys.innodb_buffer_stats_by_table where object_name = 't1'\G Empty set (2 min 38.19 sec)