Description:
When a simple query executed using primary key reading more number of pages into buffer pool in 8.0 compared to 5.7.
We have noticed that the query running in mysql 8.0.34 is slower compared to the same query executed against same data set on 5.7.43 version. The difference in query time observed only when the query executed for the first time. Also, we have buffer pool warmup disabled.
When the query executed for the first time, its reading more number of pages into disk in 8.0 compared 5.7 which looks like main reason for slowness.
In specific, 8.0 reading all pages in the tables to serve the query but 5.7 reading less number of pages to serve same query.
How to repeat:
Steps:-
1. Consider to load approx 1 GB of data into a table using sysbench.
2. Now, restart the database with buffer pool warmup disabled.
3. Ensure that there are no pages related to the particular table inside buffer pool by querying innodb_buffer_page table in information_schema database.
4. Finally execute the query and you can see that query on 8.0 takes long time compared 5.7.
5. Now, recheck the number of pages read into buffer pool and you can notice 8.0 reading lot more pages than 5.7.
Additional details:-
57 (my.cnf)
[mysql57@testbox ~]$ cat mysql5743/my.cnf
[mysqld]
server-id=5743
user=mysql57
port=5743
log-bin = mysql-logbin
socket=/tmp/mysql5743.sock
datadir=/home/mysql57/mysql5743/data
basedir=/home/mysql57/mysql5743
performance_schema=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
skip_slave_start
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF
mysql> show create database sbtest;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest.sbtest100\G
*************************** 1. row ***************************
Table: sbtest100
Create Table: CREATE TABLE `sbtest100` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) CHARACTER SET latin1 NOT NULL DEFAULT '',
`pad` char(60) CHARACTER SET latin1 NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_100` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4194217 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show table status from sbtest like 'sbtest100'\G
*************************** 1. row ***************************
Name: sbtest100
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3936186
Avg_row_length: 234
Data_length: 921698304
Max_data_length: 0
Index_length: 68812800
Data_free: 4194304
Auto_increment: 4194217
Create_time: 2023-10-19 10:50:26
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)
mysql> select * from information_schema.innodb_sys_tables where name ='sbtest/sbtest100';
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
| 50 | sbtest/sbtest100 | 33 | 7 | 39 | Barracuda | Dynamic | 0 | Single |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+
1 row in set (0.00 sec)
mysql> select count(1) from information_schema.innodb_buffer_page where space=39;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.02 sec)
mysql> select count(id) from sbtest.sbtest100;
+-----------+
| count(id) |
+-----------+
| 4096001 |
+-----------+
1 row in set (0.92 sec)
mysql> select count(1) from information_schema.innodb_buffer_page where space=39;
+----------+
| count(1) |
+----------+
| 3671 |
+----------+
1 row in set (0.02 sec)
mysql> explain select count(id) from sbtest.sbtest100;
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | sbtest100 | NULL | index | NULL | k_100 | 4 | NULL | 3936186 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
80 (my.cnf)
[mysql80@testbox ~]$ cat mysql8034/my.cnf
[mysqld]
server-id=8034
user=mysql80
port=8034
log-bin = mysql-logbin
socket=/tmp/mysql8034.sock
datadir=/home/mysql80/mysql8034/data
basedir=/home/mysql80/mysql8034
performance_schema=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
skip_slave_start
innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF
log_error_verbosity=3
bind_address=*
mysql> show create database sbtest;
+----------+-------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------+
| sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest.sbtest100\G
*************************** 1. row ***************************
Table: sbtest100
Create Table: CREATE TABLE `sbtest100` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`pad` char(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_100` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4194217 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql> show create database sbtest;
+----------+-------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------+
| sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table sbtest.sbtest100\G
*************************** 1. row ***************************
Table: sbtest100
Create Table: CREATE TABLE `sbtest100` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`pad` char(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_100` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4194217 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql> show table status from sbtest like 'sbtest100'\G
*************************** 1. row ***************************
Name: sbtest100
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3936326
Avg_row_length: 234
Data_length: 921698304
Max_data_length: 0
Index_length: 68812800
Data_free: 7340032
Auto_increment: 4194217
Create_time: 2023-10-19 10:47:54
Update_time: NULL
Check_time: NULL
Collation: utf8mb3_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_tables where name ='sbtest/sbtest100';
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| 1064 | sbtest/sbtest100 | 33 | 7 | 2 | Dynamic | 0 | Single | 0 | 0 |
+----------+------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.02 sec)
mysql> select count(1) from information_schema.innodb_buffer_page where space=2;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)
mysql> select count(id) from sbtest.sbtest100;
+-----------+
| count(id) |
+-----------+
| 4096001 |
+-----------+
1 row in set (2.06 sec)
mysql> select count(1) from information_schema.innodb_buffer_page where space=2;
+----------+
| count(1) |
+----------+
| 7655 |
+----------+
1 row in set (0.03 sec)
mysql> explain select count(id) from sbtest.sbtest100;
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | sbtest100 | NULL | index | NULL | k_100 | 4 | NULL | 3936326 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
Description: When a simple query executed using primary key reading more number of pages into buffer pool in 8.0 compared to 5.7. We have noticed that the query running in mysql 8.0.34 is slower compared to the same query executed against same data set on 5.7.43 version. The difference in query time observed only when the query executed for the first time. Also, we have buffer pool warmup disabled. When the query executed for the first time, its reading more number of pages into disk in 8.0 compared 5.7 which looks like main reason for slowness. In specific, 8.0 reading all pages in the tables to serve the query but 5.7 reading less number of pages to serve same query. How to repeat: Steps:- 1. Consider to load approx 1 GB of data into a table using sysbench. 2. Now, restart the database with buffer pool warmup disabled. 3. Ensure that there are no pages related to the particular table inside buffer pool by querying innodb_buffer_page table in information_schema database. 4. Finally execute the query and you can see that query on 8.0 takes long time compared 5.7. 5. Now, recheck the number of pages read into buffer pool and you can notice 8.0 reading lot more pages than 5.7. Additional details:- 57 (my.cnf) [mysql57@testbox ~]$ cat mysql5743/my.cnf [mysqld] server-id=5743 user=mysql57 port=5743 log-bin = mysql-logbin socket=/tmp/mysql5743.sock datadir=/home/mysql57/mysql5743/data basedir=/home/mysql57/mysql5743 performance_schema=ON master_info_repository=TABLE relay_log_info_repository=TABLE skip_slave_start innodb_buffer_pool_dump_at_shutdown=OFF innodb_buffer_pool_load_at_startup=OFF mysql> show create database sbtest; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table sbtest.sbtest100\G *************************** 1. row *************************** Table: sbtest100 Create Table: CREATE TABLE `sbtest100` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) CHARACTER SET latin1 NOT NULL DEFAULT '', `pad` char(60) CHARACTER SET latin1 NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_100` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4194217 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show table status from sbtest like 'sbtest100'\G *************************** 1. row *************************** Name: sbtest100 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 3936186 Avg_row_length: 234 Data_length: 921698304 Max_data_length: 0 Index_length: 68812800 Data_free: 4194304 Auto_increment: 4194217 Create_time: 2023-10-19 10:50:26 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.01 sec) mysql> select * from information_schema.innodb_sys_tables where name ='sbtest/sbtest100'; +----------+------------------+------+--------+-------+-------------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+------------------+------+--------+-------+-------------+------------+---------------+------------+ | 50 | sbtest/sbtest100 | 33 | 7 | 39 | Barracuda | Dynamic | 0 | Single | +----------+------------------+------+--------+-------+-------------+------------+---------------+------------+ 1 row in set (0.00 sec) mysql> select count(1) from information_schema.innodb_buffer_page where space=39; +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.02 sec) mysql> select count(id) from sbtest.sbtest100; +-----------+ | count(id) | +-----------+ | 4096001 | +-----------+ 1 row in set (0.92 sec) mysql> select count(1) from information_schema.innodb_buffer_page where space=39; +----------+ | count(1) | +----------+ | 3671 | +----------+ 1 row in set (0.02 sec) mysql> explain select count(id) from sbtest.sbtest100; +----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest100 | NULL | index | NULL | k_100 | 4 | NULL | 3936186 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 80 (my.cnf) [mysql80@testbox ~]$ cat mysql8034/my.cnf [mysqld] server-id=8034 user=mysql80 port=8034 log-bin = mysql-logbin socket=/tmp/mysql8034.sock datadir=/home/mysql80/mysql8034/data basedir=/home/mysql80/mysql8034 performance_schema=ON master_info_repository=TABLE relay_log_info_repository=TABLE skip_slave_start innodb_buffer_pool_dump_at_shutdown=OFF innodb_buffer_pool_load_at_startup=OFF log_error_verbosity=3 bind_address=* mysql> show create database sbtest; +----------+-------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------------------------------------------+ | sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table sbtest.sbtest100\G *************************** 1. row *************************** Table: sbtest100 Create Table: CREATE TABLE `sbtest100` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', `pad` char(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_100` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4194217 DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec) mysql> show create database sbtest; +----------+-------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------------------------------------------+ | sbtest | CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table sbtest.sbtest100\G *************************** 1. row *************************** Table: sbtest100 Create Table: CREATE TABLE `sbtest100` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', `pad` char(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_100` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4194217 DEFAULT CHARSET=utf8mb3 1 row in set (0.00 sec) mysql> show table status from sbtest like 'sbtest100'\G *************************** 1. row *************************** Name: sbtest100 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 3936326 Avg_row_length: 234 Data_length: 921698304 Max_data_length: 0 Index_length: 68812800 Data_free: 7340032 Auto_increment: 4194217 Create_time: 2023-10-19 10:47:54 Update_time: NULL Check_time: NULL Collation: utf8mb3_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> select * from information_schema.innodb_tables where name ='sbtest/sbtest100'; +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1064 | sbtest/sbtest100 | 33 | 7 | 2 | Dynamic | 0 | Single | 0 | 0 | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ 1 row in set (0.02 sec) mysql> select count(1) from information_schema.innodb_buffer_page where space=2; +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.03 sec) mysql> select count(id) from sbtest.sbtest100; +-----------+ | count(id) | +-----------+ | 4096001 | +-----------+ 1 row in set (2.06 sec) mysql> select count(1) from information_schema.innodb_buffer_page where space=2; +----------+ | count(1) | +----------+ | 7655 | +----------+ 1 row in set (0.03 sec) mysql> explain select count(id) from sbtest.sbtest100; +----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest100 | NULL | index | NULL | k_100 | 4 | NULL | 3936326 | 100.00 | Using index | +----+-------------+-----------+------------+-------+---------------+-------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)