Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Database Workload Simulator

by DrAxeman (Scribe)
on Sep 19, 2005 at 19:13 UTC ( #493262=perlquestion: print w/replies, xml ) Need Help??

DrAxeman has asked for the wisdom of the Perl Monks concerning the following question:

I've been looking for some sort of database workload simulator, but can't find one. I just want to be able to create a database with some data, and perform SELECT, UPDATE, INSERT, DELETE type funtions to show a CPU/RAM activity and disk I/O. If anyone knows of a free utility that may fit my needs, please point me to it.

Otherwise I'm going to try and develop something for myself. I'm going to be using an Oracle 10g database, and would like to use perl as the front-end.

Does anyone have any existing scripts that may help me out? Anything that will help cut back on the amount of work I have will help. In the end, I'll share the simulator for anyone who has similar needs.

Replies are listed 'Best First'.
Re: Database Workload Simulator
by jZed (Prior) on Sep 19, 2005 at 20:30 UTC
    The best thing you can do to optimize your performance is to a) design the SCHEMA to best support the kinds of queries you want to make, including making sure to index the right columns and b) phrase your queries in the best way - which you should be able to tell with Oracle's own tools (EXPLAIN, or whatever they call the SQL command that optimizes and summarizes queries) c) make sure you are using DBI placeholders and looping correctly to minimize multiple prepares d) use bind_cols and/or the array fetches

    Unless your Perl is quite invovled, it's unlikely that anything else will have anywhere as much impact as those things.

      Maybe use prepare_cached too.

      Walking the road to enlightenment... I found a penguin and a camel on the way.....
      Fancy a Just ask!!!
Re: Database Workload Simulator
by InfiniteLoop (Hermit) on Sep 19, 2005 at 19:36 UTC
    DrAxeman, what data base are you using ? There are few free benchmarking utilities for MySQL, which you can use to measure the cpu/disk usage:
    1. sql-bench
    2. MySQL Super Smack
    3. MyBench
      I've been asked for this on Oracle 10gR2. If other databases were an option I would probably use MySQL.
Re: Database Workload Simulator
by graff (Chancellor) on Sep 20, 2005 at 17:31 UTC
    I'm not familiar with specifics, but I believe that Oracle does provide tools for profiling the performance of the query engine, as well as analyzing specific queries. You might need DBA-level access to find and use them.

    As for perl/oracle interaction, a few general rules of thumb are usually sufficient. Some have been cited in previous replies; another I would add, which could have major impact, is to use the oracle-native sqlload(er) tool to do bulk insertions into tables, rather than iterating over lots of DBI "insert into..." executions -- even when you use prepared/placeholder statements, bound variables, etc, in your DBI insert calls, this is still one or two orders of magnitude slower than sqlload.

Re: Database Workload Simulator
by jplindstrom (Monsignor) on Sep 21, 2005 at 08:51 UTC
    Here's a simple script that executes some SQL as often as possible. I've used it to e.g. measure the overhead of Java stored procedures in Sybase and stuff like that, but I think any SQL statement should work as long as it doesn't return billions and billions of rows. Check out the DBD::Oracle docs on how to form the connect string.

    Start as many instances if you need to simulate many connections.

    #!/usr/bin/perl -w $|++; use strict; use Time::HiRes qw/time/; use DBI; use DBD::Sybase; my ($dbi, $user, $pass, $sql, $logEvery) = @ARGV or die(q{ Syntax: call_sp connect_string user password sql Example: call_sp dbi:Sybase:server=DEV_DB_01_DS;database=test_performance sa PA +SSWORD "exec jpl_test" }); $logEvery ||= 200; my $dbh = DBI->connect( $dbi, $user, $pass, { RaiseError => 1, PrintError => 0 } ); my $sth = $dbh->prepare($sql); my @aOldAverage; my $maxOldAverage = 10; my $no = 0; my $timeLatest = time(); while(1) { if($no++ > $logEvery) { my $timeNow = time(); my $timeDuration = $timeNow - $timeLatest; my $freq = sprintf("%0.2d", $no / ($timeDuration || 1)); $timeLatest = $timeNow; $no = 0; push(@aOldAverage, $freq); if(@aOldAverage > $maxOldAverage) { shift(@aOldAverage); } my $oldCount = @aOldAverage; my $sum = 0; $sum += $_ for(@aOldAverage); my $avg = sprintf("%0.2d", $sum / $oldCount); print localtime() . ": $freq / sec ($avg/sec for the last $old +Count readings)\n"; } eval { $sth->execute(); }; if($@) { warn("$@\n"); $sth = $dbh->prepare($sql); next; } $sth->fetchall_arrayref; $sth->finish; } __END__

    If you need more complex behaviour, you may need to develop a robot (bot) user that simulates typical behaviour with a proper mix of idle time, selects, inserts, etc. If most of the time is spent idle, look into POE (or something like that) to run hundreds or thousands of users in a single process. Start more processes until the machine can't handle any more, then add extra machines.


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://493262]
Approved by saberworks
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2023-10-04 23:21 GMT
Find Nodes?
    Voting Booth?

    No recent polls found