When I’m working with MySQL, I care not just about whether a single query is slow, but how much total load the server can handle before performance starts to drop.

Tools like mysqlslap help by generating synthetic workloads to simulate multiple clients and measure the server’s Queries Per Second (QPS).

In this post, I’ll walk through a script I used to benchmark MySQL server performance with mysqlslap, and discuss when it makes sense to use it over more advanced tools like sysbench. I originally used this script in a blog post published at ReadySet: Vertical Scaling of MySQL vs. Horizontal Scaling with ReadySet.

Why Benchmark a MySQL Server?

Benchmarking gives a rough idea of how a MySQL server performs under load, but it’s not a guaranteed measure of full capacity.

It can help you estimate:

How many concurrent connections the server can handle before performance starts to drop.
The approximate QPS limit before queries get slower.
The potential gains from adding more CPU, memory, or better hardware.
How much impact tuning settings like innodb_buffer_pool_size have on overall performance.

Keep in mind: these tests provide guidance, but real-world workloads are often more complex, and results may vary outside of synthetic benchmarks.

Benchmark Setup

For this example, we used:

Database: Employees sample dataset (~300,000 rows)
Server: MySQL 8.0 running on a regular VM
Benchmarking Tool: mysqlslap
Automation: A Bash script running 10 different query files at once

The idea is simple:

Simulate 200 concurrent client connections
Split that load evenly across 10 different queries
Measure total QPS the server can deliver

How the Benchmark Works

The Bash script provided:

Starts one mysqlslap process per query
Runs each query with a controlled concurrency and iteration count
Collects how long it takes to execute
Calculates approximate Queries Per Second (QPS) for each query
Sums up the QPS to estimate server throughput

Example run:

Running queries with 20 concurrency each...

================== QPS Results ==================
q1        :    150.12 QPS
q2        :    145.77 QPS
q3        :    160.21 QPS
q4        :      7.45 QPS
...
-------------------------------------------------
Total estimated QPS (sum): 782.00
=================================================

Running queries with 20 concurrency each...

================== QPS Results ==================

q1 : 150.12 QPS

q2 : 145.77 QPS

q3 : 160.21 QPS

q4 : 7.45 QPS

...

-------------------------------------------------

Total estimated QPS (sum): 782.00

=================================================

This 782 QPS number is a rough idea of what the server can handle under this type of load.

What mysqlslap Tells You (and What It Doesn’t)

mysqlslap gives you a basic sense of:

Overall server throughput (QPS)
How well your server handles concurrency
Whether a bigger instance or more tuning is needed

But mysqlslap does not:

Simulate complex application behavior (mixed reads/writes, transactions)
Show latency percentiles (like 95th, 99th percentiles)
Analyze detailed performance metrics like CPU or IO usage

It’s a basic benchmarking tool, but sometimes that’s all you need to get useful numbers quickly.

mysqlslap vs sysbench

Here’s a straight comparison:

Feature	mysqlslap	sysbench
Goal	Quick QPS measurement	Detailed load testing
Setup	Very easy, no config needed	Needs installation and Lua scripts
Types of tests	Mostly simple queries	Read, write, OLTP, custom transactions
Metrics	Total time, QPS	QPS, latency stats, errors, TPS
Best Use Case	Quick server capacity check	Full performance and stress testing

Summary:

Use mysqlslap if you just want a quick and rough estimate of server performance.
Use sysbench if you want more detailed analysis or simulate real workloads.

Practical Tips When Using mysqlslap

Warm up the server before benchmarking. Run some queries first so caches are populated.
Use realistic queries. If your real application does joins or aggregations, test with those.
Measure under different settings. Try different innodb_buffer_pool_size, max_connections, and so on.
Monitor server metrics separately. Use top , htop, or MySQL’s SHOW GLOBAL STATUS during tests to catch CPU or IO bottlenecks. Tools like pt-stalk can help you to gather data while the load is going on.

Final Thoughts

Benchmarking helps me understand what kind of load a MySQL server can realistically handle.

mysqlslap won’t give me deep profiling or real-world modeling, but it’s quick, scriptable, and good enough for estimating QPS and spotting performance trends.

It tells me:

How much concurrent load the server handles before slowing down
If tuning changes had any real effect
When it might be time to scale up or scale out

If I need to go deeper, I bring in sysbench. But for many use cases, a quick test with mysqlslap gives me what I need to move forward.

Example Bash Script to Benchmark

Here’s the basic script used to run 10 queries in parallel:

#!/bin/bash
# mysqlslap benchmark script for MySQL server

USER="sysbench"
PASS="sysbench"
HOST="172.25.31.248"
PORT="3306"
DB="employees"

QUERIES=("q1.sql" "q2.sql" "q3.sql" "q4.sql" "q5.sql" "q6.sql" "q7.sql" "q8.sql" "q9.sql" "q10.sql")
TOTAL_CONCURRENCY=200
CONCURRENCY_PER_QUERY=$(( TOTAL_CONCURRENCY / ${#QUERIES[@]} ))
ITERATIONS=10
TMP_DIR="./mysqlslap_results"
mkdir -p "$TMP_DIR"

for QUERY_FILE in "${QUERIES[@]}"; do
  BASENAME=$(basename "$QUERY_FILE" .sql)
  OUTPUT_FILE="$TMP_DIR/${BASENAME}.out"
  mysqlslap -u "$USER" -p"$PASS" -h "$HOST" -P "$PORT" \
    --concurrency="$CONCURRENCY_PER_QUERY" \
    --iterations="$ITERATIONS" \
    --query="$(cat "$QUERY_FILE")" \
    --delimiter=";" \
    --create-schema="$DB" &gt; "$OUTPUT_FILE" 2&gt;&amp;1 &amp;
done

wait

sed -i '/Using a password on the command line interface can be insecure/d' "$TMP_DIR"/*.out

echo "================== QPS Results =================="

TOTAL_QPS=0
for OUTPUT in "$TMP_DIR"/*.out; do
  QUERY_NAME=$(basename "$OUTPUT" .out)
  AVG_TIME=$(grep -i 'Average number of seconds to run all queries' "$OUTPUT" | awk -F ':' '{print $2}' | xargs)
  if [[ -n "$AVG_TIME" ]]; then
    QPS=$(awk "BEGIN {printf \"%.2f\", ($CONCURRENCY_PER_QUERY * $ITERATIONS) / $AVG_TIME}")
    printf "%-10s : %10s QPS\n" "$QUERY_NAME" "$QPS"
    TOTAL_QPS=$(awk "BEGIN {print $TOTAL_QPS + $QPS}")
  else
    echo "$QUERY_NAME: QPS not available or query failed"
  fi
done

echo "-------------------------------------------------"
echo "Total estimated QPS (sum): $TOTAL_QPS"
echo "================================================="

#!/bin/bash

# mysqlslap benchmark script for MySQL server

USER="sysbench"

PASS="sysbench"

HOST="172.25.31.248"

PORT="3306"

DB="employees"

QUERIES=("q1.sql" "q2.sql" "q3.sql" "q4.sql" "q5.sql" "q6.sql" "q7.sql" "q8.sql" "q9.sql" "q10.sql")

TOTAL_CONCURRENCY=200

CONCURRENCY_PER_QUERY=$(( TOTAL_CONCURRENCY / ${#QUERIES[@]} ))

ITERATIONS=10

TMP_DIR="./mysqlslap_results"

mkdir -p "$TMP_DIR"

for QUERY_FILE in "${QUERIES[@]}"; do

BASENAME=$(basename "$QUERY_FILE" .sql)

OUTPUT_FILE="$TMP_DIR/${BASENAME}.out"

mysqlslap -u "$USER" -p"$PASS" -h "$HOST" -P "$PORT" \

--concurrency="$CONCURRENCY_PER_QUERY" \

--iterations="$ITERATIONS" \

--query="$(cat "$QUERY_FILE")" \

--delimiter=";" \

--create-schema="$DB" > "$OUTPUT_FILE" 2>&1 &

done

wait

sed -i '/Using a password on the command line interface can be insecure/d' "$TMP_DIR"/*.out

echo "================== QPS Results =================="

TOTAL_QPS=0

for OUTPUT in "$TMP_DIR"/*.out; do

QUERY_NAME=$(basename "$OUTPUT" .out)

AVG_TIME=$(grep -i 'Average number of seconds to run all queries' "$OUTPUT" | awk -F ':' '{print $2}' | xargs)

if [[ -n "$AVG_TIME" ]]; then

QPS=$(awk "BEGIN {printf \"%.2f\", ($CONCURRENCY_PER_QUERY * $ITERATIONS) / $AVG_TIME}")

printf "%-10s : %10s QPS\n" "$QUERY_NAME" "$QPS"

TOTAL_QPS=$(awk "BEGIN {print $TOTAL_QPS + $QPS}")

else

echo "$QUERY_NAME: QPS not available or query failed"

done

echo "-------------------------------------------------"

echo "Total estimated QPS (sum): $TOTAL_QPS"

echo "================================================="

I hope you liked it, and see you in the next post!

Written by

Vinicius Grippa

Writes this blog. Mostly about databases. Boring on purpose.

More about me →

Benchmarking MySQL Server With mysqlslap: Estimating Capacity, Not Absolute Limits

Why Benchmark a MySQL Server?

Benchmark Setup

How the Benchmark Works

What mysqlslap Tells You (and What It Doesn’t)

mysqlslap vs sysbench

Practical Tips When Using mysqlslap

Final Thoughts

Example Bash Script to Benchmark

The floor is yours.

Add a comment Cancel

Why Benchmark a MySQL Server?

Benchmark Setup

How the Benchmark Works

What mysqlslap Tells You (and What It Doesn’t)

mysqlslap vs sysbench

Practical Tips When Using mysqlslap

Final Thoughts

Example Bash Script to Benchmark

Add a comment Cancel

Related posts in MySQL

Big O for MySQL: Why the Same Query Gets Slow at Scale

Using PHP and Readyset for the First Time with MySQL

Determining the Correct innodb_buffer_pool_chunk_size

A quiet, technical email about databases.