Menu

Use mysqlslap for SQL Query testing

March 17, 2017 - SQL

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

optiondescription
userMySQL username to connect to the database server
passwordPassword for the user account. It’s best to leave it blank in command line
hostMySQL database server name
portPort number for connecting to MySQL if the default is not used
concurrencyThe number of simultaneous client connections mysqlslap will emulate
iterationsThe number of times the test query will be run
create-schemaThe database where the query will be run
queryThe query to execute. This can either be a SQL query string or a path to a SQL script file
createThe query to create a table. Again, this can be a query string or a path to a SQL file
delimiterThe delimiter used to separate multiple SQL statements
engineThe MySQL database engine to use (e.g., InnoDB)
auto-generate-sqlLets 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