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.

Referensi

• http://www.techrepublic.com/blog/how-do-i/how-do-i-stress-test-mysql-with-mysqlslap/