Mysqlslap: benchmark MySQL: Difference between revisions

From OnnoCenterWiki
Jump to navigationJump to search
Newer edit →
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
No edit summary
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 02: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://lms.onnocenter.or.id/wiki/index.php?title=Mysqlslap:_benchmark_MySQL&oldid=43506"