MySQL Bugs: #114154: Parallel read is slow after parition table is read in parallel

Bug #114154	Parallel read is slow after parition table is read in parallel
Submitted:	28 Feb 9:57	Modified:	14 Jun 14:18
Reporter:	Ke Yu (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After a partition table is read in parallel for many times, it becomes very slow to read a
common innodb table or a partition table in parallel.

How to repeat:
step 1. start mysqld with --innodb-buffer-pool-size=6442450944 --innodb-flush-method=O_DIRECT  --innodb-use-native-aio=on

step 2. use sysbench to prepare a table with size = 40000000:
sysbench oltp_read_write --tables=1 --table_size=40000000 --rand-type=uniform --threads=1 prepare

step 3. prepare a parition table and insert some data
mysql> CREATE TABLE t1(
    ->   id      INT,
    ->   PRIMARY KEY(id)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE(id) (
    -> PARTITION p0 VALUES LESS THAN (5000),
    -> PARTITION p1 VALUES LESS THAN (10000));
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER |
mysql> CREATE PROCEDURE populate_t1()
    -> BEGIN
    ->   DECLARE i int DEFAULT 1;
    -> 
    ->   WHILE (i <= 5000) DO
    ->     INSERT INTO t1 VALUES (i);
    ->     SET i = i + 1;
    ->   END WHILE;
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> 
mysql> call populate_t1();
Query OK, 1 row affected (1.06 sec)

step 4. set innodb_parallel_read_threads = 32, execute a select count(*) with table sbtest1. The time cost is 2.58s.
mysql> set innodb_parallel_read_threads = 32;
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (2.59 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (2.58 sec)

step 5. In another session, set innodb_parallel_read_threads = 1, execute a select count(*) in the partition table for 256 times. Then exit the session.
mysql> set innodb_parallel_read_threads = 1;
mysql> DELIMITER |
mysql> CREATE PROCEDURE select_count_t1()
    -> BEGIN
    ->   DECLARE i int DEFAULT 1;
    ->   WHILE (i <= 256) DO
    ->     select count(*) from t1;
    ->     SET i = i + 1;
    ->   END WHILE;
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call select_count_t1();
mysql> exit;
Bye

step 6. set innodb_parallel_read_threads = 32, execute a select count(*) with table sbtest1. The time cost is 23.89s.
mysql> set innodb_parallel_read_threads = 32;
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (24.81 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (23.89 sec)

Before and after step 5, the time cost of 'select count(*) from sbtest1' is 23.89s and 2.582, respectively.
parallel read is slowing down a lot and is invalid.

Suggested fix:
In the function ha_innopart::records(ha_rows *num_rows), if the local variable n_threads has a value of 1,
the parallel read  will not be executed, but the s_active_threads variable will be increased by one and will
not be released, resulting in leakage of the s_active_threads variable.

When the s_active_threads reaches 256, other sessions cannot obtain parallel read threads. Thus causing
parallel read is invalid. Subsequent count(*)\chekck table\parallel DDL statements can only be executed
single-threaded, thus slowing down. If we restart the mysqld, the s_active_threads variable can be reset
and parallel read can be valid.

In the code, the n_threads needs to be released in ha_innopart::records(ha_rows *num_rows) if the value is 1.

Hello Ke Yu,

Thank you for the report and test case.
I'm not seeing such huge diff i.e. 2.89s and 2.582s vs 24.81 and 23.89 sec but 2-3 X between first time and later one(13sec vs 27sec). I'm starting instance with default settings. Could you please attach configuration file from your instance if it is not on default? Thank you. Anyway, verifying for now.

regards,
Umesh

The config is --innodb-buffer-pool-size=6442450944 --innodb-flush-method=O_DIRECT  --innodb-use-native-aio=on,
all other parameters retain their default values.

I think the cpu\disk io performance may affect the result.
Can you share the time cost you execute 'select count(*) from sbtest1' before and after step 5 ?

The main point I want to make is that in this case parallel read will not be able to get threads, that is,
it will invalid.

Thank you for the details.
Before and after the 5th step in my environment:

--
mysql> set innodb_parallel_read_threads = 32;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (13.97 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (13.53 sec)

--
mysql> set innodb_parallel_read_threads = 32;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (26.99 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (27.49 sec)

Hi, MySQL Verification Team

  Thank you for the details.

  I suspect that your and my different results are due to disk performance.

Hello Ke Yu,

Agree, before and after 5th steps:

mysql> set innodb_parallel_read_threads = 32;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (1.37 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (1.80 sec)

mysql> set innodb_parallel_read_threads = 32;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (16.32 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 40000000 |
+----------+
1 row in set (16.55 sec)

regards,
Umesh

This is my fix code, based on 8.0.36. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug114154.diff (application/octet-stream, text), 8.46 KiB.

Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.0.38, 8.4.1, and 9.0.0 releases, and here's the proposed changelog entry from the documentation team:

---
If a partition table was read with innodb_parallel_read_threads=1, read
performance greatly decreased from any table after 256 reads. InnoDB
behaved as if it reached the maximum capacity of parallel read threads
despite not using any.

Our thanks to Ke Yu for the contribution.
---

Thank you for the bug report.