mysqlslap is native mysql utility to check client load in MySQL server. It’s introduced in MySQl 5.1. It seems very useful as it has lots of options to check load of your server by running different kind of queries with specific iterations, specific concurrency. Output will give you how much max/min/avg time has been taken by the queries.
I have used sql-bench utility before, but looks like this one is more easy for benchmarking on MySQL server.
mysqlslap will run in three steps.
- It will create either schema/table or any routine or any test data with single client connection.
- It will run the load test on server with one or many client connections. (It depends on your options given while running)
- It will remove/drop all objects (schema/table/routines) which are created by itself before start load test with single client connection.
Some useful options:
–create/–query : specify the sql statements or file to run. (for file, for each line one statement should be there)
–delimiter: option to specify delimiter to run the statements.
–concurrency: no. of clients to run given statements.
–iterations: no. of time you want to run the test
–auto-generate-sql: It will generate SQL statements automatically.
Here are some examples which I ran locally.
For create table, insert records and select the records for load testing, you can run mysqlslap like this.
root@HP-ProBook-4410s:~# mysqlslap -uroot -p --delimiter=";" > --create="CREATE TABLE foo (id int, name varchar(10)); > INSERT INTO foo VALUES (1,'name')" > --query="SELECT * FROM foo" --concurrency=25 --iterations=500 Enter password: Benchmark Average number of seconds to run all queries: 0.004 seconds Minimum number of seconds to run all queries: 0.002 seconds Maximum number of seconds to run all queries: 0.025 seconds Number of clients running queries: 25 Average number of queries per client: 1 root@HP-ProBook-4410s:~#
By using –auto-generate-sql option, you can run like this.
root@HP-ProBook-4410s:~# mysqlslap -uroot -p --auto-generate-sql > --number-int-cols=3 --number-char-cols=4 > --concurrency=10 --iterations=50 Enter password: Benchmark Average number of seconds to run all queries: 0.014 seconds Minimum number of seconds to run all queries: 0.012 seconds Maximum number of seconds to run all queries: 0.024 seconds Number of clients running queries: 10 Average number of queries per client: 0 root@HP-ProBook-4410s:~#
More details are here: mysqlslap