Bug #112767 SELECT COUNT(*) degraded performance on 8.0 compared to 5.7
Submitted: 19 Oct 2023 6:07 Modified: 16 Feb 2024 22:07
Reporter: Aristotle Po Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:8.0.34, 8.0.35 OS:Any
Assigned to: CPU Architecture:Any

[19 Oct 2023 6:07] Aristotle Po 
Description:
Query performance degraded for "SELECT COUNT(*) FROM <TABLE_NAME>" on 8.0 compared to 5.7.
We can see below that query1 performance remained almost same on both version. However, query2 which has 8 times the execution time on 8.0 than on 5.7. 

| 5.7.42-log | MySQL Community Server (GPL) |
mysql [localhost:5742] {msandbox} (test) > SHOW PROFILES;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.16019925 | SELECT COUNT(*) FROM T1 WHERE c > 0 |  query1
|        2 | 0.09918300 | SELECT COUNT(*) FROM T1             |  query2

| 8.0.34    | MySQL Community Server - GPL |
mysql [localhost:8034] {msandbox} (test) > SHOW PROFILES;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.16873475 | SELECT COUNT(*) FROM T1 WHERE c > 0 |  query1
|        2 | 0.77702675 | SELECT COUNT(*) FROM T1             |  query2

How to repeat:
############################################
# CREATE the table and insert 1 million records
############################################

CREATE DATABASE test;
use test;

CREATE TABLE `T1` (
  `a` bigint(20) NOT NULL AUTO_INCREMENT,
  `b` varchar(50) DEFAULT NULL,
  `c` datetime NOT NULL,
  `d` json DEFAULT NULL,
  `e` json DEFAULT NULL,
  `f` varchar(15) DEFAULT NULL,
  `g` varchar(50) DEFAULT NULL,
  `h` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `IDX_b` (`b`),
  KEY `IX_c` (`c`)
) ENGINE=InnoDB ;

DROP PROCEDURE IF EXISTS InsertData2;
DELIMITER //
CREATE PROCEDURE InsertData2(IN N INT, IN M BIGINT)
BEGIN
  DECLARE i BIGINT DEFAULT M;

  WHILE i < M + N DO
     INSERT INTO T1 (b,c,d,e,f,g,h) VALUES (
      RPAD('a', 50, 'a'),
      NOW(),
      CONCAT('{"key1":"', RPAD('a', 250, 'a'), '","key2":"', RPAD('a', 250, 'a'), '"}'),
      CONCAT('{"key1":"', RPAD('a', 750, 'a'), '","key2":"', RPAD('a', 750, 'a'), '"}'),
      RPAD('a', 15, 'a'),
      RPAD('a', 50, 'a'),
      'Active'
    );

    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS InsertData3;
DELIMITER //
CREATE PROCEDURE InsertData3(IN N INT)
BEGIN
  DECLARE i INT DEFAULT 0;

  WHILE i < N DO
    INSERT INTO T1(b,c,d,e,f,g,h) SELECT b,NOW(),d,e,f,g,h FROM T1 LIMIT 100000;
    SELECT i, count(*) from T1;
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

-- initial data insert
CALL InsertData2(9, 0);

-- for 100k records insert
CALL InsertData3(13);
INSERT INTO T1(b,c,d,e,f,g,h) SELECT b,NOW(),d,e,f,g,h FROM T1 LIMIT 26272;

-- for 900k records insert to complete 1 million record
CALL InsertData3(9);

############################################
# Test below queries on both 8.0 and 5.7
############################################
SELECT @@version, @@version_comment;
SET PROFILING = ON;
SELECT COUNT(*) FROM T1 WHERE c > 0;
SELECT COUNT(*) FROM T1;
SHOW PROFILES;

EXPLAIN SELECT COUNT(*) FROM T1 WHERE c > 0;
EXPLAIN SELECT COUNT(*) FROM T1;

SELECT database_name, table_name, index_name, SUM(stat_value) pages,
       SUM(stat_value)*@@innodb_page_size/(1024*1024) size_MB
  FROM mysql.innodb_index_stats
 WHERE stat_name = 'size'
   AND database_name = 'test'
 GROUP BY database_name, table_name, index_name;

############################################
# Output 5.7
############################################
mysql [localhost:5742] {msandbox} (test) > SELECT @@version, @@version_comment;
+------------+------------------------------+
| @@version  | @@version_comment            |
+------------+------------------------------+
| 5.7.42-log | MySQL Community Server (GPL) |
+------------+------------------------------+
1 row in set (0.00 sec)

mysql [localhost:5742] {msandbox} (test) > SET PROFILING = ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:5742] {msandbox} (test) > SELECT COUNT(*) FROM T1 WHERE c > 0;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.16 sec)

mysql [localhost:5742] {msandbox} (test) > SELECT COUNT(*) FROM T1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.10 sec)

mysql [localhost:5742] {msandbox} (test) > SHOW PROFILES;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.16019925 | SELECT COUNT(*) FROM T1 WHERE c > 0 |
|        2 | 0.09918300 | SELECT COUNT(*) FROM T1             |
+----------+------------+-------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:5742] {msandbox} (test) > EXPLAIN SELECT COUNT(*) FROM T1 WHERE c > 0;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | T1    | NULL       | range | IX_c          | IX_c | 5       | NULL | 416935 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:5742] {msandbox} (test) > EXPLAIN SELECT COUNT(*) FROM T1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | T1    | NULL       | index | NULL          | IX_c | 5       | NULL | 833870 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:5742] {msandbox} (test) > SELECT database_name, table_name, index_name, SUM(stat_value) pages,
    ->        SUM(stat_value)*@@innodb_page_size/(1024*1024) size_MB
    ->   FROM mysql.innodb_index_stats
    ->  WHERE stat_name = 'size'
    ->    AND database_name = 'test'
    ->  GROUP BY database_name, table_name, index_name;
+---------------+------------+------------+--------+-----------+
| database_name | table_name | index_name | pages  | size_MB   |
+---------------+------------+------------+--------+-----------+
| test          | T1         | IDX_b      |   4083 |   63.7969 |
| test          | T1         | IX_c       |   1188 |   18.5625 |
| test          | T1         | PRIMARY    | 166144 | 2596.0000 |
+---------------+------------+------------+--------+-----------+
3 rows in set (0.00 sec)

############################################
# Output 8.0
############################################
mysql [localhost:8034] {msandbox} (test) > SELECT @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 8.0.34    | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > SET PROFILING = ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > SELECT COUNT(*) FROM T1 WHERE c > 0;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.17 sec)

mysql [localhost:8034] {msandbox} (test) > SELECT COUNT(*) FROM T1;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.78 sec)

mysql [localhost:8034] {msandbox} (test) > SHOW PROFILES;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.16873475 | SELECT COUNT(*) FROM T1 WHERE c > 0 |
|        2 | 0.77702675 | SELECT COUNT(*) FROM T1             |
+----------+------------+-------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > EXPLAIN SELECT COUNT(*) FROM T1 WHERE c > 0;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | T1    | NULL       | range | IX_c          | IX_c | 5       | NULL | 417036 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > EXPLAIN SELECT COUNT(*) FROM T1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | T1    | NULL       | index | NULL          | IX_c | 5       | NULL | 834073 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8034] {msandbox} (test) > SELECT database_name, table_name, index_name, SUM(stat_value) pages,
    ->        SUM(stat_value)*@@innodb_page_size/(1024*1024) size_MB
    ->   FROM mysql.innodb_index_stats
    ->  WHERE stat_name = 'size'
    ->    AND database_name = 'test'
    ->  GROUP BY database_name, table_name, index_name;
+---------------+------------+------------+--------+-----------+
| database_name | table_name | index_name | pages  | size_MB   |
+---------------+------------+------------+--------+-----------+
| test          | T1         | IDX_b      |   4083 |   63.7969 |
| test          | T1         | IX_c       |   1188 |   18.5625 |
| test          | T1         | PRIMARY    | 166272 | 2598.0000 |
+---------------+------------+------------+--------+-----------+
3 rows in set (0.00 sec)

Suggested fix:
Please have query optimizer performance for the "SELECT COUNT(*) FROM <TABLE_NAME>" on 8.0 same or better with 5.7 like for query "SELECT COUNT(*) FROM <TABLE_NAME> WHERE <COLUMN_NAME> > 0" which did not degrade.
[26 Oct 2023 7:50] MySQL Verification Team 
Hello Aristotle Po,

Thank you for the report and feedback.
Could you please share exact configuration files used for both 5.7/8.0 for the tests? I'm currently trying with default settings(but comparison will be not accurate since binary logging is off by default in 5.7 and is on by default in 8.0 and diff in charset as well). Thank you.

Sincerely,
Umesh
[26 Oct 2023 10:31] Aristotle Po 
my.cnf and global variables

Attachment: bug112767-my-cnf-5.7.txt (text/plain), 882 bytes.
[26 Oct 2023 10:35] Aristotle Po 
5.7 and 8.0 my.cnf and global variables in a zip file.

Attachment: my-cnf-and-global-variables.zip (application/zip, text), 22.71 KiB.
[26 Oct 2023 10:37] Aristotle Po 
Hi Umesh,
Please refer to the attached zip file.

Regards,
Aristotle
[26 Oct 2023 14:05] MySQL Verification Team 
Hello Aristotle Po,

Thank you for the requested details.

regards,
Umesh
[26 Oct 2023 14:06] MySQL Verification Team 
Related - Bug #97709
[27 Oct 2023 7:16] MySQL Verification Team 
Test results - 8.0.11+,  5.7.44

Attachment: 112767_5.7_8.0.results (application/octet-stream, text), 24.49 KiB.
[18 Jan 2024 19:52] Lucas Migliorini 
Hi team,

I have the same situation described by Aristotle Po.

Do you have any update or fix for this issue ?

The time is bigger when the content is bigger.

Mysql 8.0.34

MySQL [customer1]> SELECT COUNT(*) FROM table_name ;
+----------+
| COUNT(*) |
+----------+
| 55541365 |
+----------+
1 row in set (6 min 29.40 sec)

MySQL [customer1]> SELECT COUNT(*) FROM table_name WHERE id > 0 ;
+----------+
| COUNT(*) |
+----------+
| 55549130 |
+----------+
1 row in set (21.34 sec)

Regards,
[19 Jan 2024 5:11] Rahul Sisondia 
Posted by developer:
 
@ Lucas Migliorini

I am investigating this issue.
[23 Jan 2024 5:32] Rahul Sisondia 
Posted by developer:
 
[Updates]
So far I could pinpoint that problem was introduced in the 8.0.17 through WL#12978 InnoDB:Fix imbalance during parallel scan. 
This worklog reads ahead pages in advance to utilize the parallel_read_threads. The overall idea is novel, it reduces the query time for in-memory workload.
However, my suspicion is that it reads ahead pages either more than required or not optimally. This causes excessive read IO that slows down the query execution. 

I shall continue to investigate the problem. I may not able to be able to provide regular updates as I am juggling with multiple things at the moment.

In the interim, workarounds that we already know  : 
- Increase the buffer pool size and tune --innodb-parallel-read-threads
- If possible use the predicate
[23 Jan 2024 13:41] Lucas Migliorini 
Thank you for your reply.

We will wait for more updates. 

At this moment, we started to use PREDICATES as you suggested to avoid this issue.

We thought that increase the innodb_buffer_poll_size would be too high to handle the amount of data that we have in some situations.

Regards,

Lucas Migliorini
[5 Feb 2024 9:08] Rahul Sisondia 
Posted by developer:
 
The query reported in the bug uses the seconday index and WL#12978 enabled parallel scan for index scan which was not the case before. 
In other words, removing the following method which was added through this worklog brings the performance on par with 5.7 for such queries.

--- a/storage/innobase/handler/ha_innodb.h
+++ b/storage/innobase/handler/ha_innodb.h
@@ -227,10 +227,10 @@ class ha_innobase : public handler {

   int records(ha_rows *num_rows) override;

-  int records_from_index(ha_rows *num_rows, uint) override {
-    /* Force use of cluster index until we implement sec index parallel scan. */
-    return ha_innobase::records(num_rows);
-  }
[5 Feb 2024 13:26] Rahul Sisondia 
Posted by developer:
 
Note my previous comment is an observation not a fix :)
[16 Feb 2024 22:07] Philip Olson 
Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.0.37 and 8.4.0 releases, and here's the proposed changelog entry from the documentation team:

A SELECT COUNT(*) query that used a secondary index for scanning would
perform much slower than the same query did in MySQL 5.7.

Thank you for the bug report.
[16 Apr 2024 5:24] MySQL Verification Team 
Bug #100597 is marked as duplicate of this one
[17 Apr 2024 22:38] Philip Olson 
Posted by developer:
 
Release note updated and now reads as follows for both MySQL Bug #100597 and MySQL Bug #112767:

        MySQL no longer ignores the Optimizer hint to use a secondary
        index scan, which instead forced a clustered (parallel) index
        scan.