Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

db-stress - put workload on database

by busunsl (Vicar)
on May 16, 2001 at 16:05 UTC ( #80859=sourcecode: print w/ replies, xml ) Need Help??

Category: database stuff
Author/Contact Info busunsl
Description: The main purpose of db-stress is to put a heavy load on a database server, so you have the ability to try out several tuning measurements.
Another purpose is to use it as a data generator to test data replication.

Documentation and sample configuration is at the end of the source.

This is fairly old, so bear with me.
Any comments, bugfixes, improvements welcome.

#! /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

#---------------------------------------------------------------------
+---------------------------------------------------

Comment on db-stress - put workload on database
Download Code

Back to Code Catacombs

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://80859]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (3)
As of 2015-07-04 03:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (57 votes), past polls