Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the MySQL stress testing tool mydbtest. I hope you will get something after reading this article. Let's discuss it together.

A preface

This paper introduces a green installation-free version of the database pressure testing tool-mydbtest (mydbtest_linux64.bin. Different from sysbench, tpcc and other common pressure testing tools, this pressure testing software is installation-free, quick to use, and can be customized for business sql.

How to use it

2.1 Random data Generator

We specify the type of random data in the configuration file, such as an int 10 30000, and randomly generate integers from 10 to 30000. Note that a must be the value used in the where condition, such as where id=:a:, syntax.

Varname VARTYPE minimum maximum

The tool supports 7 types

Sequence sequence

Int: i_value int min max

Char: c_value char min_length max_length

Float: f_value float min max

Double: d_value double min max

Date: create_time date-10 10 produces random times before and after ten days, such as 2016-04-01.

Timestamp: the usage is the same as date, with the format YYYY-MM-DD HH24:MI:SS

The software supports the use of multiple field types in the same profile.

2.2 Profil

The format of the configuration file is as follows

Option

Name yangyi_20160331

User username/password@hostname:port:database

Number of loop N cycles

Log generates the results of the log file, you can use% p to specify a log file for each stress test process

How long does wait N wait after each loop execution (not the interval between each sql execution). The default value is 0.

The response time of show N display stress test per second is 60s by default.

Tran [yes] transactional. If this parameter is set to yes, all sql in begin will be executed in the same transaction.

Commit [N] how often does loop execute the commit command. If you have configured a large amount of test data, it is recommended that you set this value a little higher. Note that the commit parameter works only when tran is set to yes.

Time Specify how duration to be executed for the SQL statements, default value is

3600 seconds, you can specify "D1" for one day, "H2" for on hour, "M1" for one minute, and "S60" for 60 seconds.

Declare

# Random number

Varname1 int 10 100000

Varname2 char 1 30

[.]

Begin

# # you can configure select and insert,update,delete statements, or you can configure database statements contained in {}, each of which must be separated by a semicolon.

INSERT INTO (…) VALUES (: varname1,: varname2, …)

SELECT * FROM WHERE... >: varname1

{begin

Update t_kc_center set col2=col2-1 where col1 =: id

Update t_kc_center set col2=col2 + 1 where col1 =: id + 1

End

}

End

A specific example:

Test.cnf

The configuration file indicates connecting to the database and performing 2000 queries.

Option

Name mysql_test

Loop 2000

User yang/yang@127.0.0.1:3308:test

Declare

An int 10 30000

Begin

Select * from t_mytest where col1 =: a

End

3. Specific use

Root@rac3:~# >. / mydbtest_linux64.bin-- help

MYDBTEST: MySQL Database Test Utility, Release 1.0.1

Copyright Lou Fangxin (AnySQL.net) 2012-2013, all rights reserved.

Usage: mysqldbtest keyword=value [keyword=value [...]]

Valid Keywords:

User = username/password@tnsname for source and target.

Query = test case file.

Degree = parallel degree for multiple threads test

Root@rac3:~# >. / mydbtest_linux64.bin query=test.cnf degree=1

MYDBTEST: MySQL Database Test Utility, Release 1.0.1

Copyright Lou Fangxin (AnySQL.net) 2012-2013, all rights reserved.

2016-03-25 14:08:27 = = mysql_test = =

2016-03-25 14:08:27 SQL01 exe=2000 fail=0 row=1105 ela=231 ms avg=115 us

2016-03-25 14:08:27 SQL01 1 ms exec= 1996, ela= 225ms, avg= 113us, pct= 99,99

2016-03-25 14:08:27 SQL01 2 ms exec= 3, ela= 3 ms, avg= 1031 us, pct= 0,99

2016-03-25 14:08:27 SQL01 3 ms exec= 1, ela= 2 ms, avg= 2481 us, pct= 0100

2016-03-25 14:08:27 Total tran=2000=8333/s, qtps=2000=8333/s, ela=233 ms, avg=116 us

Summary: SQL01 exec=2000, rows=1105=55/e, avg=115 us

Summary: exec=2000/s, qtps=2000/s

During the pressure test, if you feel that such a report does not meet the requirements, you can cooperate with orzdba to view the qps tps of the database, and select the data to draw a chart.

After reading this article, I believe you have a certain understanding of "how to use MySQL stress testing tool mydbtest". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.