Mysqlslap: benchmark MySQL
I have been checking with different tools for benchmarking a mysql server. I went through a lot of blogs and manuals and decided to use a tool named mysqlslap. In this post we will discuss how to install mysqlslap and use it. We are using Centos Linux 5.4 to test this.
Luckily mysqlslap comes with mysql-client rpm itself. With the versions 5.1.4 and above. So you can either install mysql with yum or rpm. rpms are available on mysql.com for download.
I have tested it with MySQL-client-5.5.25-1.rhel5.i386.rpm. Just install it as
- rpm -ivh MySQL-client-5.5.25-1.rhel5.i386.rpm
Then you will get the command "mysqlslap"
Testing : Command : [root@mysql ~]# mysqlslap -v -h hostname -u root -ppassword --auto-generate-sql Output : Benchmark
Average number of seconds to run all queries: 0.002 seconds
Minimum number of seconds to run all queries: 0.002 seconds
Maximum number of seconds to run all queries: 0.002 seconds
Number of clients running queries: 1
Average number of queries per client: 0
The –auto-generate-sql argument tells mysqlslap to automatically generate and execute SQL statements. The results shows that MySQL took 0.002 seconds to execute a single sql statement. The –auto-generate-sql argument creates a table, executes an INSERT query and saves dummy data to it, executes a SELECT query to retrieve the dummy data, and then drops the table. You can see behind-the-scenes action by adding the -v option. You can use multiple v's to get more detailed output. Eg: [root@mysql ~]# mysqlslap -vvv -h hostname -u root --auto-generate-sql -ppassword
Suppose you can to test the mysql with a 100 concurrent users each executing a single query. It can be done with the following command. [root@mysql ~]# mysqlslap -vvv -h hostname -u root -ppassword --auto-generate-sql --concurrency=100 --number-of-queries=1 Benchmark
Average number of seconds to run all queries: 0.055 seconds
Minimum number of seconds to run all queries: 0.055 seconds
Maximum number of seconds to run all queries: 0.055 seconds
Number of clients running queries: 100
Average number of queries per client: 1
Using the number of Queries: If you want to test with 100 concurrent users and 10,000 queries, [root@mysql ~]# mysqlslap -vvv -h hostname -u root -ppassword --auto-generate-sql --concurrency=100 --number-of-queries=10000
Benchmark
Average number of seconds to run all queries: 0.450 seconds
Minimum number of seconds to run all queries: 0.450 seconds
Maximum number of seconds to run all queries: 0.450 seconds
Number of clients running queries: 100
Average number of queries per client: 100
Using iterations: You can tell mysqlslap to repeat the query more than once using the iterations argument. [root@mysql ~]# mysqlslap -vvv -h hostname -u root -ppassword --auto-generate-sql --concurrency=100 --number-of-queries=10000 --iterations=5
Using custom queries. By default mysqlslap will use its own schema and queries to test. Suppose you want to test a mysql server where exists a database and schema, you can then specify the database and custom query in the mysqlslap command as,
[root@mysql ~]# mysqlslap -vv -h hostname -u root -ppassword --create-schema=DATABASE_NAME --query="SELECT * from table_name;" --concurrency=100 --number-of-queries=100
Comparing the Engines:
You know the performance varies when using different engines. You can compare the performance by engine using the argument --engine.
Using the engine innodb
[root@mysql ~]# mysqlslap -vvv -h hostname -u root -ppassword --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=innodb
Using the engine myisam [root@mysql ~]# mysqlslap -vvv -h hostname -u root -ppassword --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=myisam
Thats it. Try it and comment your experiences.