Using the database tester to measure connection and query execution times

Introduction

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 dbtester.zip

Executing a runnable JAR

The tool is a runnable JAR file, which takes two arguments. The first argument is the db.properties 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 db.properties queries.txt

Configuring the db.properties file

The db.properties 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:

hostname=localhost

portNumber=5432

dbName=jasperserver

dbType=postgres

dbUsername=postgres

dbPassword=password

Here is the list of parameters you can use:

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:

 SELECT COUNT(1) FROM JITENANT SELECT COUNT(1) FROM JIUSER 

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 db.properties 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 db.properties 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:

Measure and Test

• Do Performance Testing with JMeter
• Measure Ad Hoc Performance

Ad Hoc

• Set query limits on the "Ad Hoc Options" page
• Modify the Ad Hoc caching values if your data does not update constantly

Tomcat

• Compress HTTP responses at the Tomcat level
• Increase the maximum thread count at the Tomcat level
• Set up a cache filter for Tomcat or JBoss
• Disable automatic deployment on production servers using Tomcat

Database

• Index columns in the database to speed up domain-based reports
• Set the fetch size in the jasperreports.properties file to modify the number of rows to fetch

JasperReports Configuration Items

• Modify Virtualizer Settings for Very Large Reports
• Modify the jasperreports.properties file to disable multi-line data processing

dbtester.zip