Mysqlslap: tutorial stress test

From OnnoWiki
Revision as of 09:27, 8 June 2015 by Onnowpurbo (talk | contribs) (→‎Menggunakan Custom Query)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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