Database performance testing using JMeter

In this post we will see how we can build a test plan and run load test on databases using JMeter. We will consider MySQL database for our discussion. In order to test the SQL Query performance using JMeter, first you should be aware of the below concepts and terminologies.

A . JDBC Driver :

JDBC driver is a software component enabling a Java application to interact with a database. JDBC drivers are analogous to ODBC drivers, ADO.NET data providers, and OLE DB providers. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. 

 

B. JDBC Request Sampler in JMeter : 

This sampler lets you send an SQL query to the database using JDBC server . 

C . JDBC Connection Configuration ( Config Element ) :

This helps us to create a database connection from the supplied JDBC Connection settings .

Scenario : Load Testing an SQL query on MySQL Database

In order to establish a connection from JMeter to MySQL database , you should download the proper JDBC Driver that connects to your database .You can download the JDBC Driver for MySQL from the below link

http://dev.mysql.com/downloads/connector/j/

Note : The downloaded "jar" file should be placed under the JMeter/lib directory .
The classes from jars are automatically found by the Jmeter in the following directories:

JMETER_HOME/lib - used for utility jars

Note: Only .jar files are found by JMeter not the .zip files.

Now you can start the JMeter and you are ready to build the DB test plan.

Step 1 :

Add the thread group and provide the appropriate name to it . In this case we have given it as DB Test plan .

Step 2 : 

Add the JDBC Connection configuration element and provide the appropriate JDBC connection settings relevant to your database.

You have to carefully fill the fields listed below and for the remaining you can use the default values.( In this case we have used the MySQL database installed in the local machine and the database name is given as “Test”).
Variable name bound to pool. This needs to uniquely identify the configuration. It is used by the JDBC Sampler to identify the configuration to be used.

• Database URL: jdbc:mysql://localhost:3306/Test 
• JDBC Driver class: com.mysql.jdbc.Driver 
• Username: Suman 
• Password: **** 

Step 3 :

Add the JDBC Sampler which lets you send the SQL request to the server.

We have given an ‘insert’ statement and the query type as ‘Update statement’.

Note : The same variable name which we have used ( in the Variable name field ) in the JDBC Config elment should be used in the sampler as well.


Step 4 :

Add the listeners required as per your requirement.

Now the test plan is ready for you to perform SQL query performance testing on MySQL.

Frequent error you may get while execution and their resolution:

1. Response code: 080010

Response message: java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/sakila

This error may occur if there is any mistake in the Database URL field. This may occur even if there is a “leading space” in the Database URL Field. Another reason would be, the database driver is not present in the JMeter Lib directory .

2. Response code: 42000 1049

Response message: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'sakil'

This message is self-exploratory; if the database name is incorrect or the database is not created you may encounter the above error.

3. Response code: 28000 1045

Response message: java.sql.SQLException: Access denied for user 'sumana'@'localhost' (using password: YES)

Even the above error message is self-exploratory; If the database access credentials are not correct then you may receive this error.

Hope this post is useful .