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