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 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:
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:
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
- Ad Hoc
- JasperReports Configuration Items