Note: mysqlslap is installed together with mysql server

Official description of mysqlslap

mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.

Link to article: https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap

Options

option	description
user	MySQL username to connect to the database server
password	Password for the user account. It’s best to leave it blank in command line
host	MySQL database server name
port	Port number for connecting to MySQL if the default is not used
concurrency	The number of simultaneous client connections mysqlslap will emulate
iterations	The number of times the test query will be run
create-schema	The database where the query will be run
query	The query to execute. This can either be a SQL query string or a path to a SQL script file
create	The query to create a table. Again, this can be a query string or a path to a SQL file
delimiter	The delimiter used to separate multiple SQL statements
engine	The MySQL database engine to use (e.g., InnoDB)
auto-generate-sql	Lets MySQL perform load testing with its own auto-generated SQL command

Test it with Autogenerated Database mysqlslap

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

Benchmarking with Custom Queries

sudo mysqlslap --user=root --password --host=localhost  --concurrency=50 --iterations=10 --create-schema=employees --query="SELECT * FROM 24ur;" --verbose

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

Query in a file

Put a query in the file and execute the test.

sudo echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql
sudo cp ~/select_query.sql /mysqlslap_tutorial/
sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="/mysqlslap_tutorial/select_query.sql" --delimiter=";" --verbose --iterations=2 --debug-info

Setup log on the Database Server

-- turn on
SET GLOBAL general_log=1, general_log_file='capture_queries.log';

-- turn off
SET GLOBAL general_log=0;

-- locate log
sudo ls -l /var/lib/mysql/capt*

-rw-rw----. 1 mysql mysql 861 Sep 24 15:09 /var/lib/mysql/capture_queries.log

Put query in a .sql file without any line breaks and without a semicolon at the end.

Reset the cache

RESET QUERY CACHE;

Run the test

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose