Using the database tester to measure connection and query execution times


The database tester is a simple command line tool that lets you test Oracle, Postgres, and MySQL databases for their speed in executing queries. You can configure what queries to execute and what database test against.

You get value in using this tool when you test it from different locations in a network, which will allow you to detect any latency issues that might exist.

You can download the zip file containing the tool from here

Executing a runnable JAR

The tool is a runnable JAR file, which takes two arguments. The first argument is the file, which contains the information the tool needs to connect to the database. The second argument is the list of queries that the tool will run and time. The tool will return data in a comma-delimited format, which you can save to a CSV file for more analysis. To use it, run it from the command line like this:

 % java -jar dbtester.jar queries.txt

Configuring the file

The file contains a set of key-value pairs, which contains the information the tool needs to connect to the database. For example, it might look like this:


Here is the list of parameters you can use:

hostname The hostname or IP address where the database lives
portNumber The port that the database listens on. Usually it's 3306 for MySQL, 1521 for Oracle, and 5432 for Postgres
dbName The Name of database. For example, "sugarcrm", "foodmart", "jasperserver", etc.
dbType Right now, the values available are "oracle", "postgres", or "mysql"
dbUsername The username for the database account
dbPassword The password for the database account
delimiter The JDBC URL and service name delimiter. By default, it's the ":" character, but when using Oracle service names, the delimiter is "/"
showJdbcUrl This will output the JDBC URL if its value is set to "true"; by default it's "false"
printQueries This will output the queries it's testing if its value is set to "true"; by default it's "false"

Configuring the queries.txt file

The queries.txt file contains the list of queries that the tool will test. It expects to have one query per line, so if you have a multi-line queries, make sure you've joined them up as one line. Here's an example of a queries.txt file:


The key is choosing good queries. You can do this by enabling the JDBC query logger in JRS, and then pulling out a few interesting queries from the log files. This will give you good insight into database execution times.

Executing the tool

After you have configured the two files, you run it from the command line:

 $ java -jar dbtester.jar queries.txt Query Executed,No. of Times Executed,Elapsed Time (only query execution),Total Elapsed Time (including connection time) 1,50,36,135 2,50,19,38 1,500,183,203 2,500,159,178 1,2000,731,752 2,2000,538,559

As you can see, the output is in the form of comma-delimited data, which you can save to a CSV file, which will allow you to do more analysis with a a desktop tool like Excel.

 $ java -jar dbtester.jar queries.txt > test_results.csv

The above command saves the results to a file, which you can then view with Excel, and do the analysis you are used to doing.

Interpreting the results

The key is to compare one set of results with another - compare the results between running the test in one machine versus another, for example, to measure the effects of network latency.

For example, if you ran the tool from the host that's running the database, you would expect it to be a lot faster than from the host that's running the application server. However, would you expect it to be five times slower? If it was that much slower, then it's time to start evaluating network issues.

Other Performance Tips: