mysqlslap is a load-testing and benchmarking tool that ships with MySQL. It measures how your database responds under simulated client load by running SQL workloads repeatedly and reporting aggregate timing: average, minimum, and maximum seconds to complete the workload, plus the number of concurrent clients. Understanding these metrics helps you establish baselines, find saturation points, and validate changes (such as new indexes or configuration) before they affect production.

mysqlslap is bundled with MySQL 8.0 and does not require separate installation. This tutorial covers auto-generated load tests for hardware baselines, custom SQL queries for application-specific benchmarks, concurrency simulation to find scaling limits, engine-specific benchmarking (InnoDB vs. MyISAM), and testing against remote or managed MySQL instances such as DigitalOcean Managed MySQL.

Warning: Do not run benchmarks against a production database. Use a dedicated test server or a managed database instance isolated from production traffic.

• An Ubuntu server (any supported LTS version)
• MySQL 8.0 or later installed, or access to a DigitalOcean Managed MySQL instance
• A non-root MySQL user with sufficient privileges for the databases you will test
• At least 2 GB RAM recommended for the sample database exercises in this tutorial

• mysqlslap is bundled with MySQL 8.0 and requires no separate installation.
• Use --auto-generate-sql for hardware baseline tests; use --query with a .sql file for application-specific benchmarks.
• --concurrency controls simulated parallel clients; --iterations controls test repetition for statistical reliability.
• Always benchmark against a non-production copy of your database.
• The query cache was removed in MySQL 8.0; remaining caching comes from the InnoDB buffer pool and the OS page cache, so expect faster “warm cache” runs and, when needed, flush the buffer pool or restart MySQL between runs to measure cold-cache performance.
• After identifying slow queries, use EXPLAIN ANALYZE to diagnose the execution plan and validate index changes with a follow-up mysqlslap run.
• mysqlslap supports remote and managed MySQL databases via --host, --port, and --ssl-ca flags.
• For full OLTP workload simulation, pair mysqlslap with sysbench.

mysqlslap uses a client-simulation model: it spawns a configurable number of parallel client threads, each running the same SQL workload (either auto-generated or supplied by you). It runs the workload one or more times (iterations), then reports aggregate timing across all clients and runs. This lets you see how average and worst-case latency change as you increase concurrency or vary the query mix.

Understanding what mysqlslap does to your databases during a test run matters before you run any command. When you use --auto-generate-sql, mysqlslap creates its own temporary database called mysqlslap, builds a simple table inside it, runs the workload, then drops the entire database when the test ends. Nothing in your existing databases is touched. When you use --create-schema with your own queries, mysqlslap connects to that existing database and runs your queries against it as a normal client would. It does not create or drop the database, and it does not modify schema or data unless your query does. This distinction determines which flag combination is appropriate for each testing scenario.

Reference options:

Confirm that mysqlslap is present after a standard MySQL 8.0 install:

mysqlslap --version

Expected output format:

mysqlslap  Ver 8.0.xx Distrib 8.0.xx, for Linux (x86_64)

If mysqlslap is missing, it is provided by the mysql-client package. Install it with:

sudo apt update
sudo apt install mysql-client

Run mysqlslap --version again to verify.

Note: In MySQL 8.0, mysqlslap connects using the caching_sha2_password authentication plugin by default. If your MySQL server requires TLS (common for managed services), connect with --ssl-mode=REQUIRED and, if needed, --ssl-ca=/path/to/ca.pem. If you see authentication errors, ensure you are using a client that supports caching_sha2_password or, only as a last resort, change the MySQL user to use mysql_native_password. For non‑TLS connections using caching_sha2_password, you may also need to add --get-server-public-key so the client can retrieve the server’s public key.

Install MySQL Server on Ubuntu, enable it, and run the security script:

sudo apt update
sudo apt install mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation

Connect to MySQL as the root user:

sudo mysql

You will see the MySQL prompt:

Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>

Create a non-root test user using MySQL 8.0 syntax. Replace your_password with a strong password. At the MySQL prompt, run:

CREATE USER 'benchuser'@'localhost' IDENTIFIED BY 'your_password';

-- Optional: dedicated schema for mysqlslap auto-generated tests
CREATE DATABASE IF NOT EXISTS mysqlslap_benchmark;

-- Read-only access to sample schemas used in this tutorial
GRANT SELECT ON employees.* TO 'benchuser'@'localhost';
GRANT SELECT ON employees_backup.* TO 'benchuser'@'localhost';

-- Broader privileges only on the dedicated benchmark schema
GRANT CREATE, DROP, SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER
    ON mysqlslap_benchmark.* TO 'benchuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

You can now use benchuser for all subsequent commands in this tutorial.

Download and load the MySQL employees sample database:

mkdir ~/mysqlslap_tutorial && cd ~/mysqlslap_tutorial
wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip
sudo apt install unzip
unzip master.zip
cd test_db-master
mysql -u benchuser -p < employees.sql

When prompted, enter the password for benchuser. Verify the import:

SHOW DATABASES;
USE employees;
SHOW TABLES;
SELECT COUNT(*) FROM employees;

Expected output for COUNT(*):

+----------+
| count(*) |
+----------+
|   300024 |
+----------+

Create a backup database for safe benchmarking:

mysqldump -u benchuser -p employees > ~/mysqlslap_tutorial/employees_backup.sql
mysql -u benchuser -p -e "CREATE DATABASE employees_backup;"
mysql -u benchuser -p employees_backup < ~/mysqlslap_tutorial/employees_backup.sql

Enter the password when prompted for each command. You now have employees and employees_backup available for tests.

The --auto-generate-sql option creates a temporary database named mysqlslap, runs INSERT and SELECT operations against an auto-generated table, then drops the database. Use it for hardware-level baseline testing, not for testing your application’s specific queries. This is the right starting point before testing your own queries. If your server cannot handle auto-generated load at a given concurrency level, it will not handle real application queries any better. Establish this baseline first, then move to custom query testing.

Run a single-client baseline:

mysqlslap --user=benchuser --password --host=localhost \
  --auto-generate-sql --verbose

Enter the password when prompted. Expected output:

Benchmark
        Average number of seconds to run all queries: 0.009 seconds
        Minimum number of seconds to run all queries: 0.009 seconds
        Maximum number of seconds to run all queries: 0.009 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

Increase concurrency to 50 clients and run 10 iterations:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --auto-generate-sql --verbose

Expected output:

Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

Notice that average time has increased from 0.009 seconds (single client) to 0.197 seconds under 50 concurrent clients. The maximum spike to 0.399 seconds shows contention beginning to appear even on this lightweight auto-generated workload.

Widen the simulated table to 5 integer columns and 20 varchar columns, with 100 iterations:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=100 \
  --number-int-cols=5 --number-char-cols=20 \
  --auto-generate-sql --verbose

Expected output:

Benchmark
        Average number of seconds to run all queries: 0.521 seconds
        Minimum number of seconds to run all queries: 0.389 seconds
        Maximum number of seconds to run all queries: 1.642 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

The wider table schema increases average time further and widens the gap between minimum and maximum, reflecting the higher I/O cost of reading more columns per row under concurrent load.

The mysqlslap database is created at test start and dropped at test end. From a separate MySQL session you can run SHOW DATABASES; while a test is running to see the temporary database.

Interpreting the output:

Use custom queries when you want to benchmark real application workloads, not just raw hardware limits. The distinction matters because auto-generated queries run against a two-column table with no joins. Your application’s queries likely involve multiple tables, complex joins, and ORDER BY clauses. A server that scores well on auto-generated tests can still perform poorly on specific query patterns. Inline single-query and multi-query examples, plus a SQL file workflow, are shown below.

Inline single query:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --create-schema=employees \
  --query="SELECT * FROM dept_emp;" \
  --verbose

Expected output:

Benchmark
        Average number of seconds to run all queries: 18.486 seconds
        Minimum number of seconds to run all queries: 15.590 seconds
        Maximum number of seconds to run all queries: 28.381 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

The dept_emp table contains over 300,000 rows. At 50 concurrent clients each running a full table scan with no WHERE clause, an average of 18 seconds is expected on a 2 GB instance. The spike to 28 seconds on the worst run reflects resource contention (CPU/I/O and buffer pool pressure) and increased queueing under load as clients compete to scan the table simultaneously.

Multiple inline queries with --delimiter:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=20 --iterations=10 \
  --create-schema=employees \
  --query="SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;" \
  --delimiter=";" --verbose

Expected output:

Benchmark
        Average number of seconds to run all queries: 23.800 seconds
        Minimum number of seconds to run all queries: 22.751 seconds
        Maximum number of seconds to run all queries: 26.788 seconds
        Number of clients running queries: 20
        Average number of queries per client: 3

Notice that “Average number of queries per client” is now 3, one for each statement in the query string. The average time has increased from 18.486 seconds (single query, 50 clients) to 23.800 seconds despite using fewer clients, because each client is now running three sequential table scans per iteration instead of one.

SQL file (preferred for complex queries):

Create the file:

cat > ~/mysqlslap_tutorial/select_query.sql << 'EOF'
SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments
EOF

Run the benchmark using the file and distribute 1000 total queries across 20 clients (50 per client):

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=20 --number-of-queries=1000 \
  --create-schema=employees \
  --query="$HOME/mysqlslap_tutorial/select_query.sql" \
  --delimiter=";" --verbose --iterations=2 --debug-info

The --number-of-queries option distributes 1000 total query executions across 20 clients, meaning each client runs approximately 50 queries. Example output including the --debug-info block:

Benchmark
        Average number of seconds to run all queries: 217.151 seconds
        Minimum number of seconds to run all queries: 213.368 seconds
        Maximum number of seconds to run all queries: 220.934 seconds
        Number of clients running queries: 20
        Average number of queries per client: 50

User time 58.16, System time 18.31
Maximum resident set size 909008, Integral resident set size 0
Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 102785, Involuntary context switches 43

Interpret each debug field explicitly:

• User time / System time: CPU time consumed in user space and kernel space respectively. A high System time relative to User time suggests the workload is I/O-bound rather than computation-bound.
• Maximum resident set size: Peak memory usage in kilobytes. If this approaches your available RAM, the working set is not fitting in memory and disk I/O is likely inflating your query times.
• Non-physical pagefaults: Memory pages that had to be fetched from the page cache (not disk). A large number here means MySQL is pulling data that was not already in the InnoDB buffer pool.
• Physical pagefaults: Pages fetched from disk. Any non-zero value here means the buffer pool is undersized for this workload.
• Involuntary context switches: The OS preempted the process because another process needed CPU time. A high count here indicates CPU contention, either from other processes on the server or from the workload exceeding CPU capacity.

Note: The MySQL query cache was deprecated in 5.7 and removed entirely in 8.0. Options like SQL_NO_CACHE only affected the old query cache and do not prevent data from being served out of the InnoDB buffer pool or the OS page cache in MySQL 8.0+. Similarly, innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now are designed to save and restore a warm buffer pool across restarts, not to clear it between benchmark runs. For realistic steady-state benchmarks, it is usually best to measure performance with a warmed buffer pool (after running the workload a few times). If you truly need cold-cache measurements, you must restart MySQL between runs (for example, with sudo systemctl restart mysql) and, where appropriate and safe for your environment, consider also dropping the OS page cache. Both approaches are operationally disruptive and should be used only on non-production systems.

Test several concurrency levels in sequence to find where latency grows sharply (saturation point). The goal is not to find a number that your server can handle, it is to find the number where performance degrades non-linearly. That threshold is your practical concurrency limit for that query under the current schema and configuration. Everything you do after this point (adding indexes, tuning buffer pool, rewriting the query) should move that threshold higher. Run:

for CONCURRENCY in 10 25 50 100; do
  echo "--- Concurrency: $CONCURRENCY ---"
  mysqlslap --user=benchuser --password --host=localhost \
    --concurrency=$CONCURRENCY --iterations=5 \
    --create-schema=employees \
    --query="SELECT e.first_name, e.last_name, d.dept_name FROM employees e INNER JOIN dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no ORDER BY e.last_name" \
    --verbose 2>&1 | grep -E "Average|Minimum|Maximum|clients"
done

Example comparative output (your numbers will vary):

When average time grows roughly linearly with concurrency, scaling is consistent. When it jumps at a certain concurrency level, you have likely hit a bottleneck: lock contention, I/O saturation, or connection pool limits.

The --engine flag tells mysqlslap which storage engine to use for the auto-generated test table. This is useful when you are evaluating whether switching engines would improve performance for a specific workload type.

Run the InnoDB test first:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --auto-generate-sql \
  --engine=InnoDB \
  --verbose

Example output:

Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

Run the MyISAM test with the same parameters:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --auto-generate-sql \
  --engine=MyISAM \
  --verbose

Example output:

Benchmark
        Average number of seconds to run all queries: 0.163 seconds
        Minimum number of seconds to run all queries: 0.130 seconds
        Maximum number of seconds to run all queries: 0.289 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

MyISAM may show lower times than InnoDB on pure SELECT workloads because it does not maintain transaction logs or support row-level locking overhead. However, this changes significantly under concurrent writes. MyISAM uses table-level locking: when one client writes, all other clients querying the same table must wait. InnoDB uses row-level locking, so concurrent writes to different rows do not block each other.

Note: For read-heavy workloads with no concurrent writes, the difference between engines may be small. For any workload that mixes reads and writes, InnoDB will consistently outperform MyISAM at higher concurrency levels. MyISAM is still available in MySQL 8.0 but is not recommended for new applications.

This section walks through a production-safe workflow: capture real queries from a production server, then replay them against a test copy of the database.

Workflow overview:

1. Create a backup of the production database.
2. Restore the backup to a test environment.
3. Enable the general query log on the production server.
4. Trigger the workload you want to test.
5. Disable query logging.
6. Extract target queries from the log.
7. Run mysqlslap against the test database using the extracted queries.
8. Analyze results and apply optimizations.
9. Re-run mysqlslap to validate improvements.

Create a backup of the production database and restore it into a dedicated test database. Run the following on your test server (or the same server if you are using a separate test schema):

mysqldump -u benchuser -p employees > ~/mysqlslap_tutorial/employees_backup.sql

Create the test database and restore the backup into it:

mysql -u benchuser -p -e "CREATE DATABASE employees_backup;"
mysql -u benchuser -p employees_backup < ~/mysqlslap_tutorial/employees_backup.sql

Verify the restore completed:

USE employees_backup;
SHOW TABLES;
SELECT COUNT(*) FROM employees;

Expected output:

+----------+
| count(*) |
+----------+
|   300024 |
+----------+

All subsequent benchmark commands in this section run against employees_backup, not the original employees database.

On the production server, enable the general query log only for the short capture window:

SET GLOBAL general_log = 1;
SET GLOBAL general_log_file = '/var/lib/mysql/capture_queries.log';

Run the workload you want to capture. Example complex query:

USE employees;
SELECT SQL_NO_CACHE e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
INNER JOIN departments d ON de.dept_no = d.dept_no
INNER JOIN titles t ON e.emp_no = t.emp_no
ORDER BY e.first_name, e.last_name, d.dept_name, t.from_date;

Disable logging as soon as the workload finishes:

SET GLOBAL general_log = 0;

Extract the query from the log:

sudo grep "SELECT" /var/lib/mysql/capture_queries.log | tail -5

Save the query to a .sql file (no semicolon at end, no line breaks in the query):

cat > ~/mysqlslap_tutorial/captured_query.sql << 'EOF'
SELECT SQL_NO_CACHE e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY e.first_name, e.last_name, d.dept_name, t.from_date
EOF

Run the benchmark against the backup database:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=10 --iterations=2 \
  --create-schema=employees_backup \
  --query="$HOME/mysqlslap_tutorial/captured_query.sql" \
  --verbose

Example baseline output:

Benchmark
        Average number of seconds to run all queries: 68.692 seconds
        Minimum number of seconds to run all queries: 59.301 seconds
        Maximum number of seconds to run all queries: 78.084 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

Apply index optimization on the test database only:

USE employees_backup;
CREATE INDEX employees_empno ON employees(emp_no);
CREATE INDEX dept_emp_empno ON dept_emp(emp_no);
CREATE INDEX titles_empno ON titles(emp_no);

Re-run the same mysqlslap command. Example output after adding indexes:

Benchmark
        Average number of seconds to run all queries: 55.869 seconds
        Minimum number of seconds to run all queries: 55.706 seconds
        Maximum number of seconds to run all queries: 56.033 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

The roughly 19% reduction in average query time (68.7s to 55.9s) demonstrates the value of indexing high-cardinality JOIN columns. Validate improvements on the test database before applying to production. Index additions have write-overhead trade-offs that should be measured under mixed read/write workloads.

mysqlslap can benchmark remote and managed MySQL instances. Use --host, --port, and, for managed databases that require TLS, --ssl-ca and --ssl-mode.

Retrieve the host, port, user, and SSL CA certificate path from the DigitalOcean control panel for your managed database.

To download the CA certificate, log in to the DigitalOcean control panel, navigate to Databases, select your MySQL cluster, and click the Connection Details tab. Under Download the CA certificate, click Download CA Certificate. Save the file to your server, for example at /etc/ssl/certs/do-mysql-ca.crt:

scp ~/Downloads/ca-certificate.crt your_server_user@your_server_ip:/etc/ssl/certs/do-mysql-ca.crt

Or copy the certificate contents directly on the server if you are already logged in:

sudo nano /etc/ssl/certs/do-mysql-ca.crt

Paste the certificate contents, save, and exit. Then use the full path in the --ssl-ca flag.

Then run:

mysqlslap --user=doadmin --password \
  --host=your-managed-db-host.db.ondigitalocean.com \
  --port=25060 \
  --ssl-ca=/etc/ssl/certs/do-mysql-ca.crt \
  --ssl-mode=REQUIRED \
  --concurrency=25 --iterations=5 \
  --auto-generate-sql --verbose

Replace your-managed-db-host.db.ondigitalocean.com and the port and user values with your actual connection details. The --ssl-ca path matches where you saved the certificate in the setup steps above. DigitalOcean Managed MySQL requires SSL: always include --ssl-mode=REQUIRED and a valid --ssl-ca path.

Note: When benchmarking managed databases, network latency between your client and the managed instance is included in mysqlslap’s timings. For results that reflect application conditions, run mysqlslap from a Droplet in the same region and VPC as the managed database. Avoid running benchmarks from outside DigitalOcean’s network if you want latency-isolated numbers.

Use the following as a quick reference for interpreting output and deciding what to do next:

Worked example

Suppose you ran the concurrency loop from Step 5 and got these results:

From 10 to 25 clients, average time roughly doubles (4.2s to 9.1s). That is consistent with linear scaling: each additional client adds proportional load with no significant contention. From 25 to 50 clients, average time increases by a factor of 2.5 instead of 2. That is the beginning of non-linear growth. From 50 to 100 clients, average time jumps by a factor of 2.5 again and the maximum spikes to 78.1 seconds against an average of 55.8, a gap of over 22 seconds. That gap between average and maximum under high concurrency is the most important signal: it indicates that some clients are waiting significantly longer than others, which is characteristic of lock contention or connection queue buildup.

The action here is not to immediately increase hardware. First run:

SHOW ENGINE INNODB STATUS\G

Look for the TRANSACTIONS section. If you see transactions in a LOCK WAIT state, lock contention is the bottleneck. Next check max_connections:

SHOW VARIABLES LIKE 'max_connections';

If your concurrency ceiling is near max_connections, connections are being queued or refused. Then:

1. Run EXPLAIN ANALYZE on the slow query to look for full table scans (type: ALL).
2. Add indexes on columns in JOIN, WHERE, and ORDER BY clauses.
3. Re-run the mysqlslap loop to validate the improvement.
4. For deeper profiling, see How To Use MySQL Query Profiling and How To Optimize Queries and Tables in MySQL and MariaDB.

Use mysqlslap for fast, scriptable query-level benchmarks that fit into existing MySQL workflows. Use sysbench when you need full OLTP simulation (mixed reads, writes, and transactions at scale). In CI/CD, mysqlslap’s simple CLI makes it easy to script baseline regression tests: for example, fail a build if average query time exceeds a threshold.

No output or “Lost connection to MySQL server during query”
The server may be overloaded. Lower --concurrency and --iterations and retry. If it still fails, use a larger instance or reduce workload size.

Error 1044: Access denied
The MySQL user lacks privileges. Grant ALL PRIVILEGES on the test schema or at least SELECT, INSERT, CREATE, and DROP.

Error 2003: Can’t connect
Check --host and --port. Confirm MySQL is listening with sudo ss -tlnp | grep 3306.

MySQL 8.0 auth plugin errors
Verify which auth plugin the user is configured for:

SELECT user, plugin FROM mysql.user WHERE user = 'benchuser';

Expected output if correctly configured:

+-----------+-----------------------+
| user      | plugin                |
+-----------+-----------------------+
| benchuser | caching_sha2_password |
+-----------+-----------------------+

If the plugin column shows a different value, update it:

ALTER USER 'benchuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_password';
FLUSH PRIVILEGES;

For legacy clients that do not support caching_sha2_password, mysql_native_password is still available but deprecated in MySQL 8.4 and should not be used for new setups.

RESET QUERY CACHE fails
The query cache was removed in MySQL 8.0. Statements like RESET QUERY CACHE and hints such as SQL_NO_CACHE are no longer applicable and do not disable other forms of caching (InnoDB buffer pool or OS file cache). To benchmark with mysqlslap on MySQL 8.x:

• For warm-cache performance, run the same test multiple times and use results from later runs once timings stabilize.
• For cold-cache performance, restart MySQL (and, if needed, the host) or wait until the buffer pool has been evicted, then run the test once after restart.
• When comparing results, always note whether the cache was warm or cold so you interpret differences correctly. SSL errors against managed instances
  Verify --ssl-ca points to the correct CA file and that --ssl-mode=REQUIRED is set.

Q: What is mysqlslap and what does it measure?
mysqlslap is a load-testing tool bundled with MySQL. It runs SQL workloads under a configurable number of simulated clients and reports average, minimum, and maximum execution time and the number of clients. You use it to measure query throughput and how performance changes with concurrency.

Q: Is mysqlslap included in MySQL 8.0 and MySQL 8.2?
Yes. mysqlslap is part of the MySQL client utilities in 8.0 and 8.2. On Ubuntu it is installed with mysql-server or mysql-client; no separate package is required.

Q: How do I test multiple levels of concurrency with mysqlslap?
Loop over different values of --concurrency and run mysqlslap for each (see Step 5). Compare average, minimum, and maximum seconds across runs to find where latency increases sharply.

Q: What is the difference between --iterations and --concurrency in mysqlslap?
--concurrency is how many simulated clients run the workload at once. --iterations is how many times the full test (all clients running the workload) is repeated. Higher iterations improve statistical reliability of the reported timings.

Q: Can mysqlslap test against a remote or managed MySQL database?
Yes. Use --host and --port. For managed MySQL (e.g., DigitalOcean) that requires SSL, add --ssl-ca and --ssl-mode=REQUIRED.

Q: How do I use a custom SQL query file with mysqlslap?
Pass the file path to --query. For multiple statements in one file, set --delimiter (e.g., ;). Example: --query=/path/to/file.sql --delimiter=";".

Q: What should I do if mysqlslap results show high average query time under concurrency?
Run EXPLAIN ANALYZE on the slow query, look for full table scans and missing indexes, and add indexes on JOIN/WHERE/ORDER BY columns. Re-run mysqlslap to validate. Check SHOW ENGINE INNODB STATUS and the slow query log for lock or I/O issues.

Q: How does mysqlslap compare to sysbench for MySQL performance testing?
mysqlslap is built into MySQL and is best for quick query-level and concurrency tests. sysbench is a separate install and is better for full OLTP workloads (mixed reads and writes, transactions). Use mysqlslap for fast iteration; use sysbench when you need realistic OLTP simulation.

Q: Why was RESET QUERY CACHE removed in MySQL 8.0 and how does this affect benchmarking?
The query cache was removed in MySQL 8.0 because it often caused contention and unpredictable performance. In 8.0 there is no query cache to clear, so RESET QUERY CACHE has no replacement, and using SELECT SQL_NO_CACHE no longer meaningfully changes caching behavior or affects the InnoDB buffer pool. Benchmark variance is now driven mostly by how “warm” the InnoDB buffer pool and OS filesystem cache are, plus concurrency effects. For more repeatable results, either run warm‑up iterations before timing, or explicitly restart the server or drop OS caches if you truly need cold‑cache tests.

mysqlslap gives you a fast, built-in way to measure MySQL query throughput and behavior under concurrent load. Use auto-generated SQL for hardware baselines and custom queries (or captured production queries) for application-specific tuning. Always run benchmarks against a non-production copy of your data. After identifying slow queries with mysqlslap, use How To Use MySQL Query Profiling and How To Optimize Queries and Tables in MySQL and MariaDB to refine execution plans and indexes. To manage users and permissions, see How To Create a New User and Grant Permissions in MySQL. For context on how MySQL compares to other relational databases, see SQLite vs MySQL vs PostgreSQL.