What is DBStressor

DB Stressor is the tool to perform the tests of your databases productivity and stability. DBStressor simulates the multi-user concurrent work, collects and stores the performance statistics and represents the results in the preprocessed view.

System requirements

Client

• MS Windows 9x/NT/2000/XP (Windows 9x family is not recommended)
• Microsoft Data Access Components (MDAC) version 2.7 or higher installed
• size of RAM as recommended for your operational system
• about 10 MB of HDD space

Database server:

• availability of ODBC or OLE-DB driver
• ability to execute the scripts (SQL-scripts) using the procedural extension

All widely known DBMS are conformed to requirements, for example: MSSQL and Sybase ASE (Transact-SQL), Oracle (PL/SQL), InterBase (procedural extension), Sybase SQL Anywhere (Watcom-SQL) and many others.

How to install

1. For Windows XP or earlies versions, download and install Microsoft Data Access Components (MDAC) version 2.7 or higher if it is not installed yet on your PC
2. Download DBStressor
3. Unpack DBStressor into the folder on your PC (I recommend you to create new folder)
4. Run bin\DBStressor.exe

Planning tests

The most general purposes of the tests are the productivity and response time measurements on various:

• hardware configurations of database server
• users number

Generally, for performing the tests you need to:

• determine the tests initial data:
  • the end-users structure: their roles and typical use-cases the database (scenarios)
  • the use-cases time characteristics
• develop the typical end-users scripts using your DBMS programming language
• perform the tests
• analyze the results

There are no detailed instructions to complete all of these tasks, therefore we shall try to show the essence and the actions sequence in a simple example.

Example of test development

Determine initial data

Suppose, you develop the database application for 100 concurrent users. Let 15 users of these 100 to be an "operators" who input various information in the database. Let 5 users to be a "proof-readers" and experts who selectively check the brought information, correct mistakes or delete disinformation. And let the rest of 80 users to be a "readers", who search and select the information with groupings or simple calculations (total, average, etc.) using the entire database and the various criteria.

According our use-cases, every "operator" input the unit of information (assume that it is a document) 15 times in one hour on average. The "proof-reader" modifies 15 and deletes 5 documents per hour on average. The "reader" produce 30 queries per hour on average: 25 are the simple search by values of some attributes and 5 are the groupings and calculating. These are the average values.

Sure, we have always the restrictions of the minimal values also. In our example the "operator" is not able to input one document faster than for 20 seconds, the "proof-reader" is not able to check the document faster than for 30 seconds and the "reader" cannot form the new query faster than for 20 seconds. These are the minimal values. Now, we have the average and minimal values, so we can to compute the undefined maximal values
using the formula (1)

D_max = D_min + 2 * D_avg (1)

Indeed, we need only maximal and minimal values for the tests, so if you know them initially, you don't need to calculate the average ones.

Using our example's initial data we can write them to the table as shown below. Note, that initial time values for DBStressor are set in milliseconds.

If you're testing the multi-tier system using the multiplexing of database connections, there will not be principal difference, you stay to measure the DBMS performance. In this case you can use the same scenarios but only take into account the multiplexing. You can use these simple formulas:

K = U / C_as  (2)

U_{i as} = U_i / K  (3)

D_{i as} = D_i * K  (4)

where

• К - multiplexing coefficient
• U - total end-users count
• C_as - number of application server connections to the DBMS (if it is variable, take the average value)
• U_i - users count of i-th type
• U_{i as} - users count of i-th type in view of multiplexing
• D_i - delay between the actions of the user of i-th type
• D_{i as} - delay between the actions of the user of i-th type in view of multiplexing

Thus, we accept the important assumption, that the data not cached at the application server's level. The cache technology should increase productivity only, not decrease. If ever the productivity at using the cache will be lower than shown one in DBStressor tests, I advise you to throw out such caching in a
garbage.

Developing the scripts

For our example we're using simple database schema, containing only 2 tables: "Document headers" and "Document lines". For this simple schema, the scripts are simple too, but sufficient to show you the main
DBStressor features. See Appendix A for the database schema and the Transact-SQL code scripts (for MSSQL 2000, uses demo database "pubs").

Perform the tests

When you start DBStressor application, the page "Test settings" will become active.

Firstly, you must specify the ADO connection string to your database. It is easy to do using "Build" button.

Output options permit you to manage the view of results which is requested every N seconds, where N - is a value of the field "Refresh interval". The log displays the messages, containing the process states information like starting or finishing processes and so on. The number of displayed log's lines is always restricted to preserve the memory usage, but if you want to save all messages, you can specify the file where the log messages should be written. If you increase the messages level, the number of the messages will be decreased and their content will include the debug information, like the text of currently execute SQL script or other detailed process information.

Surely, you need to manage the duration of our experiences. You can specify various conditions, having achieved which the DBStressor will try to stop the processes. Note, that DBStressor cannot stop immediately, because it is waiting till all of the processes will terminate correctly, so it may take time up to
tens seconds.

After you set the test options, go to "Scenario parameters" page to specify the users types (roles), their number, delays values and SQL script file.

Now, you are ready to start the tests! Click on the button "Start" or press hotkey combination "Ctrl+S". The page "Test results" will become active.

The results information will be refreshed every N seconds you set in options. You may also refresh it immediately manually.

DBStressor store all results into local database, which is located in the subfolder "Database" in DBStressor home folder. You can view all results by changing the filter from "Last results" to "All results". To remove all results from database, click to "Clear all button".

After you finished the test, you can export results to MS Excel sheet to calculate some values or get the graphical chart.

DBStressor macros

Appendix A

Database schema

/*
  DBStressor sample script for MS SQL 2000
  uses PUBS database to create tables
*/
use pubs
go
 
create table DocumentHeaders (
  Number int not null identity(1, 1),
  Name varchar(128) not null,
  DocDate datetime not null,
  constraint PK_DocumentHeaders primary key (Number)
)
go
create index IX_Name_DocumentHeaders on DocumentHeaders(Name)
go
 
create table DocumentLines (
  DocNumber   int not null,
  LineNumber  int not null,
  LineText    varchar(255),
  constraint PK_DocumentLines primary key (DocNumber, LineNumber),
  constraint FK_DocumentLines_DocumentHeaders
    foreign key (DocNumber) references DocumentHeaders(Number)
)
go
create index IX_LineText_DocumentLines on DocumentLines(LineText)
go

Operator's script

/*
  DBStressor sample script for MS SQL 2000
*/
/* Operator actions scenario */
use pubs
declare @DocNumber int
-- insert document header
-- inserting current date and time value
insert into DocumentHeaders(DocDate, Name)
  values (getdate(), 'Doc %Random(100)%')
-- getting last value of autoincrement column
select @DocNumber = @@identity
-- insert random count of document lines from a range of 30 < @Count < 80
declare @Count int, @i integer
set @Count = 30 + %Random(50)%
set @i = 1
while @i <= @Count begin
  insert into DocumentLines(DocNumber, LineNumber, LineText)
    values (@DocNumber, @i, 'Line %Random(1000000)% text %Random(1000000)% text2 %Random(1000000)%')
  set @i = @i + 1
end

Proof-editor's script

/*
  DBStressor sample script for MS SQL 2000
*/
 
/* Editor actions scenario */
 
use pubs
 
-- select random document
declare @DocNumber      int,
        @MaxDocNumber   int,
        @LineNumber     int,
        @MaxLineNumber  int
 
select @MaxDocNumber = max(Number) from DocumentHeaders
select @DocNumber = @MaxDocNumber * %Random()%
-- select nearest existing document
select top 1 @DocNumber = Number from DocumentHeaders
  where Number >= @DocNumber
  order by Number Asc
if @@rowcount = 0 begin
  select top 1 @DocNumber = Number from DocumentHeaders
    where Number <= @DocNumber
    order by Number Desc
end
 
if %Random(20)% < 15 begin
  select @MaxLineNumber = count(*) from DocumentLines where DocNumber = @DocNumber
  select @LineNumber = @MaxLineNumber * %Random()%
  update DocumentLines
    set LineText = 'Line %Random(1000000)% corrected %Random(1000000)% corrected2 %Random(1000000)%'
    where DocNumber = @DocNumber and LineNumber = @LineNumber
end
else begin
  begin transaction
  delete from DocumentLines where DocNumber = @DocNumber
  delete from DocumentHeaders where Number = @DocNumber
  commit
end

Reader's script

/*
  DBStressor sample script for MS SQL 2000
*/
 
/* Reader actions scenario */
use pubs
 
-- select random document
if %Random(30)% < 25 begin
  select *
    from DocumentHeaders DH join DocumentLines DL on DH.Number = DL.DocNumber
    where datediff(ss, DH.DocDate, getdate()) > %Random(3600)% and
    DL.LineText like '%%Random(1000000)%%'
end
else begin
  select count(*)
    from DocumentHeaders DH join DocumentLines DL on DH.Number = DL.DocNumber
    where DL.LineText like '%%Random(1000000)%%'
end