1#!/usr/bin/perl -w 2 3# Copyright (C) 2000, 2001 MySQL AB 4# Use is subject to license terms 5# 6# This program is free software; you can redistribute it and/or modify 7# it under the terms of the GNU General Public License, version 2.0, 8# as published by the Free Software Foundation. 9# 10# This program is also distributed with certain software (including 11# but not limited to OpenSSL) that is licensed under separate terms, 12# as designated in a particular file or component or in included license 13# documentation. The authors of MySQL hereby grant you an additional 14# permission to link the program and your derivative works with the 15# separately licensed software that they have included with MySQL. 16# 17# This program is distributed in the hope that it will be useful, 18# but WITHOUT ANY WARRANTY; without even the implied warranty of 19# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 20# GNU General Public License, version 2.0, for more details. 21# 22# You should have received a copy of the GNU General Public License 23# along with this program; if not, write to the Free Software 24# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA 25 26# This is a test with uses 5 processes to insert, update and select from 27# two tables. 28# One inserts records in the tables, one updates some record in it and 29# the last 3 does different selects on the tables. 30# Er, hmmm..., something like that :^) 31# Modified to do crazy-join, � la Nasdaq. 32# 33# This test uses the old obsolete mysql interface. For a test that uses 34# DBI, please take a look at fork_big.pl 35 36$opt_loop_count=10000; # Change this to make test harder/easier 37 38##################### Standard benchmark inits ############################## 39 40use Mysql; 41use Getopt::Long; 42use Benchmark; 43 44package main; 45 46$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= 47 $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; 48$opt_host=""; $opt_db="test"; 49 50GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in", 51 "skip-delete", "verbose","fast-insert","lock-tables","debug","fast", 52 "force") || die "Aborted"; 53$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$Mysql::db_errstr=$opt_force=undef; # Ignore warnings from these 54 55print "Testing 10 multiple connections to a server with 1 insert/update\n"; 56print "and 8 select connections and one ALTER TABLE.\n"; 57 58 59@testtables = qw(bench_f21 bench_f22 bench_f23 bench_f24 bench_f25); 60$numtables = $#testtables; # make emacs happier 61$dtable = "directory"; 62#### 63#### Start timeing and start test 64#### 65 66$start_time=new Benchmark; 67if (!$opt_skip_create) 68{ 69 $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; 70 $Mysql::QUIET = 1; 71 foreach $table (@testtables) { 72 $dbh->Query("drop table $table"); 73 } 74 $dbh->Query("drop table $dtable"); 75 $Mysql::QUIET = 0; 76 77 foreach $table (@testtables) { 78 print "Creating table $table in database $opt_db\n"; 79 $dbh->Query("create table $table". 80 " (id int(6) not null,". 81 " info varchar(32),". 82 " marker timestamp,". 83 " primary key(id))") 84 or die $Mysql::db_errstr; 85 } 86 print "Creating directory table $dtable in $opt_db\n"; 87 $dbh->Query("create table $dtable (id int(6), last int(6))") 88 or die $Mysql::db_errstr; 89 # Populate directory table 90 for $i ( 0 .. $numtables ) { 91 $dbh->Query("insert into $dtable values($i, 0)"); 92 } 93 $dbh=0; # Close handler 94} 95$|= 1; # Autoflush 96 97#### 98#### Start the tests 99#### 100 101#$test_index = 0; 102 103test_1() if (($pid=fork()) == 0); $work{$pid}="insert"; 104test_2() if (($pid=fork()) == 0); $work{$pid}="simple1"; 105test_3() if (($pid=fork()) == 0); $work{$pid}="funny1"; 106test_2() if (($pid=fork()) == 0); $work{$pid}="simple2"; 107test_3() if (($pid=fork()) == 0); $work{$pid}="funny2"; 108test_2() if (($pid=fork()) == 0); $work{$pid}="simple3"; 109test_3() if (($pid=fork()) == 0); $work{$pid}="funny3"; 110test_2() if (($pid=fork()) == 0); $work{$pid}="simple4"; 111test_3() if (($pid=fork()) == 0); $work{$pid}="funny4"; 112alter_test() if (($pid=fork()) == 0); $work{$pid}="alter"; 113 114$errors=0; 115while (($pid=wait()) != -1) 116{ 117 $ret=$?/256; 118 print "thread '" . $work{$pid} . "' finished with exit code $ret\n"; 119 $errors++ if ($ret != 0); 120} 121 122if (!$opt_skip_delete && !$errors) 123{ 124 $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; 125 foreach $table (@testtables) { 126 $dbh->Query("drop table $table"); 127 } 128} 129print ($errors ? "Test failed\n" :"Test ok\n"); 130 131$end_time=new Benchmark; 132print "Total time: " . 133 timestr(timediff($end_time, $start_time),"noc") . "\n"; 134 135exit(0); 136 137# 138# Insert records in the ?? tables the Nasdaq way 139# 140 141sub test_1 142{ 143 my ($dbh,$table,$tmpvar,$rows,$found,$i); 144 145 $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; 146 $tmpvar=1; 147 $rows=$found=0; 148 for ($i=0 ; $i < $opt_loop_count; $i++) 149 { 150 $tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables); 151 # Nasdaq step 1: 152 $sth=$dbh->Query("select id,last from $dtable where id='$tmpvar'") 153 or die "Select directory row: $Mysql::db_errstr\n"; 154 # Nasdaq step 2: 155 my ($did,$dlast) = $sth->FetchRow 156 or die "Fetch directory row: $Mysql::db_errstr\n"; 157 $dlast++; 158 $sth=$dbh->Query("INSERT into $testtables[$did]". 159 " VALUES($dlast,'This is entry $dlast',NULL)") 160 || die "Got error on insert table $testtable[$did]:". 161 " $Mysql::db_errstr\n"; 162 # Nasdaq step 3 - where my application hangs 163 $sth=$dbh->Query("update $dtable set last='$dlast' where id='$tmpvar'") 164 or die "Updating directory for table $testtable[$did]:". 165 " Mysql::db_errstr\n"; 166 $rows++; 167 } 168 $dbh=0; 169 print "Test_1: Inserted $rows rows\n"; 170 exit(0); 171} 172 173# 174# Nasdaq simple select 175# 176 177sub test_2 178{ 179 my ($dbh,$id,$tmpvar,$rows,$found,$i); 180 181 $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; 182 $rows=$found=0; 183 $tmpvar=1; 184 for ($i=0 ; $i < $opt_loop_count ; $i++) 185 { 186 $tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables); 187 $sth=$dbh->Query("select a.id,a.info from $testtables[$tmpvar] as a,". 188 "$dtable as d". 189 " where a.id=d.last and $i >= 0") 190 || die "Got error select max: $Mysql::db_errstr\n"; 191 if ((@row = $sth->FetchRow()) && defined($row[0])) 192 { 193 $found++; 194 } 195 } 196 $dbh=0; 197 print "Test_2: Found $found rows\n"; 198 exit(0); 199} 200 201 202# 203# Nasdaq not-so-simple select 204# 205 206sub test_3 207{ 208 my ($dbh,$id,$tmpvar,$rows,$i); 209 $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; 210 $rows=0; 211 $tmpvar ||= $numtables; 212 for ($i=0 ; $i < $opt_loop_count ; $i++) 213 { 214 $tmpvar^= ((($tmpvar + 63) + $i)*3 % $numtables); 215 $id1 = ($tmpvar+1) % $numtables; 216 $id2 = ($id1+1) % $numtables; 217 $id3 = ($id2+1) % $numtables; 218 $sth = $dbh->Query("SELECT greatest(a.id, b.id, c.id), a.info". 219 " FROM $testtables[$id1] as a,". 220 " $testtables[$id2] as b,". 221 " $testtables[$id3] as c,". 222 " $dtable as d1, $dtable as d2, $dtable as d3". 223 " WHERE ". 224 " d1.last=a.id AND d2.last=b.id AND d3.last=c.id". 225 " AND d1.id='$id1' AND d2.id='$id2'". 226 " AND d3.id='$id3'") 227 or die "Funny select: $Mysql::db_errstr\n"; 228 $rows+=$sth->numrows; 229 } 230 $dbh=0; 231 print "Test_3: Found $rows rows\n"; 232 exit(0); 233} 234 235# 236# Do an ALTER TABLE every 20 seconds 237# 238 239sub alter_test 240{ 241 my ($dbh,$count,$old_row_count,$row_count,$id,@row,$sth); 242 243 $dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr; 244 $id=$count=$row_count=0; $old_row_count= -1; 245 246 # Execute the test as long as we get more data into the table 247 while ($row_count != $old_row_count) 248 { 249 sleep(10); 250 $sth=$dbh->Query("ALTER TABLE $testtables[$id] modify info varchar(32)") or die "Couldn't execute ALTER TABLE\n"; 251 $sth=0; 252 $id=($id+1) % $numtables; 253 254 # Test if insert test has ended 255 $sth=$dbh->query("select count(*) from $testtables[0]") or die "Couldn't execute count(*)\n"; 256 @row = $sth->FetchRow(); 257 $old_row_count= $row_count; 258 $row_count=$row[0]; 259 $count++; 260 } 261 $dbh=0; 262 print "alter: Executed $count ALTER TABLE commands\n"; 263 exit(0); 264} 265