Azure Database for MySQL Blog

9 MIN READ

Load Test Emulation for Azure Database for MySQL - Flexible Server using mysqlslap

Deepak8817

Microsoft

Mar 05, 2024

Guidance for using mysqlslap to simulate client load and measure performance

Introduction

Mysqlslap is a diagnostic program included with the MySQL server binary that you can use to emulate client load for a MySQL server and report the timing of each stage. Mysqlslap works as if multiple clients are accessing the server simultaneously.

In this post, I’ll show you how to use mysqlslap to perform load test emulation for Azure Database for MySQL - Flexible Server, a fully managed and scalable MySQL service on Azure. I’ll install mysqlslap, configure the connection parameters, run different types of tests, and then analyze the results.

Prerequisites

Before you begin, ensure that the following prerequisites are in place:

An instance of Azure Database for MySQL – Flexible Server. To create one, follow this guidance in this tutorial: Quickstart: Create with Azure portal - Azure Database for MySQL - Flexible Server.
A MySQL client tool that supports mysqlslap - download them from MySQL :: MySQL Community Downloads.
A test database and table on your Azure Database for MySQL Flexible Server instance. Use the following queries to create a test database and table with one million dummy records:

mysql> CREATE DATABASE loadtestdb;

mysql> use loadtestdb;

mysql> CREATE TABLE loadtesttable (
ID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255),
Age INT,
Salary DECIMAL(10, 2),
Department VARCHAR(50),
City VARCHAR(100),
Country VARCHAR(100)
);

mysql> INSERT INTO loadtesttable (Name, Age, Salary, Department, City, Country)
SELECT
    CONCAT(CHAR(FLOOR(RAND() * 26) + 65), 'Person', n),
    FLOOR(RAND() * 100) + 18,
    ROUND(RAND() * 10000000, 2),
    CASE WHEN RAND() < 0.5 THEN 'IT' ELSE 'Sales' END,
    CASE WHEN RAND() < 0.5 THEN 'New York' ELSE 'Los Angeles' END,
    CASE WHEN RAND() < 0.5 THEN 'USA' ELSE 'Canada' END
FROM (
    SELECT
        a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + 1 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) f
) AS Numbers;

Installing mysqlslap

Instructions for installing mysqlslap on a computer running Windows or Linux appear in the following sections.

Note: If the Azure Cloud Shell is installed, mysqlslap is included automatically.

Windows

To install mysqlslap on a computer running Windows, download the MySQL Installer, run it, and then follow the wizard. Mysqlslap will be installed in the folder C:\Program Files\MySQL\MySQL Server 8.1\bin (assuming the installation is on C:\). Alternately, you can download MySQL ZIP Archive and extract mysqlslap from \mysql-8.0.36-winx64.zip\mysql-8.0.36-winx64\bin.

Linux

To install mysqlslap on a computer running Linux, install the MySQL client package (which includes mysqlslap) by running the following command:

sudo apt update
sudo apt install mysql-client
mysqlslap --version

Configuring the connection parameters

To connect to the Azure Database for MySQL - Flexible Server instance, run the following command:

mysqlslap --host=myserver.mysql.database.azure.com --user=myuser --password=mypassword --port=3306 --ssl-mode=REQUIRED

With this command, you can specify the following parameters:

--host: The host name or IP address of your server. You can find it on the Azure portal under the Overview section of your server.
--port: The port number of your server. The default is 3306.
--user: The username to log in to your server. You can use the admin user that you created when you provisioned your server, or any other user that has access to the test database.
--password: The password to log in to your server. You will be prompted to enter it when you run mysqlslap.
--ssl-mode: The SSL mode to use for the connection. You can use REQUIRED, VERIFY_CA, or VERIFY_IDENTITY. The default is REQUIRED. For more information about SSL modes, see MySQL 8.0 : Configuring MySQL to Use Encrypted Connections.

Running different types of tests

The mysqlslap test process includes three stages:

Create schema, table, and optionally any stored programs or data to use for the test. This stage uses a single client connection.
Run the load test. This stage can use many client connections.
Clean up (disconnect, drop table if specified). This stage uses a single client connection.

Use mysqlslap to run different types of tests, such as concurrency tests, stress tests, or benchmark tests. To specify the test parameters, consider the following options:

Option Name	Description
--concurrency	Specifies the number of simultaneous client connections. You can provide a single value or a comma-separated list of values. For example, --concurrency=10 means 10 threads, and --concurrency=10,20,30 means three tests with 10, 20, and 30 threads respectively.
--iterations	Defines the number of times the benchmark test should be repeated. The default is 1.
--number-of-queries	The number of queries to run per thread. The default is 0, which means unlimited.
--query	Specifies the SQL query to be executed during the test. You can provide a single query or multiple queries. For example, *--query="SELECT FROM testtable"** means to run a simple SELECT query.
--create-schema	The name of the database to use for the test. The default is the mysqlslap database.
--create	The statement to create the test table. You can provide a single statement or multiple statements. For example, --create="CREATE TABLE testtable (id INT)" means to create a simple test table.
--delimiter	Use the --delimiter option to specify a different delimiter, which enables you to specify statements that span multiple lines or place multiple statements on a single line.
--auto-generate-sql	A flag to indicate whether to generate random queries for the test. The default is FALSE. If you set it to TRUE, you can use the following options to control the query generation.
--auto-generate-sql-add-autoincrement	A flag to indicate whether to add an AUTO_INCREMENT column to the test table. The default is FALSE.
--auto-generate-sql-execute-number	The number of queries to generate and execute per thread. The default is 10.
--auto-generate-sql-load-type	The type of queries to generate. You can use MIXED, UPDATE, WRITE, or READ. The default is MIXED.
--auto-generate-sql-unique-query-number	The number of unique queries to generate. The default is 10.
--auto-generate-sql-unique-write-number	The number of unique queries to generate for write load. The default is 10.

You can also use the --engine option to specify the storage engine to use for the test table. The default is InnoDB. For more information about mysqlslap options, see MySQL 8.0 Reference Manual :: 6.5.8 mysqlslap.

Before running a test with mysqlslap, please ensure to use an empty user database or the default mysqlslap database when using --create or --auto-generate-sql-* option. If the --create or --auto-generate-sql-* option is given, mysqlslap drops the schema at the end of the test run. This means that any existing data in the database will be lost.

Some examples showing how to run different types of tests using mysqlslap follow.

To run a concurrency test with 10, 20, and 30 threads, each executing 100 queries 10 times, run the following command:

mysqlslap --host=server-name.mysql.database.azure.com --port=3306 --user=user-name --password --ssl-mode=REQUIRED --concurrency=10,20,30 --iterations=10 --number-of-queries=100 --query="SELECT ID, Name, Age, Salary, Department, City, Country FROM loadtesttable WHERE Name like 'A%' AND Age BETWEEN 30 AND 40;" --create-schema=loadtestdb --verbose

To run a stress test with 50 threads, each executing the query 20 times, run the following command:

mysqlslap --host=server-name.mysql.database.azure.com --port=3306 --user=user-name --password --ssl-mode=REQUIRED --concurrency=50 --iterations=25 --query="SELECT ID, Name, Age, Salary, Department, City, Country FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS R FROM loadtesttable) AS ranked WHERE R <= 5;" --create-schema=loadtestdb --verbose

To run a benchmark test with 10 threads, each executing 1000 randomly generated queries with a mixed load type, run the following command:

mysqlslap --host=server-name.mysql.database.azure.com --port=3306 --user=user-name --password --ssl-mode=REQUIRED --concurrency=10 --iterations=1 --number-of-queries=1000 --auto-generate-sql --auto-generate-sql-load-type=MIXED --verbose

Analyzing the results

After running a test, mysqlslap displays the results as output., which includes the:

Average number of seconds to run all queries: The average time it took to run all the queries per thread.
Minimum number of seconds to run all queries: The minimum time it took to run all the queries per thread.
Maximum number of seconds to run all queries: The maximum time it took to run all the queries per thread.
Number of clients running queries: The number of threads that simulated the client load.
Average number of queries per client: The average number of queries that each thread executed.

You can use the –silent option to suppress the verbose output and display only the results. You can also use the –csv option to format the results as comma-separated values, which can easily be imported into a spreadsheet or a database for further analysis.

An example of the results from a concurrency test with 10, 20, and 30 threads, each executing 100 queries 10 times, follows:

Benchmark
        Average number of seconds to run all queries: 2.024 seconds
        Minimum number of seconds to run all queries: 2.003 seconds
        Maximum number of seconds to run all queries: 2.041 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10

Benchmark
        Average number of seconds to run all queries: 2.070 seconds
        Minimum number of seconds to run all queries: 2.022 seconds
        Maximum number of seconds to run all queries: 2.228 seconds
        Number of clients running queries: 20
        Average number of queries per client: 5

Benchmark
        Average number of seconds to run all queries: 1.885 seconds
        Minimum number of seconds to run all queries: 1.849 seconds
        Maximum number of seconds to run all queries: 2.021 seconds
        Number of clients running queries: 30
        Average number of queries per client: 3

You can use the results to compare the performance of an Azure Database for MySQL – Flexible Server instance under different load scenarios, and to identify any potential bottlenecks or issues. You can also use the results to tune the server configuration, such as the number of connections, the buffer pool size, the query cache size, or the index statistics.

Best practices

When you’re using mysqlslap to perform load test emulation for an Azure Database for MySQL - Flexible Server instance, consider the following best practices.

Before using the mysqlslap utility on your production environment, test the mysqlslap utility thoroughly in your lowest environment against a test database.
Define benchmarking scenarios that closely resemble your production environment.
Use realistic datasets and queries representative of your actual workload.
Adjust benchmarking parameters such as concurrency, iterations, and query complexity to match your workload characteristics.
Test different combinations of parameters to understand their impact on performance.
Analyze results carefully and consider multiple metrics for performance evaluation.
Monitor system resources (CPU, memory, disk I/O) during benchmark tests to identify any resource bottlenecks.
Repeat benchmark tests multiple times to validate results and ensure consistency.

Demonstration

For a demonstration of this functionality, see the following video:

Conclusion

In this post, I’ve described how to use mysqlslap to perform load test emulation for an Azure Database for MySQL - Flexible Server instance. I’ve described how to install mysqlslap, configure the connection parameters, run different types of tests, and analyze the results. Be sure to use mysqlslap to simulate client load and measure the performance of your MySQL flexible server, as well as to optimize your server configuration and query performance.

If you have any questions about the detail provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!

References

For more information about using mysqlslap, in the MySQL documentation, see https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

Updated Apr 10, 2024

Version 2.0

Deepak8817

Microsoft

Joined December 21, 2022

View Profile

Azure Database for MySQL Blog

Follow this blog board to get notified when there's new activity