1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
#!/usr/bin/perl
############################################################################
# Stress test for MySQL/InnoDB combined database
# (c) 2002 Innobase Oy & MySQL AB
#
############################################################################
use Cwd;
use DBI;
use Benchmark;
$opt_loop_count = 100000;
$pwd = cwd(); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
print "Innotest1: MySQL/InnoDB stress test in Perl\n";
print "-------------------------------------------\n";
print "This is a randomized stress test for concurrent inserts,\n";
print "updates, deletes, commits and rollbacks. The test will generate\n";
print "also a lot of deadlocks, duplicate key errors, and other SQL errors.\n";
print "\n";
print "You should run innotest1, innotest1a, and innotest1b concurrently.\n";
print "The thing to watch is that the server does not crash or does not\n";
print "print to the .err log anything. Currently, due to a buglet in MySQL,\n";
print "warnings about MySQL lock reservations can appear in the .err log.\n";
print "The test will run very long, even several hours. You can kill\n";
print "the perl processes running this test at any time and do CHECK\n";
print "TABLE on table innotest1 in the 'test' database.\n";
print "\n";
print "Some of these stress tests will print a lot of SQL errors\n";
print "to the standard output. That is not to be worried about.\n";
print "You can direct the output to a file like this:\n";
print "perl innotest1 > out1\n\n";
print "Generating random keys\n";
$random[$opt_loop_count] = 0;
$rnd_str[$opt_loop_count] = "a";
for ($i = 0; $i < $opt_loop_count; $i++) {
$random[$i] = ($i * 63857) % $opt_loop_count;
if (0 == ($random[$i] % 3)) {
$rnd_str[$i] = "kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfyufyufyufyufyyufujhfghd";
} else { if (1 == ($random[$i] % 3)) {
$rnd_str[$i] = "khd";
} else { if (2 == ($random[$i] % 3)) {
$rnd_str[$i] = "kh";
}}}
for ($j = 0; $j < (($i * 764877) % 20); $j++) {
$rnd_str[$i] = $rnd_str[$i]."k";
}
}
####
#### Connect
####
$dbh = $server->connect()
|| die $dbh->errstr;
$dbh->do("set autocommit = 0");
$n = 0;
for ($i = 0; $i < 1; $i++) {
print "Dropping table innotest1\n";
$dbh->do("drop table innotest1");
print "Creating table innotest1\n";
$dbh->do(
"create table innotest1 (A INT NOT NULL AUTO_INCREMENT, D INT NOT NULL, B VARCHAR(200) NOT NULL, C VARCHAR(175), E TIMESTAMP, F TIMESTAMP, G DATETIME, PRIMARY KEY (A, D), INDEX
(B, C), INDEX (C), INDEX (D), INDEX(E), INDEX(G)) TYPE = INNODB")
|| die $dbh->errstr;
for ($j = 2; $j < $opt_loop_count - 10; $j = $j + 2) {
if ($j % 10 == 0) {
$dbh->do(
"insert into innotest1 (D, B, C, F, G) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."', NULL, NULL)");
} else {
$dbh->do(
"insert into innotest1 (D, B, C, F, G) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."', NOW(), NOW())");
}
$dbh->do("update innotest1 set B = '".$rnd_str[$j + 7]."' where A = ".$random[$j + 5]);
$dbh->do("update innotest1 SET D = D + 1 where A =".($j / 2 - 500));
$dbh->do("update innotest1 set B = '".$rnd_str[$j + 1]."' where A =".($j / 2 - 505));
$dbh->do("delete from innotest1 where A = ".$random[$random[$j]]);
fetch_all_rows($dbh, "select b, c from innotest1 where a > ".$random[$j]." and a < ".($random[$j] + 7));
if (0 == ($j % 10)) {
$dbh->do("commit");
}
if (0 == ($j % 97)) {
fetch_all_rows($dbh, "select c, e, f, g from innotest1 where c = '".$rnd_str[$j - 68]."'");
fetch_all_rows($dbh, "select b, e, f, g from innotest1 where b = '".$rnd_str[$j - 677]."'");
fetch_all_rows($dbh, "select b, c, e, f, g from innotest1 where c = '".$rnd_str[$j - 68]."'");
fetch_all_rows($dbh, "select b, c, g from innotest1 where b = '".$rnd_str[$j - 677]."'");
fetch_all_rows($dbh, "select a, b, c, e, f, g from innotest1 where c = '".$rnd_str[$j - 68]."'");
fetch_all_rows($dbh, "select a, b, c, e, f, g from innotest1 where b = '".$rnd_str[$j - 677]."'");
fetch_all_rows($dbh, "select d, b, c from innotest1 where c = '".$rnd_str[$j - 68]."'");
fetch_all_rows($dbh, "select d, b, c, g from innotest1 where b = '".$rnd_str[$j - 677]."'");
$dbh->do("rollback");
}
for ($k = 1; $k < 10; $k++) {
$n += fetch_all_rows($dbh,
"SELECT a, d from innotest1 where a = ".(($k * 1764767) % $j));
$n += fetch_all_rows($dbh,
"SELECT * from innotest1 where a = ".(($k * 187567) % $j));
}
if (0 == ($j % 1000)) {
print "round $j, $n rows fetched\n";
}
if (0 == ($j % 20000)) {
print "Checking table innotest1...\n";
$dbh->do("check table innotest1");
print "Table checked.\n";
}
}
$dbh->do("commit");
}
$dbh->disconnect; # close connection
|