release/src/router/mysql/sql-bench/test-select.sh

   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
  24 use Cwd;
  25 use DBI;
  26 use Getopt::Long;
  27 use 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 '');
  36 require "$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
  41 if ($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
  49 print "Testing the speed of selecting on keys that consist of many parts\n";
  50 print "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
  63 goto select_test if ($opt_skip_create);
  64
  65 print "Creating table\n";
  66 $dbh->do("drop table bench1" . $server->{'drop_attr'});
  67
  68 do_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)"]));
  76 if ($opt_lock_tables)
  77 {
  78   do_query($dbh,"LOCK TABLES bench1 WRITE");
  79 }
  80
  81 if ($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
  94 print "Inserting $opt_loop_count rows\n";
  95
  96 $loop_time=new Benchmark;
  97
  98 if ($opt_fast && $server->{transactions})
  99 {
 100   $dbh->{AutoCommit} = 0;
 101 }
 102
 103 $query="insert into bench1 values (";
 104 $half_done=$opt_loop_count/2;
 105 for ($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
 116 if ($opt_fast && $server->{transactions})
 117 {
 118   $dbh->commit;
 119   $dbh->{AutoCommit} = 1;
 120 }
 121
 122 $end_time=new Benchmark;
 123 print "Time to insert ($opt_loop_count): " .
 124     timestr(timediff($end_time, $loop_time),"all") . "\n\n";
 125
 126 if ($opt_lock_tables)
 127 {
 128   do_query($dbh,"UNLOCK TABLES");
 129 }
 130
 131 if ($opt_fast && defined($server->{vacuum}))
 132 {
 133   $server->vacuum(0,\$dbh,"bench1");
 134 }
 135
 136 if ($opt_lock_tables)
 137 {
 138   do_query($dbh,"LOCK TABLES bench1 WRITE");
 139 }
 140
 141 ####
 142 #### Do some selects on the table
 143 ####
 144
 145 select_test:
 146
 147 if ($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
 178 print "Testing big selects on the table\n";
 179 $loop_time=new Benchmark;
 180 $rows=0;
 181 for ($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;
 196 print "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
 208 for ($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
 253 print_time($estimated);
 254 print " 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
 261 if ($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
 316 if ($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
 329 if ($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
 440 if ($opt_lock_tables)
 441 {
 442   do_query($dbh,"UNLOCK TABLES");
 443 }
 444 if (!$opt_skip_delete)
 445 {
 446   do_query($dbh,"drop table bench1" . $server->{'drop_attr'});
 447 }
 448
 449 if ($opt_fast && defined($server->{vacuum}))
 450 {
 451   $server->vacuum(0,\$dbh);
 452 }
 453
 454 $dbh->disconnect;                               # close connection
 455
 456 end_benchmark($start_time);