summaryrefslogtreecommitdiff
path: root/sql-bench
diff options
context:
space:
mode:
Diffstat (limited to 'sql-bench')
-rw-r--r--sql-bench/Makefile.am3
-rw-r--r--sql-bench/bench-init.pl.sh13
-rw-r--r--sql-bench/compare-results.sh2
-rw-r--r--sql-bench/example9
-rw-r--r--sql-bench/myisam.cnf3
-rw-r--r--sql-bench/server-cfg.sh43
-rw-r--r--sql-bench/test-ATIS.sh2
-rw-r--r--sql-bench/test-alter-table.sh2
-rw-r--r--sql-bench/test-big-tables.sh2
-rw-r--r--sql-bench/test-connect.sh45
-rw-r--r--sql-bench/test-create.sh14
-rw-r--r--sql-bench/test-select.sh29
-rwxr-xr-xsql-bench/test-table-elimination.sh316
-rw-r--r--sql-bench/test-transactions.sh4
-rw-r--r--sql-bench/test-wisconsin.sh2
15 files changed, 443 insertions, 46 deletions
diff --git a/sql-bench/Makefile.am b/sql-bench/Makefile.am
index a7aff83e7aa..93f9a61d9d4 100644
--- a/sql-bench/Makefile.am
+++ b/sql-bench/Makefile.am
@@ -81,6 +81,3 @@ SUFFIXES = .sh
$< > $@-t
@CHMOD@ +x $@-t
@MV@ $@-t $@
-
-# Don't update the files from bitkeeper
-%::SCCS/s.%
diff --git a/sql-bench/bench-init.pl.sh b/sql-bench/bench-init.pl.sh
index 2d767fc5f78..a46bb2d7a49 100644
--- a/sql-bench/bench-init.pl.sh
+++ b/sql-bench/bench-init.pl.sh
@@ -40,8 +40,8 @@ require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
$|=1; # Output data immediately
-$opt_skip_test=$opt_skip_create=$opt_skip_delete=$opt_verbose=$opt_fast_insert=$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=$opt_log=$opt_use_old_results=$opt_help=$opt_odbc=$opt_small_test=$opt_small_tables=$opt_samll_key_tables=$opt_stage=$opt_old_headers=$opt_die_on_errors=$opt_tcpip=$opt_random=$opt_only_missing_tests=0;
-$opt_cmp=$opt_user=$opt_password=$opt_connect_options="";
+$opt_skip_test=$opt_skip_create=$opt_skip_delete=$opt_verbose=$opt_fast_insert=$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=$opt_log=$opt_use_old_results=$opt_help=$opt_odbc=$opt_small_test=$opt_small_tables=$opt_samll_key_tables=$opt_stage=$opt_old_headers=$opt_die_on_errors=$opt_tcpip=$opt_random=$opt_only_missing_tests=$opt_temporary_tables=0;
+$opt_cmp=$opt_user=$opt_password=$opt_connect_options=$opt_connect_command= "";
$opt_server="mysql"; $opt_dir="output";
$opt_host="localhost";$opt_database="test";
$opt_machine=""; $opt_suffix="";
@@ -60,7 +60,7 @@ $log_prog_args=join(" ", skip_arguments(\@ARGV,"comments","cmp","server",
"use-old-results","skip-test",
"optimization","hw",
"machine", "dir", "suffix", "log"));
-GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","threads=i","random","old-headers","die-on-errors","create-options=s","hires","tcpip","silent","optimization=s","hw=s","socket=s","connect-options=s","only-missing-tests") || usage();
+GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","threads=i","random","old-headers","die-on-errors","create-options=s","hires","tcpip","silent","optimization=s","hw=s","socket=s","connect-options=s","connect-command=s","only-missing-tests","temporary-tables") || usage();
usage() if ($opt_help);
$server=get_server($opt_server,$opt_host,$opt_database,$opt_odbc,
@@ -455,6 +455,9 @@ All benchmarks takes the following options:
create all MySQL tables as InnoDB tables use:
--create-options=ENGINE=InnoDB
+--temporary-tables
+ Use temporary tables for all tests.
+
--database (Default $opt_database)
In which database the test tables are created.
@@ -596,6 +599,10 @@ All benchmarks takes the following options:
Add options, which uses at DBI connect.
For example --connect-options=mysql_read_default_file=/etc/my.cnf.
+--connect-command='SQL command'
+ Initialization command to execute when logged in. Useful for setting
+ up the environment.
+
EOF
exit(0);
}
diff --git a/sql-bench/compare-results.sh b/sql-bench/compare-results.sh
index 9ca355a8e46..cae7f974df1 100644
--- a/sql-bench/compare-results.sh
+++ b/sql-bench/compare-results.sh
@@ -513,7 +513,7 @@ sub print_value
else
{
$first=1 if ($first == 0); # Assume that it took one second instead of 0
- $tmp= sprintf("%.2f",$value/$first);
+ $tmp= sprintf("%.3f",$value/$first);
}
if (defined($flags))
{
diff --git a/sql-bench/example b/sql-bench/example
index df2a9b8be69..877fd080ac5 100644
--- a/sql-bench/example
+++ b/sql-bench/example
@@ -6,15 +6,14 @@ machine="Linux-x64"
# InnoDB tests
-./run-all-tests --suffix=-innodb --comments="Engine=InnoDB --innodb_log_file_size=100M" --create-options="ENGINE=InnoDB" --hw="$hw" --optimization="$optimization" --machine="$machine" --log
-
-./run-all-tests --suffix=_fast-innodb --comments="Engine=InnoDB --innodb_log_file_size=100M" --create-options="ENGINE=InnoDB" --hw="$hw" --optimization="$optimization" --machine="$machine" --fast --log
+./run-all-tests --suffix=-innodb --comments="Engine=InnoDB --innodb_buffer_pool_size=256M --innodb_additional_mem_pool_size=20M --innodb_log_file_size=1000M --innodb_log_buffer_size=16M --innodb_lock_wait_timeout=50 --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DIRECT --innodb_log_files_in_group=2 --skip-innodb-doublewrite" --create-options="ENGINE=InnoDB" --hw="$hw" --optimization="$optimization" --machine="$machine" --log
+./run-all-tests --suffix=_fast-innodb --comments="Engine=InnoDB --innodb_buffer_pool_size=256M --innodb_additional_mem_pool_size=20M --innodb_log_file_size=1000M --innodb_log_buffer_size=16M --innodb_lock_wait_timeout=50 --innodb_flush_log_at_trx_commit=1 --innodb_flush_method=O_DIRECT --innodb_log_files_in_group=2 --skip-innodb-doublewrite" --create-options="ENGINE=InnoDB" --hw="$hw" --optimization="$optimization" --machine="$machine" --fast --log
# MyISAM tests
-./run-all-tests --suffix=-myisam --comments="Engine=MyISAM key_buffer_size=16M" --create-options="ENGINE=myisam" --hw="$hw" --optimization="$optimization" --machine="$machine" --log
+./run-all-tests --suffix=-myisam --comments="Engine=MyISAM key_buffer_size=256M" --create-options="ENGINE=myisam" --hw="$hw" --optimization="$optimization" --machine="$machine" --log
-./run-all-tests --suffix=_fast-myisam --comments="Engine=MyISAM key_buffer_size=16M" --create-options="ENGINE=myisam" --hw="$hw" --optimization="$optimization" --machine="$machine" --fast --log
+./run-all-tests --suffix=_fast-myisam --comments="Engine=MyISAM key_buffer_size=256M" --create-options="ENGINE=myisam" --hw="$hw" --optimization="$optimization" --machine="$machine" --fast --log
compare-results --relative output/RUN-mysql-myisam-* output/RUN-mysql_fast-myisam* output/RUN-mysql*
diff --git a/sql-bench/myisam.cnf b/sql-bench/myisam.cnf
new file mode 100644
index 00000000000..4229f56cefb
--- /dev/null
+++ b/sql-bench/myisam.cnf
@@ -0,0 +1,3 @@
+[mysqld]
+data=/data
+key_buffer_size=256M
diff --git a/sql-bench/server-cfg.sh b/sql-bench/server-cfg.sh
index 9137e57bfe1..35512f36560 100644
--- a/sql-bench/server-cfg.sh
+++ b/sql-bench/server-cfg.sh
@@ -160,6 +160,7 @@ sub new
$limits{'max_index'} = 16; # Max number of keys
$limits{'max_index_parts'} = 16; # Max segments/key
$limits{'max_tables'} = (($machine || '') =~ "^win") ? 5000 : 65000;
+ $limits{'max_temporary_tables'}= 400;
$limits{'max_text_size'} = 1000000; # Good enough for tests
$limits{'multi_drop'} = 1; # Drop table can take many tables
$limits{'order_by_position'} = 1; # Can use 'ORDER BY 1'
@@ -190,6 +191,7 @@ sub new
$self->{'transactions'} = 1; # Transactions enabled
$limits{'max_columns'} = 90; # Max number of columns in table
$limits{'max_tables'} = 32; # No comments
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
}
if (defined($main::opt_create_options) &&
$main::opt_create_options =~ /engine=bdb/i)
@@ -201,6 +203,7 @@ sub new
{
$limits{'working_blobs'} = 0; # Blobs not implemented yet
$limits{'max_tables'} = 500;
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
$self->{'transactions'} = 1; # Transactions enabled
}
@@ -250,6 +253,11 @@ sub connect
die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n";
$dbh->do("SET OPTION LOG_OFF=1,UPDATE_LOG=0");
+ if ($main::opt_connect_command ne "")
+ {
+ $dbh->do($main::opt_connect_command) or
+ die "Can't execute connect_command: $main::opt_connect_command error: $DBI::errstr\n";
+ }
return $dbh;
}
@@ -266,7 +274,14 @@ sub create
my($self,$table_name,$fields,$index,$options) = @_;
my($query,@queries);
- $query="create table $table_name (";
+ if ($main::opt_temporary_tables)
+ {
+ $query="create temporary table $table_name (";
+ }
+ else
+ {
+ $query="create table $table_name (";
+ }
foreach $field (@$fields)
{
# $field =~ s/ decimal/ double(10,2)/i;
@@ -389,6 +404,7 @@ sub new
$limits{'max_conditions'} = 74;
$limits{'max_columns'} = 75;
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
$limits{'max_text_size'} = 32000;
$limits{'query_size'} = 65535;
$limits{'max_index'} = 5;
@@ -618,7 +634,9 @@ sub new
$limits{'max_conditions'} = 9999; # This makes Pg real slow
$limits{'max_index'} = 64; # Big enough
$limits{'max_index_parts'} = 16;
- $limits{'max_tables'} = 5000; # 10000 crashes pg 7.0.2
+ $limits{'max_tables'} = 65000;
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 65000; # Good enough for test
$limits{'multi_drop'} = 1;
$limits{'order_by_position'} = 1;
@@ -869,6 +887,8 @@ sub new
$limits{'max_conditions'} = 9999; # Probably big enough
$limits{'max_columns'} = 2000; # From crash-me
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 65492; # According to tests
$limits{'query_size'} = 65535; # Probably a limit
$limits{'max_index'} = 64; # Probably big enough
@@ -1100,6 +1120,7 @@ sub new
# above this value .... but can handle 2419 columns
# maybe something for crash-me ... but how to check ???
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
$limits{'max_text_size'} = 4095; # max returned ....
$limits{'query_size'} = 65535; # Not a limit, big enough
$limits{'max_index'} = 64; # Big enough
@@ -1370,6 +1391,8 @@ sub new
$limits{'max_conditions'} = 9999; # (Actually not a limit)
$limits{'max_columns'} = 254; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 2000; # Limit for blob test-connect
$limits{'query_size'} = 65525; # Max size with default buffers.
$limits{'max_index'} = 16; # Max number of keys
@@ -1643,6 +1666,8 @@ sub new
$limits{'max_column_name'} = 18; # max table and column name
$limits{'max_columns'} = 994; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_index'} = 64; # Max number of keys
$limits{'max_index_parts'} = 15; # Max segments/key
$limits{'max_text_size'} = 65535; # Max size with default buffers. ??
@@ -1831,6 +1856,8 @@ sub new
$limits{'max_conditions'} = 97; # We get 'Query is too complex'
$limits{'max_columns'} = 255; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 255; # Max size with default buffers.
$limits{'query_size'} = 65535; # Not a limit, big enough
$limits{'max_index'} = 32; # Max number of keys
@@ -2016,6 +2043,8 @@ sub new
$limits{'max_conditions'} = 1030; # We get 'Query is too complex'
$limits{'max_columns'} = 250; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 9830; # Max size with default buffers.
$limits{'query_size'} = 9830; # Max size with default buffers.
$limits{'max_index'} = 64; # Max number of keys
@@ -2212,6 +2241,8 @@ sub new
$limits{'max_conditions'} = 1030; # We get 'Query is too complex'
$limits{'max_columns'} = 250; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 9830; # Max size with default buffers.
$limits{'query_size'} = 9830; # Max size with default buffers.
$limits{'max_index'} = 64; # Max number of keys
@@ -2444,6 +2475,8 @@ sub new
$limits{'max_conditions'} = 50; # (Actually not a limit)
$limits{'max_columns'} = 254; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 2000; # Limit for blob test-connect
$limits{'query_size'} = 65525; # Max size with default buffers.
$limits{'max_index'} = 16; # Max number of keys
@@ -2648,6 +2681,8 @@ sub new
$limits{'max_conditions'} = 418; # We get 'Query is too complex'
$limits{'max_columns'} = 500; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
+
$limits{'max_text_size'} = 254; # Max size with default buffers.
$limits{'query_size'} = 254; # Max size with default buffers.
$limits{'max_index'} = 48; # Max number of keys
@@ -2826,6 +2861,7 @@ sub new
$limits{'max_conditions'} = 9999; # (Actually not a limit)
$limits{'max_columns'} = 252; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
$limits{'max_text_size'} = 15000; # Max size with default buffers.
$limits{'query_size'} = 1000000; # Max size with default buffers.
$limits{'max_index'} = 32; # Max number of keys
@@ -3028,6 +3064,7 @@ sub new
$limits{'max_conditions'} = 9999; # (Actually not a limit)
$limits{'max_columns'} = 252; # Max number of columns in table
$limits{'max_tables'} = 65000; # Should be big enough
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
$limits{'max_text_size'} = 15000; # Max size with default buffers.
$limits{'query_size'} = 1000000; # Max size with default buffers.
$limits{'max_index'} = 65000; # Max number of keys
@@ -3224,6 +3261,7 @@ sub new
# The following should be 8192, but is smaller because Frontbase crashes..
$limits{'max_columns'} = 150; # Max number of columns in table
$limits{'max_tables'} = 5000; # 10000 crashed FrontBase
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
$limits{'max_text_size'} = 65000; # Max size with default buffers.
$limits{'query_size'} = 8000000; # Max size with default buffers.
$limits{'max_index'} = 38; # Max number of keys
@@ -3436,6 +3474,7 @@ sub new
$limits{'max_conditions'} = 9999; # (Actually not a limit) *
$limits{'max_columns'} = 1023; # Max number of columns in table *
$limits{'max_tables'} = 65000; # Should be big enough * unlimited actually
+ $limits{'max_temporary_tables'}= $limits{"max_tables"};
$limits{'max_text_size'} = 15000; # Max size with default buffers.
$limits{'query_size'} = 64*1024; # Max size with default buffers. *64 kb by default. May be set by system variable
$limits{'max_index'} = 510; # Max number of keys *
diff --git a/sql-bench/test-ATIS.sh b/sql-bench/test-ATIS.sh
index dc6bf28e183..ca1132435ff 100644
--- a/sql-bench/test-ATIS.sh
+++ b/sql-bench/test-ATIS.sh
@@ -29,7 +29,7 @@ use Cwd;
use DBI;
use Benchmark;
-$opt_loop_count=100; # Run selects this many times
+$opt_loop_count=5000; # Run selects this many times
$pwd = cwd(); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
diff --git a/sql-bench/test-alter-table.sh b/sql-bench/test-alter-table.sh
index a724e5cfc08..6dc5ce35841 100644
--- a/sql-bench/test-alter-table.sh
+++ b/sql-bench/test-alter-table.sh
@@ -26,7 +26,7 @@ use DBI;
use Benchmark;
$opt_start_field_count=8; # start with this many fields
-$opt_loop_count=100; # How many tests to do
+$opt_loop_count=10000; # How many tests to do
$opt_row_count=1000; # Rows in the table
$opt_field_count=1000; # Add until this many fields.
$opt_time_limit=10*60; # Don't wait more than 10 min for some tests
diff --git a/sql-bench/test-big-tables.sh b/sql-bench/test-big-tables.sh
index 67c87172cf0..68b4afaa429 100644
--- a/sql-bench/test-big-tables.sh
+++ b/sql-bench/test-big-tables.sh
@@ -26,7 +26,7 @@ use Cwd;
use DBI;
use Benchmark;
-$opt_loop_count=1000; # Change this to make test harder/easier
+$opt_loop_count=70000; # Change this to make test harder/easier
$opt_field_count=1000;
$pwd = cwd(); $pwd = "." if ($pwd eq '');
diff --git a/sql-bench/test-connect.sh b/sql-bench/test-connect.sh
index a307d5e1014..50024e7e055 100644
--- a/sql-bench/test-connect.sh
+++ b/sql-bench/test-connect.sh
@@ -29,7 +29,7 @@ use Cwd;
use DBI;
use Benchmark;
-$opt_loop_count=100000; # Change this to make test harder/easier
+$opt_loop_count=500000; # Change this to make test harder/easier
$str_length=65000; # This is the length of blob strings in PART:5
$max_test=20; # How many times to test if the server is busy
@@ -162,41 +162,48 @@ if ($opt_fast && defined($server->{vacuum}))
{
$server->vacuum(0,\$dbh);
}
-$dbh->disconnect;
+if (!$main::opt_temporary_tables)
+{
+ $dbh->disconnect;
+}
#
# First test connect/select/disconnect
#
-print "Testing connect/select 1 row from table/disconnect\n";
+if (!$main::opt_temporary_tables)
+{
+ print "Testing connect/select 1 row from table/disconnect\n";
-$loop_time=new Benchmark;
-$errors=0;
+ $loop_time=new Benchmark;
+ $errors=0;
-for ($i=0 ; $i < $small_loop_count ; $i++)
-{
- for ($j=0; $j < $max_test ; $j++)
+ for ($i=0 ; $i < $small_loop_count ; $i++)
{
- last if ($dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password));
- $errors++;
- }
- die $DBI::errstr if ($j == $max_test);
+ for ($j=0; $j < $max_test ; $j++)
+ {
+ last if ($dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password));
+ $errors++;
+ }
+ die $DBI::errstr if ($j == $max_test);
- $sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record
+ $sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record
or die $DBI::errstr;
- $dbh->disconnect;
-}
+ $dbh->disconnect;
+ }
-$end_time=new Benchmark;
-print "Warning: $errors connections didn't work without a time delay\n" if ($errors);
-print "Time to connect+select_1_row ($small_loop_count): " .
+ $end_time=new Benchmark;
+ print "Warning: $errors connections didn't work without a time delay\n" if ($errors);
+ print "Time to connect+select_1_row ($small_loop_count): " .
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+ $dbh = $server->connect();
+}
+
#
# The same test, but without connect/disconnect
#
print "Testing select 1 row from table\n";
-$dbh = $server->connect();
$loop_time=new Benchmark;
for ($i=0 ; $i < $opt_loop_count ; $i++)
diff --git a/sql-bench/test-create.sh b/sql-bench/test-create.sh
index fd9269398b2..235cacd86ec 100644
--- a/sql-bench/test-create.sh
+++ b/sql-bench/test-create.sh
@@ -48,7 +48,15 @@ if ($opt_small_test)
$create_loop_count/=1000;
}
-$max_tables=min($limits->{'max_tables'},$opt_loop_count);
+if ($opt_temporary_tables)
+{
+ $max_tables=min($limits->{'max_tables'},$opt_loop_count);
+}
+else
+{
+ $max_tables=min($limits->{'max_tables'},$opt_loop_count);
+ $max_tables=400;
+}
if ($opt_small_test)
{
@@ -72,7 +80,7 @@ $dbh = $server->connect();
if ($opt_force) # If tables used in this test exist, drop 'em
{
print "Okay..Let's make sure that our tables don't exist yet.\n\n";
- for ($i=1 ; $i <= $max_tables ; $i++)
+ for ($i=1 ; $i <= max($max_tables, $create_loop_count) ; $i++)
{
$dbh->do("drop table bench_$i" . $server->{'drop_attr'});
}
@@ -246,7 +254,7 @@ for ($i=2 ; $i <= $keys ; $i++)
}
$loop_time=new Benchmark;
-for ($i=1 ; $i <= $opt_loop_count ; $i++)
+for ($i=1 ; $i <= $create_loop_count ; $i++)
{
do_many($dbh,$server->create("bench_$i", \@fields, \@keys));
$dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr;
diff --git a/sql-bench/test-select.sh b/sql-bench/test-select.sh
index 8ea205cbfb7..1752c05079b 100644
--- a/sql-bench/test-select.sh
+++ b/sql-bench/test-select.sh
@@ -27,7 +27,7 @@ use Getopt::Long;
use Benchmark;
$opt_loop_count=10000;
-$opt_medium_loop_count=1000;
+$opt_medium_loop_count=7000;
$opt_small_loop_count=10;
$opt_regions=6;
$opt_groups=100;
@@ -69,7 +69,8 @@ do_many($dbh,$server->create("bench1",
["region char(1) NOT NULL",
"idn integer(6) NOT NULL",
"rev_idn integer(6) NOT NULL",
- "grp integer(6) NOT NULL"],
+ "grp integer(6) NOT NULL",
+ "grp_no_key integer(6) NOT NULL"],
["primary key (region,idn)",
"unique (region,rev_idn)",
"unique (region,grp,idn)"]));
@@ -106,10 +107,10 @@ for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--)
{
$grp=$id*3 % $opt_groups;
$region=chr(65+$id%$opt_regions);
- do_query($dbh,"$query'$region',$id,$rev_id,$grp)");
+ do_query($dbh,"$query'$region',$id,$rev_id,$grp,$grp)");
if ($id == $half_done)
{ # Test with different insert
- $query="insert into bench1 (region,idn,rev_idn,grp) values (";
+ $query="insert into bench1 (region,idn,rev_idn,grp,grp_no_key) values (";
}
}
@@ -324,6 +325,26 @@ if ($limits->{'group_functions'})
$end_time=new Benchmark;
print "Time for count_group_on_key_parts ($i:$rows): " .
timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $rows+=fetch_all_rows($dbh,"select grp_no_key,count(*) from bench1 group by grp_no_key");
+ }
+ $end_time=new Benchmark;
+ print "Time for count_group ($i:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $rows+=fetch_all_rows($dbh,"select grp_no_key,count(*) as cnt from bench1 group by grp_no_key order by cnt");
+ }
+ $end_time=new Benchmark;
+ print "Time for count_group_with_order ($i:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
}
if ($limits->{'group_distinct_functions'})
diff --git a/sql-bench/test-table-elimination.sh b/sql-bench/test-table-elimination.sh
new file mode 100755
index 00000000000..338a7ceb4b5
--- /dev/null
+++ b/sql-bench/test-table-elimination.sh
@@ -0,0 +1,316 @@
+#!@PERL@
+# Test of table elimination feature
+
+use Cwd;
+use DBI;
+use Getopt::Long;
+use Benchmark;
+
+$opt_loop_count=100000;
+$opt_medium_loop_count=10000;
+$opt_small_loop_count=100;
+
+$pwd = cwd(); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+if ($opt_small_test)
+{
+ $opt_loop_count/=10;
+ $opt_medium_loop_count/=10;
+ $opt_small_loop_count/=10;
+}
+
+print "Testing table elimination feature\n";
+print "The test table has $opt_loop_count rows.\n\n";
+
+# A query to get the recent versions of all attributes:
+$select_current_full_facts="
+ select
+ F.id, A1.attr1, A2.attr2
+ from
+ elim_facts F
+ left join elim_attr1 A1 on A1.id=F.id
+ left join elim_attr2 A2 on A2.id=F.id and
+ A2.fromdate=(select MAX(fromdate) from
+ elim_attr2 where id=A2.id);
+";
+$select_current_full_facts="
+ select
+ F.id, A1.attr1, A2.attr2
+ from
+ elim_facts F
+ left join elim_attr1 A1 on A1.id=F.id
+ left join elim_attr2 A2 on A2.id=F.id and
+ A2.fromdate=(select MAX(fromdate) from
+ elim_attr2 where id=F.id);
+";
+# TODO: same as above but for some given date also?
+# TODO:
+
+
+####
+#### Connect and start timeing
+####
+
+$dbh = $server->connect();
+$start_time=new Benchmark;
+
+####
+#### Create needed tables
+####
+
+goto select_test if ($opt_skip_create);
+
+print "Creating tables\n";
+$dbh->do("drop table elim_facts" . $server->{'drop_attr'});
+$dbh->do("drop table elim_attr1" . $server->{'drop_attr'});
+$dbh->do("drop table elim_attr2" . $server->{'drop_attr'});
+
+# The facts table
+do_many($dbh,$server->create("elim_facts",
+ ["id integer"],
+ ["primary key (id)"]));
+
+# Attribute1, non-versioned
+do_many($dbh,$server->create("elim_attr1",
+ ["id integer",
+ "attr1 integer"],
+ ["primary key (id)",
+ "key (attr1)"]));
+
+# Attribute2, time-versioned
+do_many($dbh,$server->create("elim_attr2",
+ ["id integer",
+ "attr2 integer",
+ "fromdate date"],
+ ["primary key (id, fromdate)",
+ "key (attr2,fromdate)"]));
+
+#NOTE: ignoring: if ($limits->{'views'})
+$dbh->do("drop view elim_current_facts");
+$dbh->do("create view elim_current_facts as $select_current_full_facts");
+
+if ($opt_lock_tables)
+{
+ do_query($dbh,"LOCK TABLES elim_facts, elim_attr1, elim_attr2 WRITE");
+}
+
+if ($opt_fast && defined($server->{vacuum}))
+{
+ $server->vacuum(1,\$dbh);
+}
+
+####
+#### Fill the facts table
+####
+$n_facts= $opt_loop_count;
+
+if ($opt_fast && $server->{transactions})
+{
+ $dbh->{AutoCommit} = 0;
+}
+
+print "Inserting $n_facts rows into facts table\n";
+$loop_time=new Benchmark;
+
+$query="insert into elim_facts values (";
+for ($id=0; $id < $n_facts ; $id++)
+{
+ do_query($dbh,"$query $id)");
+}
+
+if ($opt_fast && $server->{transactions})
+{
+ $dbh->commit;
+ $dbh->{AutoCommit} = 1;
+}
+
+$end_time=new Benchmark;
+print "Time to insert ($n_facts): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+
+####
+#### Fill attr1 table
+####
+if ($opt_fast && $server->{transactions})
+{
+ $dbh->{AutoCommit} = 0;
+}
+
+print "Inserting $n_facts rows into attr1 table\n";
+$loop_time=new Benchmark;
+
+$query="insert into elim_attr1 values (";
+for ($id=0; $id < $n_facts ; $id++)
+{
+ $attr1= ceil(rand($n_facts));
+ do_query($dbh,"$query $id, $attr1)");
+}
+
+if ($opt_fast && $server->{transactions})
+{
+ $dbh->commit;
+ $dbh->{AutoCommit} = 1;
+}
+
+$end_time=new Benchmark;
+print "Time to insert ($n_facts): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+
+####
+#### Fill attr2 table
+####
+if ($opt_fast && $server->{transactions})
+{
+ $dbh->{AutoCommit} = 0;
+}
+
+print "Inserting $n_facts rows into attr2 table\n";
+$loop_time=new Benchmark;
+
+for ($id=0; $id < $n_facts ; $id++)
+{
+ # Two values for each $id - current one and obsolete one.
+ $attr1= ceil(rand($n_facts));
+ $query="insert into elim_attr2 values ($id, $attr1, now())";
+ do_query($dbh,$query);
+ $query="insert into elim_attr2 values ($id, $attr1, '2009-01-01')";
+ do_query($dbh,$query);
+}
+
+if ($opt_fast && $server->{transactions})
+{
+ $dbh->commit;
+ $dbh->{AutoCommit} = 1;
+}
+
+$end_time=new Benchmark;
+print "Time to insert ($n_facts): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+
+####
+#### Finalize the database population
+####
+
+if ($opt_lock_tables)
+{
+ do_query($dbh,"UNLOCK TABLES");
+}
+
+if ($opt_fast && defined($server->{vacuum}))
+{
+ $server->vacuum(0,\$dbh,["elim_facts", "elim_attr1", "elim_attr2"]);
+}
+
+if ($opt_lock_tables)
+{
+ do_query($dbh,"LOCK TABLES elim_facts, elim_attr1, elim_attr2 WRITE");
+}
+
+####
+#### Do some selects on the table
+####
+
+select_test:
+
+#
+# The selects will be:
+# - N pk-lookups with all attributes
+# - pk-attribute-based lookup
+# - latest-attribute value based lookup.
+
+
+###
+### Bare facts select:
+###
+print "testing bare facts facts table\n";
+$loop_time=new Benchmark;
+$rows=0;
+for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+{
+ $val= ceil(rand($n_facts));
+ $rows+=fetch_all_rows($dbh,"select * from elim_facts where id=$val");
+}
+$count=$i;
+
+$end_time=new Benchmark;
+print "time for select_bare_facts ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+
+###
+### Full facts select, no elimination:
+###
+print "testing full facts facts table\n";
+$loop_time=new Benchmark;
+$rows=0;
+for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+{
+ $val= rand($n_facts);
+ $rows+=fetch_all_rows($dbh,"select * from elim_current_facts where id=$val");
+}
+$count=$i;
+
+$end_time=new Benchmark;
+print "time for select_two_attributes ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+###
+### Now with elimination: select only only one fact
+###
+print "testing selection of one attribute\n";
+$loop_time=new Benchmark;
+$rows=0;
+for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+{
+ $val= rand($n_facts);
+ $rows+=fetch_all_rows($dbh,"select id, attr1 from elim_current_facts where id=$val");
+}
+$count=$i;
+
+$end_time=new Benchmark;
+print "time for select_one_attribute ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+###
+### Now with elimination: select only only one fact
+###
+print "testing selection of one attribute\n";
+$loop_time=new Benchmark;
+$rows=0;
+for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+{
+ $val= rand($n_facts);
+ $rows+=fetch_all_rows($dbh,"select id, attr2 from elim_current_facts where id=$val");
+}
+$count=$i;
+
+$end_time=new Benchmark;
+print "time for select_one_attribute ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+
+;
+
+####
+#### End of benchmark
+####
+
+if ($opt_lock_tables)
+{
+ do_query($dbh,"UNLOCK TABLES");
+}
+if (!$opt_skip_delete)
+{
+ do_query($dbh,"drop table elim_facts, elim_attr1, elim_attr2" . $server->{'drop_attr'});
+}
+
+if ($opt_fast && defined($server->{vacuum}))
+{
+ $server->vacuum(0,\$dbh);
+}
+
+$dbh->disconnect; # close connection
+
+end_benchmark($start_time);
+
diff --git a/sql-bench/test-transactions.sh b/sql-bench/test-transactions.sh
index f6672619da0..b0aee8c6d38 100644
--- a/sql-bench/test-transactions.sh
+++ b/sql-bench/test-transactions.sh
@@ -29,8 +29,8 @@ use Benchmark;
$opt_groups=27; # Characters are 'A' -> Z
-$opt_loop_count=10000; # Change this to make test harder/easier
-$opt_medium_loop_count=100; # Change this to make test harder/easier
+$opt_loop_count=500000; # Change this to make test harder/easier
+$opt_medium_loop_count=10000; # Change this to make test harder/easier
$pwd = cwd(); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
diff --git a/sql-bench/test-wisconsin.sh b/sql-bench/test-wisconsin.sh
index 10519694bf0..4fb3bc65f35 100644
--- a/sql-bench/test-wisconsin.sh
+++ b/sql-bench/test-wisconsin.sh
@@ -22,7 +22,7 @@ use Cwd;
use DBI;
use Benchmark;
-$opt_loop_count=10;
+$opt_loop_count=5000;
$pwd = cwd(); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
View Lorry Controller Status