| Bug #106375 | The behavior of innodb_parallel_read_threads is different in version 8.0.27. | ||
|---|---|---|---|
| Submitted: | 4 Feb 2022 6:00 | Modified: | 6 Feb 2022 11:55 |
| Reporter: | HyunHo JUNG | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb_parallel_read_threads | ||
[4 Feb 2022 6:01]
HyunHo JUNG
#Test:1 - version 8.0.23 mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.23 | +-----------+ 1 row in set (0.00 sec) mysql> set local innodb_parallel_read_threads = 1; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (14.17 sec) mysql> explain select count(*) from sbtest1 force index(primary); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9876103 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(primary); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (14.40 sec) mysql> set local innodb_parallel_read_threads = 2; Query OK, 0 rows affected (0.01 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9876103 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (10.97 sec) mysql> explain select count(*) from sbtest1 force index(PRIMARY); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9870345 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(PRIMARY); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.93 sec) mysql> set local innodb_parallel_read_threads = 6; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9870345 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.29 sec) mysql> explain select count(*) from sbtest1 force index(primary); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9876103 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(primary); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.71 sec) As described in the feature, a higher number than innodb_parallel_read_threads=1 performs faster
[4 Feb 2022 6:01]
HyunHo JUNG
#Test:2 - version 8.0.25 mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.25 | +-----------+ 1 row in set (0.00 sec) mysql> set local innodb_parallel_read_threads = 1; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9870345 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (14.45 sec) mysql> explain select count(*) from sbtest1 force index(primary); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9870345 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(primary); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (14.12 sec) mysql> set local innodb_parallel_read_threads = 2; Query OK, 0 rows affected (0.01 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9870345 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.02 sec) mysql> explain select count(*) from sbtest1 force index(PRIMARY); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(PRIMARY); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.87 sec) mysql> set local innodb_parallel_read_threads = 6; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.32 sec) mysql> explain select count(*) from sbtest1 force index(primary); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(primary); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.77 sec)
[4 Feb 2022 6:01]
HyunHo JUNG
#Test:3 - version 8.0.26 mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.26 | +-----------+ 1 row in set (0.00 sec) mysql> set local innodb_parallel_read_threads = 1; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (14.21 sec) mysql> explain select count(*) from sbtest1 force index(primary); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(primary); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (14.41 sec) mysql> set local innodb_parallel_read_threads = 2; Query OK, 0 rows affected (0.01 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.02 sec) mysql> explain select count(*) from sbtest1 force index(PRIMARY); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(PRIMARY); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.87 sec) mysql> set local innodb_parallel_read_threads = 6; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.32 sec) mysql> explain select count(*) from sbtest1 force index(primary); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9869282 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(primary); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.21 sec)
[4 Feb 2022 6:02]
HyunHo JUNG
#Test:4 - version 8.0.27 mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.27 | +-----------+ 1 row in set (0.00 sec) mysql> set local innodb_parallel_read_threads = 1; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9871370 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (5.74 sec) <---!!!!!!! mysql> select count(*) from sbtest1 force index(PRIMARY); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (6.35 sec) <---!!!!!!! mysql> set local innodb_parallel_read_threads = 2; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9871370 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.86 sec) mysql> explain select count(*) from sbtest1 force index(PRIMARY); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9871370 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(PRIMARY); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.76 sec) set local innodb_parallel_read_threads = 6; Query OK, 0 rows affected (0.00 sec) mysql> explain select count(*) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9871370 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.78 sec) mysql> explain select count(*) from sbtest1 force index(PRIMARY); +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | PRIMARY | 4 | NULL | 9871370 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from sbtest1 force index(PRIMARY); +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (9.34 sec) Only in 8.0.27 innodb_parallel_read_threads=1 works faster. The four environments tested had the same key parameters. This test was confirmed while separately testing only the "innodb_parallel_read_threads" parameter while checking the "InnoDB Parallel Threads for Online DDL Operations" feature added in 8.0.27. ref. https://blogs.oracle.com/mysql/post/mysql80-innodb-parallel-threads-ddl It was confirmed that index creation was faster when the number of threads was higher. However, I have my doubts only about the value of the innodb_parallel_read_threads parameter related to the read process. Thanks
[4 Feb 2022 13:55]
MySQL Verification Team
Hi Mr. JUNG, Thank you for your bug report. However, this is not a bug. First of all, since InnoDB is (among other things) a MVCC engine, the query of the type: select count(*) from table; has to scan all rows of the table, without using any indices. This is all documented in our Reference Manual. There are only two ways to speed up the process. One is to force the index , which works better in the latest 8.0 releases. The other way is to get an instant, but approximate, value from one particular I_S table, which is also well described in our Reference Manual. The fact that 8.0.27 uses indices better than the older releases, when the index is forced is actually an improvement and not a bug. All our releases are meant only for fixing bugs and introducing small improvements, while the crux of the code remains the same. Code is drastically changed only when you move between versions, like 5.7 to 8.0, or in future from 8.0 to 8.1 (or 9.0). Hence , this is not a bug. However, this is very good news for our Development team.
[6 Feb 2022 11:55]
HyunHo JUNG
Hi First of all, thank you for quickly checking my report. I think my explanation was a little lacking. Let me explain again. Could you please take a look? Changes Note of 8.0.14 has the following contents https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-14.html InnoDB: InnoDB now supports parallel clustered index reads, which can improve CHECK TABLE performance. This feature does not apply to secondary index scans. The innodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads to occur. The default value is 4. The actual number of threads used to perform a parallel clustered index read is determined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller. And the worklog also describes it as follows. https://dev.mysql.com/worklog/task/?id=11720 TAB : Requirements FR1: SELECT COUNT(*) FROM TABLE T; will scan the index in parallel only if the scan is a non-locking scan and --innodb-parallel-read-threads > 1. Otherwise it will fallback to the old row by row scan. So I might not use clustered index, so I used force index() hint SQL: select count(*) from sbtest1 force index(PRIMARY); And as shown in the worklog, I performed the COUNT query in the same way. WL#11720 : SELECT COUNT(*) FROM TABLE T; And to check if clustered index is used, I checked the SQL execution plan (explain select..) The test results are summarized in a table. | version | innodb_parallel_read_threads | threads=1 - elapsed time(sec) | innodb_parallel_read_threads | threads=6 - elapsed time(sec)| |---------|------------------------------|-------------------------------|------------------------------|------------------------------| | 8.0.23 | 1 | 14.4 | 6 | 9.71 | | 8.0.25 | 1 | 14.12 | 6 | 9.77 | | 8.0.26 | 1 | 14.41 | 6 | 8.21 | | 8.0.27 | 1 | 6.35 | 6 | 9.34 | When innodb_parallel_read_threads=6, the test result execution time was 9.x seconds. And versions 8.0.23, 8.0.25, and 8.0.26, when innodb_parallel_read_threads=1, it was recorded in 14 seconds, and in 8.0.27 only, it was recorded in 6 seconds. what i think is weird is that in Ver 8.0.27 only, thread=1 is faster than thread=6. What I have read and understood the 2 documents below is that using parallel threads will make it faster https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-14.html https://dev.mysql.com/worklog/task/?id=11720 So I think it's odd that Non-parallel threads (value=1) are faster than parallel threads(value greater than 1) only in 8.0.27. Thanks
[7 Feb 2022 13:27]
MySQL Verification Team
Hi Mr. JUNG, Thank you for the feedback. However, these results are expectable, since many changes had to be made to the parallel reads of the index. Particularly, for the parallel index scans. This is all describe in the Release Notes, that are regularly published on dev.mysql.com. Also, your example is not good. Counting number of rows in InnoDB table is a long process, so you are much better of using data from the I_S schema. This is described in our manual. We would be willing to analyse any digression in the parallel reads on the secondary indices in some InnoDB table.
Description: Hi While checking innodb_parallel_read_threads added in version 8.0.14, I report behavior that is different from other versions in 8.0.27. First, below is the description of the table used. mysql> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec) Table Record Count : 10000000 The innodb_parallel_read_threads values were tested with 1,2,6 The cpu count of the test system is 6 How to repeat: In version 8.0.27, execution to count query while changing the value of "innodb_parallel_read_threads"