Benchmark MariaDB Queries Safely with mysqlslap

Someone in the standup asks whether the database will survive Black Friday, and you have no evidence either way. mysqlslap is MariaDB's built-in load generator: it hammers a schema with simulated clients and reports timings, and it can print the SQL it would run before it connects to anything.

Allow about 15 minutes, plus the time needed to watch the test server. You need the mariadb-client package, a MariaDB account that can run the statements in your test, and a disposable or explicitly approved schema. The examples do not need root. Do not benchmark a production service without an agreed traffic limit and maintenance window: the command creates load and can create or remove database objects.

1. Confirm the client and its version

The mysqlslap name is retained for compatibility. On this host it is a symlink to the MariaDB slap client, and the package version is 10.11.14:

$ command -v mysqlslap
/usr/bin/mysqlslap
$ mysqlslap --version
mysqlslap  Ver 1.0 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64)

Your distribution may show a different version. Keep this output with benchmark results, because option defaults and generated SQL are implementation details that can vary between releases.

Checkpoint: if command -v finds nothing, install the client package through your normal system administration process. Do not switch to an unverified binary simply because it has the same name.

2. Make a harmless plan before connecting

Start with --only-print. It prevents database connections and prints what the test would do:

$ mysqlslap --only-print \
    --auto-generate-sql \
    --concurrency=2 \
    --iterations=3 \
    --number-int-cols=2 \
    --number-char-cols=1
CREATE SCHEMA `mysqlslap`;
CREATE TABLE ...

The exact generated SQL is version-dependent and may be longer than this excerpt. A successful dry run proves only that the client accepted the options and could describe the work. It does not authenticate, measure latency or prove that the target account can create tables.

Do not confuse --only-print with a transaction rollback. It avoids connecting altogether. Once you remove it, the test can create a schema, create tables, insert rows and drop objects.

3. Choose the connection explicitly

Use a test account and identify the host and schema. This example selects TCP on port 3306 and asks for the password interactively:

$ mysqlslap --host=db-test.example.net --port=3306 \
    --user=bench_runner --password \
    --create-schema=bench_tmp \
    --auto-generate-sql \
    --concurrency=2 --iterations=3

--password with no value prompts on the terminal. Avoid --password=SECRET: command-line arguments can be exposed through process inspection, shell history or job logs. A MariaDB option file can supply client defaults, but inspect which files are read first. This client reads /etc/my.cnf, /etc/mysql/my.cnf and ~/.my.cnf, plus the groups [mysqlslap], [mariadb-slap], [client], [client-server] and [client-mariadb]. Use --no-defaults as the first argument when you need to exclude them.

The command's three stages are worth keeping in mind: one connection prepares the schema and data, several connections run the load, and one connection cleans up. If your test account cannot create the requested objects, the run can fail before any timing is useful.

4. Run a bounded generated-SQL test

Set both the client count and the iteration count deliberately. Here, two simulated clients perform three test iterations:

$ mysqlslap --host=db-test.example.net --port=3306 \
    --user=bench_runner --password \
    --create-schema=bench_tmp \
    --auto-generate-sql \
    --auto-generate-sql-load-type=read \
    --number-int-cols=2 --number-char-cols=1 \
    --concurrency=2 --iterations=3 --verbose
Benchmark
        Average number of seconds to run all queries: 0.012 seconds
        Minimum number of seconds to run all queries: 0.010 seconds
        Maximum number of seconds to run all queries: 0.015 seconds
        Number of clients running queries: 2
        Average number of queries per client: 3

The wording and figures are illustrative: timing output depends on the server and workload. Read the actual output rather than comparing a copied number. --concurrency controls simulated clients. --iterations controls how many times the test runs. --auto-generate-sql-load-type accepts read, write, key, update or mixed; the documented default is mixed.

Do not infer an exact total from every option combination. --number-of-queries is approximate, and its count includes statements split by the selected delimiter. Record the complete command, client version, server version, schema state and resource conditions with the result.

5. Use your own SQL when generated work is not representative

For an application-shaped read test, provide a create statement and query statement directly:

$ mysqlslap --host=db-test.example.net --user=bench_runner --password \
    --create-schema=bench_tmp \
    --create='CREATE TABLE bench_items (id INT PRIMARY KEY, label VARCHAR(80))' \
    --query='SELECT id, label FROM bench_items WHERE id = 42' \
    --concurrency=4 --iterations=25 --delimiter=';'

For files, --create and --query accept a file name or a string. Without another delimiter, a file uses one statement per line. Set --delimiter=';' when statements span lines or several statements share a line. The client does not understand comments in these files, so remove comments before testing.

The example assumes the table and row exist when the query runs. If the create phase also inserts data, use a deliberately separated statement list and test it first with --only-print. Do not paste a production schema file into a load test until you have checked every statement.

6. Protect the cleanup boundary

By default, mysqlslap drops schema objects it created after the test. That is useful for a disposable schema and dangerous when the target is shared. The --no-drop option preserves created schema objects for inspection:

$ mysqlslap --host=db-test.example.net --user=bench_runner --password \
    --create-schema=bench_tmp \
    --auto-generate-sql --concurrency=1 --iterations=1 --no-drop

Warning: removing --no-drop is a destructive choice. Confirm the schema name before starting. If the test left objects behind, remove only the known test objects with an approved database change procedure, or drop the disposable schema after verifying that it contains no other data. There is no mysqlslap undo command for a dropped object.

7. Verify failures without adding more load

Capture the exit status immediately:

$ mysqlslap --only-print --auto-generate-sql --concurrency=1 --iterations=1
$ status=$?
$ printf 'mysqlslap status: %s\n' "$status"
mysqlslap status: 0

A non-zero status means the requested operation failed, but the useful cause is normally in the preceding error text. Check credentials, host, protocol, schema privileges, SQL syntax and the server log. Run mysqlslap --help to confirm local option spelling. If defaults are surprising, inspect them with mysqlslap --print-defaults, or repeat with --no-defaults first. Do not increase concurrency to diagnose an authentication or permission error.

Done means