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