Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-193

JMeter connection

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASolution 5.0, EXASOL 6.0.0
    • Fix Version/s: None
    • Labels:
      None
    • Environment:
      You have an EXASolution database and want to test its concurrency behaviour for a new project, in a proof of concept or any other reason.
    • Solution:
      Hide

      Preface

      This article has been written for JMeter 3.2.

      Introduction

      A very common tool for emulating expected concurrency behaviour on IT infrastructure is JMeter (http://jmeter.apache.org). It allows you to define your test plans in a GUI and to run them from GUI or command line.

      In this tutorial, we would like to show you how to configure it in order to use it with an EXASOL database.

      In practice, you might face the following challenges:

      1. You want all connections to be established before the real benchmark in order to avoid delays deriving from login procedure.
      2. You need to define how the parallel queries should be orchestrated, and bundle them in thread groups.
      3. You want to run your test plans from command line without a GUI.
      4. You want to reuse a query multiple times in different thread groups or test plans, but you want to avoid to copy this query multiple times as it should be easy to change a query globally.
      5. Some of your result sets are really huge, and this results in Java "out of memory" errors.

      Prerequisites

      Create a test Plan (take template.jmx as template)


      The test plan template above shows a simple setup for concurrency tests. Besides a JDBC configuration element, we see two thread groups, which consist of SQL query definitions and various report listeners.


      It is possible to set some user defined variables in the test plan. To define them, properties can be read. For example, the following

      Name: Value:
      concurrency ${__P(concurrency,10)}

      means: If the property "concurrency" exists, set the variable "concurrency" to the value of that property. If the property "concurrency" is not defined, set the variable "concurrency" to the value "10". Please note that properties and values are different things, and the identical names in this case are just for convenience.
      The idea is to set a property on the command line, e.g.

      jmeter -n -t template.jmx -Jconcurrency=8 -Jiterations=4
      

      starts JMeter in non-gui mode and runs the test plan "template.jmx" with the properties "concurrency" and "iterations" set to its respective values.
      Test plans can be developed using the JMeter GUI, but the final test run to measure performance with should only be started via the command line.

      If you create more than one Thread Group, the switch "Run Thread Groups consecutively" becomes important. If checked, they run consecutively - if not checked, thread groups run in parallel. Beware that "setUp Thread Group" elements are always executed before the "Thread Group" elements, regardless of the switch.

      Finally, you need to define the location of "exajdbc.jar". You can download the JDBC driver from our website: https://www.exasol.com/portal/display/DOWNLOAD/

      Next, we need to configure the JDBC connection pool.
      The variable name is important, as you need to reference to it in a later step ("exa_benchmark" in this case).
      The max number of connections is set to the value of the "concurrency" variable. You can leave the defaults for "Max Wait" and "Time Between Eviction Runs". Ideally, you set "Auto Commit" to "True". As EXASOL only supports the option "SERIALIZABLE" for "Transaction Isolation", you can leave this setting to "DEFAULT". A connection validation is normally not necessary.
      For the database url, you configure the connection string with "jdbc:exa:"prefix. If you work with a cluster, use a connection string with an ip range that covers all the active and hot-stand-by database nodes, e.g.: "10.0.0.1..5:8563" for a cluster consisting of 5 database nodes in total (and respective ip addresses).
      Any further JDBC properties can be added using semicolons. Especially setting the clientname and/or clientversion can make sense to identify queries of a specific test run later on using the auditing tables. In the example above, the "test_id" variable is used, which is generated from the startup time of JMeter in date/time format and the start time of the test in unix timestamp format - for example "20170926180920_1506443755443" (JMeter started on September 26, 2017 at 18:09:20). Note that the test start time is in unix timestamp format and can be different from the JMeter startup time if you run multiple tests from the GUI without closing JMeter in between.
      Set the "JDBC driver class" to the value shown above and set user/password to appropriate values (default is sys/exasol).

      Show
      Preface This article has been written for JMeter 3.2. Introduction A very common tool for emulating expected concurrency behaviour on IT infrastructure is JMeter ( http://jmeter.apache.org ). It allows you to define your test plans in a GUI and to run them from GUI or command line. In this tutorial, we would like to show you how to configure it in order to use it with an EXASOL database. In practice, you might face the following challenges: You want all connections to be established before the real benchmark in order to avoid delays deriving from login procedure. You need to define how the parallel queries should be orchestrated, and bundle them in thread groups. You want to run your test plans from command line without a GUI. You want to reuse a query multiple times in different thread groups or test plans, but you want to avoid to copy this query multiple times as it should be easy to change a query globally. Some of your result sets are really huge, and this results in Java "out of memory" errors. Prerequisites We assume that you are familiar with JMeter. If not, please have a look at http://jmeter.apache.org/usermanual , especially chapter 7: http://jmeter.apache.org/usermanual/build-db-test-plan.html Optional - set system path environment variable to run JMeter from everywhere on the command line: e.g. C:\Users\fs\Downloads\apache-jmeter-3.2\bin Create a test Plan (take template.jmx as template) The test plan template above shows a simple setup for concurrency tests. Besides a JDBC configuration element, we see two thread groups, which consist of SQL query definitions and various report listeners. It is possible to set some user defined variables in the test plan. To define them, properties can be read. For example, the following Name: Value: concurrency ${__P(concurrency,10)} means: If the property "concurrency" exists, set the variable "concurrency" to the value of that property. If the property "concurrency" is not defined, set the variable "concurrency" to the value "10". Please note that properties and values are different things, and the identical names in this case are just for convenience. The idea is to set a property on the command line, e.g. jmeter -n -t template.jmx -Jconcurrency=8 -Jiterations=4 starts JMeter in non-gui mode and runs the test plan "template.jmx" with the properties "concurrency" and "iterations" set to its respective values. Test plans can be developed using the JMeter GUI, but the final test run to measure performance with should only be started via the command line. If you create more than one Thread Group, the switch "Run Thread Groups consecutively" becomes important. If checked, they run consecutively - if not checked, thread groups run in parallel. Beware that "setUp Thread Group" elements are always executed before the "Thread Group" elements, regardless of the switch. Finally, you need to define the location of "exajdbc.jar". You can download the JDBC driver from our website: https://www.exasol.com/portal/display/DOWNLOAD/ Next, we need to configure the JDBC connection pool. The variable name is important, as you need to reference to it in a later step ("exa_benchmark" in this case). The max number of connections is set to the value of the "concurrency" variable. You can leave the defaults for "Max Wait" and "Time Between Eviction Runs". Ideally, you set "Auto Commit" to "True". As EXASOL only supports the option "SERIALIZABLE" for "Transaction Isolation", you can leave this setting to "DEFAULT". A connection validation is normally not necessary. For the database url, you configure the connection string with "jdbc:exa:"prefix. If you work with a cluster, use a connection string with an ip range that covers all the active and hot-stand-by database nodes, e.g.: "10.0.0.1..5:8563" for a cluster consisting of 5 database nodes in total (and respective ip addresses). Any further JDBC properties can be added using semicolons. Especially setting the clientname and/or clientversion can make sense to identify queries of a specific test run later on using the auditing tables. In the example above, the "test_id" variable is used, which is generated from the startup time of JMeter in date/time format and the start time of the test in unix timestamp format - for example "20170926180920_1506443755443" (JMeter started on September 26, 2017 at 18:09:20). Note that the test start time is in unix timestamp format and can be different from the JMeter startup time if you run multiple tests from the GUI without closing JMeter in between. Set the "JDBC driver class" to the value shown above and set user/password to appropriate values (default is sys/exasol).
    • Category 1:
      3rd Party Tools
    • Category 2:
      Clients, Interfaces & Drivers - JDBC

      Attachments

      1. 0_Test Plan Window.png
        0_Test Plan Window.png
        45 kB
      2. 0_Test Plan Window - cut.png
        0_Test Plan Window - cut.png
        8 kB
      3. 1_Test Plan Node.png
        1_Test Plan Node.png
        21 kB
      4. 10_Generate Summary Results.png
        10_Generate Summary Results.png
        5 kB
      5. 11_WorkBench.png
        11_WorkBench.png
        5 kB
      6. 2_JDBC Connection Node.png
        2_JDBC Connection Node.png
        21 kB
      7. 3_Setup Thread Group Node.png
        3_Setup Thread Group Node.png
        15 kB
      8. 4_JDBC Request Setup Node.png
        4_JDBC Request Setup Node.png
        16 kB
      9. 5_Thread Group Node.png
        5_Thread Group Node.png
        16 kB
      10. 6_JDBC Request Run Node.png
        6_JDBC Request Run Node.png
        18 kB
      11. 7_View Results Tree.png
        7_View Results Tree.png
        28 kB
      12. 7_View Results Tree - request.png
        7_View Results Tree - request.png
        31 kB
      13. 7_View Results Tree - response data.png
        7_View Results Tree - response data.png
        31 kB
      14. 7_View Results Tree - sampler result.png
        7_View Results Tree - sampler result.png
        42 kB
      15. 8_Summary Report.png
        8_Summary Report.png
        22 kB
      16. 9_Aggregate Report.png
        9_Aggregate Report.png
        24 kB
      17. template.jmx
        12 kB

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: