1#!/usr/bin/perl 2# Copyright (c) 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# AS3AP single-user benchmark. 21# 22 23##################### Standard benchmark inits ############################## 24 25use Cwd; 26use DBI; 27use Benchmark; 28 29$pwd = cwd(); $pwd = "." if ($pwd eq ''); 30require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; 31 32$opt_loop_count=1; 33 34#Create tables 35 36$dbh = $server->connect(); 37 38#Create Table 39$sth = $dbh->do("drop table uniques"); 40$sth = $dbh->do("drop table updates"); 41$sth = $dbh->do("drop table hundred"); 42$sth = $dbh->do("drop table tenpct"); 43$sth = $dbh->do("drop table tiny"); 44 45#Temporary table 46$sth = $dbh->do("drop table saveupdates"); 47 48@fields=("col_key int not null", 49 "col_int int not null", 50 "col_signed int not null", 51 "col_float float not null", 52 "col_double float not null", 53 "col_decim numeric(18,2) not null", 54 "col_date char(20) not null", 55 "col_code char(10) not null", 56 "col_name char(20) not null", 57 "col_address varchar(80) not null"); 58 59do_many($dbh,$server->create("uniques",\@fields,[])); 60do_many($dbh,$server->create("updates",\@fields,[])); 61do_many($dbh,$server->create("hundred",\@fields,[])); 62do_many($dbh,$server->create("tenpct",\@fields,[])); 63do_many($dbh,$server->create("tiny",["col_key int not null"],[])); 64 65print "Start AS3AP benchmark\n\n"; 66 67$start_time=new Benchmark; 68 69print "Load DATA\n"; 70#Load DATA 71 72@table_names=("uniques","updates","hundred","tenpct","tiny"); 73 74$loop_time=new Benchmark; 75 76if ($opt_fast && $server->{'limits'}->{'load_data_infile'}) 77{ 78 for ($ti = 0; $ti <= $#table_names; $ti++) 79 { 80 my $table_name = $table_names[$ti]; 81 my $file = "$pwd/Data/AS3AP/${table_name}\.new"; 82 print "$table_name - $file\n" if ($opt_debug); 83 $row_count += $server->insert_file($table_name,$file,$dbh); 84 } 85} 86else 87{ 88 for ($ti = 0; $ti <= $#table_names; $ti++) 89 { 90 my $table_name = $table_names[$ti]; 91 print "$table_name - $file\n" if ($opt_debug); 92 my $insert_start = "insert into $table_name values ("; 93 open(DATA, "$pwd/Data/AS3AP/${table_name}\.new") || die "Can't open text file: $pwd/Data/AS3AP/${table_name}\.new\n"; 94 while(<DATA>) 95 { 96 chomp; 97 next unless ( $_ =~ /\w/ ); # skip blank lines 98 $command = $insert_start."$_".")"; 99 $command =~ $server->fix_to_insert($command); 100 print "$command\n" if ($opt_debug); 101 $sth = $dbh->do($command) or die "Got error: $DBI::errstr when executing '$command'\n"; 102 $row_count++; 103 } 104 close(DATA); 105 } 106} 107 108$end_time=new Benchmark; 109print "Time for Load Data - " . "($row_count): " . 110timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 111 112 113print "Create Index\n"; 114 115test_command("create_idx_uniques_key_bt", 116 "time for create_idx_uniques_key_bt", 117 "create unique index uniques_key_bt on uniques (col_key)",$dbh,$opt_loop_count); 118 119test_command("create_idx_updates_key_bt", 120 "time for create_idx_updates_key_bt", 121 "create unique index updates_key_bt on updates (col_key)",$dbh,$opt_loop_count); 122 123test_command("create_idx_hundred_key_bt", 124 "time for create_idx_hundred_key_bt", 125 "create unique index hundred_key_bt on hundred (col_key)", 126 $dbh,$opt_loop_count); 127 128test_command("create_idx_tenpct_key_bt", 129 "time for create_idx_tenpct_key_bt", 130 "create unique index tenpct_key_bt on tenpct (col_key)",$dbh,$opt_loop_count); 131 132test_command("create_idx_tenpct_key_code_bt", 133 "time for create_idx_tenpct_key_code_bt", 134 "create index tenpct_key_code_bt on tenpct (col_key,col_code)", 135 $dbh,$opt_loop_count); 136 137test_command("create_idx_tiny_key_bt", 138 "time for create_idx_tiny_key_bt", 139 "create index tiny_key_bt on tiny (col_key)",$dbh,$opt_loop_count); 140 141test_command("create_idx_tenpct_int_bt", 142 "time for create_idx_tenpct_int_bt", 143 "create index tenpct_int_bt on tenpct (col_int)",$dbh,$opt_loop_count); 144 145test_command("create_idx_tenpct_signed_bt", 146 "time for create_idx_tenpct_signed_bt", 147 "create index tenpct_signed_bt on tenpct (col_signed)",$dbh,$opt_loop_count); 148 149test_command("create_idx_uniques_code_h", 150 "time for create_idx_uniques_code_h", 151 "create index uniques_code_h on uniques (col_code)",$dbh,$opt_loop_count); 152 153test_command("create_idx_tenpct_double_bt", 154 "time for create_idx_tenpct_double_bt", 155 "create index tenpct_double_bt on tenpct (col_double)",$dbh,$opt_loop_count); 156 157 158test_command("create_idx_updates_decim_bt", 159 "time for create_idx_updates_decim_bt", 160 "create index updates_decim_bt on updates (col_decim)",$dbh,$opt_loop_count); 161 162test_command("create_idx_tenpct_float_bt", 163 "time for create_idx_tenpct_float_bt", 164 "create index tenpct_float_bt on tenpct (col_float)",$dbh,$opt_loop_count); 165 166test_command("create_idx_updates_int_bt", 167 "time for create_idx_updates_int_bt", 168 "create index updates_int_bt on updates (col_int)",$dbh,$opt_loop_count); 169 170test_command("create_idx_tenpct_decim_bt", 171 "time for create_idx_tenpct_decim_bt", 172 "create index tenpct_decim_bt on tenpct (col_decim)",$dbh,$opt_loop_count); 173 174test_command("create_idx_hundred_code_h", 175 "time for create_idx_hundred_code_h", 176 "create index hundred_code_h on hundred (col_code)",$dbh,$opt_loop_count); 177 178test_command("create_idx_tenpct_name_h", 179 "time for create_idx_tenpct_name_h", 180 "create index tenpct_name_h on tenpct (col_name)",$dbh,$opt_loop_count); 181 182test_command("create_idx_updates_code_h", 183 "time for create_idx_updates_code_h", 184 "create index updates_code_h on updates (col_code)",$dbh,$opt_loop_count); 185 186test_command("create_idx_tenpct_code_h", 187 "time for create_idx_tenpct_code_h", 188 "create index tenpct_code_h on tenpct (col_code)",$dbh,$opt_loop_count); 189 190test_command("create_idx_updates_double_bt", 191 "time for create_idx_updates_double_bt", 192 "create index updates_double_bt on updates (col_double)",$dbh,$opt_loop_count); 193 194test_command("create_idx_hundred_foreign", 195 "time for create_idx_hundred_foreign", 196 "alter table hundred add constraint fk_hundred_updates foreign key (col_signed) 197 references updates (col_key)",$dbh,$opt_loop_count); 198 199test_query("sel_1_cl", 200 "Time to sel_1_cl", 201 "select col_key, col_int, col_signed, col_code, col_double, col_name 202 from updates where col_key = 1000",$dbh,$opt_loop_count); 203 204test_query("join_3_cl", 205 "Time to join_3_cl", 206 "select uniques.col_signed, uniques.col_date, 207 hundred.col_signed, hundred.col_date, 208 tenpct.col_signed, tenpct.col_date 209 from uniques, hundred, tenpct 210 where uniques.col_key = hundred.col_key 211 and uniques.col_key = tenpct.col_key 212 and uniques.col_key = 1000",$dbh,$opt_loop_count); 213 214test_query("sel_100_ncl", 215 "Time to sel_100_ncl", 216 "select col_key, col_int, col_signed, col_code,col_double, col_name 217 from updates where col_int <= 100",$dbh,$opt_loop_count); 218 219test_query("table_scan", 220 "Time to table_scan", 221 "select * from uniques where col_int = 1",$dbh,$opt_loop_count); 222 223test_query("agg_func", 224 "Time for agg_func", 225 "select min(col_key) from hundred group by col_name",$dbh,$opt_loop_count); 226 227test_query("agg_scal", 228 "Time for agg_scal", 229 "select min(col_key) from uniques",$dbh,$opt_loop_count); 230 231test_query("sel_100_cl", 232 "Time for sel_100_cl", 233 "select col_key, col_int, col_signed, col_code, 234 col_double, col_name 235 from updates where col_key <= 100",$dbh,$opt_loop_count); 236 237test_query("join_3_ncl", 238 "Time for join_3_ncl", 239 "select uniques.col_signed, uniques.col_date, 240 hundred.col_signed, hundred.col_date, 241 tenpct.col_signed, tenpct.col_date 242 from uniques, hundred, tenpct 243 where uniques.col_code = hundred.col_code 244 and uniques.col_code = tenpct.col_code 245 and uniques.col_code = 'BENCHMARKS'",$dbh,$opt_loop_count); 246 247test_query("sel_10pct_ncl", 248 "Time for sel_10pct_ncl", 249 "select col_key, col_int, col_signed, col_code, 250 col_double, col_name 251 from tenpct 252 where col_name = 'THE+ASAP+BENCHMARKS+'",$dbh,$opt_loop_count); 253 254if ($limits->{'subqueries'}){ 255 test_query("agg_simple_report", 256 "Time for agg_simple_report", 257 "select avg(updates.col_decim) 258 from updates 259 where updates.col_key in 260 (select updates.col_key 261 from updates, hundred 262 where hundred.col_key = updates.col_key 263 and updates.col_decim > 980000000)",$dbh,$opt_loop_count); 264}else{ 265 print "agg_simple_report - Failed\n\n"; 266} 267 268test_query("agg_info_retrieval", 269 "Time for agg_info_retrieval", 270 "select count(col_key) 271 from tenpct 272 where col_name = 'THE+ASAP+BENCHMARKS' 273 and col_int <= 100000000 274 and col_signed between 1 and 99999999 275 and not (col_float between -450000000 and 450000000) 276 and col_double > 600000000 277 and col_decim < -600000000",$dbh,$opt_loop_count); 278 279if ($limits->{'views'}){ 280 test_query("agg_create_view", 281 "Time for agg_create_view", 282 "create view 283 reportview(col_key,col_signed,col_date,col_decim, 284 col_name,col_code,col_int) as 285 select updates.col_key, updates.col_signed, 286 updates.col_date, updates.col_decim, 287 hundred.col_name, hundred.col_code, 288 hundred.col_int 289 from updates, hundred 290 where updates.col_key = hundred.col_key",$dbh,$opt_loop_count); 291 292 test_query("agg_subtotal_report", 293 "Time for agg_subtotal_report", 294 "select avg(col_signed), min(col_signed), max(col_signed), 295 max(col_date), min(col_date), 296 count(distinct col_name), count(col_name), 297 col_code, col_int 298 from reportview 299 where col_decim >980000000 300 group by col_code, col_int",$dbh,$opt_loop_count); 301 302 303 test_query("agg_total_report", 304 "Time for agg_total_report", 305 "select avg(col_signed), min(col_signed), max(col_signed), 306 max(col_date), min(col_date), 307 count(distinct col_name), count(col_name), 308 count(col_code), count(col_int) 309 from reportview 310 where col_decim >980000000",$dbh,$opt_loop_count); 311}else{ 312 print "agg_create_view - Failed\n\n"; 313 print "agg_subtotal_report - Failed\n\n"; 314 print "agg_total_report - Failed\n\n"; 315} 316 317#fix from here 318test_query("join_2_cl", 319 "Time for join_2_cl", 320 "select uniques.col_signed, uniques.col_name, 321 hundred.col_signed, hundred.col_name 322 from uniques, hundred 323 where uniques.col_key = hundred.col_key 324 and uniques.col_key =1000" 325 ,$dbh,$opt_loop_count); 326 327test_query("join_2", 328 "Time for join_2", 329 "select uniques.col_signed, uniques.col_name, 330 hundred.col_signed, hundred.col_name 331 from uniques, hundred 332 where uniques.col_address = hundred.col_address 333 and uniques.col_address = 'SILICON VALLEY'" 334 ,$dbh,$opt_loop_count); 335 336test_query("sel_variable_select_low", 337 "Time for sel_variable_select_low", 338 "select col_key, col_int, col_signed, col_code, 339 col_double, col_name 340 from tenpct 341 where col_signed < -500000000" 342 ,$dbh,$opt_loop_count); 343 344test_query("sel_variable_select_high", 345 "Time for sel_variable_select_high", 346 "select col_key, col_int, col_signed, col_code, 347 col_double, col_name 348 from tenpct 349 where col_signed < -250000000" 350 ,$dbh,$opt_loop_count); 351 352test_query("join_4_cl", 353 "Time for join_4_cl", 354 "select uniques.col_date, hundred.col_date, 355 tenpct.col_date, updates.col_date 356 from uniques, hundred, tenpct, updates 357 where uniques.col_key = hundred.col_key 358 and uniques.col_key = tenpct.col_key 359 and uniques.col_key = updates.col_key 360 and uniques.col_key = 1000" 361 ,$dbh,$opt_loop_count); 362 363test_query("proj_100", 364 "Time for proj_100", 365 "select distinct col_address, col_signed from hundred" 366 ,$dbh,$opt_loop_count); 367 368test_query("join_4_ncl", 369 "Time for join_4_ncl", 370 "select uniques.col_date, hundred.col_date, 371 tenpct.col_date, updates.col_date 372 from uniques, hundred, tenpct, updates 373 where uniques.col_code = hundred.col_code 374 and uniques.col_code = tenpct.col_code 375 and uniques.col_code = updates.col_code 376 and uniques.col_code = 'BENCHMARKS'" 377 ,$dbh,$opt_loop_count); 378 379test_query("proj_10pct", 380 "Time for proj_10pct", 381 "select distinct col_signed from tenpct" 382 ,$dbh,$opt_loop_count); 383 384test_query("sel_1_ncl", 385 "Time for sel_1_ncl", 386 "select col_key, col_int, col_signed, col_code, 387 col_double, col_name 388 from updates where col_code = 'BENCHMARKS'" 389 ,$dbh,$opt_loop_count); 390 391test_query("join_2_ncl", 392 "Time for join_2_ncl", 393 "select uniques.col_signed, uniques.col_name, 394 hundred.col_signed, hundred.col_name 395 from uniques, hundred 396 where uniques.col_code = hundred.col_code 397 and uniques.col_code = 'BENCHMARKS'" 398 ,$dbh,$opt_loop_count); 399 400if ($limits->{'foreign_key'}){ 401 do_many($dbh,$server->create("integrity_temp",\@fields,[])); 402 403 test_query("integrity_test_1", 404 "Time for integrity_test", 405 "insert into integrity_temp select * 406 from hundred where col_int=0",$dbh,$opt_loop_count); 407 408 test_query("integrity_test_2", 409 "Time for integrity_test", 410 "update hundred set col_signed = '-500000000' 411 where col_int = 0",$dbh,$opt_loop_count); 412 413 test_query("integrity_test_3", 414 "Time for integrity_test", 415 "update hundred set col_signed = '-500000000' 416 where col_int = 0",$dbh,$opt_loop_count); 417 418 419}else{ 420 print "integrity_test - Failed\n\n"; 421} 422 423push @drop_seq_command,$server->drop_index("updates","updates_int_bt"); 424push @drop_seq_command,$server->drop_index("updates","updates_double_bt"); 425push @drop_seq_command,$server->drop_index("updates","updates_decim_bt"); 426push @drop_seq_command,$server->drop_index("updates","updates_code_h"); 427 428test_many_command("Drop updates keys", 429 "Time for drop_updates_keys", 430 \@drop_seq_command,$dbh,$opt_loop_count); 431 432do_many($dbh,$server->create("saveupdates",\@fields,[])); 433 434test_command("bulk_save", 435 "Time for bulk_save", 436 "insert into saveupdates select * 437 from updates where col_key between 5000 and 5999" 438 ,$dbh,$opt_loop_count); 439 440test_command("bulk_modify", 441 "Time for bulk_modify", 442 "update updates 443 set col_key = col_key - 100000 444 where col_key between 5000 and 5999" 445 ,$dbh,$opt_loop_count); 446 447safe_command("upd_append_duplicate", 448 "Time for upd_append_duplicate", 449 "insert into updates 450 values (6000, 0, 60000, 39997.90, 451 50005.00, 50005.00, 452 '11/10/1985', 'CONTROLLER', 453 'ALICE IN WONDERLAND', 454 'UNIVERSITY OF ILLINOIS AT CHICAGO')" 455 ,$dbh,$opt_loop_count); 456 457test_command("upd_remove_duplicate", 458 "Time for upd_remove_duplicate", 459 "delete from updates where col_key = 6000 and col_int = 0" 460 ,$dbh,$opt_loop_count); 461 462test_command("upd_app_t_mid", 463 "Time for upd_app_t_mid", 464 "insert into updates 465 values (5005, 5005, 50005, 50005.00, 50005.00, 466 50005.00, '1/1/1988', 'CONTROLLER', 467 'ALICE IN WONDERLAND', 468 'UNIVERSITY OF ILLINOIS AT CHICAGO')" 469 ,$dbh,$opt_loop_count); 470 471test_command("upd_mod_t_mid", 472 "Time for upd_mod_t_mid", 473 "update updates set col_key = '-5000' 474 where col_key = 5005" 475 ,$dbh,$opt_loop_count); 476 477test_command("upd_del_t_mid", 478 "Time for upd_del_t_mid", 479 "delete from updates 480 where (col_key='5005') or (col_key='-5000')" 481 ,$dbh,$opt_loop_count); 482 483test_command("upd_app_t_end", 484 "Time for upd_app_t_end", 485 "delete from updates 486 where (col_key='5005') or (col_key='-5000')" 487 ,$dbh,$opt_loop_count); 488 489test_command("upd_mod_t_end", 490 "Time for upd_mod_t_end", 491 "update updates 492 set col_key = -1000 493 where col_key = 1000000001" 494 ,$dbh,$opt_loop_count); 495 496test_command("upd_del_t_end", 497 "Time for upd_del_t_end", 498 "delete from updates where col_key = -1000" 499 ,$dbh,$opt_loop_count); 500 501test_command("create_idx_updates_code_h", 502 "time for create_idx_updates_code_h", 503 "create index updates_code_h on updates (col_code)", 504 $dbh,$opt_loop_count); 505 506test_command("upd_app_t_mid", 507 "Time for upd_app_t_mid", 508 "insert into updates 509 values (5005, 5005, 50005, 50005.00, 50005.00, 510 50005.00, '1/1/1988', 'CONTROLLER', 511 'ALICE IN WONDERLAND', 512 'UNIVERSITY OF ILLINOIS AT CHICAGO')" 513 ,$dbh,$opt_loop_count); 514 515test_command("upd_mod_t_cod", 516 "Time for upd_mod_t_cod", 517 "update updates 518 set col_code = 'SQL+GROUPS' 519 where col_key = 5005" 520 ,$dbh,$opt_loop_count); 521 522test_command("upd_del_t_mid", 523 "Time for upd_del_t_mid", 524 "delete from updates 525 where (col_key='5005') or (col_key='-5000')" 526 ,$dbh,$opt_loop_count); 527 528test_command("create_idx_updates_int_bt", 529 "time for create_idx_updates_int_bt", 530 "create index updates_int_bt on updates (col_int)", 531 $dbh,$opt_loop_count); 532 533test_command("upd_app_t_mid", 534 "Time for upd_app_t_mid", 535 "insert into updates 536 values (5005, 5005, 50005, 50005.00, 50005.00, 537 50005.00, '1/1/1988', 'CONTROLLER', 538 'ALICE IN WONDERLAND', 539 'UNIVERSITY OF ILLINOIS AT CHICAGO')" 540 ,$dbh,$opt_loop_count); 541 542test_command("upd_mod_t_int", 543 "Time for upd_mod_t_int", 544 "update updates set col_int = 50015 where col_key = 5005" 545 ,$dbh,$opt_loop_count); 546 547test_command("upd_del_t_mid", 548 "Time for upd_del_t_mid", 549 "delete from updates 550 where (col_key='5005') or (col_key='-5000')" 551 ,$dbh,$opt_loop_count); 552 553test_command("bulk_append", 554 "Time for bulk_append", 555 "insert into updates select * from saveupdates" 556 ,$dbh,$opt_loop_count); 557 558test_command("bulk_delete", 559 "Time for bulk_delete", 560 "delete from updates where col_key < 0" 561 ,$dbh,$opt_loop_count); 562 563################################ END ################################### 564#### 565#### End of the test...Finally print time used to execute the 566#### whole test. 567 568$dbh->disconnect; 569 570end_benchmark($start_time); 571 572############################ HELP FUNCTIONS ############################## 573 574sub test_query 575{ 576 my($test_text,$result_text,$query,$dbh,$count)=@_; 577 my($i,$loop_time,$end_time); 578 579 print $test_text . "\n"; 580 $loop_time=new Benchmark; 581 for ($i=0 ; $i < $count ; $i++) 582 { 583 defined(fetch_all_rows($dbh,$query)) or warn $DBI::errstr; 584 } 585 $end_time=new Benchmark; 586 print $result_text . "($count): " . 587 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 588} 589 590 591sub test_command 592{ 593 my($test_text,$result_text,$query,$dbh,$count)=@_; 594 my($i,$loop_time,$end_time); 595 596 print $test_text . "\n"; 597 $loop_time=new Benchmark; 598 for ($i=0 ; $i < $count ; $i++) 599 { 600 $dbh->do($query) or die $DBI::errstr; 601 } 602 $end_time=new Benchmark; 603 print $result_text . "($count): " . 604 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 605} 606 607sub safe_command 608{ 609 my($test_text,$result_text,$query,$dbh,$count)=@_; 610 my($i,$loop_time,$end_time); 611 612 print $test_text . "\n"; 613 $loop_time=new Benchmark; 614 for ($i=0 ; $i < $count ; $i++) 615 { 616 safe_do_many($dbh,$query); 617 } 618 $end_time=new Benchmark; 619 print $result_text . "($count): " . 620 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 621} 622 623sub test_many_command 624{ 625 my($test_text,$result_text,$query,$dbh,$count)=@_; 626 my($i,$loop_time,$end_time); 627 628 $loop_time=new Benchmark; 629 for ($i=0 ; $i < $count ; $i++) 630 { 631 safe_do_many($dbh, @$query); 632 } 633 $end_time=new Benchmark; 634 print $result_text . "($count): " . 635 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 636} 637 638 639