Mysqlslap: benchmark MySQL - Revision history

Onnowpurbo: /* Membandingkan Engine */

2015-06-08T02:05:04Z

Membandingkan Engine

Onnowpurbo: /* Menggunakan iterations */

2015-06-08T02:03:20Z

Menggunakan iterations

Onnowpurbo at 02:02, 8 June 2015

2015-06-08T02:02:46Z

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

@@ 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

← 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==

@@ Line 1: / Line 1: @@
-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.
+Asumsi
-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.
+* root password mysql 123456
-I have tested it with MySQL-client-5.5.25-1.rhel5.i386.rpm.
+Test
-Testing :
+ ..
-Command :
+ ..
-[root@mysql ~]# mysqlslap -v -h  hostname -u root -ppassword --auto-generate-sql
+  INSERT INTO t1 VALUES (364531492,'qMa5SuKo4M5OM7ldvisSc6WK9rsG9E8sSixocHdgfa5uiiNTGFxkDJ4EAwWC2e4NL1BpAgWiFRcp1zIH6F1BayPdmwphatwnmzdwgzWnQ6SRxmcvtd6JRYwEKdvuWr');
-Output :
+ Generating stats
-Benchmark
+ Benchmark
-        Average number of seconds to run all queries: 0.002 seconds
+ 	Average number of seconds to run all queries: 0.571 seconds
-        Minimum number of seconds to run all queries: 0.002 seconds
+ 	Minimum number of seconds to run all queries: 0.571 seconds
-        Maximum number of seconds to run all queries:  0.002  seconds
+ 	Maximum number of seconds to run all queries: 0.571 seconds
-        Number of clients running queries: 1
+ 	Number of clients running queries: 100
-        Average number of queries per client: 0
+ 	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.
+  mysqlslap -h localhost -u root -p123456 --auto-generate-sql --concurrency=100  --number-of-queries=10000
-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.
+  Benchmark
-[root@mysql ~]# mysqlslap -vvv -h  hostname -u root -ppassword --auto-generate-sql --concurrency=100  --number-of-queries=1
+ 	Average number of seconds to run all queries: 9.331 seconds
-Benchmark
+ 	Minimum number of seconds to run all queries: 9.331 seconds
-        Average number of seconds to run all queries: 0.055 seconds
+ 	Maximum number of seconds to run all queries: 9.331 seconds
-        Minimum number of seconds to run all queries: 0.055 seconds
+ 	Number of clients running queries: 100
-        Maximum number of seconds to run all queries:  0.055  seconds
+ 	Average number of queries per client: 100
-Benchmark
+==Menggunakan iterations==
-Using iterations:
+Mengulangi query lebih dari satu menggunakan iterations
-Using custom queries.
+ mysqlslap -h localhost -u root -p123456 --auto-generate-sql --concurrency=100  --number-of-queries=10000 --iterations=5
-Comparing the Engines:
+ mysqlslap -h localhost -u root -p123456  --create-schema=DATABASE_NAME  --query="SELECT * from table_name;"  --concurrency=100  --number-of-queries=100
-Using the engine innodb
+Kita dapat membandingkan engine dengan argumen --engine.
-Thats it. Try it and comment your experiences.
+ mysqlslap -h localhost -u root  -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=innodb