MySQL several performance testing tools to use

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

--concurrency represents the number of concurrent, multiple can be separated by commas, concurrency=10,50,100, the number of concurrent connection threads are 10, 50, 100 concurrency respectively.

--engines represents the engine to be tested, can have multiple, separated by delimiters.
--iterations represents how many times to run these tests.
The--auto-generate-sql represents a SQL script generated by the system itself.
Whether the--auto-generate-sql-load-type represents a read or write or a mixture of the two (read,write,update,mixed)
--number-of-queries represents the total number of queries to run. The number of queries that each client runs can be calculated using the total number of queries/concurrency.
The--debug-info represents the additional output of CPU and memory related information.
--number-int-cols: Number of int fields to create test table
--auto-generate-sql-add-autoincrement: Represents the automatic addition of auto_increment columns to the generated table, starting with version 5.1.18
--number-char-cols the number of char fields that created the test table.
The schema of the--create-schema test Schema,mysql is database.
--query uses a custom script to perform tests, such as a custom stored procedure or SQL statement that can be invoked to perform the test.
--only-print If you just want to print and see what the SQL statement is, you can use this option.

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

Benchmark
Average number of seconds to run all queries:25.225 seconds
Minimum number of seconds to run all queries:25.225 seconds
Maximum number of seconds to run all queries:25.225 seconds
Number of clients running queries:100
Average number of queries per client:0

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

2, Sysbench
Installation:
Can be downloaded from http://sourceforge.net/projects/sysbench/
Tar zxf sysbench-0.4.12.tar.gz
CD sysbench-0.4.12
./autogen.sh
./configure && make && make install
Strip/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 installation, you will need to load the MySQL installation path by specifying the--with-mysql-includes and--with-mysql-libs parameters
2. If you have 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 would 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
Then perform the following:
N-/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 Benchmark

No DB drivers specified, using MySQL
Running the test with following options:
Number of threads:100

Doing OLTP test.
Running Mixed OLTP Test
Using Special Distribution (iterations, 1 pct of values is returned in the PCT cases)
Using "BEGIN" for starting transactions
Using auto_inc on the ID column
Maximum number of requests for OLTP test are limited to 4000
Threads started!
Done.

OLTP Test Statistics:
Queries performed:
read:56014
write:20005
other:8002
total:84021
transactions: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.4393s
Total number of events:4001
Total time taken by event execution:1504.7744
Per-request Statistics:
Min:33.45ms
Avg:376.10ms
Max:861.53ms
Approx. percentile:505.65ms

Threads Fairness:
Events (Avg/stddev): 40.0100/0.67
Execution 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.html
Export C_include_path=/usr/include/mysql
Export Path=/usr/bin: $PATH
Export Ld_library_path=/usr/lib/mysql
Cd/tmp/tpcc/src
Make
The TPCC command-line tool Tpcc_load, Tpcc_start, is then generated under/tmp/tpcc-mysql.

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 a library
Mysql>create database tpcc10;
To create a table:
Shell>mysql TPCC10 < Create_table.sql
To add a 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 modify)
shell>./load.sh tpcc300 300
| database | | warehouse|
3. Testing
./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/tpcc100_2013522.tx T
***************************************
# # #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 ' 100 '
Option r with value ' 120 '
Option L with value ' 60 '
Option I with value ' 10 '
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]: (sec.)
[Measure]: (sec.)

Ramp-up time. (sec.)

Measuring START.

10, 245 (77): 10.923|28.902, 242 (0): 3.677|10.796, 25 (0): 1.579|2.198, 24 (0): 17.451|21.047, 25 (4): 19.999|33.776
20, 262 (75): 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.018
30, 247 (90): 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.874
40, 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.495
50, 252 (69): 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.398
60, 256 (78): 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.855

Stopping 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 the 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%) [OK]
delivery:4.36% (>= 4%) [OK]
stock-level:4.36% (>= 4%) [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/

Reference: Several common MySQL performance testing tools

Related: Several common MySQL performance testing tools

MySQL several performance testing tools to use