Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL pressure testing tool Sysbench1.0 how to use, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

I. brief introduction

The commonly used versions of Sysbench stress testing tools are 0.4 (older), 0.5 (said to be an unofficial open source version) and 1.0 (the latest official open source version). There is little difference in system stress testing between different versions. However, there are great differences in the pressure test parameters and results of the database. This paper introduces the installation and use of the sysbench pressure test MYSQL database.

Second, pressure testing environment

Hardware environment:

Testing machine

Dell PowerEdge R730xd

CPU

Intel (R) Xeon (R) CPU E5-2620 v3 @ 2.40GHz (2 physical CPU, each 6 cores, 2 threads per core, total 24processor)

Memory

128G

Raid card

PERC H730P Mini

Memory Size: 2048MB

Raid level

Raid-5

File system

Xfs

Hard disk

4T SAS * 12

Software environment:

Operating system

Red Hat Enterprise Linux Server release 6.5 (Santiago)

Kernel

2.6.32-431.el6.x86_64

MYSQL version

5.7.17

Pressure testing tool

Sysbench 1.0.3

Third, MYSQL core parameters

IV. Pressure test model

Testing tool

Sysbench

Number of test records

1000000

Test mode

Complex

Run time (seconds)

three hundred

Maximum number of requests

100000

Number of concurrent threads

one thousand

5. MYSQL pressure testing process

Taking oltp_read_write.lua pressure test as a case

1. Sysbench2.0 pressure test tool installation-please refer to the Sysbench of the pressure test tool (1 _ system pressure test)

2. Preparation of sysbench oltp pressure test data

Sysbench/ usr/local/sysbench/share/sysbench/oltp_read_write.lua-mysql-host=10.1.0.10-mysql-port=3306-mysql-user=dbuser-mysql-password=abc.1234-mysql-db=dbtest-tables=10-table-size=1000000-report-interval=10 threads=1000-time=300 prepare

Click (here) to collapse or open

# sysbench/ usr/local/sysbench/share/sysbench/oltp_read_write.lua-- mysql-host=10.1.0.10-- mysql-port=3306-- mysql-user=dbuser-- mysql-password=abc.1234-- mysql-db=dbtest-- tables=10-- table-size=1000000-- report-interval=10-- threads=1000-- time=300 prepare

Sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest7'...Creating table' sbtest6'...

Creating table 'sbtest2'...

Creating table 'sbtest10'...

Creating table 'sbtest8'...

Creating table 'sbtest1'...

Creating table 'sbtest4'...

Creating table 'sbtest3'...

Creating table 'sbtest5'...

Creating table 'sbtest9'...

Inserting 1000000 records into 'sbtest3'

Inserting 1000000 records into 'sbtest8'

Inserting 1000000 records into 'sbtest2'

Inserting 1000000 records into 'sbtest6'

Inserting 1000000 records into 'sbtest10'

Inserting 1000000 records into 'sbtest7'

Inserting 1000000 records into 'sbtest1'

Inserting 1000000 records into 'sbtest5'

Inserting 1000000 records into 'sbtest9'

Inserting 1000000 records into 'sbtest4'

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest7'...

Creating a secondary index on 'sbtest5'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest9'...

Creating a secondary index on 'sbtest6'...

Creating a secondary index on 'sbtest10'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest8'...

2. Sysbench oltp stress test begins.

Sysbench/ usr/local/sysbench/share/sysbench/oltp_read_write.lua-mysql-host=10.1.0.10-mysql-port=3306-mysql-user=dbuser-mysql-password=abc.1234-mysql-db=dbtest-tables=10-table-size=1000000-report-interval=10 threads=1000-time=300 run

Click (here) to collapse or open

# sysbench/ usr/local/sysbench/share/sysbench/oltp_read_write.lua-- mysql-host=10.1.0.10-- mysql-port=3306-- mysql-user=dbuser-- mysql-password=abc.1234-- mysql-db=dbtest-- tables=10-- table-size=1000000-- report-interval=10-- threads=1000-- time=300 run

Sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 1000

Report intermediate results every 10 second (s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

# # the pressure test report data generated every 10s below is also quite different from the previous version

[10s] thds: 1000 tps: 1290.73 qps: 27199.93 (r/w/o: 19259.14 tps) lat (ms,95%): 2680.11 err/s: 1.30 reconn/s: 0.00

[20s] thds: 1000 tps: 1291.33 qps: 25644.68 (r/w/o: 17913.30 tps) lat (ms,95%): 2120.76 err/s: 2.70 reconn/s: 0.00

[30s] thds: 1000 tps: 1749.09 qps: 34514.35 (r/w/o: 24078.49 tps) lat (ms,95%): 1258.08 err/s: 5.10 reconn/s:

...

...

[280s] thds: 1000 tps: 1608.70 qps: 32380.81 (r/w/o: 22645.91 reconn/s 6509.20) lat (ms,95%): 2680.11 err/s: 8.30 reconn/s: 0.00

[290s] thds: 1000 tps: 1441.71 qps: 28987.87 (r/w/o: 20274.92 tps) lat (ms,95%): 1803.47 err/s: 6.50 reconn/s: 0.00

[300s] thds: 1000 tps: 1794.92 qps: 36050.03 (r/w/o: 25318.80 tps) lat (ms,95%): 3639.94 err/s: 14.80 reconn/s: 0.00

SQL statistics:

Queries performed:

Read: 7030618 # # Total number of select

Write: 2000604 # # Total number of update/insert/delete statements

Other: 1001648 # # contains commit/unlock tables and other mutex statements

Total: 10032870

Transactions: 499461 (1662.65 per sec.) # # TPS

Queries: 10032870 (33398.37 per sec.) # # QPS

Ignored errors: 2726 (9.07 per sec.) # # number of errors ignored

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 300.3980s # # Total time of pressure test

Total number of events: 499461 # # Total number of events, which is generally the same as transactions

Latency (ms):

Min: 2.57

Avg: 601.02 # #% 95 statement RT, average response time (in milliseconds). Percentage can be adjusted by-percentile=100

Max: 13302.01

95th percentile: 1739.68

Sum: 300186288.55

Threads fairness:

Events (avg/stddev): 499.4610 take 27.20

Execution time (avg/stddev): 300.1863Accord0.07

Note: the data you need to focus on are total execution time, TPS: if you use-skip-trx=on, the number of transactions is always 0, and you need to use total number of events to remove the total time to get tps (in fact, it can be divided into read tps and write tps), QPS and RT (average response time), as well as system stress test indicators such as CPU, memory, IO, etc.

3. Sysbench oltp pressure test data cleaning

Sysbench/ usr/local/sysbench/share/sysbench/oltp_read_write.lua-mysql-host=10.1.0.10-mysql-port=3306-mysql-user=dbuser-mysql-password=abc.1234-mysql-db=dbtest-tables=10-table-size=1000000-report-interval=10 threads=1000-time=300 cleanup

Click (here) to collapse or open

# sysbench/ usr/local/sysbench/share/sysbench/oltp_read_write.lua-- mysql-host=10.1.0.10-- mysql-port=3306-- mysql-user=dbuser-- mysql-password=abc.1234-- mysql-db=dbtest-- tables=10-- table-size=1000000-- report-interval=10-- threads=1000-- time=300 cleanup

Sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Dropping table 'sbtest5'...

Dropping table 'sbtest6'...

Dropping table 'sbtest7'...

Dropping table 'sbtest8'...

Dropping table 'sbtest9'...

Dropping table 'sbtest10'...

VI. Appendix of MYSQL pressure test

1. Stress test lua script

Bulk_insert.lua

Oltp_common.lua

Oltp_delete.lua

Oltp_insert.lua

Oltp_point_select.lua

Oltp_read_only.lua

Oltp_read_write.lua

Oltp_update_index.lua

Oltp_update_non_index.lua

Oltp_write_only.lua

Select_random_points.lua

Select_random_ranges.lua

2. Other important parameters

-- histogram [= on | off] # # whether to enable generation of histograms. Default is off.

-- percentile=N # # adjusts the percentage of response time. Default is 95.

-- events=N # # the maximum request value. Default is 0, which means there is no maximum event limit. Alias is-- max-requests=N.

-- db-driver=STRING # # Database-driven mysql/oracle/pg

Temporarily simple to do the new version of the test use, and then improve the stress test script, pressure test results collation.

Thank you for reading this article carefully. I hope the article "how to use Sysbench1.0 in MySQL" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.