DBStressor
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
- 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
- Download DBStressor
- Unpack DBStressor into the folder on your PC (I recommend you to create new folder)
- 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)
Dmax = Dmin + 2 * Davg (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.
User type (role) | Number of users | One user's actions count per time unit | Delay between the user actions | ||
---|---|---|---|---|---|
Average (Davg) | Minimal (Dmin) | Maximal (Dmax) | |||
Operators | 15 | 15 actions per hour | 4 min (240 000 ms) |
20 sec (20 000 ms) |
500 000 ms |
Proof-editors | 5 | 20 actions per hour | 3 min (180 000 ms.) |
30 sec (30 000 ms) |
390 000 ms |
Readers | 80 | 30 actions per hour | 2 min (120000 ms) | 20 sec (20000 ms) | 260 000 ms |
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 / Cas (2)
Ui as = Ui / K (3)
Di as = Di * K (4)
where
- К - multiplexing coefficient
- U - total end-users count
- Cas - number of application server connections to the DBMS (if it is variable, take the average value)
- Ui - users count of i-th type
- Ui as - users count of i-th type in view of multiplexing
- Di - delay between the actions of the user of i-th type
- Di 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
Macro name | Macro description |
---|---|
%UserNumber% | will be substituted by a integer value which is the number of the user process from a range of 0 <= UserNumber < Total amount of users - 1 |
%Random()% | will be substituted by a random 15-digits float value X from a range of 0 <= X < 1 |
%Random(N)% | will be substituted by a random integer value X from a range of 0 <= X < N |
%Delay(N)% | defines the end of script's block beginning at the start of the script file or at the previous %Delay% macro. DBStressor will execute this script block and will wait N milliseconds before continue processing |
%DelayRandom(N)% | do the same, but the delay X milliseconds will be the random value from a range of 0 <= X < N |
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