1
by brian
clean slate |
1 |
#!/usr/bin/perl -w
|
2 |
#
|
|
3 |
# This is a test with uses many processes to test a MySQL server.
|
|
4 |
#
|
|
5 |
# Tested a lot with: --threads=30
|
|
6 |
||
7 |
$opt_loop_count=500000; # Change this to make test harder/easier |
|
8 |
||
9 |
##################### Standard benchmark inits ##############################
|
|
10 |
||
11 |
use DBI; |
|
12 |
use Getopt::Long; |
|
13 |
use Benchmark; |
|
14 |
||
15 |
package main; |
|
16 |
||
17 |
$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= |
|
18 |
$opt_lock_tables=$opt_debug=$opt_skip_drop=$opt_fast=$opt_force=0; |
|
19 |
$opt_thread_factor=1; |
|
20 |
$opt_insert=1; |
|
21 |
$opt_select=6;$opt_join=4; |
|
22 |
$opt_select_count=$opt_join_count=0; |
|
23 |
$opt_update=1;$opt_delete=0; |
|
24 |
$opt_flush=$opt_check=$opt_repair=$opt_alter=0; |
|
25 |
$opt_join_range=100; |
|
26 |
$opt_resize_interval=0; |
|
27 |
$opt_time=0; |
|
28 |
$opt_host=$opt_user=$opt_password=""; $opt_db="test"; |
|
29 |
$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these |
|
30 |
||
31 |
GetOptions("host=s","db=s","user=s","password=s","loop-count=i","skip-create","skip-in","skip-drop", |
|
32 |
"verbose","fast-insert","lock-tables","debug","fast","force","thread-factor=i", |
|
33 |
"insert=i", "select=i", "join=i", "select-count=i", "join-count=i", "update=i", "delete=i", |
|
34 |
"flush=i", "check=i", "repair=i", "alter=i", "resize-interval=i", "max-join_range=i", "time=i") || die "Aborted"; |
|
35 |
||
36 |
print "Test of multiple connections that test the following things:\n"; |
|
37 |
print "insert, select, delete, update, alter, check, repair and flush\n"; |
|
38 |
||
39 |
@testtables = ( ["bench_f31", ""], |
|
40 |
["bench_f32", "row_format=fixed"], |
|
41 |
["bench_f33", "delay_key_write=1"], |
|
42 |
["bench_f34", "checksum=1"], |
|
43 |
["bench_f35", "delay_key_write=1"]); |
|
44 |
$abort_table="bench_f39"; |
|
45 |
||
46 |
$numtables = $#testtables+1; |
|
47 |
srand 100; # Make random numbers repeatable |
|
48 |
||
49 |
####
|
|
50 |
#### Start timeing and start test
|
|
51 |
####
|
|
52 |
||
53 |
$opt_insert*=$opt_thread_factor; |
|
54 |
$opt_select*=$opt_thread_factor; |
|
55 |
$opt_join*=$opt_thread_factor; |
|
56 |
$opt_select_count*=$opt_thread_factor; |
|
57 |
$opt_join_count*=$opt_thread_factor; |
|
58 |
$opt_update*=$opt_thread_factor; |
|
59 |
$opt_delete*=$opt_thread_factor; |
|
60 |
||
61 |
if ($opt_time == 0 && $opt_insert == 0) |
|
62 |
{
|
|
63 |
$opt_insert=1; |
|
64 |
}
|
|
65 |
||
66 |
$start_time=new Benchmark; |
|
67 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
68 |
$opt_user, $opt_password, |
|
69 |
{ PrintError => 0}) || die $DBI::errstr; |
|
70 |
if (!$opt_skip_create) |
|
71 |
{
|
|
72 |
my $table_def; |
|
73 |
foreach $table_def (@testtables) |
|
74 |
{
|
|
75 |
my ($table,$extra)= ($table_def->[0], $table_def->[1]); |
|
76 |
print "Creating table $table in database $opt_db\n"; |
|
77 |
$dbh->do("drop table if exists $table"); |
|
78 |
$dbh->do("create table $table". |
|
79 |
" (id int(6) not null auto_increment,". |
|
80 |
" info varchar(32)," . |
|
81 |
" marker timestamp," . |
|
82 |
" flag int not null," . |
|
83 |
" primary key(id)) $extra") |
|
84 |
||
85 |
or die $DBI::errstr; |
|
86 |
# One row in the table will make future tests easier
|
|
87 |
$dbh->do("insert into $table (id) values (null)") |
|
88 |
or die $DBI::errstr; |
|
89 |
}
|
|
90 |
# Create the table we use to signal that we should end the test
|
|
91 |
$dbh->do("drop table if exists $abort_table"); |
|
92 |
$dbh->do("create table $abort_table (id int(6) not null) ENGINE=heap") || |
|
93 |
die $DBI::errstr; |
|
94 |
}
|
|
95 |
||
96 |
$dbh->do("delete from $abort_table"); |
|
97 |
$dbh->disconnect; $dbh=0; # Close handler |
|
98 |
$|= 1; # Autoflush |
|
99 |
||
100 |
####
|
|
101 |
#### Start the tests
|
|
102 |
####
|
|
103 |
if ($opt_time != 0) |
|
104 |
{
|
|
105 |
test_abort() if (($pid=fork()) == 0); $work{$pid}="abort"; |
|
106 |
}
|
|
107 |
for ($i=0 ; $i < $opt_insert ; $i ++) |
|
108 |
{
|
|
109 |
test_insert() if (($pid=fork()) == 0); $work{$pid}="insert"; |
|
110 |
}
|
|
111 |
$threads=$i; |
|
112 |
for ($i=0 ; $i < $opt_select ; $i ++) |
|
113 |
{
|
|
114 |
test_select() if (($pid=fork()) == 0); $work{$pid}="select"; |
|
115 |
}
|
|
116 |
$threads+=$i; |
|
117 |
for ($i=0 ; $i < $opt_join ; $i ++) |
|
118 |
{
|
|
119 |
test_join() if (($pid=fork()) == 0); $work{$pid}="join"; |
|
120 |
}
|
|
121 |
$threads+=$i; |
|
122 |
for ($i=0 ; $i < $opt_select_count ; $i ++) |
|
123 |
{
|
|
124 |
test_select_count() if (($pid=fork()) == 0); $work{$pid}="select_count"; |
|
125 |
}
|
|
126 |
$threads+=$i; |
|
127 |
for ($i=0 ; $i < $opt_join_count ; $i ++) |
|
128 |
{
|
|
129 |
test_join_count() if (($pid=fork()) == 0); $work{$pid}="join_count"; |
|
130 |
}
|
|
131 |
$threads+=$i; |
|
132 |
for ($i=0 ; $i < $opt_update ; $i ++) |
|
133 |
{
|
|
134 |
test_update() if (($pid=fork()) == 0); $work{$pid}="update"; |
|
135 |
}
|
|
136 |
$threads+=$i; |
|
137 |
for ($i=0 ; $i < $opt_delete ; $i ++) |
|
138 |
{
|
|
139 |
test_delete() if (($pid=fork()) == 0); $work{$pid}="delete"; |
|
140 |
}
|
|
141 |
$threads+=$i; |
|
142 |
for ($i=0 ; $i < $opt_flush ; $i ++) |
|
143 |
{
|
|
144 |
test_flush() if (($pid=fork()) == 0); $work{$pid}="flush"; |
|
145 |
}
|
|
146 |
$threads+=$i; |
|
147 |
for ($i=0 ; $i < $opt_check ; $i ++) |
|
148 |
{
|
|
149 |
test_check() if (($pid=fork()) == 0); $work{$pid}="check"; |
|
150 |
}
|
|
151 |
$threads+=$i; |
|
152 |
for ($i=0 ; $i < $opt_repair ; $i ++) |
|
153 |
{
|
|
154 |
test_repair() if (($pid=fork()) == 0); $work{$pid}="repair"; |
|
155 |
}
|
|
156 |
$threads+=$i; |
|
157 |
for ($i=0 ; $i < $opt_alter ; $i ++) |
|
158 |
{
|
|
159 |
test_alter() if (($pid=fork()) == 0); $work{$pid}="alter"; |
|
160 |
}
|
|
161 |
$threads+=$i; |
|
162 |
if ($opt_resize_interval != 0) |
|
163 |
{
|
|
164 |
test_resize() if (($pid=fork()) == 0); $work{$pid}="resize"; |
|
165 |
$threads+=1; |
|
166 |
}
|
|
167 |
||
168 |
print "Started $threads threads\n"; |
|
169 |
||
170 |
$errors=0; |
|
171 |
$running_insert_threads=$opt_insert; |
|
172 |
while (($pid=wait()) != -1) |
|
173 |
{
|
|
174 |
$ret=$?/256; |
|
175 |
print "thread '" . $work{$pid} . "' finished with exit code $ret\n"; |
|
176 |
if ($opt_time == 0) |
|
177 |
{
|
|
178 |
if ($work{$pid} =~ /^insert/) |
|
179 |
{
|
|
180 |
if (!--$running_insert_threads) |
|
181 |
{
|
|
182 |
||
183 |
# Time to stop other threads
|
|
184 |
signal_abort(); |
|
185 |
}
|
|
186 |
}
|
|
187 |
}
|
|
188 |
$errors++ if ($ret != 0); |
|
189 |
}
|
|
190 |
||
191 |
#
|
|
192 |
# Cleanup
|
|
193 |
#
|
|
194 |
||
195 |
if (!$opt_skip_drop && !$errors) |
|
196 |
{
|
|
197 |
my $table_def; |
|
198 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
199 |
$opt_user, $opt_password, |
|
200 |
{ PrintError => 0}) || die $DBI::errstr; |
|
201 |
||
202 |
$dbh->do("drop table $abort_table"); |
|
203 |
foreach $table_def (@testtables) |
|
204 |
{
|
|
205 |
$dbh->do("drop table " . $table_def->[0]); |
|
206 |
}
|
|
207 |
$dbh->disconnect; $dbh=0; # Close handler |
|
208 |
}
|
|
209 |
||
210 |
print ($errors ? "Test failed\n" :"Test ok\n"); |
|
211 |
$end_time=new Benchmark; |
|
212 |
print "Total time: " . |
|
213 |
timestr(timediff($end_time, $start_time),"noc") . "\n"; |
|
214 |
||
215 |
exit(0); |
|
216 |
||
217 |
#
|
|
218 |
# Sleep and then abort other threads
|
|
219 |
#
|
|
220 |
||
221 |
sub test_abort |
|
222 |
{
|
|
223 |
sleep($opt_time); |
|
224 |
signal_abort(); |
|
225 |
exit(0); |
|
226 |
}
|
|
227 |
||
228 |
||
229 |
#
|
|
230 |
# Insert records in the table
|
|
231 |
#
|
|
232 |
||
233 |
sub test_insert |
|
234 |
{
|
|
235 |
my ($from_table,$to_table)= @_; |
|
236 |
my ($dbh,$i,$j,$count,$table_def,$table); |
|
237 |
||
238 |
if (!defined($from_table)) |
|
239 |
{
|
|
240 |
$from_table=0; $to_table=$numtables-1; |
|
241 |
}
|
|
242 |
||
243 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
244 |
$opt_user, $opt_password, |
|
245 |
{ PrintError => 0}) || die $DBI::errstr; |
|
246 |
||
247 |
for ($i=$count=0 ; $i < $opt_loop_count; $i++) |
|
248 |
{
|
|
249 |
for ($j= $from_table ; $j <= $to_table ; $j++) |
|
250 |
{
|
|
251 |
my ($table)= ($testtables[$j]->[0]); |
|
252 |
$dbh->do("insert into $table values (NULL,'This is entry $i','',0)") || die "Got error on insert: $DBI::errstr\n"; |
|
253 |
$count++; |
|
254 |
}
|
|
255 |
}
|
|
256 |
$dbh->disconnect; $dbh=0; |
|
257 |
print "Test_insert: Inserted $count rows\n"; |
|
258 |
exit(0); |
|
259 |
}
|
|
260 |
||
261 |
||
262 |
#
|
|
263 |
# select records
|
|
264 |
# Do continously select over all tables as long as there is changed
|
|
265 |
# rows in the table
|
|
266 |
#
|
|
267 |
||
268 |
sub test_select |
|
269 |
{
|
|
270 |
my ($dbh, $i, $j, $count, $loop); |
|
271 |
||
272 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
273 |
$opt_user, $opt_password, |
|
274 |
{ PrintError => 0}) || die $DBI::errstr; |
|
275 |
||
276 |
$count_query=make_count_query($numtables); |
|
277 |
$count=0; |
|
278 |
$loop=9999; |
|
279 |
||
280 |
$i=0; |
|
281 |
while (($i++ % 100) || !test_if_abort($dbh)) |
|
282 |
{
|
|
283 |
if ($loop++ >= 100) |
|
284 |
{
|
|
285 |
$loop=0; |
|
286 |
$row_counts=simple_query($dbh, $count_query); |
|
287 |
}
|
|
288 |
for ($j=0 ; $j < $numtables ; $j++) |
|
289 |
{
|
|
290 |
my ($id)= int rand $row_counts->[$j]; |
|
291 |
my ($table)= $testtables[$j]->[0]; |
|
292 |
simple_query($dbh, "select id,info from $table where id=$id"); |
|
293 |
$count++; |
|
294 |
}
|
|
295 |
}
|
|
296 |
$dbh->disconnect; $dbh=0; |
|
297 |
print "Test_select: Executed $count selects\n"; |
|
298 |
exit(0); |
|
299 |
}
|
|
300 |
||
301 |
#
|
|
302 |
# Do big select count(distinct..) over the table
|
|
303 |
#
|
|
304 |
||
305 |
sub test_select_count |
|
306 |
{
|
|
307 |
my ($dbh, $i, $j, $count, $loop); |
|
308 |
||
309 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
310 |
$opt_user, $opt_password, |
|
311 |
{ PrintError => 0}) || die $DBI::errstr; |
|
312 |
||
313 |
$count=0; |
|
314 |
$i=0; |
|
315 |
while (!test_if_abort($dbh)) |
|
316 |
{
|
|
317 |
for ($j=0 ; $j < $numtables ; $j++) |
|
318 |
{
|
|
319 |
my ($table)= $testtables[$j]->[0]; |
|
320 |
simple_query($dbh, "select count(distinct marker),count(distinct id),count(distinct info) from $table"); |
|
321 |
$count++; |
|
322 |
}
|
|
323 |
sleep(20); # This query is quite slow |
|
324 |
}
|
|
325 |
$dbh->disconnect; $dbh=0; |
|
326 |
print "Test_select: Executed $count select count(distinct) queries\n"; |
|
327 |
exit(0); |
|
328 |
}
|
|
329 |
||
330 |
#
|
|
331 |
# select records
|
|
332 |
# Do continously joins between the first and second table
|
|
333 |
#
|
|
334 |
||
335 |
sub test_join |
|
336 |
{
|
|
337 |
my ($dbh, $i, $j, $count, $loop); |
|
338 |
||
339 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
340 |
$opt_user, $opt_password, |
|
341 |
{ PrintError => 0}) || die $DBI::errstr; |
|
342 |
||
343 |
$count_query=make_count_query($numtables); |
|
344 |
$count=0; |
|
345 |
$loop=9999; |
|
346 |
||
347 |
$i=0; |
|
348 |
while (($i++ % 100) || !test_if_abort($dbh)) |
|
349 |
{
|
|
350 |
if ($loop++ >= 100) |
|
351 |
{
|
|
352 |
$loop=0; |
|
353 |
$row_counts=simple_query($dbh, $count_query); |
|
354 |
}
|
|
355 |
for ($j=0 ; $j < $numtables-1 ; $j++) |
|
356 |
{
|
|
357 |
my ($id)= int rand $row_counts->[$j]; |
|
358 |
my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]); |
|
359 |
simple_query($dbh, "select $t1.id,$t2.info from $t1, $t2 where $t1.id=$t2.id and $t1.id=$id"); |
|
360 |
$count++; |
|
361 |
}
|
|
362 |
}
|
|
363 |
$dbh->disconnect; $dbh=0; |
|
364 |
print "Test_join: Executed $count joins\n"; |
|
365 |
exit(0); |
|
366 |
}
|
|
367 |
||
368 |
#
|
|
369 |
# select records
|
|
370 |
# Do continously joins between the first and second for range and count selected rows
|
|
371 |
#
|
|
372 |
||
373 |
sub test_join_count |
|
374 |
{
|
|
375 |
my ($dbh, $i, $j, $count, $loop); |
|
376 |
||
377 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
378 |
$opt_user, $opt_password, |
|
379 |
{ PrintError => 0}) || die $DBI::errstr; |
|
380 |
||
381 |
$count_query=make_count_query($numtables); |
|
382 |
$count=0; |
|
383 |
$loop=9999; |
|
384 |
$sum=0; |
|
385 |
||
386 |
srand(); |
|
387 |
||
388 |
$i=0; |
|
389 |
while (($i++ % 10) || !test_if_abort($dbh)) |
|
390 |
{
|
|
391 |
if ($loop++ >= 10) |
|
392 |
{
|
|
393 |
$loop=0; |
|
394 |
$row_counts=simple_query($dbh, $count_query); |
|
395 |
}
|
|
396 |
for ($j=0 ; $j < $numtables-1 ; $j++) |
|
397 |
{
|
|
398 |
my ($id1)= int rand $row_counts->[$j]; |
|
399 |
my ($id2)= int rand $row_counts->[$j]; |
|
400 |
if ($id1 > $id2) |
|
401 |
{
|
|
402 |
my $id0=$id1; $id1=$id2; $id2=$id0; |
|
403 |
if ($id2-$id1 > $opt_join_range) |
|
404 |
{
|
|
405 |
$id2=$id1+$opt_join_range; |
|
406 |
}
|
|
407 |
}
|
|
408 |
my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]); |
|
409 |
$row=simple_query($dbh, "select count(*) from $t1, $t2 where $t1.id=$t2.id and $t1.id between $id1 and $id2"); |
|
410 |
$sum+=$row->[0]; |
|
411 |
$count++; |
|
412 |
}
|
|
413 |
}
|
|
414 |
$dbh->disconnect; $dbh=0; |
|
415 |
print "Test_join_count: Executed $count joins: total $sum rows\n"; |
|
416 |
exit(0); |
|
417 |
}
|
|
418 |
||
419 |
||
420 |
#
|
|
421 |
# Delete 1-5 rows from the first 2 tables.
|
|
422 |
# Test ends when the number of rows for table 3 didn't change during
|
|
423 |
# one loop
|
|
424 |
#
|
|
425 |
||
426 |
sub test_delete |
|
427 |
{
|
|
428 |
my ($dbh, $i,$j, $row_counts, $count_query, $table_count, $count); |
|
429 |
||
430 |
$table_count=2; |
|
431 |
$count=0; |
|
432 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
433 |
$opt_user, $opt_password, |
|
434 |
{ PrintError => 0}) || die $DBI::errstr; |
|
435 |
||
436 |
$count_query=make_count_query($table_count+1); |
|
437 |
||
438 |
sleep(5); # Give time to insert some rows |
|
439 |
$i=0; |
|
440 |
while (($i++ % 10) || !test_if_abort($dbh)) |
|
441 |
{
|
|
442 |
sleep(1); |
|
443 |
$row_counts=simple_query($dbh, $count_query); |
|
444 |
||
445 |
for ($j=0 ; $j < $table_count ; $j++) |
|
446 |
{
|
|
447 |
my ($id)= int rand $row_counts->[$j]; |
|
448 |
my ($table)= $testtables[$j]->[0]; |
|
449 |
$dbh->do("delete from $table where id >= $id-2 and id <= $id +2") || die "Got error on delete from $table: $DBI::errstr\n"; |
|
450 |
$count++; |
|
451 |
}
|
|
452 |
}
|
|
453 |
$dbh->disconnect; $dbh=0; |
|
454 |
print "Test_delete: Executed $count deletes\n"; |
|
455 |
exit(0); |
|
456 |
}
|
|
457 |
||
458 |
#
|
|
459 |
# Update the flag for table 2 and 3
|
|
460 |
# Will abort after a while when table1 doesn't change max value
|
|
461 |
#
|
|
462 |
||
463 |
sub test_update |
|
464 |
{
|
|
465 |
my ($dbh, $i, $j, $row_counts, $count_query, $count, $loop); |
|
466 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
467 |
$opt_user, $opt_password, |
|
468 |
{ PrintError => 0}) || die $DBI::errstr; |
|
469 |
||
470 |
$count_query=make_count_query(3); |
|
471 |
$loop=9999; |
|
472 |
$count=0; |
|
473 |
||
474 |
sleep(5); # Give time to insert some rows |
|
475 |
$i=0; |
|
476 |
while (($i++ % 100) || !test_if_abort($dbh)) |
|
477 |
{
|
|
478 |
if ($loop++ >= 100) |
|
479 |
{
|
|
480 |
$loop=0; |
|
481 |
$row_counts=simple_query($dbh, $count_query); |
|
482 |
}
|
|
483 |
||
484 |
for ($j=1 ; $j <= 2 ; $j++) |
|
485 |
{
|
|
486 |
my ($id)= int rand $row_counts->[$j]; |
|
487 |
my ($table)= $testtables[$j]->[0]; |
|
488 |
# Fix to not change the same rows as the above delete
|
|
489 |
$id= ($id + $count) % $row_counts->[$j]; |
|
490 |
||
491 |
$dbh->do("update $table set flag=flag+1 where id >= $id-2 and id <= $id +2") || die "Got error on update of $table: $DBI::errstr\n"; |
|
492 |
$count++; |
|
493 |
}
|
|
494 |
}
|
|
495 |
$dbh->disconnect; $dbh=0; |
|
496 |
print "Test_update: Executed $count updates\n"; |
|
497 |
exit(0); |
|
498 |
}
|
|
499 |
||
500 |
||
501 |
#
|
|
502 |
# Run a check on all tables except the last one
|
|
503 |
# (The last one is not checked to put pressure on the key cache)
|
|
504 |
#
|
|
505 |
||
506 |
sub test_check |
|
507 |
{
|
|
508 |
my ($dbh, $row, $i, $j, $type, $table); |
|
509 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
510 |
$opt_user, $opt_password, |
|
511 |
{ PrintError => 0}) || die $DBI::errstr; |
|
512 |
||
513 |
$type= "check"; |
|
514 |
for ($i=$j=0 ; !test_if_abort($dbh) ; $i++) |
|
515 |
{
|
|
516 |
sleep(1000); |
|
517 |
$table=$testtables[$j]->[0]; |
|
518 |
$sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; |
|
519 |
$sth->execute || die $DBI::errstr; |
|
520 |
||
521 |
while (($row=$sth->fetchrow_arrayref)) |
|
522 |
{
|
|
523 |
if ($row->[3] ne "OK") |
|
524 |
{
|
|
525 |
print "Got error " . $row->[3] . " when doing $type on $table\n"; |
|
526 |
exit(1); |
|
527 |
}
|
|
528 |
}
|
|
529 |
if (++$j == $numtables-1) |
|
530 |
{
|
|
531 |
$j=0; |
|
532 |
}
|
|
533 |
}
|
|
534 |
$dbh->disconnect; $dbh=0; |
|
535 |
print "test_check: Executed $i checks\n"; |
|
536 |
exit(0); |
|
537 |
}
|
|
538 |
||
539 |
#
|
|
540 |
# Do a repair on the first table once in a while
|
|
541 |
#
|
|
542 |
||
543 |
sub test_repair |
|
544 |
{
|
|
545 |
my ($dbh, $row, $i, $type, $table); |
|
546 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
547 |
$opt_user, $opt_password, |
|
548 |
{ PrintError => 0}) || die $DBI::errstr; |
|
549 |
||
550 |
$type= "repair"; |
|
551 |
for ($i=0 ; !test_if_abort($dbh) ; $i++) |
|
552 |
{
|
|
553 |
sleep(2000); |
|
554 |
$table=$testtables[0]->[0]; |
|
555 |
$sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; |
|
556 |
$sth->execute || die $DBI::errstr; |
|
557 |
||
558 |
while (($row=$sth->fetchrow_arrayref)) |
|
559 |
{
|
|
560 |
if ($row->[3] ne "OK") |
|
561 |
{
|
|
562 |
print "Got error " . $row->[3] . " when doing $type on $table\n"; |
|
563 |
exit(1); |
|
564 |
}
|
|
565 |
}
|
|
566 |
}
|
|
567 |
$dbh->disconnect; $dbh=0; |
|
568 |
print "test_repair: Executed $i repairs\n"; |
|
569 |
exit(0); |
|
570 |
}
|
|
571 |
||
572 |
#
|
|
573 |
# Do a flush tables on table 3 and 4 once in a while
|
|
574 |
#
|
|
575 |
||
576 |
sub test_flush |
|
577 |
{
|
|
578 |
my ($dbh,$count,$tables); |
|
579 |
||
580 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
581 |
$opt_user, $opt_password, |
|
582 |
{ PrintError => 0}) || die $DBI::errstr; |
|
583 |
||
584 |
$tables=$testtables[2]->[0] . "," . $testtables[3]->[0]; |
|
585 |
||
586 |
$count=0; |
|
587 |
while (!test_if_abort($dbh)) |
|
588 |
{
|
|
589 |
sleep(3000); |
|
590 |
$dbh->do("flush tables $tables") || |
|
591 |
die "Got error on flush $DBI::errstr\n"; |
|
592 |
$count++; |
|
593 |
}
|
|
594 |
$dbh->disconnect; $dbh=0; |
|
595 |
print "flush: Executed $count flushs\n"; |
|
596 |
exit(0); |
|
597 |
}
|
|
598 |
||
599 |
#
|
|
600 |
# Do a resize key cache every periodically
|
|
601 |
#
|
|
602 |
||
603 |
sub test_resize |
|
604 |
{
|
|
605 |
my ($dbh, $key_buffer_size); |
|
606 |
||
607 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
608 |
$opt_user, $opt_password, |
|
609 |
{ PrintError => 0}) || die $DBI::errstr; |
|
610 |
||
611 |
$count=0; |
|
612 |
$key_buffer_size=1024*64; |
|
613 |
while (!test_if_abort($dbh)) |
|
614 |
{
|
|
615 |
sleep($opt_resize_interval); |
|
616 |
$dbh->do("set global key_buffer_size=$key_buffer_size") || |
|
617 |
die "Got error on resize key cache $DBI::errstr\n"; |
|
618 |
$key_buffer_size+=1024*16; |
|
619 |
$count++; |
|
620 |
}
|
|
621 |
$dbh->disconnect; $dbh=0; |
|
622 |
print "Test_resize: Executed $count times resize key cache\n"; |
|
623 |
exit(0); |
|
624 |
}
|
|
625 |
||
626 |
#
|
|
627 |
# Test all tables in a database
|
|
628 |
#
|
|
629 |
||
630 |
sub test_database |
|
631 |
{
|
|
632 |
my ($database) = @_; |
|
633 |
my ($dbh, $row, $i, $type, $tables); |
|
634 |
$dbh = DBI->connect("DBI:mysql:$database:$opt_host", |
|
635 |
$opt_user, $opt_password, |
|
636 |
{ PrintError => 0}) || die $DBI::errstr; |
|
637 |
||
638 |
$tables= join(',',$dbh->func('_ListTables')); |
|
639 |
$type= "check"; |
|
640 |
for ($i=0 ; !test_if_abort($dbh) ; $i++) |
|
641 |
{
|
|
642 |
sleep(120); |
|
643 |
$sth=$dbh->prepare("$type table $tables") || die "Got error on prepare: $DBI::errstr\n"; |
|
644 |
$sth->execute || die $DBI::errstr; |
|
645 |
||
646 |
while (($row=$sth->fetchrow_arrayref)) |
|
647 |
{
|
|
648 |
if ($row->[3] ne "OK") |
|
649 |
{
|
|
650 |
print "Got error " . $row->[2] . " " . $row->[3] . " when doing $type on " . $row->[0] . "\n"; |
|
651 |
exit(1); |
|
652 |
}
|
|
653 |
}
|
|
654 |
}
|
|
655 |
$dbh->disconnect; $dbh=0; |
|
656 |
print "test_check: Executed $i checks\n"; |
|
657 |
exit(0); |
|
658 |
}
|
|
659 |
||
660 |
#
|
|
661 |
# Test ALTER TABLE on the second table
|
|
662 |
#
|
|
663 |
||
664 |
sub test_alter |
|
665 |
{
|
|
666 |
my ($dbh, $row, $i, $type, $table); |
|
667 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
668 |
$opt_user, $opt_password, |
|
669 |
{ PrintError => 0}) || die $DBI::errstr; |
|
670 |
||
671 |
for ($i=0 ; !test_if_abort($dbh) ; $i++) |
|
672 |
{
|
|
673 |
sleep(100); |
|
674 |
$table=$testtables[1]->[0]; |
|
675 |
$sth=$dbh->prepare("ALTER table $table modify info char(32)") || die "Got error on prepare: $DBI::errstr\n"; |
|
676 |
$sth->execute || die $DBI::errstr; |
|
677 |
}
|
|
678 |
$dbh->disconnect; $dbh=0; |
|
679 |
print "test_alter: Executed $i ALTER TABLE\n"; |
|
680 |
exit(0); |
|
681 |
}
|
|
682 |
||
683 |
||
684 |
#
|
|
685 |
# Help functions
|
|
686 |
#
|
|
687 |
||
688 |
sub signal_abort |
|
689 |
{
|
|
690 |
my ($dbh); |
|
691 |
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", |
|
692 |
$opt_user, $opt_password, |
|
693 |
{ PrintError => 0}) || die $DBI::errstr; |
|
694 |
||
695 |
$dbh->do("insert into $abort_table values(1)") || die $DBI::errstr; |
|
696 |
$dbh->disconnect; $dbh=0; |
|
697 |
}
|
|
698 |
||
699 |
||
700 |
sub test_if_abort() |
|
701 |
{
|
|
702 |
my ($dbh)=@_; |
|
703 |
$row=simple_query($dbh,"select * from $opt_db.$abort_table"); |
|
704 |
return (defined($row) && defined($row->[0]) != 0) ? 1 : 0; |
|
705 |
}
|
|
706 |
||
707 |
||
708 |
sub make_count_query |
|
709 |
{
|
|
710 |
my ($table_count)= @_; |
|
711 |
my ($tables, $count_query, $i, $tables_def); |
|
712 |
$tables=""; |
|
713 |
$count_query="select high_priority "; |
|
714 |
$table_count--; |
|
715 |
for ($i=0 ; $i < $table_count ; $i++) |
|
716 |
{
|
|
717 |
my ($table_def)= $testtables[$i]; |
|
718 |
$tables.=$table_def->[0] . ","; |
|
719 |
$count_query.= "max(" . $table_def->[0] . ".id),"; |
|
720 |
}
|
|
721 |
$table_def=$testtables[$table_count]; |
|
722 |
$tables.=$table_def->[0]; |
|
723 |
$count_query.= "max(" . $table_def->[0] . ".id) from $tables"; |
|
724 |
return $count_query; |
|
725 |
}
|
|
726 |
||
727 |
sub simple_query() |
|
728 |
{
|
|
729 |
my ($dbh, $query)= @_; |
|
730 |
my ($sth,$row); |
|
731 |
||
732 |
$sth=$dbh->prepare($query) || die "Got error on '$query': " . $dbh->errstr . "\n"; |
|
733 |
$sth->execute || die "Got error on '$query': " . $dbh->errstr . "\n"; |
|
734 |
$row= $sth->fetchrow_arrayref(); |
|
735 |
$sth=0; |
|
736 |
return $row; |
|
737 |
}
|