1#!/usr/bin/perl 2# Copyright (c) 2000, 2001, 2003, 2006 MySQL AB, 2009 Sun Microsystems, Inc. 3# Use is subject to license terms. 4# 5# This library is free software; you can redistribute it and/or 6# modify it under the terms of the GNU Library General Public 7# License as published by the Free Software Foundation; version 2 8# of the License. 9# 10# This library is distributed in the hope that it will be useful, 11# but WITHOUT ANY WARRANTY; without even the implied warranty of 12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 13# Library General Public License for more details. 14# 15# You should have received a copy of the GNU Library General Public 16# License along with this library; if not, write to the Free 17# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, 18# MA 02110-1301, USA 19# 20# Test of selecting on keys that consist of many parts 21# 22##################### Standard benchmark inits ############################## 23 24use Cwd; 25use DBI; 26use Getopt::Long; 27use Benchmark; 28 29$opt_loop_count=10000; 30$opt_medium_loop_count=1000; 31$opt_small_loop_count=10; 32$opt_regions=6; 33$opt_groups=100; 34 35$pwd = cwd(); $pwd = "." if ($pwd eq ''); 36require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; 37 38$columns=min($limits->{'max_columns'},500,($limits->{'query_size'}-50)/24, 39 $limits->{'max_conditions'}/2-3); 40 41if ($opt_small_test) 42{ 43 $opt_loop_count/=10; 44 $opt_medium_loop_count/=10; 45 $opt_small_loop_count/=10; 46 $opt_groups/=10; 47} 48 49print "Testing the speed of selecting on keys that consist of many parts\n"; 50print "The test-table has $opt_loop_count rows and the test is done with $columns ranges.\n\n"; 51 52#### 53#### Connect and start timeing 54#### 55 56$dbh = $server->connect(); 57$start_time=new Benchmark; 58 59#### 60#### Create needed tables 61#### 62 63goto select_test if ($opt_skip_create); 64 65print "Creating table\n"; 66$dbh->do("drop table bench1" . $server->{'drop_attr'}); 67 68do_many($dbh,$server->create("bench1", 69 ["region char(1) NOT NULL", 70 "idn integer(6) NOT NULL", 71 "rev_idn integer(6) NOT NULL", 72 "grp integer(6) NOT NULL"], 73 ["primary key (region,idn)", 74 "unique (region,rev_idn)", 75 "unique (region,grp,idn)"])); 76if ($opt_lock_tables) 77{ 78 do_query($dbh,"LOCK TABLES bench1 WRITE"); 79} 80 81if ($opt_fast && defined($server->{vacuum})) 82{ 83 $server->vacuum(1,\$dbh); 84} 85 86#### 87#### Insert $opt_loop_count records with 88#### region: "A" -> "E" 89#### idn: 0 -> count 90#### rev_idn: count -> 0, 91#### grp: distributed values 0 - > count/100 92#### 93 94print "Inserting $opt_loop_count rows\n"; 95 96$loop_time=new Benchmark; 97 98if ($opt_fast && $server->{transactions}) 99{ 100 $dbh->{AutoCommit} = 0; 101} 102 103$query="insert into bench1 values ("; 104$half_done=$opt_loop_count/2; 105for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--) 106{ 107 $grp=$id*3 % $opt_groups; 108 $region=chr(65+$id%$opt_regions); 109 do_query($dbh,"$query'$region',$id,$rev_id,$grp)"); 110 if ($id == $half_done) 111 { # Test with different insert 112 $query="insert into bench1 (region,idn,rev_idn,grp) values ("; 113 } 114} 115 116if ($opt_fast && $server->{transactions}) 117{ 118 $dbh->commit; 119 $dbh->{AutoCommit} = 1; 120} 121 122$end_time=new Benchmark; 123print "Time to insert ($opt_loop_count): " . 124 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 125 126if ($opt_lock_tables) 127{ 128 do_query($dbh,"UNLOCK TABLES"); 129} 130 131if ($opt_fast && defined($server->{vacuum})) 132{ 133 $server->vacuum(0,\$dbh,"bench1"); 134} 135 136if ($opt_lock_tables) 137{ 138 do_query($dbh,"LOCK TABLES bench1 WRITE"); 139} 140 141#### 142#### Do some selects on the table 143#### 144 145select_test: 146 147if ($limits->{'group_functions'}) 148{ 149 my ($tmp); $tmp=1000; 150 print "Test if the database has a query cache\n"; 151 152 # First ensure that the table is read into memory 153 fetch_all_rows($dbh,"select sum(idn+$tmp),sum(rev_idn-$tmp) from bench1"); 154 155 $loop_time=new Benchmark; 156 for ($tests=0 ; $tests < $opt_loop_count ; $tests++) 157 { 158 fetch_all_rows($dbh,"select sum(idn+100),sum(rev_idn-100) from bench1"); 159 } 160 $end_time=new Benchmark; 161 print "Time for select_cache ($opt_loop_count): " . 162 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 163 164 # If the database has a query cache, the following loop should be much 165 # slower than the previous loop 166 167 $loop_time=new Benchmark; 168 for ($tests=0 ; $tests < $opt_loop_count ; $tests++) 169 { 170 fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn-$tests) from bench1"); 171 } 172 $end_time=new Benchmark; 173 print "Time for select_cache2 ($opt_loop_count): " . 174 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 175} 176 177 178print "Testing big selects on the table\n"; 179$loop_time=new Benchmark; 180$rows=0; 181for ($i=0 ; $i < $opt_small_loop_count ; $i++) 182{ 183 $grp=$i*11 % $opt_groups; 184 $region=chr(65+$i%($opt_regions+1)); # One larger to test misses 185 $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region'"); 186 $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region' and idn=$i"); 187 $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region' and rev_idn=$i"); 188 $rows+=fetch_all_rows($dbh,"select idn from bench1 where region='$region' and grp=$grp"); 189 $rows+=fetch_all_rows($dbh,"select idn from bench1 where region>='B' and region<='C' and grp=$grp"); 190 $rows+=fetch_all_rows($dbh,"select idn from bench1 where region>='B' and region<='E' and grp=$grp"); 191 $rows+=fetch_all_rows($dbh,"select idn from bench1 where grp=$grp"); # This is hard 192} 193$count=$opt_small_loop_count*7; 194 195$end_time=new Benchmark; 196print "Time for select_big ($count:$rows): " . 197 timestr(timediff($end_time, $loop_time),"all") . "\n"; 198 199# Test select with many OR's 200 201$loop_time=new Benchmark; 202$tmpvar=0; 203$count=0; 204$estimated=0; 205$max_and_conditions=$limits->{'max_conditions'}/2; 206$rows=0; 207 208for ($i=0 ; $i < $opt_small_loop_count ; $i++) 209{ 210 $region=chr(65+$i%($opt_regions+1)); # One larger to test out-of-regions 211 $query="select * from bench1 where "; 212 $or_part="grp = 1"; 213 $or_part2="region='A' and grp=1"; 214 215 for ($j=1 ; $j < $columns; $j++) 216 { 217 $tmpvar^= ((($tmpvar + 63) + $j)*3 % 100000); 218 $tmp=$tmpvar % $opt_groups; 219 $tmp_region=chr(65+$tmpvar%$opt_regions); 220 $or_part.=" or grp=$tmp"; 221 if ($j < $max_and_conditions) 222 { 223 $or_part2.=" or region='$tmp_region' and grp=$tmp"; 224 } 225 } 226 $or_part="region='$region' and ($or_part)"; 227 228# Same query, but use 'func_extra_in_num' instead. 229 if ($limits->{'func_extra_in_num'}) 230 { 231 $in_part=$or_part; 232 $in_part=~ s/ = / IN \(/; 233 $in_part=~ s/ or grp=/,/g; 234 $in_part.= ")"; 235 defined($found=fetch_all_rows($dbh,$query . $in_part)) || die $DBI::errstr; 236 $rows+=$found; 237 $count++; 238 } 239 for ($j=0; $j < 10 ; $j++) 240 { 241 $rows+=fetch_all_rows($dbh,$query . $or_part); 242 $rows+=fetch_all_rows($dbh,$query . $or_part2); 243# Do it a little harder by setting a extra range 244 $rows+=fetch_all_rows($dbh,"$query ($or_part) and idn < 50"); 245 $rows+=fetch_all_rows($dbh,"$query (($or_part) or (region='A' and grp < 10)) and region <='B'") 246 } 247 $count+=$j*4; 248 $end_time=new Benchmark; 249 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 250 $opt_small_loop_count)); 251} 252 253print_time($estimated); 254print " for select_range ($count:$rows): " . 255 timestr(timediff($end_time, $loop_time),"all") . "\n"; 256 257# 258# Testing MIN() and MAX() on keys 259# 260 261if ($limits->{'group_functions'} && $limits->{'order_by_unused'}) 262{ 263 $loop_time=new Benchmark; 264 $count=0; 265 $estimated=0; 266 for ($tests=0 ; $tests < $opt_loop_count ; $tests++) 267 { 268 $count+=7; 269 $grp=$tests*3 % $opt_groups; 270 $region=chr(65+$tests % $opt_regions); 271 if ($limits->{'group_func_sql_min_str'}) 272 { 273 fetch_all_rows($dbh,"select min(region) from bench1"); 274 fetch_all_rows($dbh,"select max(region) from bench1"); 275 fetch_all_rows($dbh,"select min(region),max(region) from bench1"); 276 } 277 fetch_all_rows($dbh,"select min(rev_idn) from bench1 where region='$region'"); 278 279 fetch_all_rows($dbh,"select max(grp) from bench1 where region='$region'"); 280 fetch_all_rows($dbh,"select max(idn) from bench1 where region='$region' and grp=$grp"); 281 if ($limits->{'group_func_sql_min_str'}) 282 { 283 fetch_all_rows($dbh,"select max(region) from bench1 where region<'$region'"); 284 } 285 $end_time=new Benchmark; 286 last if ($estimated=predict_query_time($loop_time,$end_time,\$count, 287 $tests+1, $opt_loop_count)); 288 } 289 print_time($estimated); 290 print " for min_max_on_key ($count): " . 291 timestr(timediff($end_time, $loop_time),"all") . "\n"; 292 293 $loop_time=new Benchmark; 294 $count=0; 295 $estimated=0; 296 for ($tests=0 ; $tests < $opt_loop_count ; $tests++) 297 { 298 $count+=5; 299 $grp=$tests*3 % $opt_groups; 300 $region=chr(65+$tests % $opt_regions); 301 fetch_all_rows($dbh,"select count(*) from bench1 where region='$region'"); 302 fetch_all_rows($dbh,"select count(*) from bench1 where region='$region' and grp=$grp"); 303 fetch_all_rows($dbh,"select count(*) from bench1 where region>'$region'"); 304 fetch_all_rows($dbh,"select count(*) from bench1 where region<='$region'"); 305 fetch_all_rows($dbh,"select count(*) from bench1 where region='$region' and grp>$grp"); 306 $end_time=new Benchmark; 307 last if ($estimated=predict_query_time($loop_time,$end_time,\$count, 308 $tests+1, $opt_loop_count)); 309 } 310 print_time($estimated); 311 print " for count_on_key ($count): " . 312 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 313 314} 315 316if ($limits->{'group_functions'}) 317{ 318 $loop_time=new Benchmark; 319 $rows=0; 320 for ($i=0 ; $i < $opt_medium_loop_count ; $i++) 321 { 322 $rows+=fetch_all_rows($dbh,"select grp,count(*) from bench1 group by grp"); 323 } 324 $end_time=new Benchmark; 325 print "Time for count_group_on_key_parts ($i:$rows): " . 326 timestr(timediff($end_time, $loop_time),"all") . "\n"; 327} 328 329if ($limits->{'group_distinct_functions'}) 330{ 331 print "Testing count(distinct) on the table\n"; 332 $loop_time=new Benchmark; 333 $rows=$estimated=$count=0; 334 for ($i=0 ; $i < $opt_medium_loop_count ; $i++) 335 { 336 $count++; 337 $rows+=fetch_all_rows($dbh,"select count(distinct region) from bench1"); 338 $end_time=new Benchmark; 339 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 340 $opt_medium_loop_count)); 341 } 342 print_time($estimated); 343 print " for count_distinct_key_prefix ($count:$rows): " . 344 timestr(timediff($end_time, $loop_time),"all") . "\n"; 345 346 $loop_time=new Benchmark; 347 $rows=$estimated=$count=0; 348 for ($i=0 ; $i < $opt_medium_loop_count ; $i++) 349 { 350 $count++; 351 $rows+=fetch_all_rows($dbh,"select count(distinct grp) from bench1"); 352 $end_time=new Benchmark; 353 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 354 $opt_medium_loop_count)); 355 } 356 print_time($estimated); 357 print " for count_distinct ($count:$rows): " . 358 timestr(timediff($end_time, $loop_time),"all") . "\n"; 359 360# Workaround mimer's behavior 361 if ($limits->{'multi_distinct'}) 362 { 363 $loop_time=new Benchmark; 364 $rows=$estimated=$count=0; 365 for ($i=0 ; $i < $opt_medium_loop_count ; $i++) 366 { 367 $count++; 368 $rows+=fetch_all_rows($dbh,"select count(distinct grp),count(distinct rev_idn) from bench1"); 369 $end_time=new Benchmark; 370 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 371 $opt_medium_loop_count)); 372 } 373 print_time($estimated); 374 print " for count_distinct_2 ($count:$rows): " . 375 timestr(timediff($end_time, $loop_time),"all") . "\n"; 376 } 377 378 $loop_time=new Benchmark; 379 $rows=$estimated=$count=0; 380 for ($i=0 ; $i < $opt_medium_loop_count ; $i++) 381 { 382 $count++; 383 $rows+=fetch_all_rows($dbh,"select region,count(distinct idn) from bench1 group by region"); 384 $end_time=new Benchmark; 385 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 386 $opt_medium_loop_count)); 387 } 388 print_time($estimated); 389 print " for count_distinct_group_on_key ($count:$rows): " . 390 timestr(timediff($end_time, $loop_time),"all") . "\n"; 391 392 $loop_time=new Benchmark; 393 $rows=$estimated=$count=0; 394 for ($i=0 ; $i < $opt_medium_loop_count ; $i++) 395 { 396 $count++; 397 $rows+=fetch_all_rows($dbh,"select grp,count(distinct idn) from bench1 group by grp"); 398 $end_time=new Benchmark; 399 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 400 $opt_medium_loop_count)); 401 } 402 print_time($estimated); 403 print " for count_distinct_group_on_key_parts ($count:$rows): " . 404 timestr(timediff($end_time, $loop_time),"all") . "\n"; 405 406 $loop_time=new Benchmark; 407 $rows=$estimated=$count=0; 408 for ($i=0 ; $i < $opt_medium_loop_count ; $i++) 409 { 410 $count++; 411 $rows+=fetch_all_rows($dbh,"select grp,count(distinct rev_idn) from bench1 group by grp"); 412 $end_time=new Benchmark; 413 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 414 $opt_medium_loop_count)); 415 } 416 print_time($estimated); 417 print " for count_distinct_group ($count:$rows): " . 418 timestr(timediff($end_time, $loop_time),"all") . "\n"; 419 420 $loop_time=new Benchmark; 421 $rows=$estimated=$count=0; 422 $test_count=$opt_medium_loop_count/10; 423 for ($i=0 ; $i < $test_count ; $i++) 424 { 425 $count++; 426 $rows+=fetch_all_rows($dbh,"select idn,count(distinct region) from bench1 group by idn"); 427 $end_time=new Benchmark; 428 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, 429 $test_count)); 430 } 431 print_time($estimated); 432 print " for count_distinct_big ($count:$rows): " . 433 timestr(timediff($end_time, $loop_time),"all") . "\n"; 434} 435 436#### 437#### End of benchmark 438#### 439 440if ($opt_lock_tables) 441{ 442 do_query($dbh,"UNLOCK TABLES"); 443} 444if (!$opt_skip_delete) 445{ 446 do_query($dbh,"drop table bench1" . $server->{'drop_attr'}); 447} 448 449if ($opt_fast && defined($server->{vacuum})) 450{ 451 $server->vacuum(0,\$dbh); 452} 453 454$dbh->disconnect; # close connection 455 456end_benchmark($start_time); 457