3
# This is a test with uses many processes to test a MySQL server.
5
# Tested a lot with: --threads=30
7
$opt_loop_count=500000; # Change this to make test harder/easier
9
##################### Standard benchmark inits ##############################
17
$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert=
18
$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0;
20
$opt_host=$opt_user=$opt_password=""; $opt_db="test";
22
GetOptions("host=s","db=s","user=s","password=s","loop-count=i","skip-create","skip-in","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","threads=i") || die "Aborted";
23
$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these
25
print "Test of multiple connections that test the following things:\n";
26
print "insert, select, delete, update, alter, check, repair and flush\n";
28
@testtables = ( ["bench_f31", ""],
29
["bench_f32", "row_format=fixed"],
30
["bench_f33", "delay_key_write=1"],
31
["bench_f34", "checksum=1"],
32
["bench_f35", "delay_key_write=1"]);
33
$abort_table="bench_f39";
35
$numtables = $#testtables+1;
36
srand 100; # Make random numbers repeatable
39
#### Start timeing and start test
42
$start_time=new Benchmark;
43
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
44
$opt_user, $opt_password,
45
{ PrintError => 0}) || die $DBI::errstr;
46
if (!$opt_skip_create)
49
foreach $table_def (@testtables)
51
my ($table,$extra)= ($table_def->[0], $table_def->[1]);
52
print "Creating table $table in database $opt_db\n";
53
$dbh->do("drop table if exists $table");
54
$dbh->do("create table $table".
55
" (id int(6) not null auto_increment,".
56
" info varchar(32)," .
57
" marker timestamp," .
58
" flag int not null," .
59
" primary key(id)) $extra")
62
# One row in the table will make future tests easier
63
$dbh->do("insert into $table (id) values (null)")
66
# Create the table we use to signal that we should end the test
67
$dbh->do("drop table if exists $abort_table");
68
$dbh->do("create table $abort_table (id int(6) not null) ENGINE=heap") ||
72
$dbh->do("delete from $abort_table");
73
$dbh->disconnect; $dbh=0; # Close handler
80
for ($i=0 ; $i < $opt_threads ; $i ++)
82
test_insert() if (($pid=fork()) == 0); $work{$pid}="insert";
84
for ($i=0 ; $i < $numtables ; $i ++)
86
test_insert($i,$i) if (($pid=fork()) == 0); $work{$pid}="insert_one";
88
for ($i=0 ; $i < $opt_threads ; $i ++)
90
test_select() if (($pid=fork()) == 0); $work{$pid}="select_key";
92
test_join() if (($pid=fork()) == 0); $work{$pid}="test_join";
93
test_select_count() if (($pid=fork()) == 0); $work{$pid}="select_count";
94
test_delete() if (($pid=fork()) == 0); $work{$pid}="delete";
95
test_update() if (($pid=fork()) == 0); $work{$pid}="update";
96
test_flush() if (($pid=fork()) == 0); $work{$pid}= "flush";
97
test_check() if (($pid=fork()) == 0); $work{$pid}="check";
98
test_repair() if (($pid=fork()) == 0); $work{$pid}="repair";
99
test_alter() if (($pid=fork()) == 0); $work{$pid}="alter";
100
#test_database("test2") if (($pid=fork()) == 0); $work{$pid}="check_database";
102
print "Started " . ($opt_threads*2+4) . " threads\n";
105
$running_insert_threads=$opt_threads+$numtables;
106
while (($pid=wait()) != -1)
109
print "thread '" . $work{$pid} . "' finished with exit code $ret\n";
110
if ($work{$pid} =~ /^insert/)
112
if (!--$running_insert_threads)
114
# Time to stop other threads
118
$errors++ if ($ret != 0);
125
if (!$opt_skip_delete && !$errors)
128
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
129
$opt_user, $opt_password,
130
{ PrintError => 0}) || die $DBI::errstr;
132
$dbh->do("drop table $abort_table");
133
foreach $table_def (@testtables)
135
$dbh->do("drop table " . $table_def->[0]);
137
$dbh->disconnect; $dbh=0; # Close handler
140
print ($errors ? "Test failed\n" :"Test ok\n");
141
$end_time=new Benchmark;
142
print "Total time: " .
143
timestr(timediff($end_time, $start_time),"noc") . "\n";
149
# Insert records in the table
154
my ($from_table,$to_table)= @_;
155
my ($dbh,$i,$j,$count,$table_def,$table);
157
if (!defined($from_table))
159
$from_table=0; $to_table=$numtables-1;
162
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
163
$opt_user, $opt_password,
164
{ PrintError => 0}) || die $DBI::errstr;
166
for ($i=$count=0 ; $i < $opt_loop_count; $i++)
168
for ($j= $from_table ; $j <= $to_table ; $j++)
170
my ($table)= ($testtables[$j]->[0]);
171
$dbh->do("insert into $table values (NULL,'This is entry $i','',0)") || die "Got error on insert: $DBI::errstr\n";
175
$dbh->disconnect; $dbh=0;
176
print "Test_insert: Inserted $count rows\n";
183
# Do continously select over all tables as long as there is changed
189
my ($dbh, $i, $j, $count, $loop);
191
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
192
$opt_user, $opt_password,
193
{ PrintError => 0}) || die $DBI::errstr;
195
$count_query=make_count_query($numtables);
200
while (($i++ % 100) || !test_if_abort($dbh))
205
$row_counts=simple_query($dbh, $count_query);
207
for ($j=0 ; $j < $numtables ; $j++)
209
my ($id)= int rand $row_counts->[$j];
210
my ($table)= $testtables[$j]->[0];
211
simple_query($dbh, "select id,info from $table where id=$id");
215
$dbh->disconnect; $dbh=0;
216
print "Test_select: Executed $count selects\n";
221
# Do big select count(distinct..) over the table
224
sub test_select_count
226
my ($dbh, $i, $j, $count, $loop);
228
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
229
$opt_user, $opt_password,
230
{ PrintError => 0}) || die $DBI::errstr;
234
while (!test_if_abort($dbh))
236
for ($j=0 ; $j < $numtables ; $j++)
238
my ($table)= $testtables[$j]->[0];
239
simple_query($dbh, "select count(distinct marker),count(distinct id),count(distinct info) from $table");
242
sleep(20); # This query is quite slow
244
$dbh->disconnect; $dbh=0;
245
print "Test_select: Executed $count select count(distinct) queries\n";
251
# Do continously joins between the first and second table
256
my ($dbh, $i, $j, $count, $loop);
258
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
259
$opt_user, $opt_password,
260
{ PrintError => 0}) || die $DBI::errstr;
262
$count_query=make_count_query($numtables);
267
while (($i++ % 100) || !test_if_abort($dbh))
272
$row_counts=simple_query($dbh, $count_query);
274
for ($j=0 ; $j < $numtables-1 ; $j++)
276
my ($id)= int rand $row_counts->[$j];
277
my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]);
278
simple_query($dbh, "select $t1.id,$t2.info from $t1, $t2 where $t1.id=$t2.id and $t1.id=$id");
282
$dbh->disconnect; $dbh=0;
283
print "Test_join: Executed $count joins\n";
288
# Delete 1-5 rows from the first 2 tables.
289
# Test ends when the number of rows for table 3 didn't change during
295
my ($dbh, $i,$j, $row_counts, $count_query, $table_count, $count);
299
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
300
$opt_user, $opt_password,
301
{ PrintError => 0}) || die $DBI::errstr;
303
$count_query=make_count_query($table_count+1);
305
sleep(5); # Give time to insert some rows
307
while (($i++ % 10) || !test_if_abort($dbh))
310
$row_counts=simple_query($dbh, $count_query);
312
for ($j=0 ; $j < $table_count ; $j++)
314
my ($id)= int rand $row_counts->[$j];
315
my ($table)= $testtables[$j]->[0];
316
$dbh->do("delete from $table where id >= $id-2 and id <= $id +2") || die "Got error on delete from $table: $DBI::errstr\n";
320
$dbh->disconnect; $dbh=0;
321
print "Test_delete: Executed $count deletes\n";
326
# Update the flag for table 2 and 3
327
# Will abort after a while when table1 doesn't change max value
332
my ($dbh, $i, $j, $row_counts, $count_query, $count, $loop);
333
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
334
$opt_user, $opt_password,
335
{ PrintError => 0}) || die $DBI::errstr;
337
$count_query=make_count_query(3);
341
sleep(5); # Give time to insert some rows
343
while (($i++ % 100) || !test_if_abort($dbh))
348
$row_counts=simple_query($dbh, $count_query);
351
for ($j=1 ; $j <= 2 ; $j++)
353
my ($id)= int rand $row_counts->[$j];
354
my ($table)= $testtables[$j]->[0];
355
# Fix to not change the same rows as the above delete
356
$id= ($id + $count) % $row_counts->[$j];
358
$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";
362
$dbh->disconnect; $dbh=0;
363
print "Test_update: Executed $count updates\n";
369
# Run a check on all tables except the last one
370
# (The last one is not checked to put pressure on the key cache)
375
my ($dbh, $row, $i, $j, $type, $table);
376
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
377
$opt_user, $opt_password,
378
{ PrintError => 0}) || die $DBI::errstr;
381
for ($i=$j=0 ; !test_if_abort($dbh) ; $i++)
384
$table=$testtables[$j]->[0];
385
$sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n";
386
$sth->execute || die $DBI::errstr;
388
while (($row=$sth->fetchrow_arrayref))
390
if ($row->[3] ne "OK")
392
print "Got error " . $row->[3] . " when doing $type on $table\n";
396
if (++$j == $numtables-1)
401
$dbh->disconnect; $dbh=0;
402
print "test_check: Executed $i checks\n";
407
# Do a repair on the first table once in a while
412
my ($dbh, $row, $i, $type, $table);
413
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
414
$opt_user, $opt_password,
415
{ PrintError => 0}) || die $DBI::errstr;
418
for ($i=0 ; !test_if_abort($dbh) ; $i++)
421
$table=$testtables[0]->[0];
422
$sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n";
423
$sth->execute || die $DBI::errstr;
425
while (($row=$sth->fetchrow_arrayref))
427
if ($row->[3] ne "OK")
429
print "Got error " . $row->[3] . " when doing $type on $table\n";
434
$dbh->disconnect; $dbh=0;
435
print "test_repair: Executed $i repairs\n";
440
# Do a flush tables on table 3 and 4 once in a while
445
my ($dbh,$count,$tables);
447
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
448
$opt_user, $opt_password,
449
{ PrintError => 0}) || die $DBI::errstr;
451
$tables=$testtables[2]->[0] . "," . $testtables[3]->[0];
454
while (!test_if_abort($dbh))
457
$dbh->do("flush tables $tables") ||
458
die "Got error on flush $DBI::errstr\n";
461
$dbh->disconnect; $dbh=0;
462
print "flush: Executed $count flushs\n";
468
# Test all tables in a database
474
my ($dbh, $row, $i, $type, $tables);
475
$dbh = DBI->connect("DBI:mysql:$database:$opt_host",
476
$opt_user, $opt_password,
477
{ PrintError => 0}) || die $DBI::errstr;
479
$tables= join(',',$dbh->func('_ListTables'));
481
for ($i=0 ; !test_if_abort($dbh) ; $i++)
484
$sth=$dbh->prepare("$type table $tables") || die "Got error on prepare: $DBI::errstr\n";
485
$sth->execute || die $DBI::errstr;
487
while (($row=$sth->fetchrow_arrayref))
489
if ($row->[3] ne "OK")
491
print "Got error " . $row->[2] . " " . $row->[3] . " when doing $type on " . $row->[0] . "\n";
496
$dbh->disconnect; $dbh=0;
497
print "test_check: Executed $i checks\n";
502
# Test ALTER TABLE on the second table
507
my ($dbh, $row, $i, $type, $table);
508
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
509
$opt_user, $opt_password,
510
{ PrintError => 0}) || die $DBI::errstr;
512
for ($i=0 ; !test_if_abort($dbh) ; $i++)
515
$table=$testtables[1]->[0];
516
$sth=$dbh->prepare("ALTER table $table modify info char(32)") || die "Got error on prepare: $DBI::errstr\n";
517
$sth->execute || die $DBI::errstr;
519
$dbh->disconnect; $dbh=0;
520
print "test_alter: Executed $i ALTER TABLE\n";
532
$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
533
$opt_user, $opt_password,
534
{ PrintError => 0}) || die $DBI::errstr;
536
$dbh->do("insert into $abort_table values(1)") || die $DBI::errstr;
537
$dbh->disconnect; $dbh=0;
545
$row=simple_query($dbh,"select * from $opt_db.$abort_table");
546
return (defined($row) && defined($row->[0]) != 0) ? 1 : 0;
552
my ($table_count)= @_;
553
my ($tables, $count_query, $i, $tables_def);
555
$count_query="select high_priority ";
557
for ($i=0 ; $i < $table_count ; $i++)
559
my ($table_def)= $testtables[$i];
560
$tables.=$table_def->[0] . ",";
561
$count_query.= "max(" . $table_def->[0] . ".id),";
563
$table_def=$testtables[$table_count];
564
$tables.=$table_def->[0];
565
$count_query.= "max(" . $table_def->[0] . ".id) from $tables";
571
my ($dbh, $query)= @_;
574
$sth=$dbh->prepare($query) || die "Got error on '$query': " . $dbh->errstr . "\n";
575
$sth->execute || die "Got error on '$query': " . $dbh->errstr . "\n";
576
$row= $sth->fetchrow_arrayref();
added
removed
tests/fork_big.pl
