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

Transfer connection http://blog.itpub.net/22996654/viewspace-2655331/

Sysbench stress testing environment:

Sysbench server:172.16.7.105

Stress test database server: MHA architecture, 172.16.7.100 (master node), 172.16.7.101, 172.16.7.102

MySQL deployed at 172.16.7.105 (install MySQL5.7.26) directory: / opt/mysql/

Main pressure test 172.16.7.100 main node

172.16.7.100 configuration:

MySQL5.7.26

CPU:4 kernel

Memory: 8GB

Innodb_buffer_pool_size = 28G

Innodb_log_file_size = 512m

Innodb_log_files_in_group = 2

Innodb_log_buffer_size=16M

MySQL8.0.17

CPU:4 kernel

Memory: 8GB

Innodb_buffer_pool_size = 28G

Innodb_log_file_size = 512m

Innodb_log_files_in_group = 2

Innodb_log_buffer_size=16M

Front work

1. Complete the installation of MySQL

two。 Complete the installation of dynamic library files

Yum install-y openssl libtool

# errors that may be encountered

1. Before make, you need to modify the lib library, otherwise you will get an error: / usr/bin/ld: cannot find-lmysqlclient_r

Because the lib pointed to in the installation is / opt/mysql/lib

Cd / opt/mysql/lib

Ln-s libmysqlclient.so.20.3.9 libmysqlclient_r.so

There is no obvious error in 2.make, execution. / sysbench Times error

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

Ln-s / opt/mysql/lib/libmysqlclient.so.18 / usr/lib64

If the error is reported as

/ usr/local/sysbench/bin/sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

Then execute it.

Ln-s / opt/mysql/lib/libmysqlclient.so.20 / usr/lib64

Download sysbench:

Wget http://imysql.com/wp-content/uploads/2014/09/sysbench-0.4.12-1.1.tgz

Deployment:

Tar-xf sysbench-0.4.12-1.1.tgz

Mv sysbench-0.4.12-1.1 sysbench

Cp-r sysbench / usr/local/sysbench

Cd / usr/local/sysbench

. / autogen.sh

# generate configure file

. / configure-prefix=/usr/local/sysbench/-with-mysql=/opt/mysql/-with-mysql-includes=/opt/mysql/include/-with-mysql-libs=/opt/mysql/lib/

Note that the includes here must be the include directory corresponding to mysql, and libs must be the lib directory corresponding to mysql.

Complete the installation

Make&&make install

(confirm that there is no error)

Verify installation

/ usr/local/sysbench/bin/sysbench-- help

If the following words appear, the deployment is successful

[root@GTID01 bin] # / usr/local/sysbench/bin/sysbench-- help

Missing required command argument.Usage: sysbench [general-options]... Test= [test-options]... Command

General options:

-- num-threads=N number of threads to use [1]

-- max-requests=N limit for total number of requests [10000]

...

Increase environmental variables

Echo-e "export PATH=\ $PATH:/usr/local/sysbench/bin" > > / etc/profile

Source / etc/profile

Use sysbench for testing

First go to the tested library to build the library, build users, and authorize (here, use 105 to test 100 libraries)

Mysql-h 172.16.7.100-uroot-p

...

Create database sbtest; # # because what is set up here in the lua script is the sbtest library, and the library name needs to be consistent with the content in the script

Create user tpcc@'%' identified by 'tpcc'

Grant all privileges on sbtest.* to tpcc@'%'

Flush privileges

MySQL8.0 needs to be added:

Alter user tpcc@'%' identified with 'mysql_native_password' by' tpcc'

An error operation will occur (after changing mysiam in oltp.lua to innodb, the table will not be found)

Cd / usr/local/sysbench/sysbench/tests/db/

Cp oltp.lua oltp_innodb.lua

Sed-I's Universe MyisamUniverse innodbUniverse g'/ usr/local/sysbench/sysbench/tests/db/oltp_innodb.lua

Pressure test of multi-meter

Execute prepare

Sysbench-mysql-host=172.16.7.100-mysql-port=3306-mysql-user=tpcc-mysql-password=tpcc-test=/usr/local/sysbench/sysbench/tests/db/oltp.lua-oltp_tables_count=10-oltp-table-size=100000-rand-init=on prepare

Execute run

Sysbench-mysql-host=172.16.7.100-mysql-port=3306-mysql-user=tpcc-mysql-password=tpcc-test=/usr/local/sysbench/sysbench/tests/db/oltp.lua-oltp_tables_count=10-oltp-table-size=100000-num-threads=4-oltp-read---report-interval=5-rand-type=uniform max-time=30 max-requests=0 max-requests=0 percentile=99 run

-- number of num-threads=4 / / threads is 4

-- max-time=30 / / Test time is 30s

-- report-interval=5 / / report printing cycle is 5s

-- oltp-read- >

-- max-requests=0 / / the maximum number of execution times is not limited here, but only by max-time

MySQL8.0, I use port 30001.

MySQL5.7 uses 32 threads to stress test the results:

Sysbench 0.5: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 32

Report intermediate results every 5 second (s)

Random number generator seed is 0 and will be ignored

Threads started!

[5s] threads: 32, tps: 842.19, reads/s: 11867.59, writes/s: 3376.34, response time: 172.23ms

[10s] threads: 32, tps: 1013.21, reads/s: 14187.90, writes/s: 4053.03, response time: 96.36ms

[15s] threads: 32, tps: 1195.60, reads/s: 16709.15, writes/s: 4778.79, response time: 63.71ms (99%)

[20s] threads: 32, tps: 1095.19, reads/s: 15341.21, writes/s: 4383.35, response time: 87.04ms

[25s] threads: 32, tps: 1111.01, reads/s: 15567.40, writes/s: 4446.26, response time: 78.19ms (99%)

[30s] threads: 32, tps: 1045.20, reads/s: 14628.00, writes/s: 4174.40, response time: 85.90ms

OLTP test statistics:

Queries performed:

Read: 441616

Write: 126176

Other: 63088

Total: 630880

Transactions: 31544 (1050.71 per sec.)

Deadlocks: 0 (0.00 per sec.)

Read/write requests: 567792 (18912.78 per sec.)

Other operations: 63088 (2101.42 per sec.)

General statistics:

Total time: 30.0216s

Total number of events: 31544

Total time taken by event execution: 954.2759s

Response time:

Min: 9.17ms

Avg: 30.25ms

Max: 389.96ms

Approx. 99 percentile: 94.36ms

Threads fairness:

Events (avg/stddev): 985.7500Universe 40.24

Execution time (avg/stddev): 29.8211 + 0.03

Read: 441616 / / Total number of reads, select statement

Write: 126176 / / total number of writes, insert, delete, update statements

Other: 63088 / / other statements, such as commit, etc

Total: 630880 / / Total number of executive statements

Transactions: 31544 (1050.71 per sec.) / / Total number of things (★ deals with things per second: TPS ★)

Read/write requests: 567792 (18912.78 per sec.) / / number of read and write requests (★ reads and writes per second: QPS ★)

Other operations: 63088 (2101.42 per sec.) / / execution per second of other operations

General statistics:

Total time: 30.0216s / / Total time

Total number of events:

31544 / / ★ Total number of things ★

Total time taken by event execution: 954.2759s / / all transactions take time to add up (regardless of parallelism)

Response time: / / answer time

Min: 9.17ms / / minimum

Avg: 30.25ms / / average

Max: 389.96ms / / Max

Approx. 99 percentile: 94.36ms / / 99% statement execution time

Threads fairness: / / Thread fairness

Events (avg/stddev): 985.7500Universe 40.24

Execution time (avg/stddev): 29.8211 + 0.03

Several test results that need to be focused on

Total number of things in ★, transactions per second TPS,QPS, time statistics (maximum, minimum, average, above 99% statement response time) ★

The ability to judge the database by sysbench results is mainly determined by TPS.

Perform cleanup

Sysbench-mysql-host=172.16.7.100-mysql-port=3306-mysql-user=tpcc-mysql-password=tpcc-mysql-db=sbtest-oltp-tables-count=10-oltp-table-size=100000-num-threads=16-max-requests=0-max-time=30 report-interval=1 test=/usr/local/sysbench/sysbench/tests/db/oltp.lua cleanup

Sysbench 0.5: multi-threaded system evaluation benchmark

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

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.