Shulou(Shulou.com)06/01 Report--

The following brings you about which tools can be used in mysql performance testing. I'm sure you've read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I'm sure you'll get something after reading which tools you can use in mysql performance testing.

1. Mysqlslap

Installation: you can have it with mysql installed.

Function: simulate the performance of the concurrent test database.

Advantages: simple and easy to use.

Deficiency: the scale of generated data cannot be specified, and it is not clear in the testing process to do tests for 100, 000 or million-level data, so it is not suitable for comprehensive testing, and it is more suitable for optimized testing of a single sql for existing databases.

How to use it:

You can use mysqlslap-- help to show how to use it:

Default options are 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. There can be multiple, separated by delimiters.

-- iterations: represents how many times you want to run these tests.

-- auto-generate-sql: stands for testing with a SQL script generated by the system itself.

-- auto-generate-sql-load-type: represents whether to test read or write or a mix of both (read,write,update,mixed)

-- number-of-queries: represents the total number of queries to run. The number of queries run by each customer can be calculated as the total number of queries / concurrency.

-- debug-info represents additional output of CPU and memory related information.

-- number-int-cols: the number of int fields used to create the test table

-- auto-generate-sql-add-autoincrement: indicates that auto_increment columns are automatically added to the generated table, starting with version 5.1.18

-- the number of char fields in which number-char-cols creates the test table.

-- the schema in the schema,MySQL tested by create-schema is database.

-- query uses custom scripts to execute tests, such as calling a custom stored procedure or sql statement to execute the test.

-- only-print can use this option if you just want to print to see what the SQL statement is.

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 has been done, you can 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

This shows that it takes 25 seconds for 100 clients to run at the same time.

Another example is:

Mysqlslap-uroot-p123456-- concurrency=100-- iterations=1-- engine=myisam-- create-schema='haodingdan112'-- query='select * From order_boxing_transit where id = 10'--number-of-queries=1-- debug-info

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

There may be an error during installation, and then baidu found a good text. Http://blog.csdn.net/icelemon1314/article/details/7004955 is afraid that he won't find it later, so post it, too.

1. If mysql is not installed in the default path, you need to load the mysql installation path by specifying the-- with-mysql-includes and-- with-mysql-libs parameters

two。 If an error is reported:

.. / 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: Xmurg: command not found

.. / libtool: line 2231: X-O2: command not found

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

3. If an error is reported:

Sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

Then execute:

N-s / usr/local/mysql5.5/mysql/lib/libmysqlclient.so.18 / usr/lib64/

4. If you execute autogen.sh, 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 annotate it, and then add AC_PROG_RANLIB

Function: simulates concurrency and can perform performance tests in CPU/ memory / thread / IO/ database and other aspects. The database currently supports MySQL/Oracle/PostgreSQL

Advantages: you can specify the size of the test data, you can test the performance of reading and writing separately, and you can also test the performance of mixed reading and writing.

Deficiency: when testing, due to network reasons, the test is very slow, but the final result is very good, concurrency support is very high, so it gives me the impression that it is not very accurate. Of course, maybe I don't understand the principle.

How to use it:

Prepare 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 a test

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 (12 iterations, 1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is 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. 95 percentile: 505.65ms

Threads fairness:

Events (avg/stddev): 40.0100amp 0.67

Execution time (avg/stddev): 15.0477 + 0.22

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

Then the tpcc command line tools tpcc_load and tpcc_start will be generated under / tmp/tpcc-mysql

Purpose: to test the overall performance of the mysql database

Advantages: in line with tpcc standards, there are standard methods, simulation of real trading activities, the results are more reliable.

Deficiency: read or write performance cannot be tested separately, so it doesn't make so much sense for query-based or write-only applications.

How to use it:

Load data

Create a library

Mysql > create database tpcc10

Create a table:

Shell > mysql tpcc10

< create_table.sql 添加外键： shell>

Mysql tpcc10

< add_fkey_idx.sql 加载数据: 1、单进程加载： shell>

. / tpcc_load 192.168.11.172 tpcc10 root pwd 300

| | Host | | Database | | user | | password | | warehouse |

2. Concurrent loading: (recommended, but needs to be modified)

Shell >. / load.sh tpcc300 300

| | Database | | warehouse |

3. Testing

. / tpcc_start-h292.168.11.172-d tpcc-u root-p 'pwd'-w 10-c10-r 10-l 60-I 10-f / mnt/hgfs/mysql/tpcc100_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 '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'

[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 (77): 10.923 | 28.902 (0): 3.677 | 10.796, 25 (0): 1.579 | 2.198, 24 (0): 17.451 | 21.047, 25 (4): 19.999 | 33.776

20262 (75): 9.070 | 11.917 (0): 3.407 | 4.716, 26 (0): 1.608 | 1.776, 27 (0): 11.347 | 16.408, 26 (1): 19.166 | 21.018

30247 (90): 11.130 | 14.131: 2.367 | 2.654, 24 (0): 0.960 | 1.095, 24 (0): 9.308 | 16.538, 25 (3): 19.999 | 24.874

40237 (69): 11.840 | 13.009 (1): 3.638 | 7.245, 24 (0): 0.692 | 0.773, 23 (0): 8.756 | 10.456, 23 (1): 19.527 | 20.495

50252 (69): 10.548 | 17.925 (0): 2.652 | 2.893, 26 (0): 1.177 | 3.579, 27 (0): 14.648 | 15.018, 25 (4): 19.999 | 26.398

60256 (78): 9.323 | 11.328 (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.

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

[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

(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.005% [OK]

Delivery: 100.005% [OK]

Stock-Level: 91.33% [OK]

1499.000 TpmC

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

Here, post the introduction of the test case.

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

Each warehouse is responsible for supplying 10 points of sale, each of which serves 3000 customers, with an average of 10 products per order submitted by each customer.

About 1% of all orders are not in stock in the warehouse to which they directly belong and must be supplied by warehouses in other areas. At the same time, each warehouse maintains inventory records of 100000 items sold by the company.

IV. The MySQL Benchmark Suite

This testing tool is released with the MySQL binding and is based on the Perl language and two of its modules: DBI and Benchmark. If necessary, it supports all databases that support DBI drivers. You can modify the options for bench-init.pl to suit your needs. Another reminder is that it does not support multi-CPU.

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

5. MySQL super-smack

This is a powerful and award-winning stress testing tool that supports MySQL and PostgreSQL.

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

The installation is easy. Please read the instructions in the directory carefully first.

Preparing test data

When doing tests, it is best to use your own data. Because of the use of real data, the test becomes close to reality and objectivity.

Configuration

Smack file settings, looks very simple.

6. MyBench: a Home-Grown Solution

MyBench is a testing tool based on the Perl language that is easy to extend.

Do you think it's what you want about which tools you can use for mysql performance testing above? If you want to know more about it, you can continue to follow our industry information section.

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.