Saturday, June 30, 2012

Benchmarking MySQL with mysqlslap

Sponsored Links
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. 


Recommended Reading

1. High Performance MySQL: Optimization, Backups, and Replication
2. MySQL (4th Edition)
3. MYSQL in a Nutshell (In a Nutshell (O'Reilly))

Sponsored Links

No comments:

Post a Comment

Be nice. That's all.