« back to all changes in this revision
Viewing changes to tests/fork_big2.pl
- browse files at revision 51
- compare with another revision
- compare with revision 38
- download diff
- download tarball
- view history from revision 51
- Committer: Brian Aker
- Date: 2008-07-02 21:31:48 UTC
- mfrom: (28.1.41 libtool-patch)
- Revision ID: brian@tangent.org-20080702213148-pc4f4hjt5c9qvp00
Merge from Monty Taylor.
- files removed:
- client/mysql_upgrade.c
- tests
- files renamed:
- configure.in => configure.ac
- files modified:
- Makefile.am
added
removed
tests/fork_big2.pl
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
}
Loggerhead is a web-based interface for Breezy
Version: 2.0.1