#!/usr/bin/perl use DBI; use POSIX qw( floor ); use Benchmark; use Getopt::Long; use Data::Dumper; $opt_db_host="localhost"; $opt_db_socket="/tmp/mysql.sock"; $opt_db_name="test"; $opt_db_engine="InnoDB"; $opt_start=0; $opt_num=1; $opt_count=15; $opt_buffering=""; $opt_stat=""; $delete_loop=$opt_count; $opt_loop_count=100000; #$opt_loop_count=30000; $random_loop_count=$opt_loop_count; $opt_update=$opt_delete=$opt_create=0; GetOptions("start=s","count=s","num=s","db-socket=s","db-engine=s","create","update","delete","stat","buffering=s"); $dbh = DBI->connect("dbi:mysql:$opt_db_name;host=$opt_db_host;mysql_socket=$opt_db_socket","root","") or die $DBI::errstr; $sth = $dbh->prepare("select VERSION()") or die $DBI::errstr; $version="MySQL ?"; if ($sth->execute && (@row = $sth->fetchrow_array)) { $row[0] =~ s/-/ /g; # To get better tables with long names $version="MySQL $row[0]"; } $sth->finish; print "$version, ENGINE: $opt_db_engine \n\n"; if ($opt_buffering ne '') { print "Set innodb_change_buffering=$opt_buffering\n\n"; $dbh->do("set GLOBAL innodb_change_buffering=$opt_buffering") or die $DBI::errstr; } $sth = $dbh->prepare('select @@innodb_change_buffering') or die $DBI::errstr; if ($sth->execute && (@row = $sth->fetchrow_array)) { $buffering=$row[0]; } $sth->finish; print "BUFFERING: $buffering\n\n"; print "Generating random keys\n"; $random[$random_loop_count]=0; for ($i=0 ; $i < $random_loop_count ; $i++) { $random[$i]=$i; } my $tmpvar=1; for ($i=0 ; $i < $random_loop_count ; $i++) { $tmpvar^= ((($tmpvar + 63) + $i)*3 % $random_loop_count); $swap=$tmpvar % $random_loop_count; $tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp; } sub random { my $i = shift; $i += $opt_loop_count; return $random[$i % $random_loop_count] + floor($i/$random_loop_count)*$random_loop_count; } if ($opt_create) { $create_table_bench1="CREATE TABLE bench1 ( field1 tinyint(4) NOT NULL, field_search tinyint(4) NOT NULL, field2 mediumint(9) NOT NULL, field3 smallint(6) NOT NULL, field4 char(16) NOT NULL, field5 int(11) NOT NULL, field6 float NOT NULL, field7 double NOT NULL, field8 char(6) NOT NULL, field9 char(6) NOT NULL, field10 char(6) NOT NULL, field11 char(6) NOT NULL, field12 char(6) NOT NULL, field13 char(6) NOT NULL, field14 char(6) NOT NULL, field15 char(6) NOT NULL, field16 char(6) NOT NULL, PRIMARY KEY (field1,field2,field3,field4,field5,field6, field7,field8,field9,field10,field11,field12, field13,field14,field15,field16), KEY index2 (field_search), KEY index3 (field3), KEY index4 (field4), KEY index5 (field5), KEY index6 (field6), KEY index7 (field7), KEY index8 (field8), KEY index9 (field9), KEY index10 (field10), KEY index11 (field11), KEY index12 (field12), KEY index13 (field13), KEY index14 (field14), KEY index15 (field15), KEY index16 (field16) ) ENGINE=$opt_db_engine"; $dbh->do("drop table if exists bench1"); $dbh->do($create_table_bench1); $many_keys_loop_count=$opt_loop_count; @types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1); print "Inserting $many_keys_loop_count rows\n"; $t0=new Benchmark; { $query_size=1000000; $query="insert into bench1 values "; $res=$query; for ($i=0; $i < $many_keys_loop_count; $i++) { if (floor($i/10000)==$i/10000) { print "Inserted $i rows\n"; } $id= $i & 127; $rand=&random($i); $tmp="($id,$id,$rand," . ($i & 32766) . ",'ABCDEF$rand',0,$rand,$rand.0,"; for ($j=8; $j <= 16 ; $j++) { $tmp.= ($types[$j] == 0) ? "$rand," : "'$rand',"; } substr($tmp,-1)=")"; if (length($tmp)+length($res) < $query_size) { $res.= $tmp . ","; } else { $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; $res=$query . $tmp . ","; } } $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; } $t1=new Benchmark; print "Time for insert ($many_keys_loop_count): ",timestr(timediff($t1, $t0),"all"),"\n\n"; show_stat() if ($opt_stat); check_table(); show_stat() if ($opt_stat); } if ($opt_update) { print "Testing update of keys: start:$opt_start count:$opt_count update with:$opt_num\n"; $t0=new Benchmark; for ($i=$opt_start ; $i < $opt_start+$opt_count ; $i++) { #print "$i\n"; $dbh->do("update bench1 set field5=$opt_num where field_search=$i") or die "Got error $DBI::errstr with query: update bench1 set field5=$opt_num where field_search=$i\n"; } $t1=new Benchmark; print "Time for update_of_primary_key_many_keys ($buffering:$opt_count): ",timestr(timediff($t1, $t0),"all"),"\n\n"; show_stat("INSERT") if ($opt_stat); check_table(); show_stat("INSERT") if ($opt_stat); } if ($opt_delete) { # # Delete everything from table # print "Deleting rows from the table start:$opt_start count:$opt_count \n"; $loop_time=new Benchmark; $count=0; #for ($i=0 ; $i < $delete_loop ; $i++) for ($i=$opt_start ; $i < $opt_start+$opt_count ; $i++) { $count++; $dbh->do("delete from bench1 where field_search = $i") or die $DBI::errstr; } $end_time=new Benchmark; print "Time for delete_big_many_keys ($buffering:$count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; show_stat() if ($opt_stat); check_table(); show_stat() if ($opt_stat); } sub check_table() { $t0=new Benchmark; $dbh->do("check table bench1") or die $DBI::errstr; $t1=new Benchmark; print "Time for check table bench1(1): ",timestr(timediff($t1, $t0),"all"),"\n\n"; } sub show_stat() { my (@sections)=@_; my $header=0; my $stat=""; my %data; if (!@section) { push @section,"ALL"; } $dbh->do('show engine innodb status') or die $DBI::errstr; $sth = $dbh->prepare('show engine innodb status') or die $DBI::errstr; if ($sth->execute && (@row = $sth->fetchrow_array)) { $stat=$row[2]; } $sth->finish; #print $stat,"\n"; foreach $line (split("\n",$stat)) { if ($line) { if ($line=~/^\-*$|^=*$/) { $header= $header ? 0 : 1; } elsif ($header) { $header_name=$line; } else { push @{$data{$header_name}},$line; } } } foreach $key (keys %data) { foreach $section (@sections) { if ($section=~/ALL/ || $key=~/$section/g) { print "-"x80,"\n$key\n","-"x80,"\n",join("\n",@{$data{$key}}),"\n","-"x80,"\n"; } } } }