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

.

Referensi

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