MySQL Bugs: #103213: Poor performance while executing 'select count(*)' on a table

Bug #103213	Poor performance while executing 'select count(*)' on a table
Submitted:	6 Apr 2021 5:13	Modified:	6 Apr 2021 13:00
Reporter:	Niranjan R	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.17 - 8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	performance

Description:
Starting from MySQL 8.0.17, running 'select count(*)' on a big table is very slow.

== The query completes within 3-4 seconds in 5.7 and 8.0.16.

== Starting from 8.0.17, the query takes 1.5 to 2 minutes to complete

Explain plan in both the servers are same.
Profiling shows that the query is spending a huge time in "executing" state in 8.0.17+

How to repeat:
mysql> show create table test.dumm_table\G
*************************** 1. row ***************************
       Table: dumm_table
Create Table: CREATE TABLE `dumm_table` (
  `col1` bigint(20) NOT NULL AUTO_INCREMENT,
  `col2` datetime DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `col4` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col5` int(4) DEFAULT NULL,
  `col6` char(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col7` tinyint(4) DEFAULT NULL,
  `col8` int(11) DEFAULT NULL,
  `col9` tinyint(4) DEFAULT NULL,
  `col10` tinyint(4) DEFAULT NULL,
  `col11` int(11) DEFAULT NULL,
  `col12` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col13` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col14` int(11) DEFAULT NULL,
  `col15` tinyint(4) DEFAULT NULL,
  `col16` tinyint(4) DEFAULT NULL,
  `col17` tinyint(4) DEFAULT NULL,
  `col18` tinyint(4) DEFAULT NULL,
  `col19` tinyint(4) DEFAULT NULL,
  `col20` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col21` char(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col22` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col23` char(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col24` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col25` char(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col26` decimal(3,1) DEFAULT NULL,
  `col27` int(11) DEFAULT NULL,
  `col28` int(11) DEFAULT NULL,
  `col29` int(11) DEFAULT NULL,
  `col30` varchar(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col31` datetime DEFAULT NULL,
  `col32` datetime DEFAULT NULL,
  `col33` char(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col34` tinyint(4) DEFAULT NULL,
  `col35` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col36` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col37` varchar(98) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `col38` int(11) DEFAULT NULL,
  `col39` int(11) DEFAULT NULL,
  `col40` int(11) DEFAULT NULL,
  `col41` int(11) DEFAULT NULL,
  PRIMARY KEY (`col1`),
  KEY `index_2` (`col2`),
  KEY `index_3` (`col32`),
  KEY `index_4` (`col6`),
  KEY `index5` (`col3`,`col5`,`col11`),
  KEY `index6` (`col6`,`col3`,`col2`,`col8`,`col5`),
  KEY `index7` (`col6`,`col3`,`col2`,`col8`)
) ENGINE=InnoDB AUTO_INCREMENT=34078474 DEFAULT CHARSET=latin1

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.17    |
+-----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from test.dumm_table;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | dumm_table | NULL       | index | NULL          | index_2 | 6       | NULL | 32978504 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select count(*) from test.dumm_table;
+----------+
| count(*) |
+----------+
| 33554432 |
+----------+
1 row in set (2 min 4.67 sec)

mysql> show profiles;
+----------+--------------+--------------------------------------+
| Query_ID | Duration     | Query                                |
+----------+--------------+--------------------------------------+
|        1 | 124.66171775 | select count(*) from test.dumm_table |
+----------+--------------+--------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show profile for query 1;
+--------------------------------+------------+
| Status                         | Duration   |
+--------------------------------+------------+
| starting                       |   0.000049 |
| Executing hook on transaction  |   0.000002 |
| starting                       |   0.000006 |
| checking permissions           |   0.000004 |
| Opening tables                 |   0.000025 |
| init                           |   0.000005 |
| System lock                    |   0.000006 |
| optimizing                     |   0.000004 |
| statistics                     |   0.000017 |
| preparing                      |   0.000012 |
| executing                      | 124.661489 |
| end                            |   0.000011 |
| query end                      |   0.000010 |
| waiting for handler commit     |   0.000008 |
| closing tables                 |   0.000007 |
| freeing items                  |   0.000063 |
| cleaning up                    |   0.000002 |
+--------------------------------+------------+
17 rows in set, 1 warning (0.00 sec)

Able to reproduce even on 8.0.23 (latest version)
-----------------------------------
816> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.16    |
+-----------+
1 row in set (0.00 sec)

816> explain select count(*) from test.dumm_table;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | dumm_table | NULL       | index | NULL          | index_2 | 6       | NULL | 32978504 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

816> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

816> select count(*) from test.dumm_table;
+----------+
| count(*) |
+----------+
| 33554432 |
+----------+
1 row in set (3.70 sec)

816> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000050 |
| Executing hook on transaction  | 0.000002 |
| starting                       | 0.000006 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000023 |
| init                           | 0.000006 |
| System lock                    | 0.000006 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000016 |
| preparing                      | 0.000018 |
| executing                      | 0.000002 |
| Sending data                   | 3.699323 |
| end                            | 0.000012 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000008 |
| closing tables                 | 0.000015 |
| freeing items                  | 0.000061 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
18 rows in set, 1 warning (0.00 sec)

-----------------------------------
OBSERVATIONS MADE:
The DB server has 2vCPU and 8GB RAM

INNODB STATUS:
++ Row operations are very less in 8.0.17+
++ Row operations are high in 8.0.16

++ Buffer pool hit rate drops to a very low value as 5/1000 in 8.0.17+
++ Buffer pool hit rate is around 980/1000 in 8.0.16

COMPARING THE GLOBAL STATUS:
++ INNODB_ROWS_READ(delta) is equal to the number of rows in the table --> in 8.0.16
++ INNODB_ROWS_READ(delta) = 32 --> in 8.0.17

++ Handler_read_next(delta) is equal to the number of rows in the table --> in 8.0.16
++ Handler_read_next(delta) = 0 --> 8.0.17

++ Innodb_buffer_pool_pages_data and Innodb_buffer_pool_reads is 0 --> in 8.0.16
++ Innodb_buffer_pool_pages_data and Innodb_buffer_pool_reads is high for 8.0.17

Looks like some optimization in 8.0.17+ is leading to the issue.

Can you please confirm if this is expected?

Hello Niranjan R,

Thank you for the report and feedback.
In order to proceed further we need a reproducible test case to confirm the issue at our end. Could you please provide us with some test data to help us reproduce the bug(preferably logical dump taken using mysqldump etc) along with server's configuration file? If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-103213.zip) and upload one to sftp.oracle.com. Your Oracle Web account (niranjan28.june.96@gmail.com) and a client that supports SFTP are required in order to access the SFTP server. Please see the "Files" section of this bug page for more details.

regards,
Umesh

This is related to Bug#100597.  Even if EXPLAIN shows that an index will be used, a parallel table scan is actually used.  Hence, if the table is much larger than the index, count(*) will take much longer.  In this case, it also seems that while the index is cached in the database buffer, the table is not, so the new approach leads to increased I/O.

This is another related bug report: https://bugs.mysql.com/bug.php?id=99717

Uploaded the requested data as 'mysql-bug-data-103213-3.zip'.

Hi Mr. R,

After repeating the behaviour, we concluded that this is a serious performance regression.

However, this bug is a duplicate of the already verified bug. This one:

https://bugs.mysql.com/bug.php?id=99717

You can monitor the progress on this issue by subscribing to that bug report.

I encountered this issue too on 8.0.17, is this bug fixed?

Hi,

No, it is not yet fixed.

You can find more explanations in the original bug.