Mysqlslap: tutorial stress test
Penggunaan Dasar
mysqlslap --user=root --password=123456 --auto-generate-sql
Output:
Benchmark Average number of seconds to run all queries: 0.008 seconds Minimum number of seconds to run all queries: 0.008 seconds Maximum number of seconds to run all queries: 0.008 seconds Number of clients running queries: 1 Average number of queries per client: 0
Untuk melihat apa yang terjadi dengan --auto-generate-sql lakukan
mysqlslap --user=root --password=123456 --auto-generate-sql -vvv
Output:
Building Create Statements for Auto Building Query Statements for Auto Parsing engines to use. Starting Concurrency Test DROP SCHEMA IF EXISTS `mysqlslap`; Loading Pre-data CREATE SCHEMA `mysqlslap`; CREATE SCHEMA `mysqlslap`; CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128)); INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL'); .. .. INSERT INTO t1 VALUES (364531492,'qMa5SuKo4M5OM7ldvisSc6WK9rsG9E8sSixocHdgfa5uiiNTGFxkDJ4EAwWC2e4NL1BpAgWiFRcp1zIH6F1BayPdmwphatwnmzdwgzWnQ6SRxmcvtd6JRYwEKdvuWr'); Generating stats Benchmark Average number of seconds to run all queries: 0.011 seconds Minimum number of seconds to run all queries: 0.011 seconds Maximum number of seconds to run all queries: 0.011 seconds Number of clients running queries: 1 Average number of queries per client: 0 DROP SCHEMA IF EXISTS `mysqlslap`;
Untuk mensimulasi banyak & simultan client connections
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100
Output:
Benchmark Average number of seconds to run all queries: 0.320 seconds Minimum number of seconds to run all queries: 0.320 seconds Maximum number of seconds to run all queries: 0.320 seconds Number of clients running queries: 100 Average number of queries per client: 0
Coba naikan lagi
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=300
Output:
Benchmark Average number of seconds to run all queries: 1.674 seconds Minimum number of seconds to run all queries: 1.674 seconds Maximum number of seconds to run all queries: 1.674 seconds Number of clients running queries: 300 Average number of queries per client: 0
Catatan: pada suatu saat, kita akan menghadapi error "Too many connections". Kita perlu mengatur variable MySQL 'max_connections' yang mengatur concurrent connections ke server.
Run test lebih dari satu kali
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --iterations=5
Output:
Benchmark Average number of seconds to run all queries: 0.313 seconds Minimum number of seconds to run all queries: 0.286 seconds Maximum number of seconds to run all queries: 0.347 seconds Number of clients running queries: 100 Average number of queries per client: 0
Menentukan total queries
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=10000
Output:
Benchmark Average number of seconds to run all queries: 9.486 seconds Minimum number of seconds to run all queries: 9.486 seconds Maximum number of seconds to run all queries: 9.486 seconds Number of clients running queries: 100 Average number of queries per client: 100
Menggunakan Tables yang besar
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7
Output:
Benchmark Average number of seconds to run all queries: 0.575 seconds Minimum number of seconds to run all queries: 0.575 seconds Maximum number of seconds to run all queries: 0.575 seconds Number of clients running queries: 100 Average number of queries per client: 10
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-char-cols=4
Benchmark Average number of seconds to run all queries: 0.452 seconds Minimum number of seconds to run all queries: 0.452 seconds Maximum number of seconds to run all queries: 0.452 seconds Number of clients running queries: 100 Average number of queries per client: 0
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-int-cols=5
Benchmark Average number of seconds to run all queries: 0.404 seconds Minimum number of seconds to run all queries: 0.404 seconds Maximum number of seconds to run all queries: 0.404 seconds Number of clients running queries: 100 Average number of queries per client: 0
Menggunakan Custom Query
Asumsi kita mempunyai
- database world
mysqlslap --user=root --password=123456 --create-schema=world --query="SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = 'IND';" --concurrency=100 --iterations=5
Untuk mempercepat ada baiknya dilakukan INDEX pada tabel City
mysql -u root -p123456
USE world; CREATE INDEX idx_ccode ON City(CountryCode); exit
mysqlslap --user=john --create-schema=world --query="SELECT City.Name, City.District FROM City, Country WHERE City.CountryCode = Country.Code AND Country.Code = 'IND';" --concurrency=100 --iterations=5
Kita dapat membuat custom tabel gunakan argmen --create dengan CREATE TABLE command.
Membandingkan tabel engine
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=innodb
Benchmark Running for engine innodb Average number of seconds to run all queries: 0.196 seconds Minimum number of seconds to run all queries: 0.196 seconds Maximum number of seconds to run all queries: 0.196 seconds Number of clients running queries: 100 Average number of queries per client: 7
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=myisam
Benchmark Running for engine myisam Average number of seconds to run all queries: 0.119 seconds Minimum number of seconds to run all queries: 0.119 seconds Maximum number of seconds to run all queries: 0.119 seconds Number of clients running queries: 100 Average number of queries per client: 7
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=memory
Benchmark Running for engine memory Average number of seconds to run all queries: 0.131 seconds Minimum number of seconds to run all queries: 0.131 seconds Maximum number of seconds to run all queries: 0.131 seconds Number of clients running queries: 100 Average number of queries per client: 7
Menyimpan Laporan
Cara paling gampang
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7 >> /tmp/output.log
Kita dapat memaksa untuk menyimpan dalam CSV format.
mysqlslap --csv=/tmp/output.csv --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7
Isi CSV file,
cat /tmp/output.csv
,mixed,0.638,0.638,0.638,100,10
.