Debian Codegraph @

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

raw
1 # Online DDL operations that rebuild the table write temporary sort files
2 # to the MySQL temporary directory during index creation. Each temporary
3 # sort file is corresponds to a secondary index in the table.
4 # This test tracks the IOs on these temp sort files during the table rebuild
5 # operation. Aim is to detect any change from the current numbers.
6 # Increase in the numbers must be justified, reducing the numbers should be
7 # welcomed.
8
9 --source include/have_nodebug.inc
10 --source include/big_test.inc
11 --source include/not_windows.inc
12
13 # By default push build system uses tmpfs to run the tests. We need to
14 # create the temp files on the real file system to be able to track the temp
15 # files usage. On MacOS, /var is symlinked to /private/var.
16 SET innodb_tmpdir='/var/tmp';
17
18 CREATE DATABASE test_db;
19
20 --DELIMITER //
21
22 CREATE PROCEDURE test_db.check_read_write_io_threshold(IN threshold DOUBLE)
23 BEGIN
24 SELECT
25 SUM_NUMBER_OF_BYTES_READ < threshold AS is_total_read_less_than_threshold,
26 SUM_NUMBER_OF_BYTES_WRITE < threshold AS is_total_written_less_than_threshold,
27 ABS(SUM_NUMBER_OF_BYTES_READ - SUM_NUMBER_OF_BYTES_WRITE) <=
28 (10 * 1024 * 1024) AS is_read_write_within_tolerance
29 FROM performance_schema.file_summary_by_instance
30 WHERE file_name LIKE '%/var/tmp/Innodb Merge Temp File';
31 END //
32
33 --DELIMITER ;
34
35 --echo
36 --echo # Prepare the table with 18 non-clustered indexes
37 --exec $MYSQL test < $MYSQLTEST_VARDIR/std_data/bug36444172/dump.sql
38 SELECT COUNT(*) FROM test_db.test_table;
39
40 --echo
41 SHOW CREATE TABLE test_db.test_table;
42
43 let $default_ddl_threads=`SELECT @@innodb_ddl_threads`;
44
45 --echo #
46 --echo # Test#1 : Rebuild the table with default settings
47 --echo #
48
49 --echo
50 --echo # Reset the PFS table
51 # To measure the temp sort files stats of the DDL operatation next.
52 TRUNCATE `performance_schema`.`file_summary_by_instance`;
53
54 --echo
55 --echo # Rebuild the table.
56 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
57
58 --echo
59 --echo # check the read/write IO on the temp sort files with default settings
60 CALL test_db.check_read_write_io_threshold(2.15 * 1024 * 1024 * 1024);
61
62 --echo #
63 --echo # Test#2 : Increase the DDL threads, IOs must remain as they were before
64 --echo #
65
66 --echo # Double the DDL threads.
67 SET innodb_ddl_threads=8;
68
69 --echo # Reset the PFS table
70 TRUNCATE `performance_schema`.`file_summary_by_instance`;
71
72 --echo # Rebuild the table.
73 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
74
75 CALL test_db.check_read_write_io_threshold(2.15 * 1024 * 1024 * 1024);
76
77
78 --echo #
79 --echo # Test#3 : Reduce the DDL threads, IOs must remain as they were before.
80 --echo #
81
82 --echo # Use one DDL thread
83 SET innodb_ddl_threads=1;
84
85 --echo # Reset the PFS table
86 TRUNCATE `performance_schema`.`file_summary_by_instance`;
87
88 --echo # Rebuild the table.
89 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
90
91 CALL test_db.check_read_write_io_threshold(2.15 * 1024 * 1024 * 1024);
92
93 --echo #
94 --echo # Test#4 : Increase the DDL Buffer size, IO should be reduced this time
95 --echo #
96
97 --echo # Set buffer large enough as would have been the case prior to WL#14283
98 --echo # ddl_threads(1) * ddl_buffer_size(18MB) = 18 MB
99 SET innodb_ddl_buffer_size=18874368;
100
101 --echo # Reset the PFS table
102 TRUNCATE `performance_schema`.`file_summary_by_instance`;
103
104 --echo # Rebuild the table.
105 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
106
107 CALL test_db.check_read_write_io_threshold(1.15 * 1024 * 1024 * 1024);
108
109 --echo # Reset to default
110 eval SET innodb_ddl_threads=$default_ddl_threads;
111
112 --echo #
113 --echo # Test#5 : Reduce the DDL Buffer size, IO should increase again
114 --echo #
115
116 --echo # Set it 3MB, that is ddl_threads(4) * ddl_buffer_size(3MB) = 12MB
117 SET innodb_ddl_buffer_size=3145728;
118
119 --echo # Reset the PFS table
120 TRUNCATE `performance_schema`.`file_summary_by_instance`;
121
122 --echo # Rebuild the table.
123 ALTER TABLE test_db.test_table ENGINE INNODB, ALGORITHM=INPLACE;
124
125 CALL test_db.check_read_write_io_threshold(1.75 * 1024 * 1024 * 1024);
126
127 --echo
128 DROP DATABASE test_db;