From: monty Date: November 8 2001 1:54pm Subject: bk commit into 4.0 tree List-Archive: http://lists.mysql.com/internals/2247 Message-Id: <200111081354.fA8DsNr26893@hundin.mysql.fi> Below is the list of changes that have just been committed into a 4.0 repository of monty. When monty does a push, they will be propogated to the main repository and within 24 hours after the push to the public repository. For information on how to access the public repository see http://www.mysql.com/doc/I/n/Installing_source_tree.html ChangeSet@stripped, 2001-11-08 15:54:22+02:00, monty@stripped --fast now does all insert/update loops in one transactions sql-bench/server-cfg.sh 1.34 01/11/08 15:54:22 monty@stripped +15 -0 --fast now does all insert/update loops in one transactions sql-bench/test-ATIS.sh 1.9 01/11/08 15:54:22 monty@stripped +12 -0 --fast now does all insert/update loops in one transactions sql-bench/test-alter-table.sh 1.7 01/11/08 15:54:22 monty@stripped +14 -0 --fast now does all insert/update loops in one transactions sql-bench/test-big-tables.sh 1.7 01/11/08 15:54:22 monty@stripped +15 -1 --fast now does all insert/update loops in one transactions sql-bench/test-insert.sh 1.37 01/11/08 15:54:22 monty@stripped +61 -2 --fast now does all insert/update loops in one transactions sql-bench/test-select.sh 1.19 01/11/08 15:54:22 monty@stripped +12 -0 --fast now does all insert/update loops in one transactions sql-bench/test-wisconsin.sh 1.10 01/11/08 15:54:22 monty@stripped +12 -0 --fast now does all insert/update loops in one transactions # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: monty # Host: hundin.mysql.fi # Root: /my/bk/mysql-4.0 --- 1.33/sql-bench/server-cfg.sh Wed Oct 17 19:39:38 2001 +++ 1.34/sql-bench/server-cfg.sh Thu Nov 8 15:54:22 2001 @@ -121,6 +121,7 @@ $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'vacuum'} = 1; # When using with --fast $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'NEG'} = 1; # Supports -id $limits{'alter_add_multi_col'}= 1; #Have ALTER TABLE t add a int,add b int; @@ -370,6 +371,7 @@ $self->{'limits'} = \%limits; $self->{'double_quotes'} = 0; $self->{'drop_attr'} = ""; + $self->{'transactions'} = 0; # No transactions $self->{'blob'} = "text(" . $limits{'max_text_size'} .")"; $self->{'text'} = "text(" . $limits{'max_text_size'} .")"; @@ -565,6 +567,7 @@ $self->{'text'} = "text"; $self->{'double_quotes'} = 1; $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{"vacuum"} = 1; $limits{'join_optimizer'} = 1; # Can optimize FROM tables $limits{'load_data_infile'} = 0; @@ -854,6 +857,7 @@ $self->{'text'} = "long varchar"; $self->{'double_quotes'} = 1; $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 9999; # Probably big enough $limits{'max_columns'} = 2000; # From crash-me @@ -1095,6 +1099,7 @@ $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 1258; $limits{'max_columns'} = 226; # server is disconnecting???? @@ -1377,6 +1382,7 @@ $self->{'text'} = "long"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{"vacuum"} = 1; $limits{'max_conditions'} = 9999; # (Actually not a limit) @@ -1633,6 +1639,7 @@ $self->{'text'} = "byte in table"; $self->{'double_quotes'} = 0; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'host'} = $host; $limits{'NEG'} = 1; # Supports -id @@ -1834,6 +1841,7 @@ $self->{'text'} = "blob"; # text ? $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 97; # We get 'Query is too complex' $limits{'max_columns'} = 255; # Max number of columns in table @@ -2010,6 +2018,7 @@ $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 1030; # We get 'Query is too complex' $limits{'max_columns'} = 250; # Max number of columns in table @@ -2197,6 +2206,7 @@ $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{"vacuum"} = 1; $limits{'max_conditions'} = 1030; # We get 'Query is too complex' @@ -2389,6 +2399,7 @@ $self->{'text'} = "long"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 50; # (Actually not a limit) $limits{'max_columns'} = 254; # Max number of columns in table @@ -2603,6 +2614,7 @@ $self->{'text'} = "varchar(255)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 418; # We get 'Query is too complex' $limits{'max_columns'} = 500; # Max number of columns in table @@ -2772,6 +2784,7 @@ $self->{'text'} = "character varying(15000)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'char_null'} = "cast(NULL as char(1))"; $self->{'numeric_null'} = "cast(NULL as int)"; @@ -2972,6 +2985,7 @@ $self->{'text'} = ""; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'char_null'} = ""; $self->{'numeric_null'} = ""; @@ -3184,6 +3198,7 @@ $self->{'text'} = "varchar(8000000)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ' restrict'; + $self->{'transactions'} = 1; # Transactions enabled $self->{'error_on_execute_means_zero_rows'}=1; $limits{'max_conditions'} = 5427; # (Actually not a limit) --- 1.8/sql-bench/test-ATIS.sh Sun Dec 24 15:18:58 2000 +++ 1.9/sql-bench/test-ATIS.sh Thu Nov 8 15:54:22 2001 @@ -106,6 +106,12 @@ } else { + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); + } + for ($ti = 0; $ti <= $#table_names; $ti++) { my $table_name = $table_names[$ti]; @@ -128,8 +134,14 @@ $row_count++; } } + if ($opt_fast && $server->{transactions}) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; + } close(DATA); } + if ($opt_lock_tables) { $dbh->do("UNLOCK TABLES"); --- 1.6/sql-bench/test-alter-table.sh Wed Nov 29 03:51:30 2000 +++ 1.7/sql-bench/test-alter-table.sh Thu Nov 8 15:54:22 2001 @@ -74,11 +74,25 @@ print "Insert data into the table\n"; $loop_time=new Benchmark; + +if ($opt_fast && defined($server->{transactions})) +{ + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); +} + for ($i=0 ; $i < $opt_row_count ; $i++) { $query="insert into bench values ( " . ("$i," x ($opt_start_field_count-1)) . "$i)"; $dbh->do($query) or die $DBI::errstr; } + +if ($opt_fast && defined($server->{transactions})) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time for insert ($opt_row_count)", --- 1.6/sql-bench/test-big-tables.sh Wed Nov 29 03:51:30 2000 +++ 1.7/sql-bench/test-big-tables.sh Thu Nov 8 15:54:22 2001 @@ -127,14 +127,28 @@ sub test_query { my($test_text,$result_text,$query,$dbh,$count)=@_; - my($i,$loop_time,$end_time); + my($i,$loop_time,$end_time, $using_transactions); print $test_text . "\n"; $loop_time=new Benchmark; + + $using_transactions=0; + if ($opt_fast && server->{transactions} && $query=~ /^insert /i) + { + $using_transactions=1; + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); + } for ($i=0 ; $i < $count ; $i++) { defined(fetch_all_rows($dbh,$query)) or die $DBI::errstr; } + if ($using_transactions) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; + } + $end_time=new Benchmark; print $result_text . "($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; --- 1.36/sql-bench/test-insert.sh Wed Oct 17 19:39:38 2001 +++ 1.37/sql-bench/test-insert.sh Thu Nov 8 15:54:22 2001 @@ -29,6 +29,7 @@ use DBI; use Benchmark; +use Data::Dumper; $opt_loop_count=100000; # number of rows/3 $small_loop_count=10; # Loop for full table retrieval @@ -132,6 +133,12 @@ $query="insert into bench1 (id,id2,id3,dummy1) values "; } +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); +} + if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'}) { $query_size=$server->{'limits'}->{'query_size'}; @@ -209,6 +216,12 @@ } } +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time for insert (" . ($total_rows) . "): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -234,6 +247,12 @@ { print "Testing insert of duplicates\n"; $loop_time=new Benchmark; + + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + } + for ($i=0 ; $i < $opt_loop_count ; $i++) { $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count); @@ -244,6 +263,11 @@ die "Didn't get an error when inserting duplicate record $tmp\n"; } } + if ($opt_fast && $server->{transactions}) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; + } $end_time=new Benchmark; print "Time for insert_duplicates (" . ($opt_loop_count) . "): " . @@ -577,7 +601,6 @@ } $sth->finish; - $count++; $sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id-0.0) from bench1") or die $DBI::errstr; $sth->execute or die $DBI::errstr; @@ -1250,6 +1273,11 @@ } $loop_time=new Benchmark; +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + $fields=$#fields; if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'}) { @@ -1297,6 +1325,13 @@ $dbh->do($query) or die "Got error $DBI::errstr with query: $query\n"; } } + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time for insert_key ($many_keys_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -1325,11 +1360,24 @@ print "Testing update of keys\n"; $loop_time=new Benchmark; + +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + for ($i=0 ; $i< 256; $i++) { $dbh->do("update bench1 set field5=1 where field_search=$i") or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field_search=$i\n"; } + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time for update_of_primary_key_many_keys (256): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -1420,12 +1468,18 @@ "dummy1 char(30)"], ["primary key (id,id2)", "index index_id3 (id3)"])); + + $loop_time=new Benchmark; + if ($opt_lock_tables) { $sth = $dbh->do("LOCK TABLES bench1 write") || die $DBI::errstr; } + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + } - $loop_time=new Benchmark; print "Inserting $opt_loop_count rows with multiple values\n"; $query="insert into bench1 values "; $res=$query; @@ -1447,6 +1501,11 @@ if ($opt_lock_tables) { $sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr; + } + if ($opt_fast && $server->{transactions}) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; } $end_time=new Benchmark; --- 1.18/sql-bench/test-select.sh Sat Nov 3 21:33:10 2001 +++ 1.19/sql-bench/test-select.sh Thu Nov 8 15:54:22 2001 @@ -92,6 +92,12 @@ print "Inserting $opt_loop_count rows\n"; $loop_time=new Benchmark; + +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + $query="insert into bench1 values ("; $half_done=$opt_loop_count/2; for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--) @@ -103,6 +109,12 @@ { # Test with different insert $query="insert into bench1 (region,idn,rev_idn,grp) values ("; } +} + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; } $end_time=new Benchmark; --- 1.9/sql-bench/test-wisconsin.sh Wed Oct 17 19:39:38 2001 +++ 1.10/sql-bench/test-wisconsin.sh Thu Nov 8 15:54:22 2001 @@ -109,6 +109,11 @@ } else { + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + } + for ($ti = 0; $ti <= $#table_names; $ti++) { my $table_name = $table_names[$ti]; @@ -133,10 +138,17 @@ } close(DATA); } + if ($opt_lock_tables) { do_query($dbh,"UNLOCK TABLES"); } +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time to insert ($row_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n";