Testing performance using trantest

[former_SQLA_member1694872]

How do I use the TRANTEST? I made some changes in the structure of a database and now I want to check if the results are satisfactory. I'm using ASA 9.0.2.3951, OS Win. The TRANTEST should run via DOS? How do I use the parameters?

[MarkCulp · Accepted solution]

In your ASA 9.0.2 installation there is a readme.txt file in the same directory as trantest.exe (i.e. samplesasaPerformanceTransaction) that explains how to use trantest. Trantest is an executable that you run from a command prompt - e.g. use cmd.exe or any other shell.

The basic procedure to using this tool is to (a) create the tables in your database, (b) write one or more procedures that performs the typical transaction(s) that you want to test, and then (c) use trantest to run concurrent calls to your procedure(s). The tool will then output various statistics about the performance of your test run.

The command line options to trantest are (i.e. output from running 'trantest.exe'):

| Usage: TRANTEST <switches>
|     
| Switches:
|         -a <api>       API to use (ESQL or ODBC)
|         -c <str>       connection string
|         -d             display per-thread statistics
|         -f <file>      SQL script file
|         -g <num>       thread group id
|         -i <num>       isolation level
|         -k <num>       commit after 'num' transactions
|         -l <num>       limit for test in secs (default 300)
|         -m <num>       mean time between transactions for each thread (ms)
|         -n <list>      number of threads to run at each iteration
|                        (e.g. -n 1,5,10,20)
|         -o <file>      output results to file
|         -p <num>       number of machines (thread groups)
|         -r <num>       display refresh rate (seconds)
|         -t <str>       name of test to run
|         -u <num>       maximum desired response time (secs)
|         -w <num>       warm-up time (secs)
|         -x             prepare and drop on each execution

Example: Suppose I have procedure P1, P2, ... P3 created in my database that execute three different transactions, then I would create a file (say "mytest") which has the line:

call P{thread}()

and then I could run trantest for 60 seconds using (as an example):

trantest -c ... -f mytest -a esql -n 3 -m 1 -r 1 -k 1 -l 60

The output will look similar to (your numbers will be different!):

 -----------------------------------------------------------------
|   |         |         |      |Average |Maximum |Average |Percent|
|   |         |  Total  |      |Response|Response|Waiting | Under |
|   | #Trans. | #Trans. | T/sec|Time(ms)|Time(ms)|Time(ms)| 2 Secs|
 -----------------------------------------------------------------
          2177      2177 2214.6        0       16        0   100.0
          4469      4469 2237.9        0       16        0   100.0
          2188      6657 2210.9        0       16        0   100.0
...[snip]...

You can play with the parameters to change the frequency of commits (-k), the refresh rate of the output (-r), number of simulated machines (-p), the isolation level used (-i), etc.

HTH

[former_SQLA_member1694872 · Accepted solution]

Ok.. I'm running and is displaying the following messages: (I'm certainly doing something wrong, but still can not detect).

C:\\>trantest -c "uid=dba;pwd=xxx;eng=xxxxx" -f trantabs.sql -a odbc -n 3 -m 1 -r
1 -k 1 -l 60 -o result.txt
Error [thread 0]: Unable to connect
Error [thread 0]: Error executing statement
TRANTEST tables not found. Run:  DBISQL READ TRANTABS.SQL

archive content TRANTABS.SQL is creating tables in the database:

if exists (select * from SYS.SYSTABLE where table_name='TT_Sync') then
    drop table TT_Sync
end if
go

if exists (select * from SYS.SYSTABLE where table_name='TT_RunDesc') then
    drop table TT_RunDesc
end if
go

if exists (select * from SYS.SYSTABLE where table_name='TT_Result') then
    drop table TT_Result
end if
go

// This table is used to synchronize startup when running several client
// machines.
create table TT_Sync(
    thread_group    smallint    primary key,
    status      char(10)    not null
)
go

create table TT_RunDesc(
    run_nbr     smallint    primary key default autoincrement,
    thread_count    smallint    not null,
    run_duration    smallint    not null,
    start_time      timestamp   default current timestamp,
    parms       long varchar
)
go

create table TT_Result(
    run_nbr     smallint    not null,
    thread_group    smallint    not null,
    thread_num      smallint    not null,
    trans       int     not null,
    avg_response    int     not null,
    max_response    int     not null,
    avg_waiting     int     not null,
    percent_under   int     not null,
    primary key (run_nbr, thread_group, thread_num)
)
go

alter table TT_Result
    add foreign key RunDesc (run_nbr) references TT_RunDesc (run_nbr)
go

[MarkCulp · Accepted solution]

You need to pre-create the tables. Your TRANSTAB.SQL script should be executed against your database using dbisql prior to using trantest. I.e. Trantest's role is not to create the tables, it is to execute your sql queries/updates/deletes that you have in your transactions.

For example, in my sample above, mytest simply contained "call p{thread}()" (as stated above). All of my transaction logic was contained within the procedure p1, p2, p3 that I had already created in my database.

[former_SQLA_member1694872 · Accepted solution]

Ok...thanks for the help.

[VolkerBarth · Accepted solution]

Mark, I'd suggest to add your answer as part of the readme.txt of TRANTEST - I makes the usage much clearer, methinks...