Debian Codegraph @

mysql-test/suite/innodb/t/parallel_read_1.test

raw
1 # Bug#114154 parallel read degrades to single thread read
2 #
3 # After a partition table is read in parallel for many times(at least 256),
4 # the parallel read will degrade to single thread read.
5 #
6 # ==== Implementation ====
7 # 1. Prepare the data
8 # 2. Do 256 (max value of --innodb_parallel_read_threads) parallel reads on
9 # a partitioned table while innodb_parallel_read_threads = 1.
10 # It used to cause parallel threads leak before the fix.
11 # 3. Do parallel read under innodb_parallel_read_threads = 16 and ensure that
12 # it does not degrade to single thread read.
13 # 4. cleanup
14 #
15
16 --source include/have_debug.inc
17
18 --echo #####################################################################
19 --echo # 1. Prepare
20 --echo #####################################################################
21 --echo # Create a normal table
22 CREATE TABLE t1(
23 class INT,
24 id INT,
25 title VARCHAR(100),
26 PRIMARY KEY(class)
27 ) ENGINE=InnoDB;
28
29 --echo # Create a partition table
30 CREATE TABLE t2(
31 class INT,
32 id INT,
33 title VARCHAR(100),
34 PRIMARY KEY(class)
35 ) ENGINE=InnoDB
36 PARTITION BY RANGE(class) (
37 PARTITION p0 VALUES LESS THAN (5000),
38 PARTITION p1 VALUES LESS THAN (10000),
39 PARTITION p2 VALUES LESS THAN (15000));
40
41 --echo # Create a procedure to insert data
42 DELIMITER |;
43 CREATE PROCEDURE populate_t1_t2()
44 BEGIN
45 DECLARE i int DEFAULT 1;
46
47 START TRANSACTION;
48 WHILE (i <= 1000) DO
49 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
50 INSERT INTO t2 VALUES (i, i, CONCAT('a', i));
51 SET i = i + 1;
52 END WHILE;
53 COMMIT;
54 END |
55
56 --echo # Create a procedure to repeat execute 'select count(*) from partition table'
57 CREATE PROCEDURE select_count_partition_table()
58 BEGIN
59 DECLARE i int DEFAULT 1;
60 WHILE (i <= 256) DO
61 SELECT COUNT(*) FROM t2;
62 SET i = i + 1;
63 END WHILE;
64 END |
65 DELIMITER ;|
66
67 --disable_result_log
68 --echo # Load data
69 CALL populate_t1_t2();
70 --enable_result_log
71
72 --echo #####################################################################
73 --echo # 2. Repeat sql 'select count(*) from t2;' for 256 times.
74 --echo #####################################################################
75 SET SESSION innodb_parallel_read_threads = 1;
76
77 --disable_result_log
78 CALL select_count_partition_table();
79 --enable_result_log
80
81 --echo #######################################################################
82 --echo # 3. Parallel read will degrade to single thread read before this patch
83 --echo #######################################################################
84 SET SESSION innodb_parallel_read_threads = 16;
85
86 SET GLOBAL DEBUG="+d,ib_die_if_not_parallel_read";
87
88 SELECT COUNT(*) FROM t1;
89
90 SELECT COUNT(*) FROM t2;
91
92 SET GLOBAL DEBUG="-d,ib_die_if_not_parallel_read";
93
94 --echo #####################################################################
95 --echo # 4. Cleanup
96 --echo #####################################################################
97 DROP PROCEDURE populate_t1_t2;
98 DROP PROCEDURE select_count_partition_table;
99 DROP TABLE t1;
100 DROP TABLE t2;