Difference between revisions of "Mysqlslap: benchmark MySQL"

From OnnoWiki
Jump to navigation Jump to search
(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...)
 
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
 +
* hostname localhost
  
−
I have tested it with MySQL-client-5.5.25-1.rhel5.i386.rpm.
+
Test
−
Just install it as
 
−
#rpm -ivh MySQL-client-5.5.25-1.rhel5.i386.rpm
 
−
Then you will get the command "mysqlslap"
 
  
 +
mysqlslap -v -h localhost -u root -p123456 --auto-generate-sql
  
 +
Output :
 +
 +
Benchmark
 +
Average number of seconds to run all queries: 0.009 seconds
 +
Minimum number of seconds to run all queries: 0.009 seconds
 +
Maximum number of seconds to run all queries: 0.009 seconds
 +
Number of clients running queries: 1
 +
Average number of queries per client: 0
 +
 +
 +
 +
Perintah –auto-generate-sql  akan membuat table, execute INSERT query dan simpan dummy data, execute  SELECT query untuk mengambil dummy data, kemudian drop table. Untuk melihat apa yang terjadi di belakang layar tambahkan -vvv
 +
 +
mysqlslap -vvv -h localhost -u root --auto-generate-sql -p123456
 +
 +
 +
Membuat 100 concurrent user dan masing-masing mengexecute satu query,
 +
 +
mysqlslap -vvv -h localhost -u root -p123456 --auto-generate-sql --concurrency=100  --number-of-queries=1
 +
 +
Output:
  
−
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
 +
 +
DROP SCHEMA IF EXISTS `mysqlslap`;
 +
 
 +
==Menggunakan number-of-query==
 +
Test 100 concurrent user dan 10.000 query.
  
−
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
−
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.
+
  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
−
        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
+
==Menggunakan iterations==
−
        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:
+
Mengulangi query lebih dari satu menggunakan 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.
+
mysqlslap -h localhost -u root -p123456 --auto-generate-sql --concurrency=100  --number-of-queries=10000 --iterations=5
−
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
 
  
 +
==Menggunakan custom queries==
 +
Jika kita ingin men-test ke database yang sudah ada, kita dapat menggunakan custom query, seperti
  
−
Comparing the Engines:
+
mysqlslap -h localhost -u root -p123456  --create-schema=DATABASE_NAME  --query="SELECT * from table_name;"  --concurrency=100  --number-of-queries=100
−
You know the performance varies when using different engines. You can compare the performance by engine using the argument --engine.
 
  
 +
==Membandingkan Engine==
  
−
Using the engine innodb
+
Kita dapat membandingkan engine dengan argumen --engine.
−
[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
 
  
 +
Menggunakan engine innodb
  
−
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
  
 +
Menggunakan engine myisam
  
 +
mysqlslap -h localhost -u root  -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=myisam
  
  

Revision as of 09:02, 8 June 2015

Asumsi

  • root password mysql 123456
  • hostname localhost

Test 

mysqlslap -v -h localhost -u root -p123456 --auto-generate-sql

Output : 

Benchmark
	Average number of seconds to run all queries: 0.009 seconds
	Minimum number of seconds to run all queries: 0.009 seconds
	Maximum number of seconds to run all queries: 0.009 seconds
	Number of clients running queries: 1
	Average number of queries per client: 0


Perintah –auto-generate-sql akan membuat table, execute INSERT query dan simpan dummy data, execute SELECT query untuk mengambil dummy data, kemudian drop table. Untuk melihat apa yang terjadi di belakang layar tambahkan -vvv 

mysqlslap -vvv -h localhost -u root --auto-generate-sql -p123456


Membuat 100 concurrent user dan masing-masing mengexecute satu query, 

mysqlslap -vvv -h localhost -u root -p123456 --auto-generate-sql --concurrency=100  --number-of-queries=1

Output: 

..
..
INSERT INTO t1 VALUES (364531492,'qMa5SuKo4M5OM7ldvisSc6WK9rsG9E8sSixocHdgfa5uiiNTGFxkDJ4EAwWC2e4NL1BpAgWiFRcp1zIH6F1BayPdmwphatwnmzdwgzWnQ6SRxmcvtd6JRYwEKdvuWr');
Generating stats
Benchmark
	Average number of seconds to run all queries: 0.571 seconds
	Minimum number of seconds to run all queries: 0.571 seconds
	Maximum number of seconds to run all queries: 0.571 seconds
	Number of clients running queries: 100
	Average number of queries per client: 0

DROP SCHEMA IF EXISTS `mysqlslap`;

Menggunakan number-of-query

Test 100 concurrent user dan 10.000 query. 

mysqlslap -h localhost -u root -p123456 --auto-generate-sql --concurrency=100  --number-of-queries=10000

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


Menggunakan iterations

Mengulangi query lebih dari satu menggunakan iterations 

mysqlslap -h localhost -u root -p123456 --auto-generate-sql --concurrency=100  --number-of-queries=10000 --iterations=5


Menggunakan custom queries

Jika kita ingin men-test ke database yang sudah ada, kita dapat menggunakan custom query, seperti 

mysqlslap -h localhost -u root -p123456  --create-schema=DATABASE_NAME  --query="SELECT * from table_name;"  --concurrency=100  --number-of-queries=100

Membandingkan Engine

Kita dapat membandingkan engine dengan argumen --engine.


Menggunakan engine innodb 

mysqlslap -h localhost -u root  -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=innodb

Menggunakan engine myisam 

mysqlslap -h localhost -u root  -p123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=myisam


Referensi

Retrieved from "https://onnocenter.or.id/wiki/index.php?title=Mysqlslap:_benchmark_MySQL&oldid=43506"