Debian Codegraph @

mysql-test/suite/innodb/r/alter_table_temp_files_io.result

raw
1 SET innodb_tmpdir='/var/tmp';
2 CREATE DATABASE test_db;
3 CREATE PROCEDURE test_db.check_read_write_io_threshold(IN threshold DOUBLE)
4 BEGIN
5 SELECT
6 SUM_NUMBER_OF_BYTES_READ < threshold AS is_total_read_less_than_threshold,
7 SUM_NUMBER_OF_BYTES_WRITE < threshold AS is_total_written_less_than_threshold,
8 ABS(SUM_NUMBER_OF_BYTES_READ - SUM_NUMBER_OF_BYTES_WRITE) <=
9 (10 * 1024 * 1024) AS is_read_write_within_tolerance
10 FROM performance_schema.file_summary_by_instance
11 WHERE file_name LIKE '%/var/tmp/Innodb Merge Temp File';
12 END //
13
14 # Prepare the table with 18 non-clustered indexes
15 SELECT COUNT(*) FROM test_db.test_table;
16 COUNT(*)
17 100000
18
19 SHOW CREATE TABLE test_db.test_table;
20 Table Create Table
21 test_table CREATE TABLE `test_table` (
22 `pkcol` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
23 `col1` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
24 `col2` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
25 `col3` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
26 `col4` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
27 `col5` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
28 `col6` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
29 `col7` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
30 `col8` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
31 `col9` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
32 `col10` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
33 `col11` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
34 `col12` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
35 `col13` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
36 `col14` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
37 `col15` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
38 `col16` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
39 `col17` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
40 `col18` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '',
41 PRIMARY KEY (`pkcol`),
42 UNIQUE KEY `uniq_idx1` (`col1`),
43 KEY `idx1` (`col2`),
44 KEY `idx2` (`col3`,`col2`),
45 KEY `idx3` (`col4`),
46 KEY `idx4` (`col5`,`col4`),
47 KEY `idx5` (`col6`,`col5`),
48 KEY `idx6` (`col7`,`col6`,`col5`),
49 KEY `idx7` (`col8`,`col7`,`col6`),
50 KEY `idx8` (`col9`,`col8`),
51 KEY `idx9` (`col10`,`col9`),
52 KEY `idx10` (`col11`,`col10`),
53 KEY `idx11` (`col12`,`col11`),
54 KEY `idx12` (`col13`,`col12`),
55 KEY `idx13` (`col14`,`col13`),
56 KEY `idx14` (`col15`,`col14`),
57 KEY `idx15` (`col16`,`col15`),
58 KEY `idx16` (`col17`,`col16`),
59 KEY `idx17` (`col18`,`col17`)
60 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
61 #
62 # Test#1 : Rebuild the table with default settings
63 #
64
65 # Reset the PFS table
66 TRUNCATE `performance_schema`.`file_summary_by_instance`;
67
68 # Rebuild the table.
69 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
70
71 # check the read/write IO on the temp sort files with default settings
72 CALL test_db.check_read_write_io_threshold(2.15 * 1024 * 1024 * 1024);
73 is_total_read_less_than_threshold is_total_written_less_than_threshold is_read_write_within_tolerance
74 1 1 1
75 #
76 # Test#2 : Increase the DDL threads, IOs must remain as they were before
77 #
78 # Double the DDL threads.
79 SET innodb_ddl_threads=8;
80 # Reset the PFS table
81 TRUNCATE `performance_schema`.`file_summary_by_instance`;
82 # Rebuild the table.
83 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
84 CALL test_db.check_read_write_io_threshold(2.15 * 1024 * 1024 * 1024);
85 is_total_read_less_than_threshold is_total_written_less_than_threshold is_read_write_within_tolerance
86 1 1 1
87 #
88 # Test#3 : Reduce the DDL threads, IOs must remain as they were before.
89 #
90 # Use one DDL thread
91 SET innodb_ddl_threads=1;
92 # Reset the PFS table
93 TRUNCATE `performance_schema`.`file_summary_by_instance`;
94 # Rebuild the table.
95 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
96 CALL test_db.check_read_write_io_threshold(2.15 * 1024 * 1024 * 1024);
97 is_total_read_less_than_threshold is_total_written_less_than_threshold is_read_write_within_tolerance
98 1 1 1
99 #
100 # Test#4 : Increase the DDL Buffer size, IO should be reduced this time
101 #
102 # Set buffer large enough as would have been the case prior to WL#14283
103 # ddl_threads(1) * ddl_buffer_size(18MB) = 18 MB
104 SET innodb_ddl_buffer_size=18874368;
105 # Reset the PFS table
106 TRUNCATE `performance_schema`.`file_summary_by_instance`;
107 # Rebuild the table.
108 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
109 CALL test_db.check_read_write_io_threshold(1.15 * 1024 * 1024 * 1024);
110 is_total_read_less_than_threshold is_total_written_less_than_threshold is_read_write_within_tolerance
111 1 1 1
112 # Reset to default
113 SET innodb_ddl_threads=4;
114 #
115 # Test#5 : Reduce the DDL Buffer size, IO should increase again
116 #
117 # Set it 3MB, that is ddl_threads(4) * ddl_buffer_size(3MB) = 12MB
118 SET innodb_ddl_buffer_size=3145728;
119 # Reset the PFS table
120 TRUNCATE `performance_schema`.`file_summary_by_instance`;
121 # Rebuild the table.
122 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
123 CALL test_db.check_read_write_io_threshold(1.75 * 1024 * 1024 * 1024);
124 is_total_read_less_than_threshold is_total_written_less_than_threshold is_read_write_within_tolerance
125 1 1 1
126
127 DROP DATABASE test_db;