Details

    • Solution:
      Hide

      Background

      This is deprecated and will be replaced by https://github.com/exasol/exameter. We provide this documentation for archival purposes and to be useful to those running older versions. There will not be any modifications, enhancements or updates to the "Affected Version/s" textbox.

      Preface

      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. This article has been written for JMeter 3.2.

      Introduction

      A very common tool for emulating expected concurrency behavior 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 the 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 the 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 the 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

      How to make  JMeter connection

      Step 1. 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.

      Step 2. Set up 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.

      Step 3. Determine if you need to create more than one Thread Group

      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.

      Step 4. Define the exajdbc.jar location

      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/

      Step 5. Configure the JDBC connection pool

      The variable name is important, as you need to refer 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-standby database nodes, e.g.: "10.0.0.1..5:8563" for a cluster consisting of 5 database nodes in total (and respective ip addresses).

      Step 6. Add any other JDBC properties

      Any further JDBC properties can be added using semicolons. Especially setting the client name and/or client version 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
      Background This is deprecated and will be replaced by  https://github.com/exasol/exameter . We provide this documentation for archival purposes and to be useful to those running older versions. There will not be any modifications, enhancements or updates to the "Affected Version/s" textbox. Preface 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. This article has been written for JMeter 3.2. Introduction A very common tool for emulating expected concurrency behavior 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 the 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 the 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 the 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 How to make  JMeter connection Step 1. 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. Step 2. Set up 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. Step 3. Determine if you need to create more than one Thread Group 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. Step 4. Define the exajdbc.jar location 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/ Step 5. Configure the JDBC connection pool The variable name is important, as you need to refer 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-standby database nodes, e.g.: "10.0.0.1..5:8563" for a cluster consisting of 5 database nodes in total (and respective ip addresses). Step 6. Add any other JDBC properties Any further JDBC properties can be added using semicolons. Especially setting the client name and/or client version 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

        Issue Links

        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: