Use mysqlslap for SQL Query testing

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