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 You can tell mysqlslap to create a custom table for your load testing by using the --create command-line switch with a CREATE TABLE command.
Comparing table engines
A cool feature of mysqlslap is the ability to specify the table engine used in the test. This provides database designers with an easy way to compare the performance of different table types under different load conditions. The --engine switch accepts any of MySQL's supported table types and creates test tables using the corresponding storage engine. Here's an example of how it could be used:
shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=innodb
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.240 seconds
Minimum number of seconds to run all queries: 1.240 seconds
Maximum number of seconds to run all queries: 1.240 seconds
Number of clients running queries: 100
Average number of queries per client: 7 shell> /usr/local/mysql/bin/mysqlslap --user=john --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.676 seconds
Minimum number of seconds to run all queries: 0.676 seconds
Maximum number of seconds to run all queries: 0.676 seconds
Number of clients running queries: 100
Average number of queries per client: 7
shell> /usr/local/mysql/bin/mysqlslap --user=john --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.602 seconds
Minimum number of seconds to run all queries: 0.602 seconds
Maximum number of seconds to run all queries: 0.602 seconds
Number of clients running queries: 100
Average number of queries per client: 7
Saving reports
You might wish to save a mysqlslap report so you can compare it to a previous or future test run; you may also want to use the report as a reference when you're configuring new systems.
The easiest way to save a mysqlslap report is to pipe the output of a mysqlslap run to a file, as below:
shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7 >> /tmp/output.log
You can force mysqlslap to generate reports in CSV format; this is often useful if you need to import the data into a spreadsheet or database to build graphical reports from it. To do this, add the --csv switch to your mysqlslap command line and specify the output filename as an argument to this switch. Here's an example:
shell> /usr/local/mysql/bin/mysqlslap --csv=/tmp/output.csv --user=john --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7
Here's what the CSV file would contain if you peeked inside it:
shell> cat /tmp/output.csv ,query,1.070,1.070,1.070,100,10
Trying mysqlslap
The mysqlslap utility makes it possible to benchmark and compare MySQL performance on different hardware, as well as accurately quantify the effect of a change in database design. Try mysqlslap out for yourself and see how well your database server behaves under pressure from thousands of client connections.