Overview

The mysqlslap tool is a built-in MySQL benchmark utility that has been available since version 5.1.4. It simulates multiple concurrent clients accessing MySQL to execute stress tests, providing detailed performance reports on SQL execution. The tool is particularly useful for comparing different storage engines (such as MyISAM, InnoDB) under identical concurrent load conditions.

Official documentation: http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html

Common Command Options

--host=host_name, -h host_name        Hostname or IP address of MySQL server (default: localhost)
--user=user_name, -u user_name        Username for MySQL connection
--password[=password], -p[password]  Password for MySQL connection
--create-schema=name                   Target database/schema for testing
--query=name, -q                      Custom SQL script or statement for testing
--create=name                          SQL for creating test tables
--concurrency=N, -c N                 Number of concurrent clients (supports multiple values)
--iterations=N, -i N                  Number of test iterations for accuracy
--number-of-queries=N                 Total query count across all clients
--engine=engine_name, -e              Storage engine(s) to test
--auto-generate-sql, -a               Auto-generate test tables and data
--auto-generate-sql-load-type=type    Test type: read, write, key, update, mixed
--auto-generate-sql-add-auto-increment Add AUTO_INCREMENT to generated tables
--number-char-cols=N, -x N            Number of VARCHAR columns in auto-generated tables
--number-int-cols=N, -y N             Number of INT columns in auto-generated tables
--commit=N                            DML statements per transaction
--compress, -C                        Enable compression if supported
--only-print                          Display queries without execution
--detach=N                            Reconnect after N statements
--debug-info, -T                      Print memory and CPU statistics

Extanded Options Reference

--auto-generate-sql-execute-number=#        Total queries to execute
--auto-generate-sql-guid-primary            Add GUID primary key
--auto-generate-sql-load-type=name          Load type (mixed/update/write/key/read)
--auto-generate-sql-secondary-indexes=#    Number of secondary indexes
--auto-generate-sql-unique-query-number=#   Unique SELECT statements
--auto-generate-sql-unique-write-number=#   Unique INSERT statements
--auto-generate-sql-write-number=#          Write queries per thread
--create-schema=name                        Target database name
--csv=name                                  Output results in CSV format
--debug                                    Write debug log
--debug-check                              Print debug information after test
--default-auth=plugin                      Authentication plugin
--delimiter=str, -F                        Statement delimiter
--enable-cleartext-plugin                  Enable plaintext authentication
--login-path=name                          Login path configuration
--no-drop                                  Keep schema after test
--pipe                                    Use named pipes
--plugin-dir=path                          Plugin directory
--port=port_num, -P                        TCP port number
--post-query=value                         SQL to execute after test
--post-system=str                          System command after test
--pre-query=value                          SQL to execute before test
--pre-system=str                           System command before test
--protocol=type                            Connection protocol (tcp/socket/pipe)
--secure-auth                             Disable old password authentication
--silent, -s                              Silent mode
--socket=path, -S                         Socket file path
--ssl-ca=file_name                        SSL CA certificate

Testing Examples

Example 1: Basic Auto-Generated Test

Run 100 concurrent threads for 1 iteration, auto-generate mixed read/write load with AUTO_INCREMENT on InnoDB, executing 5000 total queries.

mysqlslap -h127.0.0.1 -uroot -pMyPassword123 --concurrency=100 --iterations=1 \
  --auto-generate-sql --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000

Sample output:

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.351 seconds
Minimum number of seconds to run all queries: 0.351 seconds
Maximum number of seconds to run all queries: 0.351 seconds
Number of clients running queries: 100
Average number of queries per client: 50

Result interpretation:

• Average number of seconds to run all queries: Mean execution time for all statements
• Minimum number of seconds: Fastest complete run
• Maximum number of seconds: Slowest complete run
• Number of clients running queries: Concurrent client count
• Average number of queries per client: Queries per clientt (calculated as total/number of clients)

Example 2: Multiple Concurrency Levels

Test with 100, 500, and 1000 concurrent clients, running 5000 total queries.

mysqlslap -h127.0.0.1 -uroot -pMyPassword123 --concurrency=100,500,1000 \
  --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000 \
  --debug-info

Example 3: Custom Query Testing

mysqlslap -h127.0.0.1 -uroot -pMyPassword123 --concurrency=100 --iterations=1 \
  --create-schema=production --query='SELECT id,name,status FROM users WHERE id > 1000;' \
  --engine=innodb --number-of-queries=5000 --debug-info

Example 4: External SQL Script

mysqlslap -h127.0.0.1 -uroot -pMyPassword123 --concurrency=100 --iterations=1 \
  --create-schema=production --query=/path/to/test_queries.sql \
  --engine=innodb --number-of-queries=5000 --debug-info

Example 5: Specifying Table Structure

Configure auto-generated tables with specific column counts:

mysqlslap -uroot -pMyPassword123 --concurrency=50 --iterations=1 \
  --number-int-cols=4 --number-char-cols=35 \
  --auto-generate-sql --auto-generate-sql-add-autoincrement \
  --auto-generate-sql-load-type=mixed --engine=myisam,innodb \
  --number-of-queries=200 --only-print

Engine Comparison Testing

Compare multiple storage engines with varying concurrency levels:

mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 \
  --engine=myisam,innodb --debug-info -uroot -pMyPassword123

For accurate results, run multiple iterations to calculate averages.

Display-Only Mode

Preview generated SQL without executing:

mysqlslap -a -uroot -pMyPassword123 --only-print

Common Issues and Solutions

Error: "Can't create UNIX socket"

Cause: Too many connections exhausting system resources.

Solution: Increase the system's open file limit. Check current limit with cat /proc/sys/fs/file-max and modify /etc/security/limits.conf:

root hard nofile 20000
root soft nofile 20000

Error: "Unknown database 'mysqlslap'"

Cause: Target database not specified or incorrectly referenced. Always use --create-schema to specify the target database.

Segmentation Fault

If mysqlslap crashes with "Segmentation fault (core dumped)", the MySQL server is likely overloaded. Reduce concurrency and retry.

Interpreting Results

When benhcmarking MySQL server performance, adjust --concurrency and --number-of-queries values iteratively. Document results for each configuration to identify the maximum sustainable concurrent load for your server. Lower average execution times indicate better performance under the tested conditions.