HANA DB test plan using JMeter

[Former Member]

When working on HANA projects, we often run into questions related to HANA DB performance; How many concurrent users can the current appliance handled; What will be the performance during the peak time when we have many concurrent users writing and reading from the same table;  How many rows can i insert per second; What is the best way to partition my table , and so ..

JMeter is a very good tool to get started on some of those performance test plans.I should mention that the performance test numbers are very subjective and depends on many factors such as, the number of columns in a table, the data type of the columns in the table, number of partitions in a table, network band width between your client and the HANA DB, number of rows in the table ( This might impact the delta merge time ) and many such factors.

In this document i would like to take though a step-by-step process of creating a test plans for two very common methods of accessing HANA; JDBC and ODATA.

In the first part we will take a look at the steps that are required to build the test plan using JDBC, and in the second part we will look at steps required to create test plan using ODATA.

DB test plan using JDBC:

Preconditions:

    Before we get started, you need the below two things to build a test plan using JDBC.

1. Download JMeter binary from Apache JMeter - Download Apache JMeter
2. You need HANA JDBC JAR file. You can find the JAR( ngdbc.jar) file in HANA client.

    Unzip the JMeter installation file that you have download from the JMeter site and copy the ngdbc.jar file that you have copied from the HANA client to the  ./lib folder. JMeter will automatically pickup any .jar files placed in the lib folder ( Only .jar files are picked up).

Building the test plan:   

    We are going to follow the below steps to create a test plan.

1. Create a Thread group.
2. Create JDBC connection.
3. Define variables ( Optional, Need only if you need some random numbers or counters)
4. Create JDBC request.
5. Add a listener.

1. Create a Thread group

             First step for any test plan is to create a thread group.

            Thread group configuration will control the number of threads JMeter will use to execute the test.

             Test plan -> Add -> Threads -> Thread Group.

            

           Number of threads is 10 and the Loop count is 1000, so the number of queries will be 10 * 1000 .

           Ramp-up period is 1, so all the threads will start at the same time, there is no delay between the threads.

   2.  Create a JDBC Connection

         Next step is to create Database connection pool configuration. We specify the connection pool configuration

         and database connection details in this section.

         Right click on the Thread group, Add -> Config element -> JDBC connection config  

          

         Database URL: jdbc:sap://<Hostname>:3<Instance number>15

         JDBC Driver class: com.mysql.jdbc.Driver

         Username: DB User

         Password:  DB User password

         Variable Name is very important, it uniquely identifies the configuration.

         We will use the same Variable name in the JDBC sampler.

         You can change the Max number of connections and the Pool Timeout based on your requirement.

     3. Define variables ( Optional)

            This step is optional. You can define Counter or variables optionally.

            You can use these later in your SQL statements to dynamically send the data.

             Right click on the Thread group, Add -> Config element -> Counter

         

             We are going to use this config element as a sequence when we insert the data.

             REFERENCE NAME is particularly important as we will be referring the counter by this name.

     4. Create JDBC request

             Next step is to add a JDBC sampler. The sampler would let you send JDBC request to a database.

             Right click on the Thread group, Add -> Sampler -> JDBC Request .

            

             The variable name is the same name we have used for the variable name field in the JDBC connection.

             In the above case we are inserting data into the ORDERS table.

             We are using ORDER_ID as a counter to increment the value for the ORDER_ID column.  

      5. Add a listener

             Listeners helps to view, save and read test results. There are lot of listeners available, based on the desired

            output you can choose the one that fits the needs. There are listeners that saves the results to file, and there

            are listeners that show a tabular view of the results and so on.We can add one or more lister.

             In our case we are going to add Summary report, results tree and results in a table.

             Right click on the Thread group, Add -> Listener -> Summary result.

             Summary Report:

           

                From the above report you can see that that there were total 10,000 requests that were made and the

                average time for each request is 21 ms.

             Results In Table:

            

                 The above report gives the time it took for each request and the status of the request.

            Results Tree:

            

                From the above report you can see the request that was sent and you can see the response data as well.

In the next part we can take a look at steps needed to do performance tests for ODATA services.

Part 2:

HANA DB test plan using JMeter - Part 2- ODATA

References:

  Apache JMeter - Apache JMeter&amp;trade;

Performance Testing Your #SAP #HANA Server