Jump to content
  • 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 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[/code]

    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:

    hostnameThe hostname or IP address where the database lives
    portNumberThe port that the database listens on. Usually it's 3306 for MySQL, 1521 for Oracle, and 5432 for Postgres
    dbNameThe Name of database. For example, "sugarcrm", "foodmart", "jasperserver", etc.
    dbTypeRight now, the values available are "oracle", "postgres", or "mysql"
    dbUsernameThe username for the database account
    dbPasswordThe password for the database account
    delimiterThe JDBC URL and service name delimiter. By default, it's the ":" character, but when using Oracle service names, the delimiter is "/"
    showJdbcUrlThis will output the JDBC URL if its value is set to "true"; by default it's "false"
    printQueriesThis 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 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 [/code]

    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 [/code]

    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:


    User Feedback

    Recommended Comments

    There are no comments to display.

    This is now closed for further comments

  • Create New...