summaryrefslogtreecommitdiff
path: root/sql-bench/test-transactions.sh
diff options
context:
space:
mode:
authorunknown <monty@hundin.mysql.fi>2001-11-22 17:55:18 +0200
committerunknown <monty@hundin.mysql.fi>2001-11-22 17:55:18 +0200
commit38357b30e6ccf4278807cbd8b91ca14efe3fff15 (patch)
tree5c88c46555c8f2f0d49c779e6347d2d6139a84e0 /sql-bench/test-transactions.sh
parente673b6dcf8b55ec4457ab554a1c09ccf3c079463 (diff)
downloadmariadb-git-38357b30e6ccf4278807cbd8b91ca14efe3fff15.tar.gz
Fixed fulltext after merge from 3.23.45
First (incomplete) version of transaction and as3ap tests. BitKeeper/etc/ignore: Added sql-bench/test-transactions to the ignore list mysql-test/r/fulltext.result: Update fulltext results after merge sql-bench/Makefile.am: Added transaction test sql-bench/run-all-tests.sh: Added transaction test sql-bench/server-cfg.sh: Added transaction test sql-bench/test-ATIS.sh: Cleanup sql/item_func.cc: Fix bad merge sql/mysqld.cc: Cleanup sql/sql_base.cc: Fix bad merge sql/sql_delete.cc: Cleanup sql/sql_parse.cc: Fix bad merge sql/sql_select.cc: Fix bad merge sql/sql_union.cc: Fix bad merge tools/mysqlmanager.c: C
Diffstat (limited to 'sql-bench/test-transactions.sh')
-rw-r--r--sql-bench/test-transactions.sh268
1 files changed, 268 insertions, 0 deletions
diff --git a/sql-bench/test-transactions.sh b/sql-bench/test-transactions.sh
new file mode 100644
index 00000000000..62de009ab33
--- /dev/null
+++ b/sql-bench/test-transactions.sh
@@ -0,0 +1,268 @@
+#!@PERL@
+#
+# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Library General Public
+# License as published by the Free Software Foundation; either
+# version 2 of the License, or (at your option) any later version.
+#
+# This library is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+# Library General Public License for more details.
+#
+# You should have received a copy of the GNU Library General Public
+# License along with this library; if not, write to the Free
+# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
+# MA 02111-1307, USA
+#
+# Test of transactions performance.
+#
+
+##################### Standard benchmark inits ##############################
+
+use DBI;
+use Benchmark;
+use warnings;
+
+$opt_groups=27; # Characters are 'A' -> Z
+
+$opt_loop_count=100000; # Change this to make test harder/easier
+$opt_medium_loop_count=100; # Change this to make test harder/easier
+
+chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+# Avoid warnings for variables in bench-init.pl
+our ($opt_small_test, $opt_small_tables, $opt_debug, $opt_force);
+
+if ($opt_small_test || $opt_small_tables)
+{
+ $opt_loop_count/=100;
+ $opt_medium_loop_count/=10;
+}
+
+
+if (!$server->{transactions} && !$opt_force)
+{
+ print "Test skipped because the database doesn't support transactions\n";
+ exit(0);
+}
+
+####
+#### Connect and start timeing
+####
+
+$start_time=new Benchmark;
+$dbh = $server->connect();
+
+###
+### Create Table
+###
+
+print "Creating tables\n";
+$dbh->do("drop table bench1");
+$dbh->do("drop table bench2");
+
+do_many($dbh,$server->create("bench1",
+ ["idn int NOT NULL",
+ "rev_idn int NOT NULL",
+ "region char(1) NOT NULL",
+ "grp int NOT NULL",
+ "updated tinyint NOT NULL"],
+ ["primary key (idn)",
+ "unique (region,grp)"]));
+do_many($dbh,$server->create("bench2",
+ ["idn int NOT NULL",
+ "rev_idn int NOT NULL",
+ "region char(1) NOT NULL",
+ "grp int NOT NULL",
+ "updated tinyint NOT NULL"],
+ ["primary key (idn)",
+ "unique (region,grp)"]));
+
+$dbh->{AutoCommit} = 0;
+
+###
+### Test insert perfomance
+###
+
+test_insert("bench1","insert_commit",0);
+test_insert("bench2","insert_autocommit",1);
+
+sub test_insert
+{
+ my ($table, $test_name, $auto_commit)= @_;
+ my ($loop_time,$end_time,$id,$rev_id,$grp,$region);
+
+ $dbh->{AutoCommit}= $auto_commit;
+ $loop_time=new Benchmark;
+
+ for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ;
+ $id++,$rev_id--)
+ {
+ $grp=$id/$opt_groups;
+ $region=chr(65+$id%$opt_groups);
+ do_query($dbh,"insert into $table values ($id,$rev_id,'$region',$grp,0)");
+ }
+
+ $dbh->commit if (!$auto_commit);
+ $end_time=new Benchmark;
+ print "Time for $test_name ($opt_loop_count): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+}
+
+###
+### Test update perfomance
+###
+
+test_update("bench1","update_commit",0);
+test_update("bench2","update_autocommit",1);
+
+sub test_update
+{
+ my ($table, $test_name, $auto_commit)= @_;
+ my ($loop_time,$end_time,$id);
+
+ $dbh->{AutoCommit}= $auto_commit;
+ $loop_time=new Benchmark;
+
+ for ($id=0 ; $id < $opt_loop_count ; $id++)
+ {
+ do_query($dbh,"update bench1 set updated=1 where idn=$id");
+ }
+
+ $dbh->commit if (!$auto_commit);
+ $end_time=new Benchmark;
+ print "Time for $test_name ($opt_loop_count): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+}
+
+###
+### Test delete perfomance
+###
+
+test_delete("bench1","delete_commit",0);
+test_delete("bench2","delete_autocommit",1);
+
+sub test_delete
+{
+ my ($table, $test_name, $auto_commit)= @_;
+ my ($loop_time,$end_time,$id);
+
+ $dbh->{AutoCommit}= $auto_commit;
+ $loop_time=new Benchmark;
+
+ for ($id=0 ; $id < $opt_loop_count ; $id++)
+ {
+ do_query($dbh,"delete from $table where idn=$id");
+ }
+ $dbh->commit if (!$auto_commit);
+ $end_time=new Benchmark;
+ print "Time for $test_name ($opt_loop_count): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+}
+
+###
+### Test rollback performance
+###
+
+print "Test transactions rollback performance\n" if($opt_debug);
+
+##
+## Insert rollback test
+##
+
+#
+# Test is done by inserting 10 rows in a table with lots of rows and
+# then doing a rollback on these
+#
+
+{
+ my ($id,$rev_id,$grp,$region,$end,$loop_time,$end_time,$commit_loop,$count);
+
+ $dbh->{AutoCommit} = 0;
+ $loop_time=new Benchmark;
+ $end=$opt_loop_count*2;
+ $count=0;
+
+ for ($commit_loop=1, $id=$opt_loop_count ; $id < $end ;
+ $id++, $commit_loop++)
+ {
+ $rev_id=$end-$id;
+ $grp=$id/$opt_groups;
+ $region=chr(65+$id%$opt_groups);
+ do_query($dbh,"insert into bench1 values ($id,$rev_id,'$region',$grp,0)");
+ if ($commit_loop >= $opt_medium_loop_count)
+ {
+ $dbh->rollback;
+ $commit_loop=0;
+ $count++;
+ }
+ }
+ if ($commit_loop > 1)
+ {
+ $dbh->rollback;
+ $count++;
+ }
+ $end_time=new Benchmark;
+ print "Time for insert_rollback ($count:$opt_loop_count): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+}
+
+###
+### Update rollback test
+###
+
+if (0)
+{
+
+ $dbh->{AutoCommit} = 0;
+
+ $loop_time=new Benchmark;
+
+ for ($id=0,$rev_id=$opt_medium_loop_count ; $id < $opt_medium_loop_count; $id++,$rev_id--)
+ {
+ $grp=$id/$opt_groups;
+ $region=chr(65+$id%$opt_groups);
+ do_query($dbh,"update bench1 set region='$region',grp=$grp where idn=$id");
+ }
+
+ $dbh->rollback;
+
+ $end_time=new Benchmark;
+ print "Time for update rollback (" . ($opt_medium_loop_count) . "): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+
+### Delete rollback test
+ $dbh->{AutoCommit} = 0;
+
+ $loop_time=new Benchmark;
+
+ for ($id=0,$rev_id=$opt_medium_loop_count; $id < $opt_medium_loop_count; $id++,$rev_id--)
+ {
+ $grp=$id/$opt_groups;
+ $region=chr(65+$id%$opt_groups);
+ do_query($dbh,"delete from bench1 where idn=$id");
+ }
+
+ $dbh->rollback;
+
+ $end_time=new Benchmark;
+ print "Time for delete rollback (" . ($opt_medium_loop_count) . "): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+
+ $dbh->{AutoCommit} = 1;
+}
+
+
+####
+#### End of benchmark
+####
+
+$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
+$sth = $dbh->do("drop table bench2" . $server->{'drop_attr'}) or die $DBI::errstr;
+
+$dbh->disconnect; # close connection
+end_benchmark($start_time);