How to Automatically Pull Data From a Database for Your Performance Test

Your performance test script needs test data to run your scenarios. But instead of hard-coding values in your test script, you can benefit from dynamically populating parameters with data from a database such as MySQL or MongoDB. That way you have a clean separation between (potentially sensitive) data and logic, and it's easier to maintain your test scripts.

Another benefit you get when running a performance test — for example, a load test — by automatically pulling data sets from a database, is that each iteration can use a unique set of data, which will result in a test that resembles more realistic traffic to your system under test (e.g. a Website or API).

In this post, I'll show you how to use data from your MySQL database in an Apache JMeter™ or BlazeMeter test with Jenkins CI. (Previously, we described how to integrate GitHub, Jenkins CI, and BlazeMeter test). We will configure the database to export query results to a CSV file, and then we will use this CSV file in the BlazeMeter test, every time the test is run. You can also use this method for your JMeter test.

Step 1. Install Jenkins CI (for Ubuntu)

Step 2. Install MySQL Server (if you don't have it) and MySQL workbench (optional — if you prefer working with a GUI)

Step 3. Configure MySQL

After installing the MySQL server and workbench, we need to make some changes to the server's configuration. In the Workbench, looking for where the MySQL configuration file is located:

Open this file and look for the "secure-file-priv" value. You can read more about this value here. In this value, configure the folder for the database to write files to. I set secure-file-priv="C:\\tmp\\". In this folder, I'll store my *.sql script and the result.csv file that this script will create.

Restart your MySQL server.

Step 4. Create a database and write a script.

I created a "test" database and a "users" table in this database:

In the "C:\\tmp\\" folder I have a script "script.sql":

SELECT name,pass INTO OUTFILE 'C:\\tmp\\res.csv'
FIELDS TERMINATED BY ','  
LINES TERMINATED BY '\n'
FROM test.users;

This script turns the DB into a CSV file.

Let's check it in the command-line:

The execution syntax to connect to the database is 

mysql -u <user_name> -p<password_without_space_after_p> <database_name> < "<path_to_sql_script>"

Step 5. Configure Jenkins.

Now switch to Jenkins CI, create a FreeStyle Job and add a Windows batch / Shell build step:

For Windows I created the following script:

del "C:\tmp\res.csv"
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql" -u artem -partem test < "C:\tmp\script.sql"
mkdir %BUILD_NUMBER%
copy "C:\tmp\res.csv" "%WORKSPACE%\%BUILD_NUMBER%\data.csv"
del "C:\tmp\res.csv"

What this script does:

• Removes the result file if it exists in the database, because MySQL cannot overwrite it
• Executes the SQL script that will write the result output from the MySQL DB to a CSV file
• Creates a folder with the name =BUILD_NUMBER in the Jenkins workspace
• Copies the result file to JENKINS_WORKSPACE\BUILD_NUMBER folder and name it.
• Removes the result file from C:\\tmp

After the execution of this script, we will have a file named data.csv in JENKINS_WORKSPACE\BUILD_NUMBER folder.

You can take this file and run it with JMeter through Jenkins and view results in the Jenkins performance plugin.

Or, you can run it in your BlazeMeter test. Configure the BlazeMeter Build step that will use this data.csv file in the BlazeMeter test:

When I run my test I see that the SQL query finished successfully and that my data.csv was uploaded to a BlazeMeter test:

You can automate your performance test in Jenkins. Every time it runs, this scenario will generate a new CSV file, overwrite this file in the BlazeMeter test, and then run the BlazeMeter test.