commit fad17855775486f99a4716e79ba177c3efbf2d66
Author: zhongbei.yk <zhongbei.yk@alibaba-inc.com>
Date: Tue Apr 2 11:05:51 2024 +0800
Bug#114154 Parallel read degrades to single-thread read
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.
See bug#114154.
Analysis
========
In the function ha_innopart::records(ha_rows *num_rows), if the local
variable n_threads has a return value of 1, the parallel read will not
be executed, but the s_active_threads variable will be increased by one
and
will bot 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.
Fix
===
When the s_active_threads variable has already been increased but
parallel read partition table is not executed,
the s_active_threads variable needs to be released.
diff --git a/mysql-test/suite/innodb/r/innodb_bug114154.result b/mysql-test/suite/innodb/r/innodb_bug114154.result
new file mode 100644
index 00000000000..8b18ee267b0
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb_bug114154.result
@@ -0,0 +1,68 @@
+#####################################################################
+# 1. Prepare
+#####################################################################
+# Create a normal table
+CREATE TABLE t1(
+class INT,
+id INT,
+title VARCHAR(100),
+PRIMARY KEY(class)
+) ENGINE=InnoDB;
+# Create a partition table
+CREATE TABLE t2(
+class INT,
+id INT,
+title VARCHAR(100),
+PRIMARY KEY(class)
+) ENGINE=InnoDB
+PARTITION BY RANGE(class) (
+PARTITION p0 VALUES LESS THAN (5000),
+PARTITION p1 VALUES LESS THAN (10000),
+PARTITION p2 VALUES LESS THAN (15000));
+# Create a procedure to insert data
+CREATE PROCEDURE populate_t1_t2()
+BEGIN
+DECLARE i int DEFAULT 1;
+START TRANSACTION;
+WHILE (i <= 10000) DO
+INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
+INSERT INTO t2 VALUES (i, i, CONCAT('a', i));
+SET i = i + 1;
+END WHILE;
+COMMIT;
+END |
+# Create a procedure to repeat execute 'select count(*) from partition table'
+CREATE PROCEDURE select_count_partition_table()
+BEGIN
+DECLARE i int DEFAULT 1;
+WHILE (i <= 256) DO
+select count(*) from t2;
+SET i = i + 1;
+END WHILE;
+END |
+# Load data
+CALL populate_t1_t2();
+#####################################################################
+# 2. Repeat sql 'select count(*) from t2;' for 256 times.
+#####################################################################
+set innodb_parallel_read_threads = 1;
+call select_count_partition_table();
+#######################################################################
+# 3. Parallel read will degrade to single thread read before this patch
+#######################################################################
+set innodb_parallel_read_threads = 16;
+set global DEBUG="+d,die_if_not_parallel_read";
+select count(*) from t1;
+count(*)
+10000
+select count(*) from t2;
+count(*)
+10000
+set global DEBUG="-d,die_if_not_parallel_read";
+#####################################################################
+# 4. Cleanup
+#####################################################################
+DROP PROCEDURE populate_t1_t2;
+DROP PROCEDURE select_count_partition_table;
+DROP table t1;
+DROP table t2;
diff --git a/mysql-test/suite/innodb/t/innodb_bug114154.test b/mysql-test/suite/innodb/t/innodb_bug114154.test
new file mode 100644
index 00000000000..d3fc1767f45
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb_bug114154.test
@@ -0,0 +1,100 @@
+#
+# Bug#114154 parallel read degrades to single thread read
+#
+# After a partition table is read in parallel for many times, the parallel
+# read will degrade to single thread read. This test repeat the bug.
+#
+# ==== Implementation ====
+# 1. Prepare.
+# 2. Do multiple parallel read on a partitioned table
+# under innodb_parallel_read_threads = 1 to cause parallel threads leak.
+# 3. Do parallel read under innodb_parallel_read_threads = 16, it
+# will degrade to single thread read.
+# 4. cleanup
+#
+
+--source include/have_debug.inc
+
+--echo #####################################################################
+--echo # 1. Prepare
+--echo #####################################################################
+--echo # Create a normal table
+CREATE TABLE t1(
+ class INT,
+ id INT,
+ title VARCHAR(100),
+ PRIMARY KEY(class)
+) ENGINE=InnoDB;
+
+--echo # Create a partition table
+CREATE TABLE t2(
+ class INT,
+ id INT,
+ title VARCHAR(100),
+ PRIMARY KEY(class)
+) ENGINE=InnoDB
+PARTITION BY RANGE(class) (
+PARTITION p0 VALUES LESS THAN (5000),
+PARTITION p1 VALUES LESS THAN (10000),
+PARTITION p2 VALUES LESS THAN (15000));
+
+--echo # Create a procedure to insert data
+DELIMITER |;
+CREATE PROCEDURE populate_t1_t2()
+BEGIN
+ DECLARE i int DEFAULT 1;
+
+ START TRANSACTION;
+ WHILE (i <= 10000) DO
+ INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
+ INSERT INTO t2 VALUES (i, i, CONCAT('a', i));
+ SET i = i + 1;
+ END WHILE;
+ COMMIT;
+END |
+
+--echo # Create a procedure to repeat execute 'select count(*) from partition table'
+CREATE PROCEDURE select_count_partition_table()
+BEGIN
+ DECLARE i int DEFAULT 1;
+ WHILE (i <= 256) DO
+ select count(*) from t2;
+ SET i = i + 1;
+ END WHILE;
+END |
+DELIMITER ;|
+
+-- disable_result_log
+--echo # Load data
+CALL populate_t1_t2();
+-- enable_result_log
+
+--echo #####################################################################
+--echo # 2. Repeat sql 'select count(*) from t2;' for 256 times.
+--echo #####################################################################
+set innodb_parallel_read_threads = 1;
+
+-- disable_result_log
+call select_count_partition_table();
+-- enable_result_log
+
+--echo #######################################################################
+--echo # 3. Parallel read will degrade to single thread read before this patch
+--echo #######################################################################
+set innodb_parallel_read_threads = 16;
+
+set global DEBUG="+d,die_if_not_parallel_read";
+
+select count(*) from t1;
+
+select count(*) from t2;
+
+set global DEBUG="-d,die_if_not_parallel_read";
+
+--echo #####################################################################
+--echo # 4. Cleanup
+--echo #####################################################################
+DROP PROCEDURE populate_t1_t2;
+DROP PROCEDURE select_count_partition_table;
+DROP table t1;
+DROP table t2;
\ No newline at end of file
diff --git a/storage/innobase/handler/ha_innopart.cc b/storage/innobase/handler/ha_innopart.cc
index abcc2965144..355bc0ac101 100644
--- a/storage/innobase/handler/ha_innopart.cc
+++ b/storage/innobase/handler/ha_innopart.cc
@@ -3200,6 +3200,9 @@ int ha_innopart::records(ha_rows *num_rows) {
if (dict_table_is_discarded(m_prebuilt->table)) {
ib_senderrf(ha_thd(), IB_LOG_LEVEL_ERROR, ER_TABLESPACE_DISCARDED,
m_prebuilt->table->name.m_name);
+ /* When parallel read is not executed, it needs to be released. */
+ Parallel_reader::release_threads(n_threads);
+
*num_rows = HA_POS_ERROR;
return (HA_ERR_NO_SUCH_TABLE);
}
@@ -3230,6 +3233,9 @@ int ha_innopart::records(ha_rows *num_rows) {
of the rest of the function is neglectable for each partition.
So no current reason for optimizing this further. */
+ /* When parallel read is not executed, it needs to be released. */
+ Parallel_reader::release_threads(n_threads);
+
for (uint i = m_part_info->get_first_used_partition(); i < m_tot_parts;
i = m_part_info->get_next_used_partition(i)) {
set_partition(i);
diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
index 9902acd3243..0d0a776daff 100644
--- a/storage/innobase/row/row0mysql.cc
+++ b/storage/innobase/row/row0mysql.cc
@@ -4602,6 +4602,8 @@ dberr_t row_scan_index_for_mysql(row_prebuilt_t *prebuilt, dict_index_t *index,
skip_parallel_read:
#endif /* UNIV_DEBUG */
+ DBUG_EXECUTE_IF("die_if_not_parallel_read", ut_error;);
+
bool contains_null;
rec_t *rec = nullptr;
ulint matched_fields;