summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorunknown <heikki@hundin.mysql.fi>2002-08-15 22:22:56 +0300
committerunknown <heikki@hundin.mysql.fi>2002-08-15 22:22:56 +0300
commitc1b7c246c1f6d204bb011151f914f7563c70a79b (patch)
tree6b92d11fd72f4e43d84c63f22934a74970b76ab6
parent6fbcad539d7f3c96dca4aa3cfdf4d28ee0d4ced2 (diff)
downloadmariadb-git-c1b7c246c1f6d204bb011151f914f7563c70a79b.tar.gz
Many files:
Add multithreaded stress tests for InnoDB new file sql-bench/innotest1.sh: Add multithreaded stress tests for InnoDB sql-bench/innotest1a.sh: Add multithreaded stress tests for InnoDB sql-bench/innotest1b.sh: Add multithreaded stress tests for InnoDB sql-bench/innotest2.sh: Add multithreaded stress tests for InnoDB sql-bench/innotest2a.sh: Add multithreaded stress tests for InnoDB sql-bench/innotest2b.sh: Add multithreaded stress tests for InnoDB sql-bench/Makefile.am: Add multithreaded stress tests for InnoDB
-rw-r--r--sql-bench/Makefile.am6
-rw-r--r--sql-bench/innotest1.sh141
-rw-r--r--sql-bench/innotest1a.sh107
-rw-r--r--sql-bench/innotest1b.sh100
-rw-r--r--sql-bench/innotest2.sh149
-rw-r--r--sql-bench/innotest2a.sh93
-rw-r--r--sql-bench/innotest2b.sh103
7 files changed, 697 insertions, 2 deletions
diff --git a/sql-bench/Makefile.am b/sql-bench/Makefile.am
index 80806f35418..9ed0fc29b30 100644
--- a/sql-bench/Makefile.am
+++ b/sql-bench/Makefile.am
@@ -23,14 +23,16 @@ bench_SCRIPTS = test-ATIS test-connect test-create test-insert \
test-big-tables test-select test-wisconsin \
test-alter-table graph-compare-results \
bench-init.pl compare-results run-all-tests \
- server-cfg crash-me copy-db
+ server-cfg crash-me copy-db innotest1 innotest1a \
+ innotest1b innotest2 innotest2a innotest2b
CLEANFILES = $(bench_SCRIPTS)
EXTRA_SCRIPTS = test-ATIS.sh test-connect.sh test-create.sh \
test-insert.sh test-big-tables.sh test-select.sh \
test-alter-table.sh test-wisconsin.sh \
bench-init.pl.sh compare-results.sh server-cfg.sh \
run-all-tests.sh crash-me.sh copy-db.sh \
- graph-compare-results.sh
+ graph-compare-results.sh innotest1.sh innotest1a.sh \
+ innotest1b.sh innotest2.sh innotest2a.sh innotest2b.sh
EXTRA_DIST = $(EXTRA_SCRIPTS)
dist-hook:
diff --git a/sql-bench/innotest1.sh b/sql-bench/innotest1.sh
new file mode 100644
index 00000000000..b875a431040
--- /dev/null
+++ b/sql-bench/innotest1.sh
@@ -0,0 +1,141 @@
+#!@PERL@
+############################################################################
+# Stress test for MySQL/InnoDB combined database
+# (c) 2002 Innobase Oy & MySQL AB
+#
+############################################################################
+
+use DBI;
+use Benchmark;
+
+$opt_loop_count = 100000;
+
+chomp($pwd = `pwd`); $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
diff --git a/sql-bench/innotest1a.sh b/sql-bench/innotest1a.sh
new file mode 100644
index 00000000000..d78357427bc
--- /dev/null
+++ b/sql-bench/innotest1a.sh
@@ -0,0 +1,107 @@
+#!@PERL@
+############################################################################
+# Stress test for MySQL/InnoDB combined database
+# (c) 2002 Innobase Oy & MySQL AB
+#
+############################################################################
+
+use DBI;
+use Benchmark;
+
+$opt_loop_count = 200000;
+
+chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+print "Innotest1a: 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");
+
+for ($i = 0; $i < 1; $i++) {
+ print "loop $i\n";
+
+ for ($j = 1; $j < $opt_loop_count - 10; $j = $j + 2) {
+ $dbh->do(
+ "insert into innotest1 (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')");
+ $dbh->do("update innotest1 set B = '".$rnd_str[$j + 2]."' where A = ".$random[$j + 5]);
+ $dbh->do("update innotest1 SET D = D + 1 where A =".(($j - 1) / 2 - 777));
+ $dbh->do("update innotest1 set B = '".$rnd_str[$j + 8]."' where A =".(($j - 1) / 2 - 770));
+ $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 % 37)) {
+ $dbh->do("commit");
+ }
+
+ if (0 == ($j % 533)) {
+ $dbh->do("rollback");
+ }
+
+ if (0 == ($j % 537)) {
+ print fetch_all_rows($dbh, "select c from innotest1 where c = '".$rnd_str[$j - 67]."'");
+ print fetch_all_rows($dbh, "select b from innotest1 where b = '".$rnd_str[$j - 688]."'");
+ print fetch_all_rows($dbh, "select b, c from innotest1 where c = '".$rnd_str[$j - 67]."'");
+ print fetch_all_rows($dbh, "select b, c from innotest1 where b = '".$rnd_str[$j - 622]."'");
+ print fetch_all_rows($dbh, "select a, b, c from innotest1 where c = '".$rnd_str[$j - 68]."'");
+ print fetch_all_rows($dbh, "select a, b, c from innotest1 where b = '".$rnd_str[$j - 644]."'");
+ print fetch_all_rows($dbh, "select d, b, c from innotest1 where c = '".$rnd_str[$j - 68]."'");
+ print fetch_all_rows($dbh, "select d, b, c from innotest1 where b = '".$rnd_str[$j - 677]."'");
+ print "\n";
+ }
+
+ if (0 == (($j - 1) % 1000)) {
+ print "round $j\n";
+ }
+ }
+
+ $dbh->do("commit");
+}
+
+$dbh->disconnect; # close connection
diff --git a/sql-bench/innotest1b.sh b/sql-bench/innotest1b.sh
new file mode 100644
index 00000000000..d83fce00efc
--- /dev/null
+++ b/sql-bench/innotest1b.sh
@@ -0,0 +1,100 @@
+#!@PERL@
+############################################################################
+# Stress test for MySQL/InnoDB combined database
+# (c) 2002 Innobase Oy & MySQL AB
+#
+############################################################################
+
+use DBI;
+use Benchmark;
+
+$opt_loop_count = 200000;
+
+chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+print "Innotest1b: 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");
+
+for ($i = 0; $i < 5; $i++) {
+ print "loop $i\n";
+
+ for ($j = 1; $j < $opt_loop_count - 10; $j = $j + 2) {
+
+ fetch_all_rows($dbh, "select b, c from innotest1 where a > ".$random[$j]." and a < ".($random[$j] + 7));
+
+ if (0 == ($j % 37)) {
+ $dbh->do("commit");
+ }
+
+ if (0 == ($j % 533)) {
+ $dbh->do("rollback");
+ }
+
+ if (0 == ($j % 537)) {
+ print fetch_all_rows($dbh, "select c from innotest1 where c = '".$rnd_str[$j - 67]."'");
+ print fetch_all_rows($dbh, "select b from innotest1 where b = '".$rnd_str[$j - 688]."'");
+ print fetch_all_rows($dbh, "select b, c from innotest1 where c = '".$rnd_str[$j - 67]."'");
+ print fetch_all_rows($dbh, "select b, c from innotest1 where b = '".$rnd_str[$j - 622]."'");
+ print fetch_all_rows($dbh, "select a, b, c from innotest1 where c = '".$rnd_str[$j - 68]."'");
+ print fetch_all_rows($dbh, "select a, b, c from innotest1 where b = '".$rnd_str[$j - 644]."'");
+ print fetch_all_rows($dbh, "select d, b, c from innotest1 where c = '".$rnd_str[$j - 68]."'");
+ print fetch_all_rows($dbh, "select d, b, c from innotest1 where b = '".$rnd_str[$j - 677]."'");
+ print "\n";
+ }
+
+ if (0 == (($j - 1) % 1000)) {
+ print "round $j\n";
+ }
+ }
+
+ $dbh->do("commit");
+}
+
+$dbh->disconnect; # close connection
diff --git a/sql-bench/innotest2.sh b/sql-bench/innotest2.sh
new file mode 100644
index 00000000000..05c21098249
--- /dev/null
+++ b/sql-bench/innotest2.sh
@@ -0,0 +1,149 @@
+#!@PERL@
+############################################################################
+# Stress test for MySQL/InnoDB combined database
+# (c) 2002 Innobase Oy & MySQL AB
+#
+############################################################################
+
+use DBI;
+use Benchmark;
+
+$opt_loop_count = 100000;
+
+chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+print "Innotest2: MySQL/InnoDB stress test in Perl for FOREIGN keys\n";
+print "------------------------------------------------------------\n";
+print "This is a randomized stress test for concurrent inserts,\n";
+print "updates, deletes, commits and rollbacks with foreign keys with\n";
+print "the ON DELETE ... clause. 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 innotest2, innotest2a, and innotest2b 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 tables innotest2a, b, c, d 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 innotest2 > out2\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] = "khD";
+ } else { if (1 == ($random[$i] % 3)) {
+ $rnd_str[$i] = "khd";
+ } else { if (2 == ($random[$i] % 3)) {
+ $rnd_str[$i] = "kHd";
+ }}}
+
+ for ($j = 0; $j < (($i * 764877) % 10); $j++) {
+ $rnd_str[$i] = $rnd_str[$i]."k";
+ }
+}
+
+####
+#### Connect
+####
+
+$dbh = $server->connect()
+|| die $dbh->errstr;
+
+$dbh->do("set autocommit = 0");
+
+for ($i = 0; $i < 1; $i++) {
+ print "loop $i\n";
+
+ print "dropping table innotest2a\n";
+ $dbh->do("drop table innotest2a");
+
+ print "dropping table innotest2b\n";
+ $dbh->do("drop table innotest2b");
+
+ print "dropping table innotest2c\n";
+ $dbh->do("drop table innotest2c");
+
+ print "dropping table innotest2d\n";
+ $dbh->do("drop table innotest2d");
+
+ print "creating table innotest2b\n";
+ $dbh->do(
+ "create table innotest2b (A INT NOT NULL AUTO_INCREMENT, D INT NOT NULL, B VARCHAR(200) NOT NULL, C VARCHAR(175), PRIMARY KEY (A, D, B), INDEX (B, C), INDEX (C)) TYPE = INNODB")
+ || die $dbh->errstr;
+
+ print "creating table innotest2a\n";
+
+ $dbh->do(
+ "create table innotest2a (A INT NOT NULL AUTO_INCREMENT, D INT NOT NULL, B VARCHAR(200) NOT NULL, C VARCHAR(175), PRIMARY KEY (A, D, B), INDEX (B, C), INDEX (C), FOREIGN KEY (A, D) REFERENCES innotest2b (A, D) ON DELETE CASCADE) TYPE = INNODB")
+ || die $dbh->errstr;
+
+ print "creating table innotest2c\n";
+
+ $dbh->do(
+ "create table innotest2c (A INT NOT NULL AUTO_INCREMENT, D INT NOT NULL, B VARCHAR(200) NOT NULL, C VARCHAR(175), PRIMARY KEY (A, D, B), INDEX (B, C), INDEX (C), FOREIGN KEY (A, D) REFERENCES innotest2a (A, D) ON DELETE CASCADE, FOREIGN KEY (B, C) REFERENCES innotest2a (B, C) ON DELETE CASCADE) TYPE = INNODB")
+ || die $dbh->errstr;
+
+ print "creating table innotest2d\n";
+
+ $dbh->do(
+ "create table innotest2d (A INT AUTO_INCREMENT, D INT, B VARCHAR(200), C VARCHAR(175), UNIQUE KEY (A, D, B), INDEX (B, C), INDEX (C), FOREIGN KEY (C) REFERENCES innotest2c (C) ON DELETE SET NULL, FOREIGN KEY (B, C) REFERENCES innotest2c (B, C) ON DELETE SET NULL) TYPE = INNODB")
+ || die $dbh->errstr;
+ print "created\n";
+
+ for ($j = 0; $j < $opt_loop_count - 10; $j = $j + 2) {
+ $dbh->do(
+ "insert into innotest2b (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')")
+ || print $dbh->errstr;
+
+ $dbh->do(
+ "insert into innotest2a (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')")
+ || print $dbh->errstr;
+
+ $dbh->do(
+ "insert into innotest2c (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')")
+ || print $dbh->errstr;
+
+ $dbh->do(
+ "insert into innotest2d (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')")
+ || print $dbh->errstr;
+
+ $dbh->do("delete from innotest2b where A = ".$random[$random[$j]])
+ || print $dbh->errstr;
+
+ if (0 == ($j % 10)) {
+ $dbh->do("commit");
+ }
+
+ if (0 == ($j % 39)) {
+ $dbh->do("rollback");
+ }
+
+ if (0 == ($j % 1000)) {
+ print "round $j\n";
+ }
+ if (0 == ($j % 20000)) {
+ print "Checking tables...\n";
+ $dbh->do("check table innotest2a");
+ $dbh->do("check table innotest2b");
+ $dbh->do("check table innotest2c");
+ $dbh->do("check table innotest2d");
+ print "Tables checked.\n";
+ }
+ }
+
+ $dbh->do("commit");
+}
+
+$dbh->disconnect; # close connection
diff --git a/sql-bench/innotest2a.sh b/sql-bench/innotest2a.sh
new file mode 100644
index 00000000000..e0fe2001d24
--- /dev/null
+++ b/sql-bench/innotest2a.sh
@@ -0,0 +1,93 @@
+#!@PERL@
+############################################################################
+# Stress test for MySQL/Innobase combined database
+# (c) 2000 Innobase Oy & MySQL AB
+#
+############################################################################
+
+use DBI;
+use Benchmark;
+
+$opt_loop_count = 100000;
+
+chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+print "Innotest2a: MySQL/InnoDB stress test in Perl for FOREIGN keys\n";
+print "------------------------------------------------------------\n";
+print "This is a randomized stress test for concurrent inserts,\n";
+print "updates, deletes, commits and rollbacks with foreign keys with\n";
+print "the ON DELETE ... clause. 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 innotest2, innotest2a, and innotest2b 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 tables innotest2a, b, c, d 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 innotest2 > out2\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] = "khD";
+ } else { if (1 == ($random[$i] % 3)) {
+ $rnd_str[$i] = "kHd";
+ } else { if (2 == ($random[$i] % 3)) {
+ $rnd_str[$i] = "khd";
+ }}}
+
+ 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");
+
+for ($i = 0; $i < 5; $i++) {
+ print "loop $i\n";
+
+ for ($j = 0; $j < $opt_loop_count - 10; $j = $j + 1) {
+
+ $dbh->do("update innotest2a set B = '".$rnd_str[$j + 1]."' where A = ".$random[$j + 5])
+ || print $dbh->errstr;
+
+ $dbh->do("delete from innotest2a where A = ".$random[$random[$j]])
+ || print $dbh->errstr;
+
+ if (0 == ($j % 10)) {
+ $dbh->do("commit");
+ }
+
+ if (0 == ($j % 39)) {
+ $dbh->do("rollback");
+ }
+
+ if (0 == ($j % 1000)) {
+ print "round $j\n";
+ }
+ }
+
+ $dbh->do("commit");
+}
+
+$dbh->disconnect; # close connection
diff --git a/sql-bench/innotest2b.sh b/sql-bench/innotest2b.sh
new file mode 100644
index 00000000000..83f48cf5352
--- /dev/null
+++ b/sql-bench/innotest2b.sh
@@ -0,0 +1,103 @@
+#!@PERL@
+############################################################################
+# Stress test for MySQL/Innobase combined database
+# (c) 2000 Innobase Oy & MySQL AB
+#
+############################################################################
+
+use DBI;
+use Benchmark;
+
+$opt_loop_count = 100000;
+
+chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+print "Innotest2b: MySQL/InnoDB stress test in Perl for FOREIGN keys\n";
+print "------------------------------------------------------------\n";
+print "This is a randomized stress test for concurrent inserts,\n";
+print "updates, deletes, commits and rollbacks with foreign keys with\n";
+print "the ON DELETE ... clause. 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 innotest2, innotest2a, and innotest2b 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 tables innotest2a, b, c, d 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 innotest2 > out2\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 * 98641) % $opt_loop_count;
+
+ if (0 == ($random[$i] % 3)) {
+ $rnd_str[$i] = "khD";
+ } else { if (1 == ($random[$i] % 3)) {
+ $rnd_str[$i] = "khd";
+ } else { if (2 == ($random[$i] % 3)) {
+ $rnd_str[$i] = "kHd";
+ }}}
+
+ for ($j = 0; $j < (($i * 764877) % 10); $j++) {
+ $rnd_str[$i] = $rnd_str[$i]."k";
+ }
+}
+
+####
+#### Connect
+####
+
+$dbh = $server->connect();
+
+$dbh->do("set autocommit = 0");
+
+for ($i = 0; $i < 1; $i++) {
+ print "loop $i\n";
+
+ for ($j = 0; $j < $opt_loop_count - 10; $j = $j + 2) {
+ $dbh->do(
+ "insert into innotest2b (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')")
+ || print $dbh->errstr;
+
+ $dbh->do(
+ "insert into innotest2a (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')")
+ || print $dbh->errstr;
+
+ $dbh->do(
+ "insert into innotest2c (D, B, C) values (5, '".$rnd_str[$j]."' ,'".$rnd_str[$j]."')")
+ || print $dbh->errstr;
+
+ $dbh->do("delete from innotest2b where A = ".$random[$random[$j]])
+ || print $dbh->errstr;
+
+ $dbh->do("update innotest2b set A = A + 1 where A = ".$random[$j])
+ || print $dbh->errstr;
+
+ if (0 == ($j % 10)) {
+ $dbh->do("commit");
+ }
+
+ if (0 == ($j % 39)) {
+ $dbh->do("rollback");
+ }
+
+ if (0 == ($j % 1000)) {
+ print "round $j\n";
+ }
+ }
+
+ $dbh->do("commit");
+}
+
+$dbh->disconnect; # close connection