Run massively scalable performance tests on web, mobile, and APIs

Request a Demo
Aug. 23rd, 2018

How to Automatically Pull Data from a DB 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 blog 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:

 

data driven testing with blazemeter

 

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 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:

 

getting data from a database for your performance test

 

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:

 

db, csv file, jmeter, blazemeter

 

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.

 

Running the CSV File in JMeter

 

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

 

Running the CSV File in BlazeMeter

 

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 (read more about it here):

 

running your data from your load testing in jenkins

 

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

 

performance testing automation

 

Automating Your Performance Test in Jenkins

 

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.

 

Try out running your performance tests in BlazeMeter by putting your URL in the box below, and your test will start in minutes! Or, request a live demo from one of our performance engineers.

     
arrow Please enter a valid URL

Interested in writing for our Blog? Send us a pitch!