Mysqlslap: tutorial stress test: Difference between revisions

From OnnoCenterWiki
Jump to navigationJump to search
Onnowpurbo (talk | contribs)
69,477 edits
Onnowpurbo (talk | contribs)
69,477 edits
New page: 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. This tu...
 
Onnowpurbo (talk | contribs)
69,477 edits
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
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. This tutorial shows how you can use mysqlslap to run tests involving multiple clients, custom queries, different table engines, and much more.
==Penggunaan Dasar==


One of the interesting new tools in MySQL 5.1.4 is mysqlslap, a load emulator that lets you see how well a particular query set or table engine performs under high-load conditions.


A query that consumes too many database resources may be the result of designing tables incorrectly, choosing the wrong table type, or creating an inefficient query. When a query eats up a lot of database resources, it can negatively affect other application components. By using mysqlslap to stress test a server in a non-public environment, you will discover these errors sooner, allowing you to you avoid a database meltdown once your application goes live.
mysqlslap --user=root --password=123456 --auto-generate-sql


This tutorial shows how you can use mysqlslap to run stress tests involving multiple clients, custom queries, different table engines, and much more.
Output:


Basic usage
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


This simple (and unrealistic) example uses mysqlslap to test server performance assuming only one client connection:
Untuk melihat apa yang terjadi dengan --auto-generate-sql lakukan


shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql
mysqlslap --user=root --password=123456 --auto-generate-sql -vvv


Benchmark
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`;


           


Average number of seconds to run all queries: 0.006 seconds
Untuk mensimulasi banyak & simultan client connections


Minimum number of seconds to run all queries: 0.006 seconds


Maximum number of seconds to run all queries: 0.006 seconds
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100


Number of clients running queries: 1
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


Average number of queries per client: 0
Coba naikan lagi


The --auto-generate-sql switch tells mysqlslap to automatically generate and execute SQL statements, monitor how fast MySQL performs this task, and display the result. The results indicate that MySQL took 0.006 seconds to execute the SQL statements.


The --auto-generate-sql switch creates a table, executes an INSERT query and saves dummy data to it, executes a SELECT query to retrieve the dummy data, and then drops the table. You can see behind-the-scenes action by adding the -v switch to the mysqlslap command line (adding extra 'v's increases the verbosity level):
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=300


shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql -vv
Output:


DROP SCHEMA IF EXISTS `mysqlslap`;
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


CREATE SCHEMA `mysqlslap`;


CREATE SCHEMA `mysqlslap`;
Catatan: pada suatu saat, kita akan menghadapi error "Too many connections". Kita perlu mengatur variable MySQL 'max_connections' yang mengatur concurrent connections ke server.


CREATE TABLE `t1` (intcol1 INT(32),charcol1 VARCHAR(128));
==Run test lebih dari satu kali==


INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkd


ekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxB
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --iterations=5


L');
Output:


SELECT intcol1,charcol1 FROM t1;
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


Benchmark


Average number of seconds to run all queries: 0.007 seconds
==Menentukan total queries==


Minimum number of seconds to run all queries: 0.007 seconds
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=10000


Maximum number of seconds to run all queries: 0.007 seconds
Output:


Number of clients running queries: 1
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


Average number of queries per client: 0 DROP SCHEMA IF EXISTS `mysqlslap`;


It's unlikely that you'll have only a single client connecting to the MySQL server at any given time, so you'll typically also need the --concurrency switch, which lets you simulate multiple simultaneous client connections, like this:
==Menggunakan Tables yang besar==


shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100


Benchmark
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=1000 --number-char-cols=4 --number-int-cols=7


Average number of seconds to run all queries: 0.698 seconds
Output:


Minimum number of seconds to run all queries: 0.698 seconds
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


Maximum number of seconds to run all queries: 0.698 seconds


Number of clients running queries: 100
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-char-cols=4


Average number of queries per client: 0
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


MySQL performance drops pretty significantly (from 0.007 seconds to 0.698 seconds) when it has to deal with 100 clients instead of just one.


See what happens if you increase the number of concurrent connections even more:
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-int-cols=5


shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=300
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


Benchmark


Average number of seconds to run all queries: 47.515 seconds
==Menggunakan Custom Query==


Minimum number of seconds to run all queries: 47.515 seconds
Asumsi kita mempunyai


Maximum number of seconds to run all queries: 47.515 seconds
* database world


Number of clients running queries: 300
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


Average number of queries per client: 0


Note: As you increase the number of concurrent connections, you might encounter a "Too many connections" error. You need to adjust MySQL's 'max_connections' variable, which controls the maximum number of concurrent connections allowed by the server.
Untuk mempercepat ada baiknya dilakukan INDEX pada tabel City
Running tests more than once


You can force mysqlslap to run a particular test more than once by adding the --iterations switch to the command line. This example runs the same test five times and prints a composite result:
mysql -u root -p123456


shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --iterations=5
USE world;
CREATE INDEX idx_ccode ON City(CountryCode);
exit


Benchmark


Average number of seconds to run all queries: 0.714 seconds
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


Minimum number of seconds to run all queries: 0.682 seconds


Maximum number of seconds to run all queries: 0.753 seconds
Kita dapat membuat custom tabel gunakan argmen --create dengan CREATE TABLE command.


Number of clients running queries: 100


Average number of queries per client: 0
==Membandingkan tabel engine==


Specifying the total number of queries


It's possible to force each "client" to run a specific number of queries by adding the --number-of-queries switch to the mysqlslap command line. When mysqlslap encounters this switch, it divides the corresponding value by the number of concurrent connections and uses the result to decide how many queries each client should run.
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=innodb


For example, with settings of 500 total queries and five concurrent clients, mysqlslap will run 500/5 = 100 queries per client. Take a look at an example:
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


shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-of-queries=10000


Benchmark
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=myisam


Average number of seconds to run all queries: 0.694 seconds
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


Minimum number of seconds to run all queries: 0.694 seconds
mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100 --number-of-queries=700 --engine=memory


Maximum number of seconds to run all queries: 0.694 seconds
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


Number of clients running queries: 100


Average number of queries per client: 100


Using larger tables
==Menyimpan Laporan==


The default behavior of mysqlslap when using the --auto-generate-sql switch is to create a two-column table with one integer column and one character column. If this isn't representative of the kind of tables you typically use, you can adjust these settings to include more integer and/or character columns, with the --number-char-cols and --number-int-cols switches. Here are examples:


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
Cara paling gampang


Benchmark
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


Average number of seconds to run all queries: 1.290 seconds


Minimum number of seconds to run all queries: 1.290 seconds
Kita dapat memaksa untuk menyimpan dalam CSV format.


Maximum number of seconds to run all queries: 1.290 seconds
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


Number of clients running queries: 100
Isi CSV file,
 
Average number of queries per client: 10 shell> /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-char-cols=4
Benchmark
 
Average number of seconds to run all queries: 0.968 seconds
 
Minimum number of seconds to run all queries: 0.968 seconds
 
Maximum number of seconds to run all queries: 0.968 seconds
 
Number of clients running queries: 100
 
Average number of queries per client: 0
 
shell>  /usr/local/mysql/bin/mysqlslap --user=john --auto-generate-sql --concurrency=100 --number-int-cols=5
Benchmark
 
Average number of seconds to run all queries: 1.076 seconds
 
Minimum number of seconds to run all queries: 1.076 seconds
 
Maximum number of seconds to run all queries: 1.076 seconds
 
Number of clients running queries: 100
 
Average number of queries per client: 0
 
Using custom queries
 
While the --auto-generate-sql option is fine for general load testing, you may want to test the performance of a specific query on a database that already exists. In these situations, you can bypass the --auto-generate-sql switch and instead tell mysqlslap to use your own custom query with the --query switch. Here's the next example:
 
shell> /usr/local/mysql/bin/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
 
Benchmark
 
Average number of seconds to run all queries: 2.886 seconds
 
Minimum number of seconds to run all queries: 2.137 seconds
 
Maximum number of seconds to run all queries: 4.125 seconds
 
Number of clients running queries: 100
 
Average number of queries per client: 1
 
It's helpful to use mysqlslap in this manner when you need to analyze the effect of a change in your database structure or indexing because it allows you to immediately grasp the impact of, say, an additional index on overall performance. To illustrate, look what happens to the time needed to run the previous query when an index is added to the City table:
 
mysql> CREATE INDEX idx_ccode ON City(CountryCode);
 
Query OK, 4079 rows affected (1.06 sec)
 
Records: 4079  Duplicates: 0  Warnings: 0 mysql> exit
Bye
shell> /usr/local/mysql/bin/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
Benchmark
 
Average number of seconds to run all queries: 1.682 seconds
 
Minimum number of seconds to run all queries: 1.396 seconds
 
Maximum number of seconds to run all queries: 2.109 seconds
 
Number of clients running queries: 100
 
Average number of queries per client: 1
 
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.


cat /tmp/output.csv


,mixed,0.638,0.638,0.638,100,10
.


==Referensi==
==Referensi==


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

Latest revision as of 02:27, 8 June 2015

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

Retrieved from "https://lms.onnocenter.or.id/wiki/index.php?title=Mysqlslap:_tutorial_stress_test&oldid=43510"