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

Sysbench performance pressure test and mysql performance pressure test

First, Sysbench is a modular, cross-platform, multi-threaded benchmark testing tool, which is mainly used for database load under different system parameters.

Main test mode

Cpu performance

Disk io performance

Scheduler performance

Memory allocation and transfer speed

Posix thread performance

Database performance (OLTP benchmark)

Find the maximum prime in the range {the shorter the time, the better.

Iops {the bigger the better} in different scenarios

Thread execution concurrently, cycle response semaphore time spent {less, better}

Transmit a certain amount of data throughput at different block sizes {the bigger the better}

The time it takes for a concurrent thread to apply for a mutex loop a certain number of times at the same time {as little as possible}

The higher the qps and tps, the better

At present, sysbench mainly supports the three databases of MYSQL,pgsql,oracle.

Second, install the test environment: centos6.8 mysql5.7.19

Third, install sysbench-0.5

Source code address https://github.com/BoobooWei/sysbench/archive/master.zip

Download to the computer desktop, then xshell connect to the server, download and upload software tools

# cd / usr/local/src/

# yum install lrzsz-y

# rz # Select the downloaded software for upload

# unzip sysbench-master.zip

# cd sysbench-master

# yum install-y automake libtool-y

#. / autogen.sh

Error report: autom4te: / usr/bin/m4 failed with exit status: 63

Aclocal: autom4te failed with exit status: 63

The display version is too low.

# View version information: yum info autoconf

# View path: which autoconf

# remove the earlier version: rpm-qf / usr/bin/autoconf rpm-e-- nodeps autoconf-2.63

# cd / usr/local/src

# download the new version of software: wget http://ftp.gnu.org/gnu/autoconf/autoconf-2.69.tar.gz

# tar-xzf autoconf-2.69.tar.gz

# cd autoconf-2.69

#. / configure

# make & & make install

# check if the version is correct autoconf-V # display path is wrong

# if you report an error, reinstall if you don't skip make uninstall make clean

#. / configure-prefix=/usr

# make & & make install

# autoconf-V display is normal

# cd / usr/local/src/sysbench-master

#. / autogen.sh displays normally

#. / configure

Error report: cannot find MySQL libraries. If you want to compile with MySQL support, missing mysql dependency

# yum install mysql-devel-y

#. / configure displays normally

# make & & make install

Fourth, install mysql5.7.19

# wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm

# rpm-Uvh mysql57-community-release-el6-9.noarch.rpm

# yum install mysql-community-server

# service mysqld start

# grep 'temporary password' / var/log/mysqld.log

# mysql-uroot-p

# enter the filtered password

# set global validate_password_policy=0; # set password complexity

# set global validate_password_length=6; # set password length

# ALTER USER 'root'@'localhost' IDENTIFIED BY' abc123'; # change password

# create database sbtest

# quit

5. Testing

Mysql database test

Sysbench 0.5 simulates a more realistic benchmark environment by replacing the previous oltp with a series of LUA scripts. These test scripts include: insert.lua, oltp.lua, parallel_prepare.lua, select_random_points.lua, update_index.lua, delete.luaoltp_simple.lua, select.lua, select_random_ranges.lua, update_non_index.lua, and the scripts are used in a similar way.

Sysbench 0.5 uses the sbtest library by default, but you need to create it manually. You can also specify it using-- mysql-db, and other non-default options:

-- mysql-host

-- mysql-port

-- mysql-socket

-- mysql-user

-- mysql-password

-- mysql-db

-- mysql-ssl

Prepare

Generate tables and insert data, which can be prepared in parallel using parallel_prepare.lua scripts.

-- db-driver server type mysql | drizzle. Default is mysql.

-- mysql-table-engine table inventory engine

-- myisam-max-rows MyISAM MAX_ROWS option (for large tables)

-- number of tables generated by oltp-table-count [sbtest1, sbtest2...]

-- oltp-table-size generates the number of rows of the table

The oltp-secondary ID column generates a secondary index instead of a primary key

-- oltp-auto-inc sets whether to add on to the ID column | off. Default is on-- oltp-read-only=on.

-- Test scripts under the test=sysbench-0.5/sysbench/tests directory

Sysbench\

-- test=/root/sysbench-master/sysbench/tests/db/oltp.lua\

-- mysql-host=localhost\

-- mysql-port=3306\

-- mysql-user=root\

-- mysql-password=abc123\

-- oltp-table-size=100000\

-- num-threads=8\

-- max-time=10\

-- mysql-db=sbtest\

-- max-requests=0\

-- oltp-test-mode=complex\

-- report-interval=1\

-- mysql-table-engine=innodb\

[prepare | run | cleanup] prepare / test / clear

Six # Test 8 threads, my test server is a single core 1 cpu, ready

# [root@localhost sysbench-master] # sysbench--test=/usr/local/src/sysbench-master/sysbench/tests/db/oltp.lua-- mysql-host=localhost-- mysql-port=3306-- mysql-user=root-- mysql-password=abc123-- oltp-table-size=100000-- num-threads=8-- max-time=10-- mysql-db=sbtest-- max-requests=0-- oltp-test-mode=complex-- report-interval=1-- mysql-table-engine=innodb prepare

# Test

# [root@localhost sysbench-master] # sysbench--test=/usr/local/src/sysbench-master/sysbench/tests/db/oltp.lua-- mysql-host=localhost-- mysql-port=3306-- mysql-user=root-- mysql-password=abc123-- oltp-table-size=100000-- num-threads=8-- max-time=10-- mysql-db=sbtest-- max-requests=0-- oltp-test-mode=complex-- report-interval=1-- mysql-table-engine=innodb run

Sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 8

Report intermediate results every 1 second (s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[1s] threads: 8, tps: 149.71, reads: 2204.79, writes: 613.41, response time: 164.45ms (95%), errors: 0.00, reconnects: 613.41

[2s] threads: 8, tps: 253.17, reads: 3543.39, writes: 1019.89, response time: 164.45ms (95%), errors: 0.00, reconnects: 1019.89

[3s] threads: 8, tps: 270.37, reads: 3784.14, writes: 1082.47, response time: 170.48ms (95%), errors: 0.00, reconnects: 0.00

[4s] threads: 8, tps: 314.77, reads: 4402.71, writes: 1248.04, response time: 94.10ms (95%), errors: 0.00, reconnects: 1248.04

[5s] threads: 8, tps: 251.90, reads: 3528.67, writes: 1008.62, response time: 137.35ms (95%), errors: 0.00, reconnects: 1008.62

[6s] threads: 8, tps: 261.96, reads: 3670.37, writes: 1053.82, response time: 142.39ms (95%), errors: 0.00, reconnects: 0.00

[7s] threads: 8, tps: 309.18, reads: 4328.47, writes: 1243.71, response time: 95.81ms (95%), errors: 0.00, reconnects: 0.00

[8s] threads: 8, tps: 264.82, reads: 3701.52, writes: 1054.29, response time: 193.38ms (95%), errors: 0.00, reconnects: 0.00

[9s] threads: 8, tps: 227.02, reads: 3185.27, writes: 919.08, response time: 179.94ms (95%), errors: 0.00, reconnects: 0.00

[10s] threads: 8, tps: 248.11, reads: 3457.55, writes: 973.44, response time: 144.97ms (95%), errors: 0.00, reconnects: 0.00

OLTP test statistics:

Queries performed:

Read: 35784

Write: 10224

Other: 5112

Total: 51120

Transactions: 2556 (255.33 per sec.)

Read/write requests: 46008 (4595.97 per sec.)

Other operations: 5112 (510.66 per sec.)

Ignored errors: 0 (0.00 per sec.)

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 10.1611s

Total number of events: 2556

Total time taken by event execution: 80.0554s

Latency statistics:

Min: 2.88ms

Avg: 31.32ms

Max: 330.98ms

Approx. 95th percentile: 137.35ms

Threads fairness:

Events (avg/stddev): 319.5000 Universe 6.71

Execution time (avg/stddev): 10.0069max 0.00

# try 64 and 128 threads for single-core, ultra-multithreaded rolling

[root@localhost sysbench-master] # sysbench--test=/usr/local/src/sysbench-master/sysbench/tests/db/oltp.lua-- mysql-host=localhost-- mysql-port=3306-- mysql-user=root-- mysql-password=abc123-- oltp-table-size=10000-num-threads=64-- max-time=30-- mysql-db=sbtest-- max-requests=0-- oltp-test-mode=complex-- report-interval=1-- mysql-table-engine=innodb run

[root@localhost sysbench-master] # sysbench--test=/usr/local/src/sysbench-master/sysbench/tests/db/oltp.lua-- mysql-host=localhost-- mysql-port=3306-- mysql-user=root-- mysql-password=abc123-- oltp-table-size=10000-num-threads=128-- max-time=60-- mysql-db=sbtest-- max-requests=0-- oltp-test-mode=complex-- report-interval=1-- mysql-table-engine=innodb run

# clear

# [root@localhost sysbench-master] # sysbench--test=/usr/local/src/sysbench-master/sysbench/tests/db/oltp.lua-- mysql-host=localhost-- mysql-port=3306-- mysql-user=root-- mysql-password=abc123-- oltp-table-size=100000-- num-threads=8-- max-time=10-- mysql-db=sbtest-- max-requests=0-- oltp-test-mode=complex-- report-interval=1-- mysql-table-engine=innodb cleanup

Sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...

What if you have multiple tables and increase the size of the table?

[root@localhost sysbench-master] # sysbench--test=/usr/local/src/sysbench-master/sysbench/tests/db/oltp.lua-- mysql-host=localhost-- mysql-port=3306-- mysql-user=root-- oltp-tables-count=10-- oltp-table-size=100000-- num-threads=128-- max-time=60-- mysql-db=sbtest-- max-requests=0-- oltp-test-mode=complex-- report-interval=1-- mysql-table-engine=innodb prepare

[root@localhost sysbench-master] # sysbench--test=/usr/local/src/sysbench-master/sysbench/tests/db/oltp.lua-- mysql-host=localhost-- mysql-port=3306-- mysql-user=root-- oltp-tables-count=10-- oltp-table-size=100000-- num-threads=130-- max-time=20-- mysql-db=sbtest-- max-requests=0-- oltp-test-mode=complex-- report-interval=1-- mysql-table-engine=innodb run

CPU test

Using 64-bit integers, test the time it takes to calculate a prime to a maximum

Sysbench-test=cpu-cpu-max-prime=2000 run

View CPU information method, check the number of physical cpu

Grep "physical id" / proc/cpuinfo | sort-u | wc-l to view the number of cores

Grep "core id" / proc/cpuinfo | sort-u | wc-l to check the number of threads

Grep "processor" / proc/cpuinfo | sort-u | wc-l

In the test of sysbench, the value of-num-threads is "number of threads".

Thread (thread) test

Test the performance of the thread scheduler. Useful for testing the behavior of the thread scheduler under high load

Sysbench-test=threads-num-threads=64-thread-yields=100-thread-locks=2 run

File IO performance test

Generate the required test files with a total file size of 5G and 16 concurrent threads. After execution, a bunch of small files will be generated in the current directory.

Sysbench-test=fileio-num-threads=16-file-total-size=5G prepare

Perform the test, specifying the random read-write mode:

Seqwr sequential write

Seqrewr sequential rewriting

Seqrd sequential read

Rndrd random read

Rndwr random write

Rndrw mixed random read / write

Sysbench-test=fileio-num-threads=16-init-rng=on-file-total-size=5G-file-test-mode=rndrw run

Except for test files

Sysbench-test=fileio-num-threads=16-file-total-size=5G cleanup

Memory test

The memory test tests the continuous read and write performance of memory.

Sysbench-test=memory-num-threads=16-memory-block-size=8192-memory-total-size=1G run

Mutex (Mutex) test

Test the performance of the mutex by simulating all threads running concurrently at the same time and briefly requesting mutex X.

Sysbench-test=mutex-num-threads=16-mutex-num=1024-mutex-locks=10000-mutex-loops=5000 run

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.