| 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; |