Several performance testing tools for MySQL

MySQL several performance testing tools to use

First, Mysqlslap

Installation: 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

--engines: Represents the engine to be tested, can have multiple, separated by delimiters.

--iterations: Represents how many times to run these tests.

--auto-generate-sql: Represents the SQL script generated by the system itself.

--auto-generate-sql-load-type: Represents whether to test for reading or writing or for mixing (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-u root-p--concurrency=100--iterations=1--auto-generate-sql--auto-generate-sql-add-autoincrement-- Auto-generate-sql-load-type=mixed--engine=myisam--number-of-queries=10

Or:

Specify the database and SQL statements:

Mysqlslap-h localhost-p 123456--concurrency=100--iterations=1--create-schema= ' mysql '--query= ' select * from user; '-- Number-of-queries=10-u root-p

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

Again such as:

mysqlslap-uroot-p123456--concurrency=100--iterations=1--engine=myisam--create-schema= ' haodingdan112 '--query= ' SELECT * from order_boxing_transit where id = Ten '--number-of-queries=1--debug-info

Second, 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

Third, Tpcc-mysql

Installation:

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.

Iv. 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.

V. 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.

Liu, mybench:a home-grown solution

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

Several performance testing tools for MySQL