by Dimitri |
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...
db_STRESS database contains only 5 tables simulating sort of library or stock placement, etc. (schema creation script is in attachment): ZONE, SECTION, OBJECT, HISTORY, STAT. There are following reference relations between tables:
[STAT] <=(1:P)= [HISTORY] =(20:1)=> [OBJECT] =(N:1)=> [SECTION] =(M:1)=> [ZONE]For each record of OBJECT there are 20 records of HISTORY (sort of historical trace of last 20 changes). Database size is driven by number of objects. While number of records in STAT, SECTION and ZONE is fixed:
- STAT: 1000 rows
- SECTION: 100 rows
- ZONE: 10 rows
To simplify database comparison, no foreign key or other constrains are used.
Benchmark Workload |
db_STRESS simulates a sort of OLTP workload to stress database as much as possible. Performance level of workload is measured mainly in TPS (transactions per second), but evolution of transaction response time is monitored as well. There are 2 types of transactions: READ and WRITE.
READ - read randomly chosen OBJECT and its whole information by OBJECT reference
- SEL1: time of select query to get OBJECT-SECTION-ZONE by object reference
- SEL2: time of select query to get HISTORY-STAT data by object reference
WRITE - delete + insert + modify HISTORY record of previously found OBJECT
- DEL: delete time
- INS: insert time
- UPD: update time
To simplify test portability:
- SELECT query is not using "FOR UPDATE" clause
- AUTOCOMMIT is ON, so WRITE transaction in reality is a triple-commit (or triple-transaction)
Response time is measured in microseconds (1000 ums= 1ms)
db_STRESS Injector |
Injector client is a single process simulating a single user activity. You may run as many injectors as you want to vary your workload, grow up system activity, etc. All the time of activity, each injector publish all its internal counters via common SHM. Scanning SHM by external program gives a way to get real-time reporting without any blocking on injector's work.Injector parameters:
-SHMKEY key -- SHM key (def:555) -MaxID number -- Max Client ID (for SHM size) -ID number -- Client ID (0 : MaxID-1) -Group name -- Client's Group name (def: db_STRESS) -Timeout number -- 'Think time' (sleep in sec.) between each READ transaction -RW number -- Execute single WRITE after given number of READ (0: no writes) -Base name -- Database to use (def: db_STRESS, ex: dbname@host:port) -User username -- Database user -Passwd password -- Database user's password -TotalTIME number -- Stop injector execution after number seconds -StopFile fname -- Stop injector execution if file exists -MAX numb -- Give Max OBJECT-ID to avoid table full scan -UpdateONLY yes/no -- Do UPDATE only (no INSERT/DELETE) (default: no) -AntiDEAD number -- Anti DeadLock feature: increase row ID dispersion to avoid artificial locks
dbsSTAT command |
dbsSTAT command should be used to print live statistics about running db_STRESS test activity. dbsSTAT needs few entry parameters for correct execution (and in following order):
- key - SHM key (number), the same as used by db_STRESS injector
- MaxID - max possible Client ID number (to size SHM correctly)
- timeout - time interval (in sec.) between outputs
- reset - optional SHM reset
Example:
$ dbsSTAT dbsSTAT (dim) v.1.1 Usage: dbsSTAT key MaxID timeout [reset] $ 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 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 0Where:
- Group-ID: column contains client's pair identification (group name + ID number), special case for ALL-sum and ALL-tps showing grouped avg summary
- Tot: total number of transactions executed by client during the given time interval (as well, ALL-sum shows a sum of all clients, and ALL-tps a grouped transactions pes second (tps))
- Rds: same as Tot, but read transactions only
- Wrs: same as Tot, bur write transactions only
- SEL1: avg response time for SELECT-1 query during given time interval
- SEL2: same but for SELECT-2
- DEL: same but for DELETE
- INS: same but for INSERT
- UPD: same but for UPDATE
- C/N: number of collisions/errors reported in client session during given time interval (except ALL-sum and ALL-tps showing total number of active(!) clients during given time interval)
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.shTest 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:
- Oracle v9+ (and higher)
- on Solaris/SPARC, Solaris/x86, Linux/x86
- PostgreSQL v8+ (and higher)
- Solaris/SPARC, Solaris/x86, Linux/x86
- MySQL v3+ (and higher)
- Solaris/SPARC, Solaris/x86, Linux/x86You 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)
DataSet Generation |
Data set is generating with "BenchDATA" program. Check the 'bin' directory - probably there is already a shipped binary for your platform. Otherwise you may compile it (with gcc for ex.):
$ gcc BenchDATA.c -o BenchDATAThen generate your data set according database volume you want to test. For 1M 'Objects' in database:
$ BenchDATA 1000000(the single given parameter just says how many objects you want to have in your database, I put a million just for a small test (generally few GBs in database size)), files are generated in the current directory where the program is executed. As output is pre-formatted, it will run for a while...
Create Tables (schema) |
Tables are created with SQL script "BenchBASE_tab.sql" - we creating only tables and not indexes here. According database vendor, execution may be different (where user, passwd, dbname are database login, password and database name):- Oracle: $ sqlplus user/passwd < BenchBASE_tab.sql - PostgreSQL: $ psql dbname < BenchBASE_tab.sql - MySQL: $ mysql -uuser -ppasswd dbname < BenchBASE_tab.sql
Load Data |
db_TRESS kit contains several prepared scripts for all 3 database vendors to load data set files. You may find these scripts in corresponding database directory:- oracle/load_oracle.sh -- loading data via 'sqlldr' command (very fast) to Oracle
- pgsql/load_pgsql.sh -- loading data via 'copy' order (PostgreSQL)
- mysql/load_mysql.sh -- loading data via 'load' order (MySQL)
Create Indexes |
You need to execute "BenchBASE_idx.sql" SQL script now against your database server:Depending on database volume this operation may take quite long time. Executing "analyze" after index creation is highly encouraged (database dependent).- Oracle: $ sqlplus user/passwd < BenchBASE_idx.sql - PostgreSQL: $ psql dbname < BenchBASE_idx.sql - MySQL: $ mysql -uuser -ppasswd dbname < BenchBASE_idx.sql
BEFORE you starting any test... |
Please, check first your server and database engine configurations adapted to expected workload! Adjust settings from the beginning to avoid time wasting to you (and probably others :))Following notes are reflecting some finding/fixes during different benchmarks...
Configuration adjustments:
init.ora: (parse + index)
cursor_sharing=similar
optimizer_index_cost_adj=25
Solaris /dev/tcp: (queue size)
ndd -get /dev/tcp tcp_conn_req_max_q # ==> default: 128 ndd -get /dev/tcp tcp_conn_req_max_q0 # ==> default: 1024 ndd -set /dev/tcp tcp_conn_req_max_q 1024listener.ora: (queue size)
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = DBS)) (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)(QUEUESIZE=1024)) ) )
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 ...
Generating Scenario scripts |
You may still generate different workloads by running several single-client scripts together and prepare more sophisticated scenarios. However, it may very quickly become painful to keep related timers and client IDs. So there is an "x.Scenario" script to help in this task:$ WebX x.Scenario options > test.sh options: -Prog program -- program to execute with args (ex: "WebX.ORA x.db_STRESS" ) -SHMKEY key -- SHM key for stats (def: 555) -MaxID id -- Max client ID (def: 2000) -RW number -- number Reads per Write (def: 1) -Timeout sec -- timeout sec between transactions (think time) (def: 0) -Levels list -- incremental list of levels to reach (ex: "10 20 40" etc.) -base dbname -- database name -dbsize number -- database size (number of loaded objects (to avoid count(*) scan) -host hostname -- hostname -user user -- database user name -passwd password -- database user's password -Pref prefix -- title prefix in test namingthe output of this script contains a straight forward shell script with list of commands to execute without any logic (so, execution from the beginning to the end is guaranteed :)), so just save output into scenario file and execute then by shell.
NOTES:
- -RW value may be multiple and same scenario will be replayed for each value (ex: "1 10 50")
- -base and -host values are concatenated together to give a single DB Server name (like DB@host), so in case you don't use hostname (like Oracle SID) just leave -host empty...
- -base and -host may have multiple values (to replay the same scenario on several databases or machines one by one)
- -Levels are managed in way to keep each demanded step during 90 seconds, then stay on the last one for 10 minutes, and then decrease load to several steps backward from list, and then stop scenario execution...
Example:
$ WebX x.Scenario -RW 1 -Timeout 0 -Levels "5 10 20 40 80 160" -base DBS -host @dbserver \ -dbsize 10000000 -Prog "WebX.pgSQL x.db_STRESS" -Pref "Production Box" > Test.sh $ $ sh Test.sh -- Execute scenario ...
Integration with dim_STAT |
Since v.8.2, db_STRESS Scenario Generator is shipped with dim_STAT, and it's really simplifying a scenario generation! Just select cases you want to play, fill few information fields, and run your test!...Since v.8.1, dim_STAT includes pre-installed Add-Ons to collect db_STRESS execution statistics. Since v.8.2, dbsSTAT is also shipped pre-installed within STAT-service package. So, if you don't change default values on your scenarios (shmkey=555 and maxid=2000 (max sessions)) you may start collecting data straight forward from any injector running db_STRESS!...
Standard Reference Scenario v.1.0 |
In case you want to replay the same tests I published on my site - I prepared a single shell script dbSTRESS-ReferenceRUN-v1.sh shipping with db_STRESS kit now.This scenario should be executed just after database restart. It will:
- start a cold run Read-Only (RW=0)
- start a cold run Read+Write (RW=1) - by cold here I mean there was no dirty pages yet :-)
- start a warm run Read-Only (RW=0)
- start a warm run Read+Write (RW=1)
- start a warm run 10Reads per Write (RW=1)
Each run conditions:
- concurrent sessions are increasing every 2 minutes: 1 2 4 8 16 32 64 128 256
- think time: 0 sec
- OBJECTs in database: 10M
- antideadlock: each sessions is addressing only its own (randomized) OBJECT IDs, so any row concurrency coming from test case is avoided :-)
Total test run is estimated for 3 hours.
Execution parameters:
$ prog=WebX.mySQL5|WebX.pgSQL|WebX.ORA $ dbname=databasename[@host[:port]] $ user=dbusername $ passwd=dbuserpasswd $ dbSTRESS-ReferenceRUN-v1.sh "Test title"Example for MySQL:
$ prog=WebX.mySQL5 $ dbname=dbstress@myhost:3306 $ user=dim $ passwd=dim $ dbSTRESS-ReferenceRUN-v1.sh "Test 32cores pool=12G M5000"