heikki@hundin:~/test> cat ibtest9
############################################################################
#     Stress test for MySQL/Innobase combined database
#     (c) 2000 Innobase Oy & MySQL AB
#
############################################################################

use DBI;
use Benchmark;

$opt_loop_count = 100000;
$opt_loop_count2 = 50;

chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\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] = "kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfy
ufyufyufyufyyufujhfghd";
        } 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";
        }

        if ($i % 7 == 0) {
                $rnd_str[$i] = "";
        }
}

####
####  Connect
####

$dbh = $server->connect();

$dbh->do("set autocommit = 0");

$n = 0;

for ($i = 0; $i < 1; $i++) {
        $k = 0;
        print "loop $i\n";

        $dbh->do("drop table ibtest09");

        $dbh->do(
        "create table ibtest09 (A BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, D DEC
IMAL(9,0) NOT NULL, B VARCHAR(200) NOT NULL, -- hubbabubba\n C CHAR(175), # kukk
uu\n E TIMESTAMP, F TIMESTAMP, G DATETIME, H VARCHAR(20), I ENUM('t', 'f'), PRIM
ARY KEY (A, D, C(5)), INDEX(B), INDEX(B(20), I, C(1), D), INDEX (C(150)), INDEX
(D), INDEX(H, I, A), INDEX(A, E, I), INDEX(G)) TYPE = InnoDB /* ijhjklhjhjk */")
        || die $dbh->errstr;

        for ($j = 2; $j < $opt_loop_count - 10; $j = $j + 2) {

                $dbh->do("show create table ibtest09");

                $dbh->do("show processlist");

                $dbh->do("savepoint kukkuu");

                if ($j % 10 == 0) {
                        $dbh->do(
                "insert into ibtest09 (D, B, C, F, G, H, I) values (5, '".$rnd_s
tr[$j]."' ,'".$rnd_str[$j]."', NULL, NULL, '', 't')")
                        || print $dbh->errstr;
                } else {
                        $dbh->do(
                "insert into ibtest09 (D, B, C, F, G, H, I) values (5, '".$rnd_s
tr[$j]."' ,'".$rnd_str[$j]."', NOW(), NOW(), '', 'f')")
                        || print $dbh->errstr;
                }

                $dbh->do("update ibtest09 set B = '".$rnd_str[$j + 7]."', I = 't
' where A = ".$random[$j + 5])
                        || print $dbh->errstr;


                $dbh->do("update ibtest09 SET D = D + 1, I = 'f' where A =".($j
/ 2 - 500))
                        || print $dbh->errstr;


                $dbh->do("update ibtest09 set B = '".$rnd_str[$j + 1]."', I = 't
' where A =".($j / 2 - 505))
                        || print $dbh->errstr;


                $dbh->do("delete from ibtest09 where A = ".$random[$random[$j]])
;
                fetch_all_rows($dbh, "select b, c from ibtest09 where a > ".$ran
dom[$j]." and a < ".($random[$j] + 7));

                if (0 == ($j % 3)) {
#                       $dbh->do("rollback to savepoint kukkuu");
                }

                if (0 == ($j % 10)) {
                        $dbh->do("commit");
                }

                if (0 == ($j % 97)) {
                fetch_all_rows($dbh, "select c, e, f, g, i from ibtest09 where c
 = '".$rnd_str[$j - 68]."'");
                fetch_all_rows($dbh, "select b, e, f, g from ibtest09 where b =
'".$rnd_str[$j - 677]."'");
                fetch_all_rows($dbh, "select b, c, e, f, g from ibtest09 where c
 = '".$rnd_str[$j - 68]."' LOCK IN SHARE MODE");
                fetch_all_rows($dbh, "select b, c, g from ibtest09 where b = '".
$rnd_str[$j - 677]."' FOR UPDATE");
                fetch_all_rows($dbh, "select a, b, c, e, f, g, i from ibtest09 w
here c = '".$rnd_str[$j - 68]."' LOCK IN SHARE MODE");
                fetch_all_rows($dbh, "select a, b, c, e, f, g from ibtest09 wher
e b = '".$rnd_str[$j - 677]."' FOR UPDATE");
                fetch_all_rows($dbh, "select d, b, c from ibtest09 where c = '".
$rnd_str[$j - 68]."'");
                fetch_all_rows($dbh, "select d, b, c, g from ibtest09 where b =
'".$rnd_str[$j - 677]."'");

                        $dbh->do("rollback");
                }

        for ($k = 1; $k < 10; $k++) {

                $n += fetch_all_rows($dbh,
"SELECT a, d from ibtest09 where a = ".(($k * 1764767) % $j));

                $n += fetch_all_rows($dbh,
"SELECT * from ibtest09 where a = ".(($k * 187567) % $j));

        }
                if (0 == ($j % 1000)) {
                        print "round $j, $n rows fetched\n";

#                       $dbh->do(
#                       "alter table ibtest09 add column mmm".$j." int")
#                       || print $dbh->errstr;
#
#                       print "round $j\n";
                }
        }

        $dbh->do("commit");
}

$dbh->disconnect;                               # close connection
heikki@hundin:~/test> cat ibtest9a
############################################################################
#     Stress test for MySQL/Innobase combined database
#     (c) 2000 Innobase Oy & MySQL AB
#
############################################################################

use DBI;
use Benchmark;

$opt_loop_count = 200000;
$opt_loop_count2 = 50;

chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\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] = "kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfy
ufyufyufyufyyufujhfghd";
        } 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();

$dbh->do("set autocommit = 0");

for ($i = 0; $i < 100; $i++) {
        $k = 0;
        print "loop $i\n";

        for ($j = 1; $j < $opt_loop_count - 10; $j = $j + 2) {

                $dbh->do("savepoint kukkuu".$j)
                || die $dbh->errstr;

                $dbh->do(
                "insert into ibtest09 (D, B, C) values (5, '".$rnd_str[$j]."' ,'
".$rnd_str[$j]."')")
                        || print $dbh->errstr;

                $dbh->do("update ibtest09 set B = '".$rnd_str[$j + 2]."' where A
 = ".$random[$j + 5])
                        || print $dbh->errstr;

                $dbh->do("update ibtest09 SET D = D + 1 where A =".(($j - 1) / 2
 - 777))
                        || print $dbh->errstr;

                $dbh->do("update ibtest09 set B = '".$rnd_str[$j + 8]."' where A
 =".(($j - 1) / 2 - 770))
                        || print $dbh->errstr;

                $dbh->do("delete from ibtest09 where A = ".$random[$random[$j]])
                        || print $dbh->errstr;

                fetch_all_rows($dbh, "select b, c from ibtest09 where a > ".$ran
dom[$j]." and a < ".($random[$j] + 7));

                if (0 == ($j % 7)) {
                        $dbh->do("rollback to savepoint kukkuu".$j)
                        || print $dbh->errstr;
                }

                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 ibtest09 where c = '".
$rnd_str[$j - 67]."'");
                print fetch_all_rows($dbh, "select b from ibtest09 where b = '".
$rnd_str[$j - 688]."'");
                print fetch_all_rows($dbh, "select b, c from ibtest09 where c =
'".$rnd_str[$j - 67]."'");
                print fetch_all_rows($dbh, "select b, c, i from ibtest09 where b
 = '".$rnd_str[$j - 622]."'");
                print fetch_all_rows($dbh, "select a, b, c from ibtest09 where c
 = '".$rnd_str[$j - 68]."'");
                print fetch_all_rows($dbh, "select a, b, c from ibtest09 where b
 = '".$rnd_str[$j - 644]."'");
                print fetch_all_rows($dbh, "select d, b, c from ibtest09 where c
 = '".$rnd_str[$j - 68]."'");
                print fetch_all_rows($dbh, "select d, b, c from ibtest09 where b
 = '".$rnd_str[$j - 677]."'");
                print "\n";
                }

                $dbh->disconnect;                       # close connection

                print "\nCONNECT $j\n";

                $dbh = $server->connect();

                $dbh->do("set autocommit = 0");

                if (0 == (($j - 1) % 1000)) {
                        print "round $j\n";
                }

                if (0 == (($j - 1) % 50000)) {
                        $dbh->do("check table ibtest09");
                        print "table checked\n";
                }
        }

        $dbh->do("commit");
}

$dbh->disconnect;                               # close connection
heikki@hundin:~/test> cat ibtest9b
############################################################################
#     Stress test for MySQL/Innobase combined database
#     (c) 2000 Innobase Oy & MySQL AB
#
############################################################################

use DBI;
use Benchmark;

$opt_loop_count = 200000;
$opt_loop_count2 = 50;

chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\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] = "kjgclgrtfuylfluyfyufyulfulfyyulofuyolfyufyufuyfy
ufyufyufyufyyufujhfghd";
        } 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();

$dbh->do("set autocommit = 0");

for ($i = 0; $i < 100; $i++) {
        $k = 0;
        print "loop $i\n";

        for ($j = 1; $j < $opt_loop_count - 10; $j = $j + 2) {

                fetch_all_rows($dbh, "select b, c from ibtest09 where a < ".($j
% 100)." group by b order by a desc");

                if (0 == ($j % 37)) {
                        $dbh->do("commit");
                }

                if (0 == ($j % 533)) {
                        $dbh->do("rollback");
                }

                print fetch_all_rows($dbh, "select c from ibtest09 where c = '".
$rnd_str[$j - 67]."'");
                print fetch_all_rows($dbh, "select b from ibtest09 where b = '".
$rnd_str[$j - 688]."'");
                print fetch_all_rows($dbh, "select b, c from ibtest09 where c =
'".$rnd_str[$j - 67]."'");
                print fetch_all_rows($dbh, "select b, c from ibtest09 where b =
'".$rnd_str[$j - 622]."'");
                print fetch_all_rows($dbh, "select a, b, c from ibtest09 where c
 = '".$rnd_str[$j - 68]."' group by b, c order by a desc");
                print fetch_all_rows($dbh, "select a, b, c from ibtest09 where b
 = '".$rnd_str[$j - 644]."'");
                print fetch_all_rows($dbh, "select d, b, c from ibtest09 where c
 = '".$rnd_str[$j - 68]."'");
                print fetch_all_rows($dbh, "select t1.a, t.d, t.b, t.c from ibte
st09 t, ibtest09 t1 where t.b = '".$rnd_str[$j - 677]."' and t.a = t1.a and t.d
= t1.d group by t.b, t.c order by t.d, t1.a desc");

#               $dbh->disconnect;

#               $dbh = $server->connect();

                $dbh->do("set autocommit = 0");

                if (0 == (($j - 1) % 1000)) {
                        print "round $j\n";

                        $dbh->do("optimize table ibtest09")
                        || die $dbh->errstr;

                        print "table optimized\n";
                }
        }

        $dbh->do("commit");
}

$dbh->disconnect;                               # close connection
heikki@hundin:~/test>