Created: 2007-05-15
Last modified: 2009-04-22




db_STRESS Benchmark






by Dimitri








Ces informations sont données à titre indicatif et n'engagent pas Sun Microsystems.

Table of contents



db_STRESS Benchmark Overview
This benchmark has a long history and based on a true customer's workload. My goal here is to not only measure a highest TPS value for a given database, but also follow its stability curve, response time evolution, and scalability with growing workload. I want to know where is the critical point of the database server and how it performs on even overloaded system...



Small Test example
Let's run a small test just to see how things are going. I'll start a single client named 'Reader' (which will execute read only transactions), then 2 stress clients. Test duration is less 1 minute.

Test Script:

$ more Test.sh

# Simple Test
# -----------------------
db_STRESS.sh -Base db_STRESS -ID 0 -Timeout 1 -TotalTIME 40 -Group Reader -RW 0 -SHMKEY 555 -MAX 1000  -MaxID 2000 &
sleep 15

db_STRESS.sh -Base db_STRESS -ID 1 -Timeout 1 -TotalTIME 20 -RW 1 -SHMKEY 555 -MAX 1000 -MaxID 2000 &
db_STRESS.sh -Base db_STRESS -ID 2 -Timeout 1 -TotalTIME 20 -RW 1 -SHMKEY 555 -MAX 1000 -MaxID 2000 &

wait
# -----------------------
# End of Test

$ Test.sh

Test Monitoring:

$ dbsSTAT 555 2000 10 

_______Group-ID__    Tot  Rds  Wrs    SEL1    SEL2     DEL     INS     UPD C/N
         ALL-sum       0    0    0       0       0       0       0       0   0
         ALL-tps       0    0    0       0       0       0       0       0   0

_______Group-ID__    Tot  Rds  Wrs    SEL1    SEL2     DEL     INS     UPD C/N
      Reader-0001      0    0    0       0       0       0       0       0   0
         ALL-sum       0    0    0       0       0       0       0       0   1
         ALL-tps       0    0    0       0       0       0       0       0   1

_______Group-ID__    Tot  Rds  Wrs    SEL1    SEL2     DEL     INS     UPD C/N
      Reader-0001      9    9    0    1834   46749       0       0       0   0
   db_STRESS-0002      0    0    0       0       0       0       0       0   0
   db_STRESS-0003      0    0    0       0       0       0       0       0   0
         ALL-sum       9    9    0    1834   46749       0       0       0   3
         ALL-tps       0    0    0    1834   46749       0       0       0   3

_______Group-ID__    Tot  Rds  Wrs    SEL1    SEL2     DEL     INS     UPD C/N
      Reader-0001     10   10    0    1850    7935       0       0       0   0
   db_STRESS-0002     18    9    9    4228   11968    5381    1957    1954   0
   db_STRESS-0003     18    9    9    2807   10142    6631    1929    2223   0
         ALL-sum      46   28   18    2921    9940    6006    1943    2088   3
         ALL-tps       4    2    1    2921    9940    6006    1943    2088   3

_______Group-ID__    Tot  Rds  Wrs    SEL1    SEL2     DEL     INS     UPD C/N
      Reader-0001     10   10    0    1851    6710       0       0       0   0
   db_STRESS-0002     20   10   10    3701   13395    3568    1907    2355   0
   db_STRESS-0003     20   10   10    3784   10491    6650    2068    2158   0
         ALL-sum      50   30   20    3112   10198    5109    1987    2256   3
         ALL-tps       5    3    2    3112   10198    5109    1987    2256   3

_______Group-ID__    Tot  Rds  Wrs    SEL1    SEL2     DEL     INS     UPD C/N
      Reader-0001      5    5    0    1823    6840       0       0       0   0
         ALL-sum       5    5    0    1823    6840       0       0       0   1
         ALL-tps       0    0    0    1823    6840       0       0       0   1

_______Group-ID__    Tot  Rds  Wrs    SEL1    SEL2     DEL     INS     UPD C/N
         ALL-sum       0    0    0       0       0       0       0       0   0
         ALL-tps       0    0    0       0       0       0       0       0   0


Installation
Zero step: having database server installed and running on your machine! :)

Currently 3 database vendors are supported:

You need to have a valid pair login/password to connect to your database and have permission to create table/indexes and populate data.

Then download db_STRESS kit (http://dimitrik.free.fr/db_STRESS-kit.tar) and untar it somewhere on your server and let's continue...

db_STRESS KIT contains:

./.shell -- saved syntax of few commands (examples)

./Bench.Scenario.sh -- Test examples ./Bench.all-example.sh

./BenchBASE_tab.sql -- Tables creation ./BenchBASE_idx.sql -- Index creation

./BenchDATA.c -- Data Set generator

./dbSTRESS_STAT.sh -- db_STRESS Stats tool ./dbsSTAT.c ./anySTAT.h

./bin/linux-x86 -- some pre-compiled binaries for Linux/x86 ./bin/linux-x86/BenchDATA ./bin/linux-x86/WebX ./bin/linux-x86/WebX.mySQL5 ./bin/linux-x86/WebX.pgSQL ./bin/linux-x86/dbsSTAT

./bin/solaris-sparc -- some pre-compiled binaries for Solaris/SPARC ./bin/solaris-sparc/BenchDATA ./bin/solaris-sparc/WebX ./bin/solaris-sparc/WebX.ORA ./bin/solaris-sparc/WebX.mySQL5 ./bin/solaris-sparc/WebX.pgSQL ./bin/solaris-sparc/dbsSTAT

./bin/solaris-x86 -- some pre-compiled binaries for Solaris/x86 ./bin/solaris-x86/BenchDATA ./bin/solaris-x86/WebX ./bin/solaris-x86/WebX.mySQL ./bin/solaris-x86/WebX.pgSQL ./bin/solaris-x86/dbsSTAT

./mysql -- MySQL-specific sfaff ./mysql/load_mysql.sh

./oracle -- Oracle-specific staff ./oracle/history.ctl ./oracle/load_oracle.sh ./oracle/object.ctl ./oracle/section.ctl ./oracle/stat.ctl ./oracle/zone.ctl

./pgsql -- PostgreSQL-specific staff ./pgsql/load_pgsql.sh

./x.Bench -- Scripts to generate workload scenarios ./x.Scenario

./x.db_STRESS -- Main db_STRESS script (client)



Executing Test Scenario
First of all from 'bin' directory choose a right WebX.* binary corresponding to your system and database:
     - WebX.ORA (Oracle)
     - WebX.pgSQL (PostgreSQL)
     - WebX.mySQL (MySQL)

this program will generate client workload by interpreting single-user activity script - "x.db_STRESS". You may now copy corresponding WebX*, "dbsSTAT" binaries and all "x.*" scripts into your injector machine (it also may be the same machine, but you'll mix client and server activity)...

NOTE: to execute Oracle workload you absolutely need "Oracle 9 Client" installed on injector! (otherwise connections will be not possible to your database server)...

Now, you may try if it works for you (for ex. with WebX.pgSQL for PostgreSQL, replace database and user names by yours):

$ WebX.pgSQL x.db_STRESS -Base DB@Host -User dim -Passwd dim -ID 0 -Timeout 1 -TotalTIME 40 
 -Group Reader -RW 0 -SHMKEY 555 -MAX 1000  -MaxID 2000 &

and monitor client activity with:

$ dbsSTAT 555 2000 3
  ...