Database Load Test Using Jmeter

By Author: Fleek IT Solutions
Total Articles: 45
Comment this article

In real life, there are many situations when you have to connect to a database using Jmeter for example: load testing of your database, manipulating (insert, update, delete) data in database during your load test or just fetching some data from database that will be passed to subsequent samplers. To achieve this you would require following elements to be added to your test plan:

JDBC Connection configuration
JDBC Request sampler
JDBC Connection configuration
Creates a database connection as per supplied connection settings, this connection will be used by JDBC Request Sampler

Variable name: The name of the variable the connection is tied to. Multiple connections can be used in a test plan but variable name must be different for each connection, this helps JDBC Samplers to select the appropriate connection.

Max Number of Connections: Defines maximum number of connections allowed in the pool. 0 means that each thread will get its own pool with a single connection in it, i.e. the connections are not shared between threads. If you want to use shared pooling, then set the max count to the same ...
... as the number of threads to ensure threads don’t wait on each other

Different databases and JDBC drivers require different JDBC settings. The Database URL and JDBC Driver class are defined by the provider of the JDBC implementation. In this example we are connecting to MSSQL database. For other database connection settings, please refer to: http://jmeter.apache.org/usermanual/component_reference.html#JDBC_Connection_Configuration

Database URL: JDBC Connection string for the database. jdbc:sqlserver://;;DatabaseName=;

JDBC Driver class: Fully qualified name of driver class. com.microsoft.sqlserver.jdbc.SQLServerDriver

Username: Username to connect to database

Password: Password to connect to database

JDBC Request
JDBC sampler lets you send a JDBC Request (an SQL query) to a database as per your configured JDBC Connection.

Variable name: Name of the Jmeter variable that the connection pool is bound to. This must agree with the ‘Variable Name’ field of a JDBC Connection Configuration.

Query Type: Set this according to the statement type:

Select Statement
Update Statement – use this for Inserts and Deletes as well
Callable Statement
Prepared Select Statement
Prepared Update Statement – use this for Inserts and Deletes as well
Commit
Rollback
Autocommit (false)
Autocommit (true)
Edit – this should be a variable reference that evaluates to one of the above
SQL Query: Allow you to write the actual SQL queries for example:

select * from where id=
insert into (column1,column2,…) VALUES (value1,value2,…)
update set column1=value where =
Parameter values: Comma-separated list of parameter values

Parameter types: Comma-separated list of SQL parameter types (e.g. INTEGER, DATE, VARCHAR, DOUBLE)

Variable Names: Comma-separated list of variable names to hold values returned by Select statements, Prepared Select Statements or CallableStatement.

Result Variable Name: If specified, this will create an Object variable containing a list of row maps. Each map contains the column name as the key and the column data as the value

For more details please refer to: http://jmeter.apache.org/usermanual/component_reference.html#JDBC_Request

Create your JDBC test plan
Download required sql jdbc driver and put jar file in jmeter/lib directory
Add a JDBC config element “JDBC Connection Configuration” to your test plan and define your database connection settings (database url, driver class, username and password)
Add a JDBC request sampler in a thread group and define
Variable name which must match to variable name given in JDBC connection config.
Write your sql query and choose Query Type accordingly.