Menu

Use mysqlslap for SQL Query testing

March 17, 2017 - SQL

mysqlslap is installed together with mysql server

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

Options

–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

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 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 cache

RESET QUERY CACHE;

Run a test

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