Debian Codegraph @

mysql-test/suite/ndb_binlog/t/purger.test

raw
1 #
2 # Test ability of ndb binlog purger to remove rows from mysql.ndb_binlog_index
3 # by offloading the removal to purger thread.
4 #
5 # This is done by:
6 # 1. Showing configuration required by test
7 # 2. Create 9 binary log files
8 # 3. Add additional rows to ndb_binlog_index corresponding to the binary logs,
9 # the number of rows should exceed the configured ndb_log_rate_limit
10 # 4. Purging the binary logs, first 1 and then remaininig gradually
11 # 5. Check that rows has been removed from ndb_binlog_index
12
13 --source include/have_ndb.inc
14
15 # Turn on autocommit to isolate test from MySQL config
16 SET autocommit=1;
17
18 RESET MASTER;
19
20 --echo # Find physical binlog file name format as it varies between platforms
21 CREATE TABLE check_binlog_name (a int primary key) engine = NDB;
22 INSERT INTO check_binlog_name VALUES (1);
23 DROP TABLE check_binlog_name;
24 let $binlog_name =
25 `SELECT SUBSTRING(File, 1, LENGTH(File)-1)
26 FROM mysql.ndb_binlog_index ORDER BY epoch LIMIT 1`;
27 #echo binlog_name: $binlog_name;
28
29 RESET MASTER;
30
31
32 --echo # =======================================
33 --echo # 1. Showing configuration required by test
34 --echo # =======================================
35
36 --echo # Show that binlogging of NDB changes are on
37 SHOW VARIABLES LIKE 'log_bin';
38 SHOW VARIABLES LIKE 'ndb_log_bin';
39
40 --echo # Turn off binlogging statements in this file
41 SET sql_log_bin = OFF;
42
43 --echo # Turn off writing queries to query log
44 SET sql_log_off = ON;
45
46 --echo # Turn off using backslash as escape to avoid interfering with filename
47 SET sql_mode=(SELECT CONCAT(@@sql_mode,',NO_BACKSLASH_ESCAPES'));
48
49 --echo #
50 --echo # InnoDB adaptive flushing on
51 let $adaptive_flushing =
52 `SELECT VARIABLE_VALUE
53 FROM performance_schema.global_variables
54 WHERE VARIABLE_NAME = 'innodb_adaptive_flushing'`;
55 echo adaptive_flushing: $adaptive_flushing;
56
57 --echo #
58 --echo # Innodb buffer pool size
59 let $buffer_pool_size =
60 `SELECT VARIABLE_VALUE / 1024 / 1024
61 FROM performance_schema.global_variables
62 WHERE VARIABLE_NAME = 'innodb_buffer_pool_size'`;
63 echo buffer_pool_size: $buffer_pool_size MB;
64
65 --echo #
66 --echo # InnoDB redo log capacity controls how much can be changed without flush
67 let $redo_log_capacity =
68 `SELECT VARIABLE_VALUE / 1024 / 1024
69 FROM performance_schema.global_variables
70 WHERE VARIABLE_NAME = 'innodb_redo_log_capacity'`;
71 echo redo_log_capacity: $redo_log_capacity MB;
72
73 --echo #
74 --echo # The ndb binlog purger rate limit
75 let $purge_rate =
76 `SELECT VARIABLE_VALUE
77 FROM performance_schema.global_variables
78 WHERE VARIABLE_NAME = 'ndb_log_purge_rate'`;
79 echo purge_rate: $purge_rate;
80
81 --echo #
82 --echo # Show primary keys of mysql.ndb_binlog_index table
83 SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
84 WHERE TABLE_SCHEMA = 'mysql' AND
85 TABLE_NAME = 'ndb_binlog_index' AND
86 COLUMN_KEY = 'PRI'
87 ORDER BY ORDINAL_POSITION;
88
89 --echo #
90 --echo # Show columns of mysql.ndb_binlog_index table
91 SELECT COLUMN_NAME, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
92 WHERE TABLE_SCHEMA = 'mysql' AND
93 TABLE_NAME = 'ndb_binlog_index'
94 ORDER BY ORDINAL_POSITION;
95
96 # Read initial status counter values
97 let $purged_rows_before =
98 `SELECT VARIABLE_VALUE
99 FROM performance_schema.global_status
100 WHERE VARIABLE_NAME = 'ndb_log_purged_rows'`;
101 let $purged_files_before =
102 `SELECT VARIABLE_VALUE
103 FROM performance_schema.global_status
104 WHERE VARIABLE_NAME = 'ndb_log_purged_files'`;
105
106
107 --echo # =======================================
108 --echo # 2. Create 9 binary log files
109 --echo # =======================================
110
111 let $NUM_FILES = 9;
112 let $i = 0;
113 while ($i < $NUM_FILES) {
114 FLUSH BINARY LOGS;
115 inc $i;
116 }
117
118 SHOW BINARY LOGS;
119
120 --echo # =======================================
121 --echo # 3. Add additional rows to ndb_binlog_index corresponding to the
122 --echo # binary logs, the number of rows should exceed the configured
123 --echo # ndb_log_rate_limit
124 --echo # =======================================
125
126 --disable_query_log
127 --disable_result_log
128 # Load 10000 rows into temp table
129 CREATE TEMPORARY TABLE temp (
130 `Position` bigint unsigned NOT NULL,
131 `File` varchar(255) NOT NULL,
132 `epoch` bigint unsigned NOT NULL,
133 `inserts` int unsigned NOT NULL,
134 `updates` int unsigned NOT NULL,
135 `deletes` int unsigned NOT NULL,
136 `schemaops` int unsigned NOT NULL,
137 `orig_server_id` int unsigned NOT NULL,
138 `orig_epoch` bigint unsigned NOT NULL,
139 `gci` int unsigned NOT NULL,
140 `next_position` bigint unsigned NOT NULL,
141 `next_file` varchar(255) NOT NULL,
142 PRIMARY KEY (`epoch`,`orig_server_id`,`orig_epoch`)
143 ) engine = InnoDB;
144 let $i = 0;
145 let $rows = 1000;
146 while ($i < $rows) {
147 eval INSERT INTO temp (
148 epoch, orig_server_id, orig_epoch,
149 Position, File, inserts, updates, deletes, schemaops,
150 gci, next_position, next_file
151 ) VALUES (
152 $i, 200, $i,
153 123456, 'some_filename.00001', 1, 2, 3, 5,
154 $i, 300, 'some_other_filename.00001');
155
156 inc $i;
157 }
158
159 # Load from temp table into ndb_binlog_index, setting new pks and filename
160 # for each lap
161 let $i = 1;
162 let $count = 1;
163 let $loops = $NUM_FILES;
164 while ($i <= $loops) {
165 let $file = '$binlog_name$i';
166 let $j = 1;
167 let $chunk = 10; # *1000 rows per file
168 while($j <= $chunk) {
169 eval INSERT INTO mysql.ndb_binlog_index (
170 epoch, orig_server_id, orig_epoch,
171 Position, File, inserts, updates, deletes, schemaops,
172 gci, next_position, next_file
173 )
174 SELECT epoch+$rows*$count, orig_server_id, orig_epoch,
175 Position, $file, inserts, updates, deletes, schemaops,
176 gci, next_position, next_file
177 FROM temp ORDER BY epoch, orig_server_id, orig_epoch;
178 inc $j;
179 inc $count;
180 }
181
182 inc $i;
183 }
184
185 --enable_query_log
186 --enable_result_log
187
188 SELECT COUNT(*) FROM mysql.ndb_binlog_index;
189 --replace_result $binlog_name BINLOG_NAME
190 SELECT COUNT(*), File FROM mysql.ndb_binlog_index GROUP BY File ORDER BY File;
191
192
193 --echo # =======================================
194 --echo # 4. Purging the binary logs
195 --echo # =======================================
196
197 --echo #
198 --echo # Remove the first binlog
199 PURGE BINARY LOGS TO 'binlog.000002';
200 --echo # Check that rows for first binlog file is removed
201 --replace_result $binlog_name BINLOG_NAME
202 eval SELECT COUNT(*) FROM mysql.ndb_binlog_index
203 WHERE File IN (CONCAT('$binlog_name', 1));
204
205 --echo #
206 --echo # Remove the subsequent two binlogs
207 PURGE BINARY LOGS TO 'binlog.000004';
208 --echo # Check that rows for the two purged binlog files has been removed
209 --replace_result $binlog_name BINLOG_NAME
210 eval SELECT COUNT(*) FROM mysql.ndb_binlog_index
211 WHERE File IN (CONCAT('$binlog_name', 2),
212 CONCAT('$binlog_name', 3));
213
214 --echo #
215 --echo # Remove the subsequent five binlogs
216 PURGE BINARY LOGS TO 'binlog.000009';
217 --echo # Check that rows for the five purged binlog files has been removed
218 --replace_result $binlog_name BINLOG_NAME
219 eval SELECT COUNT(*) FROM mysql.ndb_binlog_index
220 WHERE File IN (CONCAT('$binlog_name', 4),
221 CONCAT('$binlog_name', 5),
222 CONCAT('$binlog_name', 6),
223 CONCAT('$binlog_name', 7),
224 CONCAT('$binlog_name', 8));
225
226 --echo # Still rows left for the last binlog file
227 --replace_result $binlog_name BINLOG_NAME
228 SELECT COUNT(*), File FROM mysql.ndb_binlog_index GROUP BY File ORDER BY File;
229
230 --echo # Rotate to new binary log and purge the last binlog file with rows
231 FLUSH BINARY LOGS;
232 PURGE BINARY LOGS TO 'binlog.000010';
233
234 --echo # Show status counters:
235 let $purged_rows =
236 `SELECT VARIABLE_VALUE - $purged_rows_before
237 FROM performance_schema.global_status
238 WHERE VARIABLE_NAME = 'ndb_log_purged_rows'`;
239 echo purged_rows: $purged_rows;
240 let $purged_files =
241 `SELECT VARIABLE_VALUE - $purged_files_before
242 FROM performance_schema.global_status
243 WHERE VARIABLE_NAME = 'ndb_log_purged_files'`;
244 echo purged_files: $purged_files;
245
246 --echo # =======================================
247 --echo # 5. Check that all rows has been removed from ndb_binlog_index
248 --echo # =======================================
249
250 if (`SELECT COUNT(*) FROM mysql.ndb_binlog_index`) {
251 echo binlog_name: $binlog_name;
252 SELECT COUNT(*), File FROM mysql.ndb_binlog_index GROUP BY File ORDER BY File;
253 SELECT * FROM mysql.ndb_binlog_index LIMIT 10;
254 die Failed to delete all rows;
255 }