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

This article mainly gives you a brief introduction to the use of several common tools for mysql performance testing. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that the use of several common tools for mysql performance testing can bring you some practical help.

1 、 mysqlslap

Installation: easy, just install mysql

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

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

-- engines represents the engine to be tested, and there can be multiple, separated by delimiters.

-- iterations represents how many times to run these tests.

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

-- auto-generate-sql-load-type stands for testing whether to 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-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-h292.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 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.

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

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

3 、 tpcc-mysql

Installation:

If it is troublesome to download the source code from the original website, you need tools, registration, generating certificates, and so on. Here is a download package 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

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

Mysql performance testing of several common tools to use to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.