Mysqlslap: benchmark MySQL - Revision history

Onnowpurbo: /* Membandingkan Engine */

2015-06-08T02:05:04Z

Membandingkan Engine

@@ Line 84: / Line 84: @@
   mysqlslap -h localhost -u root  -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=innodb
 Menggunakan engine myisam
@@ Line 89: / Line 100: @@
   mysqlslap -h localhost -u root  -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=myisam
 ==Referensi==
 * http://www.linuxhelp.in/2012/06/benchmarking-mysql-with-mysqlslap.html

Onnowpurbo: /* Menggunakan iterations */

2015-06-08T02:03:20Z

Menggunakan iterations

← Older revision		Revision as of 02:03, 8 June 2015
Line 62:		Line 62:
	mysqlslap -h localhost -u root -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=10000 --iterations=5		mysqlslap -h localhost -u root -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=10000 --iterations=5

			Output:

			Benchmark
			Average number of seconds to run all queries: 9.278 seconds
			Minimum number of seconds to run all queries: 8.938 seconds
			Maximum number of seconds to run all queries: 9.795 seconds
			Number of clients running queries: 100
			Average number of queries per client: 100

	==Menggunakan custom queries==		==Menggunakan custom queries==

Onnowpurbo at 02:02, 8 June 2015

2015-06-08T02:02:46Z

Show changes

Onnowpurbo: New page: 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...

2015-06-04T01:27:49Z

New page: 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...

New page

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.

==Referensi==

* http://www.linuxhelp.in/2012/06/benchmarking-mysql-with-mysqlslap.html