Mysqlslap: benchmark MySQL: Difference between revisions

From OnnoCenterWiki
Jump to navigationJump to search
Onnowpurbo (talk | contribs)
69,477 edits
Onnowpurbo (talk | contribs)
69,477 edits
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...
 
Onnowpurbo (talk | contribs)
69,477 edits
 
(2 intermediate revisions by the same user not shown)
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 :


Testing :
Benchmark
Command :
Average number of seconds to run all queries: 0.009 seconds
[root@mysql ~]# mysqlslap -v -h hostname -u root -ppassword --auto-generate-sql  
Minimum number of seconds to run all queries: 0.009 seconds
Output :
Maximum number of seconds to run all queries: 0.009 seconds
Benchmark
Number of clients running queries: 1
        Average number of seconds to run all queries: 0.002 seconds
Average number of queries per client: 0
        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
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


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 --iterations=5
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.
Output:
[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:
Benchmark
If you want to test with 100 concurrent users and 10,000 queries,
Average number of seconds to run all queries: 9.278 seconds
[root@mysql ~]# mysqlslap -vvv -h  hostname -u root -ppassword --auto-generate-sql --concurrency=100 --number-of-queries=10000
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


Benchmark
==Menggunakan custom queries==
        Average number of seconds to run all queries: 0.450 seconds
Jika kita ingin men-test ke database yang sudah ada, kita dapat menggunakan custom query, seperti
        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:
mysqlslap -h localhost -u root -p123456  --create-schema=DATABASE_NAME  --query="SELECT * from table_name;"  --concurrency=100  --number-of-queries=100
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.
==Membandingkan Engine==
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
Kita dapat membandingkan engine dengan argumen --engine.




Comparing the Engines:
Menggunakan engine innodb
You know the performance varies when using different engines. You can compare the performance by engine using the argument --engine.


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


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
Output:
[root@mysql ~]# mysqlslap -vvv -h  hostname -u root  -ppassword --auto-generate-sql --concurrency=100 --number-of-queries=1000 --engine=myisam


Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.435 seconds
Minimum number of seconds to run all queries: 0.435 seconds
Maximum number of seconds to run all queries: 0.435 seconds
Number of clients running queries: 100
Average number of queries per client: 10


Thats it. Try it and comment your experiences.
Menggunakan engine myisam


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


Output:


Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.198 seconds
Minimum number of seconds to run all queries: 0.198 seconds
Maximum number of seconds to run all queries: 0.198 seconds
Number of clients running queries: 100
Average number of queries per client: 10


==Referensi==
==Referensi==


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

Latest revision as of 02:05, 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

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

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


Output: 

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.435 seconds
	Minimum number of seconds to run all queries: 0.435 seconds
	Maximum number of seconds to run all queries: 0.435 seconds
	Number of clients running queries: 100
	Average number of queries per client: 10

Menggunakan engine myisam 

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

Output: 

Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 0.198 seconds
	Minimum number of seconds to run all queries: 0.198 seconds
	Maximum number of seconds to run all queries: 0.198 seconds
	Number of clients running queries: 100
	Average number of queries per client: 10

Referensi

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