#! /usr/bin/perl -w use strict; use DBI; use Getopt::Std; use String::Random; use vars qw/$opt_d $opt_c/; getopt('dc:'); #--------------------------------------------------------------------- +------------------------------------ # variables # my $RandString = new String::Random; my $run = 1; # controls wether to continue running my $mypid = $$; # my own pid my $pid; # child pid my @pids; # pids of all forked children my $sql; # sql command my %session; # all sessions my ($session, $batch); # actual session and batch my $thissession; # child session my $lastsession; # parent session my ($connect, $user, $password, $database); my %caches; my @insertedvalues; # values of last insert (for foreign ke +ys) #--------------------------------------------------------------------- +------------------------------------ # catch sigint to break endless loop and kill children # sub catch_sigint { my $pid; foreach $pid (@pids) { kill 2, $pid; } $run = 0; } $SIG{INT} = \&catch_sigint; #--------------------------------------------------------------------- +------------------------------------ # first, as the parent, read the config-file to spawn children # $opt_c ||= 'db-stress.cfg'; open CFG, "$opt_c" or die "config-file $opt_c not found!\n"; while (my $cfgline = <CFG>) { next if ($cfgline =~ /^\s*$|^#/); + # ignore empty lines and comments chomp($cfgline); $cfgline =~ s/#.*$//; + # cut comments at end of line if ($cfgline =~ /^\s*connect\s*=\s*(.*)/) { + # find connect string $connect = $1; next; } if ($cfgline =~ /^\s*user\s*=\s*(.*)/) { # + find user $user = $1; next; } if ($cfgline =~ /^\s*password\s*=\s*(.*)/) { # + find password $password = $1; next; } if ($cfgline =~ /^\s*database\s*=\s*(.*)/) { # + find database $database = $1; next; } if ($cfgline =~ /^\s*session\s*=\s*(.*)/) { + # find sessions if ($sql) { push @{$session{$session}->{batches}->{$batch}->{commands} +}, $sql; $sql = ''; } $session = $1; $session{$session}->{count} = 1; next; } if ($cfgline =~ /^\s*count\s*=\s*(.*)/) { + # find no. of children $session{$session}->{count} = $1; next; } if ($cfgline =~ /^\s*batch\s*=\s*(.*)/) { + # find batches if ($sql) { push @{$session{$session}->{batches}->{$batch}->{commands} +}, $sql; $sql = ''; } $batch = $1; $session{$session}->{batches}->{$batch}->{batch} = $1; $session{$session}->{batches}->{$batch}->{delay} = 1; next; } if ($cfgline =~ /^\s*delay\s*=\s*(.*)/) { + # find delay $session{$session}->{batches}->{$batch}->{delay} = $1; next; } if ($cfgline =~ /^loop$/) { + # finish batch and fetch rows push @{$session{$session}->{batches}->{$batch}->{commands}}, $ +sql; push @{$session{$session}->{batches}->{$batch}->{commands}}, " +loop"; $sql = ''; next; } if ($cfgline =~ /^go$/) { + # finish batch push @{$session{$session}->{batches}->{$batch}->{commands}}, $ +sql; $sql = ''; next; } if ($cfgline !~ /^\s*commands\s*=/) { + # anything else, except 'commands =' is a sql command $sql .= "\n" . $cfgline; next; } } $lastsession = $session; #--------------------------------------------------------------------- +---------------------------------- # fork children # my $fork_this = 0; # + don't fork the very first session ($thissession) = sort keys %session; # + remember first session SPAWN: foreach my $key (sort keys %session) { print "spawning session: $key (" . $session{$key}->{count} . " child +ren)\n"; for (my $i = 0; $i < $session{$key}->{count}; $i++) { if ($fork_this && (eval {$pid = fork()})) { print "\t\tchild: " . ($i + 1) . " ($pid) ...\n"; $thissession = $key; push @pids, $pid; last SPAWN; } $fork_this = 1; } } #--------------------------------------------------------------------- +-------------------------- # connect to database # my $dbh; my $sth; print "session: $thissession; connect($connect, $user, $password)\n" i +f $opt_d >= 2; $dbh = DBI->connect($connect, $user, $password) or die "could not conn +ect: $connect, $user, $password\n"; #---- if sybase ase, use database $dbh->do("use $database") if ($connect =~ /sybase/i); #--------------------------------------------------------------------- +------------------------------ # enter loop to executes batches of this session # while ($run) { foreach $batch (keys %{$session{$thissession}->{batches}}) { foreach (@{$session{$thissession}->{batches}->{$batch}->{comma +nds}}) { my $sql = $_; if ($sql eq 'loop') { my $r = 0; while (my @row = $sth->fetchrow()) { $r++; } $sth->finish(); next; } if ($sth) {$sth->finish();} $sql =~ s/=randstring\((\d+?)\)/&randstring($1)/eg; $sql =~ s/=randregex\((.+?)\)/$RandString->randregex($1)/e +g; $sql =~ s/=randrow\(([^, )]+)(,\s*cache)?\)/&randrow($1, $ +3)/eg; $sql =~ s/=lastinsert\((\d+?)\)/&lastinserted($1)/eg; print "$sql\n" if $opt_d; $sth = $dbh->prepare($sql); $sth->execute(); &getinserted($sql) if ($sql =~ /(^\s*update)|(^\s*insert)/ +ims); } sleep $session{$thissession}->{batches}->{$batch}->{delay}; } } if ($sth) {$sth->finish();} if ($dbh) {$dbh->disconnect();} #--------------------------------------------------------------------- +----------------- # subroutines #--------------------------------------------------------------------- +----------------- #--------------------------------------------------------------------- +----------------- # create string of random characters # sub randstring() { my $count = shift; my $string = ''; for (my $i = 0; $i < rand($count); $i++) { $string .= substr('abcdefghijklmnopqrstuvwxyz', rand(26), 1); } $string; } #--------------------------------------------------------------------- +------------------ # get random row of table # sub randrow(@) { my ($tablecol, $cache) = @_; if (!defined $caches{$tablecol} || !defined($cache)) { delete $caches{$tablecol} if (!defined($cache)); my ($table, $col) = $tablecol =~ /(.+)\.(.+)/; my $sql = "select $col from $table"; my $sth = $dbh->prepare($sql); $sth->execute(); while (my @row = $sth->fetchrow()) { push @{$caches{$tablecol}}, $row[0]; } $sth->finish(); } return $caches{$tablecol}[rand(@{$caches{$tablecol}})]; } #--------------------------------------------------------------------- +------------------- # get updated/inserted value # sub lastinserted() { my $i = shift; $insertedvalues[$i - 1]; } #--------------------------------------------------------------------- +------------------- # get inserted/updated columns # sub getinserted() { my $sql = shift; if ($sql =~ /^\s*insert/ims) { my ($s) = $sql =~ /values\((.*)\)/i; $s .= ","; @insertedvalues = $s =~ /((?:["'].*?["'])|(?:[^']*?))\s*,\s*/g +; } else { my ($s) = $sql =~ /set\s+(.*)\s+(?:where.*|$)/i; $s = "," . $s . ","; @insertedvalues = $s =~ /,.*?=\s*((?:["'].*?["'])|(?:[^']*?))( +?=\s*,\s*)/g; } } __END__ #--------------------------------------------------------------------- +-------------------------------- =head1 NAME db-stress - tool for generating data and putting heavy load on databas +e servers =head1 SYNOPSIS B<db-stress.pl> [-d lvl] [-c cfg-file] =head1 DESCRIPTION The main purpose of db-stress is to put a heavy load on a database ser +ver, so you have the ability try out several tuning measurements. Another purpose is to use it as a data generator to test replication. You can define in a configuration file several sessions in which sever +al batches may run against a database. Each of these batches may run +in several instances. So you are able to simulate any nuber of client +s you want. db-stress is written in Perl and relies on the DBI by Tim Bunce. It ha +s been tested with the following DBDs: DBD::Sybase Linux, WinNT Sybase SQL-Server, ASE 11.x DBD::ASAny Linux Sybase ASA 6.0 DBD::ODBC WinNT Sybase ASE 11.x, Sybase ASA 6.0, MS SQL-Server 7.0 Following modules are also used: Getopt::Std String::Random available at http://www.cpan.org =head1 OPTIONS -d n switch on debugging of the sql-batches. level 1: print executed sql-commands level 2: print connection info -c file use file instead of db-stress.cfg =head1 CONFIGURATION FILE Comments start with #, the rest of the line is ignored. Blank lines are ignored. The configuration file is separated into several sections: The global section at the top of the file contains connection informat +ions. The remaining sections contain each a session. Each entry in the configuration file begins with a keyword and an equa +l sign. =head2 Global Section Keywords for the global section are: connect the complete connection string example: connect = dbi:Sybase:server=SYBASE user the login example: user = sa password the password for the login example: password = database the database to use example: database = pubs2 =head2 Session Sections Keywords for session sections: session the name of the session example: session = 1 count the number of children to be forked for this session, default is 1 example: count = 8 Each session can contain several batches of sql commands which are pro +cessed sequentially. Keywords for batches: batch the name of the batch example: batch = 1 delay the number of seconds execution is delayed after processing this batch, default is 1 example: delay = 0 commands tag marking the begin of the sql commands, must be on a line of its own example: commands = Each sql batch must be concluded by either a 'go' or a 'loop' on a lin +e of its own. Apart from sql the following commands and functions are available: loop fetches the rows of a result set after a select used to generate network traffic in a sql command: =randstring() generates a string of a certain maximal length example: insert into xxx values('=randstring(20)') =randregex() generates a string that matches a regular expression example: insert into xxx values('=randregex([A-Z]\d\d)') =randrow() gets the value of a certain column of a random row from a certain table, used for inserting row into tables with RI. it is possible to cache the values in the table for faster access. examples: insert into xxx values(=randrow(tbl1.col1), 'blabla') insert into xxx values('=randrow(tbl1.col1)', 'blabla') insert into xxx values(=randrow(tbl1.col1, cache), 'blabla') =lastinsert() fetches the value in a given column of the last inse +rt or update if you want to use values the preceding insert/update must res +ide in a batch of its own examples: insert into x values("1", 2) go insert into y values(=lastinsert(1), lastinsert(2)) update x set f1 = =lastinsert(1) where f1 = "9" =head1 NOTES Example configuration files are provided for Sybase Adaptive Server En +terprise, Adaptive Server Anywhere and ODBC. Please note the special +connection syntax for ASA. For connection information read the documentation of the DBD. =head1 FILES db-stress.pl main program db-stress.cfg default configuration file db-stress.pod documentation db-stress.1 man-page sybase_ase.cfg example configuration file for Sybase Adaptive Server Enterprise sybase_asa.cfg example configuration file for Sybase Adaptive Server Anywhere odbc_ase.cfg example configuration file for Sybase ASE ODBC odbc_asa.cfg example configuration file for Sybase ASA ODBC =head1 TODO - test and support for more RDBMS - better error handling - install script =head1 LIMITS On systems not supporting fork(3) only the first session will be proce +ssed in only one process. =head1 AUTHOR -- Bernd Dulfer (bdulfer@sybase.com) =head1 SEE ALSO perl(1), DBI(3), String::Random(3) =cut #--------------------------------------------------------------------- +---------------------------------------------- # # sample configuration files # #--------------------------------------------------------------------- +---------------------------------------------- # format of configuration file is described in db-stress(1) # this is for sybase adaptive server enterprise with demo database ins +talled # name: sybase_ase.cfg connect = dbi:Sybase:server=SYBASE user = sa password = database = pubs2 # session is a bunch of jobs all doing the same batches session = 1 # the count of jobs working for this session count = 5 # a batch is a sequence of sql and db-stress commands batch = 1 delay = 10 commands = insert into authors (au_id, au_lname, au_fname, address, city, state, +country, postalcode) values('=randstring(4)', '=randstring(40)', '=ra +ndstring(20)', '=randstring(40)', '=randstring(20)', '=randstring(2)' +, '=randstring(12)', '=randstring(10)') insert into titles values('=randregex([A-Z][A-Z])xxxx', '=randstring(8 +0)', '=randrow(titles.type, cache)', '=randrow(publishers.pub_id, cac +he)', rand(1234), rand(2345), rand(4321), '=randstring(200)', getdate +(), 1) go select * from titles loop session = 2 count = 20 batch = 1 delay = 5 commands = begin tran go insert into sales values('=randrow(stores.stor_id, cache)', '=randstri +ng(20)', getdate()) go insert into salesdetail values(=lastinsert(1), =lastinsert(2), '=randr +ow(titles.title_id, cache)', rand(100), 0) insert into salesdetail values(=lastinsert(1), =lastinsert(2), '=randr +ow(titles.title_id)', rand(100), 0) insert into salesdetail values(=lastinsert(1), =lastinsert(2), '=randr +ow(titles.title_id)', rand(100), 0) commit go #--------------------------------------------------------------------- +----------------------------------------------- # format of configuration file is described in db-stress(1) # for sybase adaptive server anywhere with a test database # name: sybase_asa.cfg connect = DBI:ASAny:asatest user = UID=dba;PWD=sql;ENG=asatest password = na database = # session is a bunch of jobs all doing the same batches session = 1 # the count of jobs working for this session count = 1 # a batch is a sequence of sql and db-stress commands batch = 1 delay = 3 # no. of seconds the job will wait after this + batch is run, default is 1 commands = # mark for starting of commands insert into tbl1 values(rand(999999), '=randstring(30)') insert into tbl1 values(rand(999999), '=randstring(30)') insert into tbl1 values(rand(999999), '=randstring(30)') go select * from tbl1 loop #--------------------------------------------------------------------- +---------------------------------------------- # format of configuration file is described in db-stress(1) # this is for sybase adaptive server enterprise with demo database ins +talled # you have to create the DSN on your own # name: odbc_ase.cfg connect = dbi:ODBC:ASE1 user = sa password = database = pubs2 # session is a bunch of jobs all doing the same batches session = 1 # the count of jobs working for this session count = 5 # a batch is a sequence of sql and db-stress commands batch = 1 delay = 5 commands = insert into authors (au_id, au_lname, au_fname, address, city, state, +country, postalcode) values('=randstring(4)', '=randstring(40)', '=ra +ndstring(20)', '=randstring(40)', '=randstring(20)', '=randstring(2)' +, '=randstring(12)', '=randstring(10)') insert into titles values('=randregex([A-Z][A-Z])xxxx', '=randstring(8 +0)', '=randrow(titles.type, cache)', '=randrow(publishers.pub_id, cac +he)', rand(1234), rand(2345), rand(4321), '=randstring(200)', getdate +(), 1) go select * from titles loop #--------------------------------------------------------------------- +--------------------------------------------------- # format of configuration file is described in db-stress(1) # this is for sybase adaptive server anywhere with demo database insta +lled # you have to create the DSN on your own # name: odbc_asa.cfg connect = dbi:ODBC:ASA1 user = dba password = sql database = asademo # session is a bunch of jobs all doing the same batches session = 1 # the count of jobs working for this session count = 5 # a batch is a sequence of sql and db-stress commands batch = 1 delay = 5 commands = insert into customer values(=randregex(\d\d\d\d), '=randstring(15)', ' +=randstring(20)', '=randstring(35)', '=randstring(20)', '=randstring( +2)', '=randstring(10)', '=randstring(12)', '=randstring(35)') go select * from customer loop #--------------------------------------------------------------------- +----------------------------------------------------- # format of configuration file is described in db-stress(1) # this is for ms sql-server with demo database installed # you have to create the DSN on your own # name: odbc_mssql.cfg connect = dbi:ODBC:stress user = sa password = database = pubs2 # session is a bunch of jobs all doing the same batches session = 1 # the count of jobs working for this session count = 1 # a batch is a sequence of sql and db-stress commands batch = 1 delay = 5 commands = insert into authors (au_id, au_lname, au_fname, address, city, state, +contract) values('=randregex([0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0- +9][0-9])', '=randstring(40)', '=randstring(20)', '=randstring(40)', ' +=randstring(20)', '=randstring(2)', 0) go select * from titles loop #--------------------------------------------------------------------- +---------------------------------------------------
In reply to db-stress - put workload on database by busunsl
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |