Getting Started With TRANTEST

TRANTEST is a high-speed database performance testing utility delivered in the box with SQL Anywhere since version 8.

Wait! What?

It's not your fault you've never heard of TRANTEST. That's because the phrase "delivered in the box" isn't accurate.

First of all, there is no "box", and even if there was, TRANTEST would be "hidden in the box"... you'd have to turn the box upside down and bang on the side and it might fall out.

Keyword "might".

The other reason you've never heard of TRANTEST is that almost nobody has written about it.

... and the reason for that is probably because almost nobody can figure out how to use it. The original author, sure, maybe one or two other people... but the docs are thin and the code is [cough] inscrutable ...

... which might explain why there have been no enhancements in 13 years :)

Enough whining!

This article is at least ten years overdue. That's about when I first tried to make TRANTEST work, and failed, and tried and failed again and again every few years.

This time, it's different.

This time, I was motivated.

This time, I was looking for more test cases to run against Foxhound, including a low-overhead alternative to a fat PowerBuilder application previously used to simulate heavy workloads.

So this time, I got TRANTEST to work (yay!) using Mark Culp's post as a starting point.

TRANTEST Step-By-Step

Here's a simple step-by-step demonstration of TRANTEST:

Step 1. Create a working folder to hold all the TRANTEST and related files.

This step is optional, but it simplifies test setup and protects the original TRANTEST files from accidental damage.

Step 2. Copy all the TRANTEST files ...

from where they are stored in your SQL Anywhere 16 setup

C:\Users\Public\Documents\SQL Anywhere 16\Samples\SQLAnywhere\PerformanceTransaction

to your working folder.

Tip: Be sure to have a look at the readme.txt file, but don't rely on it to explain much everything... and ignore the ancient exhortations like "Don't run with too many threads".

Step 3. Create and start an empty SQL Anywhere 16 database to use for the demonstration.

"%SQLANY16%\bin64\dbinit.exe"^ ddd16.db "%SQLANY16%\bin64\dbspawn.exe"^ -f "%SQLANY16%\bin64\dbsrv16.exe"^ -o dbsrv16_log_ddd16.txt^ -os 10M^ ddd16.db

Step 4. Apply the trantabs.sql script to the target database.

TRANTEST needs its own tables on the target database, and this is the script that creates them:

"%SQLANY16%\bin64\dbisql"^ -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql;"^ READ ENCODING Cp1252 trantabs.sql

Step 5. Create the custom procedures to be executed by TRANTEST.

In this simple demonstration, TRANTEST will use three different threads to call three different procedures simultaneously and repeatedly:

CREATE TABLE t ( pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY, inserted_by VARCHAR ( 10 ) NOT NULL, inserted_at TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP ); CREATE PROCEDURE p1() BEGIN INSERT t ( inserted_by ) VALUES ( 'p1' ); COMMIT; END; CREATE PROCEDURE p2() BEGIN INSERT t ( inserted_by ) VALUES ( 'p2' ); COMMIT; END; CREATE PROCEDURE p3() BEGIN INSERT t ( inserted_by ) VALUES ( 'p3' ); COMMIT; END;

Step 6. Create the TRANTEST -f script to call the custom procedures.

In this simple demonstration, TRANTEST will substitute the thread numbers 1, 2, 3 for the string {thread} in the following script file simple_test.sql, thus generating calls to P1, P2 and P3:

call P{thread}()

Step 7. Run TRANTEST.

As with most SQL Anywhere utilities, default values have been carefully chosen for most options and it's better to accept those defaults if you're not exactly sure what they're for. In TRANTEST's case, this means only 4 command-line switches need explicit values:

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

Here's what the TRANTEST command looks like for this simple demonstration:

trantest^ -a esql^ -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql; CON=TRANTEST;"^ -f simple_test.sql^ -n 3 PAUSE

Here's the output when both TRANTEST and the SQL Anywhere 16 engine were run on an Intel Core i7 laptop (8 processors):



Tip: Don't specify TRANTEST -p if you're only using one computer. Otherwise, you'll see nothing but endless "Waiting for other machines..." messages.

Tip: Press Ctrl+C to stop TRANTEST, and use a PAUSE command to keep the TRANTEST window from immediately disappearing when you to press Ctrl+C.

Step 8. Create a "heavy load" version of the custom procedure to be executed by TRANTEST.

You don't need 1000 procedures to make TRANTEST use 1000 connections, you can use a single procedure and have TRANTEST pass the thread number as a parameter:

CREATE PROCEDURE p ( thread_number VARCHAR ( 10 ) ) BEGIN INSERT t ( inserted_by ) VALUES ( thread_number ); COMMIT; END;

Step 9. Create the "heavy load" version of the TRANTEST -f script.

In the following file heavy_load.sql, TRANTEST uses the thread numbers 1 to 1000 to generate different parameter values rather than different procedure names:

call p ( '{thread}' )

Step 10. Run TRANTEST again.

In this heavy load test, the -m 10 switch adds a 10 millisecond delay between repetitions, and the -n 1000 switch requests 1000 connections to the target database:

trantest^ -a esql^ -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql; CON=TRANTEST;"^ -f heavy_load.sql^ -m 10^ -n 1000 PAUSE

Here's what the TRANTEST output looked like:



Here's what the test looked like in Foxhound:



Here are some observations:

• Throughput increased dramatically during the test as SQL Anywhere increased the Max Req (MultiProgrammingLevel property) from 8 to 38. TRANTEST shows throughput as T/sec, and Foxhound shows it as Req, Commits, Bytes (Req, Commit, BytesReceived and BytesSent properties).
  

• The sudden drop in #Trans from 24,627 to 6 in the TRANTEST output probably coincided with a checkpoint, but it's difficult to prove because TRANTEST doesn't show date and time.
  

• It's a bad idea to run everything on one computer (SQL Anywhere, TRANTEST and Foxhound), especially with 1000 busy connections. The Interval column shows that Foxhound was unable to gather data in a timely fashion (i.e., every 10 seconds), and the Sample Times column shows why: Foxhound needed up to 43.5 seconds to gather a single sample.
  

• A bad idea sometimes makes a good stress test, which is the whole point of this exercise :)
  

---