Mysqlslap: benchmark MySQL
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