View source for Internet Offline: Mengukur SQL

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

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


==Pranala Menarik==

* [[Internet Offline]]