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 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 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`:
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:
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):
When I run my test I see that the SQL query finished successfully and that my data.csv was uploaded to a BlazeMeter test:
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.