MySQL several performance testing tools to use

Recently due to compare MySQL and its branches mariadb, Percona performance, understand a few of these tools, including: Mysqlslap Sysbench Tpcc-mysql, do a collation, memo, share

1, Mysqlslap

Installation: Simple, with MySQL on it.

Function: Simulates concurrent test Database performance.

Advantages: Simple, easy to use.

Insufficient: Can not specify the size of the generated data, the test process is not clear for the 100,000-level or millions data to do the test, it is not very suitable for comprehensive testing, more suitable for the existing database, the optimization of a single SQL test.

How to use:

You can use Mysqlslap--help to show how to use:

Default options is read from the following files in the given order:

/ETC/MYSQL/MY.CNF/ETC/MY.CNF ~/.my.cnf

--auto-generate-sql, -a  automatically generates test tables and data, indicating that the concurrency pressure is tested using the SQL scripts generated by the Mysqlslap tool itself. --auto-generate-sql-load-type=type  the type of the test statement. Represents whether the environment to be tested is a read or write operation or a mixture of the two. The values are: Read,key,write,update and mixed (default). The--auto-generate-sql-add-auto-increment  represents the automatic addition of the Auto_increment column to the generated table and is supported from version 5.1.18. --number-char-cols=n, -x n  the automatically generated test table contains the number of character type columns, the default 1--number-int-cols=n, -y n  How many number types of columns are included in the auto-generated test table, default 1--number-of-queries=n  total number of test queries (number of concurrent customers × per customer query)--query=name,-q  using a custom script to perform the test, For example, you can invoke a custom stored procedure or SQL statement to perform the test. --create-schema  represents a custom test library name, and the Schema,mysql schema in the test is database. --commint=n  how many DML to submit once. --compress, -c  compressed information is passed if both the server and client support are compressed. --concurrency=n, -c n  represents the amount of concurrency, which is how many clients perform a select at the same time. You can specify more than one value, either as a delimiter or as a comma or as the value specified by the--delimiter parameter. For example:--concurrency=100,200,500. --engine=engine_name, -e engine_name  represents the engine to be tested, can have multiple, separated by delimiters. For example:--engines=myisam,innodb. The number of iterations that the--iterations=n, -i n  test executes represents how many times to run the tests individually in different concurrency environments. --only-print  only prints test statements and does not actually execute. --detach=n  Break the re-connection after executing n statements. --debug-info, -t  information about printing memory and CPUs.

Description

The test process needs to generate a test table, insert the test data, the MYSQLSLAP can be generated automatically, by default generate a Mysqlslap schema, if already exist, first delete. You can use--only-print to print the actual test process, leaving no traces in the database after the entire test is complete.

mysqlslap-umysql-p123--concurrency=100--iterations=1--auto-generate-sql--auto-generate-sql-add-autoincrement-- Auto-generate-sql-load-type=mixed--engine=myisam--number-of-queries=10--debug-info

Or:

Specify the database and SQL statements:

mysqlslap-h192.168.3.18-p4040--concurrency=100--iterations=1--create-schema= ' test '--query= ' select * from Test; '-- number-of-queries=10--debug-info-umysql-p123

If you look at what you've done, add:--only-print

Benchmarkaverage number of seconds to run all queries:25.225 secondsminimum number of seconds to run all queries:25.225  Secondsmaximum number of seconds to run all queries:25.225 Secondsnumber of clients running Queries:100average number of Queries per client:0

The above indicates that 100 clients run at the same time for 25 seconds

2, Sysbench

Installation:

You can download tar zxf sysbench-0.4.12.tar.gzcd sysbench-0.4.12./autogen.sh./from http://sourceforge.net/projects/sysbench/ Configure && make && make Installstrip/usr/local/bin/sysbench

Installation time may be error, and later Baidu found a good article http://blog.csdn.net/icelemon1314/article/details/7004955 afraid later find, also paste over it

1. If MySQL is not the default path to install, Then you need to load the MySQL installation path 2 by specifying the--with-mysql-includes and--with-mysql-libs parameters. If you get an error: /libtool: line 838: x--tag=cc: command not found. /libtool: line 871: libtool: ignoring unknown tag : command not  found. /libtool: line 838: x--mode=link: command not found. /libtool: line 1004: *** warning: inferring the mode of operation  is deprecated.: command not found. /libtool: line 1005: *** future versions of libtool will require  --mode=mode be specified.: command not found. /libtool: line 2231: x-g: command not found. /libtool: line 2231: x-o2: command not found 

Then execute the following root directory:autogen.sh file, then re-configure && make && make install

3. If you have an error:

Sysbench:error while loading shared libraries:libmysqlclient.so.18:cannot open Shared object file:no such file or dire Ctory so executed under: N-S/usr/local/mysql5.5/mysql/lib/libmysqlclient.so.18/usr/lib64/

4. If autogen.sh is executed, the following error is reported:

./autogen.sh:line 3:aclocal:command not found then you need to install a software: Yum install Automake

Then you need to add a parameter: find: Ac_prog_libtool to annotate it, then add Ac_prog_ranlib

function: Simulate concurrency, can perform cpu/memory/thread/io/database and other aspects of performance testing. Database currently supports Mysql/oracle/postgresql

Pros: You can specify the size of your test data, you can test read and write performance individually, or you can test the performance of read-write mixes.

Insufficient: test, due to network reasons, the test is very slow, but the final result is very good, concurrent support is very high, so I feel is not too accurate. Of course, maybe I didn't understand the principle.

How to use:

Preparing data

Sysbench--test=oltp--mysql-table-engine=myisam--oltp-table-size=400000--mysql-db=dbtest2--mysql-user=root-- MYSQL-HOST=192.168.1.101--mysql-password=pwd Prepare

Perform tests

sysbench --num-threads=100 --max-requests=4000 --test= Oltp --mysql-table-engine=innodb --oltp-table-size=400000 --mysql-db=dbtest1 --mysql-user= Root --mysql-host=192.168.1.101 --mysql-password=pwd run 

Sysbench 0.4.12: multi-threaded system evaluation benchmarkno db drivers  specified, using mysqlrunning the test with following options:number  of threads: 100doing oltp test. running mixed oltp testusing special distribution  (12 iterations, 1  pct of values are returned in 75 pct cases) Using  "BEGIN"  for starting transactionsUsing auto_inc on the id columnMaximum  number of requests for oltp test is limited to 4000threads  started! done.oltp test statistics:queries performed:read: 56014write: 20005other:  8002total: 84021transactions: 4001  (259.14 per sec.) deadlocks: 0  (0.00 per sec.) read/write requests: 76019  (4923.75 pEr sec.) other operations: 8002  (518.29 per sec.) test execution summary:total time: 15.4393stotal number of events:  4001total time taken by event execution: 1504.7744per-request statistics: Min: 33.45msavg: 376.10msmax: 861.53msapprox. 95 percentile: 505.65msthreads  fairness:events  (Avg/stddev): 40.0100/0.67execution time  (Avg/stddev):  15.0477/ 0.22

3, Tpcc-mysql

Installation:

If download source from the original website is troublesome, need tool, register, generate certificate and so on. Here is a download package for Http://blog.chinaunix.net/blog/downLoad/fileid/8532.htmlexport C_include_path=/usr/include/mysqlexport Path=/usr/bin: $PATHexport ld_library_path=/usr/lib/mysqlcd/tmp/tpcc/srcmake the TPCC command-line tool is then generated under/tmp/tpcc-mysql TPCC _load, Tpcc_start

Role: Test the overall performance of MySQL database

Advantages: Conforms to the TPCC standard, has the standard method, simulates the real trading activity, the result is more reliable.

Insufficient: Can not test the performance of reading or writing alone, for some query-based or write-only applications, there is no such a big significance.

How to use:

Loading data

Create library mysql>create database tpcc10; CREATE TABLE: Shell>mysql TPCC10 < create_table.sql add foreign key: Shell>mysql TPCC10 < add_ Fkey_idx.sql

Load data:

1. Single Process load: shell>./tpcc_load 192.168.11.172 tpcc10 root pwd 300| Host | | Database | | user | | password | | Warehouse|2, concurrent loading: (recommended, but need to be modified) shell>./load.sh tpcc300 300| Database | | Warehouse|3, test./tpcc_start-h192.168.11.172-d tpcc-u root-p ' pwd '-w 10-c 10-r 10-l 60-i 10-f/MNT/HGFS/MYSQL/TPCC 100_2013522.txt

 ## #easy ### tpc-c load generator ********** option h with value  ' 192.168.11.172 ' option d  with value  ' TPCC ' option u with value  ' root ' option p with value   ' pwd ' option w with value  ' 1 ' option c with value  ' option  r with value  ' option l with value  ' Option i with  value  ' option f with value  '/mnt/hgfs/mysql/tpcc100_2013522.txt ' < parameters>[server]: 192.168.11.172[port]: 3306[dbname]: tpcc[user]: root[pass]:  pwd[warehouse]: 1[connection]: 100[rampup]: 120  (sec.) [measure]: 60  (sec.) Ramp-up time. (120 sec.) measuring start.10, 245 (+): 10.923|28.902, 242 (0): 3.677|10.796, 25 (0): 1.579|2.198, 24 (0): 17.451|21.047,&NBSP;25 (4): 19.999|33.77620, 262 (+): 9.070|11.917, 263 (0): 3.407|4.716, 26 (0): 1.608| 1.776, 27 (0): 11.347|16.408, 26 (1): 19.166|21.01830, 247 (+): 11.130|14.131, 241 (0): 2.367| 2.654, 24 (0): 0.960|1.095, 24 (0): 9.308|16.538, 25 (3): 19.999|24.87440, 237 (69): 11.840|13.009 ,  239 (1): 3.638|7.245, 24 (0): 0.692|0.773, 23 (0): 8.756|10.456, 23 (1):19.527|20.49550,  252 (+): 10.548|17.925, 256 (0): 2.652|2.893, 26 (0): 1.177|3.579, 27 (0): 14.648|15.018, 25 (4) : 19.999|26.39860, 256:9.323|11.328, 251 (1): 3.895|5.380, 25 (0): 0.785|1.542, 25 (0) : 11.382|15.829, 26 (0): 18.481|18.855stopping threads ..... ..... ..... ..... ..... ..... ............ ........... ..... ..... ..... ..... ..... .............. .......... <raw  results>[0] sc:1041 lt:458 rt:0 fl:0 [1] sc:1490 lt:2 rt:0  fl:0 [2] sc:150 lt:0 rt:0 fl:0 [3] sc:150 lt:0 rt:0 fl:0 [4] sc:137 lt:13 rt:0 fl:0 in 60  Sec.<raw results2 (Sum ver.) >[0] sc:1041 lt:458 rt:0 fl:0 [1] sc:1490 lt:2 rt:0 fl:0  [2] sc:150 lt:0 rt:0 fl:0 [3] sc:150 lt:0 rt:0 fl:0 [4]  sc:137 lt:13 rt:0 fl:0 <Constraint Check>  (all must be  [ok]) [transaction percentage]payment: 43.36%  (>=43.0%)  [OK]Order-Status:  4.36%  (>= 4.0%)  [OK]Delivery: 4.36%  (>= 4.0%)  [OK]Stock-Level:  4.36%  (>= 4.0%)  [OK][response time  (at least 90% passed)]new-order:  69.45% [NG] *Payment: 99.87% [OK]Order-Status: 100.00% [OK]Delivery:  100.00% [ok]stock-level: 91.33% [ok]<tpmc>1499.000 tpmc

For the concept of TPCC, see http://baike.baidu.com/view/2776305.htm

Here is the test case introduction paste

The model used in the TPC-C test is a large wholesale sales company with several commodity warehouses distributed in different regions. When the business expands, the company will add a new warehouse.

Each warehouse is responsible for the delivery of 10 points of sale, each of which serves 3,000 customers, with an average of 10 products per order per customer

About 1% of all orders are not stocked in the warehouses they directly belong to, and must be supplied from warehouses in other regions. At the same time, each warehouse to maintain the company's sales of 100000 kinds of goods inventory records.

4. The MySQL Benchmark Suite

This test tool is distributed with MySQL bindings, based on the Perl language and two modules: DBI and benchmark. If necessary, it supports all DBI-driven databases. You can modify the bench-init.pl options to suit your needs. It is also a reminder that it does not support multiple CPUs.

When testing, execute the run-all-tests script, the specific command options see the README.

5. MySQL Super-smack

This is a powerful and widely acclaimed stress testing tool that supports MySQL and PostgreSQL.

http://jeremy.zawodny.com/mysql/super-smack/

The installation is simple, please read the guide document in the catalogue carefully first.

Preparing test Data

When doing tests, it's best to use your own data. Because the actual data is used, the test becomes nearly realistic and objective.

Configuration

Smack file settings, it looks very simple.

6. Mybench:a Home-grown Solution

Mybench is an easy-to-extend test tool based on the Perl language.

http://jeremy.zawodny.com/mysql/mybench/

MySQL several performance testing tools to use