Mysql-sysbench Performance Test

MySQL Performance test tool-sysbench

Sysbench Introduction
Sysbench is a cross-platform benchmarking tool that supports multiple threads, supports multiple databases, and includes several types of tests, including the following:
CPU Performance
Disk IO Performance
Scheduler performance
memory allocation and transfer speed
POSIX threading Performance
Database performance (OLTP benchmark test)
This article mainly introduces the test of database performance.

1. Download the installation package, address Https://github.com/akopytov/sysbench

#wget Https://github.com/akopytov/sysbench/archive/1.0.zip-O "Sysbench-1.0.zip"

#unzip Sysbench-1.0.zip

#cd sysbench-1.0

2. Installing dependent libraries

#yum Install Automake libtool-y

3. Start the installation

#./autogen.sh

#./configure

4. If it is a yum installed MySQL, do not know where the path is. To find the installation path for MySQL, you need to install Mysql-devel to use Mysql_config

#yum install-y Mysql-devel

5. Execution./configure

#./configure--with-mysql-includes=/usr/include/mysql--with-mysql-libs=/usr/lib64/mysql

#make &&make Install

6. To add a variable:

#export ld_library_path=/usr/local/mysql/lib/

#sysbench--help

#sysbench--version
Sysbench 1.0.12 (using bundled Luajit 2.1.0-beta2)

When executing sysbench, it should be noted that:
(1) Try not to test on the MySQL server running machine, on the one hand may not reflect the impact of the network (even the LAN), on the other hand, the operation of Sysbench (especially when the number of concurrent sets is high) will affect the performance of the MySQL server.
(2) You can incrementally increase the number of concurrent connections (--thread parameters) of the client, and observe the performance of the MySQL server in different cases, such as 10,20,50,100, respectively.
(3) General execution mode Select Complex, if you need to specifically test server read-only performance, or do not use transaction performance, you can choose Simple mode or Nontrx mode.
(4) If multiple tests are performed continuously, be sure to ensure that the previously tested data has been cleaned up.

(1) Prepare the data

Mysql>create database sbtest;

Sysbench--test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua--oltp-table-size=1000000-- Mysql-user=root--mysql-password= ' 123456 '--mysql-socket=/usr/local/mysql/mysql.sock prepare

(2) Perform the test
Sysbench--test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua--oltp-table-size=1000000-- Mysql-user=root--mysql-password= ' 123456 '--mysql-socket=/usr/local/mysql/mysql.sock--max-time=60-- Oltp-read-only=on--max-requests=0--num-threads=8 Run

(3) Cleaning up the data
Sysbench--test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua--oltp-table-size=1000000-- Mysql-user=root--mysql-password= ' 123456 '--mysql-socket=/usr/local/mysql/mysql.sock cleanup

SQL Statistics:
Queries performed:
read:313488--Total reading
write:89568--Write Total
other:44784--Other operations (operations other than curd, such as commit)
total:447840--Total
transactions:22392 (186.27 per Sec.)--Total transactions (number of transactions per second)
queries:447840 (3725.43 per sec)--Total (total per second)
Ignored errors:0 (0.00 per sec)-Total ignored errors total (number of ignores per second)
Reconnects:0 (0.00 per sec)--number of re-connected (re-connected per second)
General Statistics:
Total time:120.2098s-time-consuming
Total number of events:22392--How many transactions occurred
Latency (ms):
min:105.91-Minimum time-consuming
avg:160.86-Average time-consuming
max:850.77-the longest time-consuming
95th percentile:223.34-over 95% average time-consuming
sum:3601892.56
Threads Fairness:
Events (Avg/stddev): 746.4000/4.95--Number of total processing events/Standard deviation
Execution Time (Avg/stddev): 120.0631/0.05--total execution times/standard deviation

The performance tests on the CPU are: 1. Prime number calculation; 2 pi calculation; Sysbench uses the tests that are added by prime numbers. Run directly on the CPU test.
Sysbench--threads=20--events=10000--debug=on--test=cpu--cpu-max-prime=20000 Run
20 Threads execute 10,000 requests, and each request executes a prime number added to 20000

Memory test

Test 8K Sequential allocation:
Sysbench--threads=12--events=10000--test=memory--memory-block-size=8k--memory-total-size=100g-- Memory-access-mode=seq Run
Test 8K random allocation.
Sysbench--threads=12--events=10000--test=memory--memory-block-size=8k--memory-total-size=100g-- Memory-access-mode=rnd Run

File IO Test

IO testing is primarily used to test the load performance of IO. The main test option is--file-test-mode. There are also parameters to be concerned with--file-block-size 、--file-io-mode 、--file-fsync-freq 、--file-rw-ratio, the specific parameters are described in the parameter Interpretation section.
Sysbench--threads=12--events=10000 FileIO--file-total-size=3g--FILE-TEST-MODE=RNDRW Prepare
Sysbench--threads=12--events=10000 FileIO--file-total-size=3g--FILE-TEST-MODE=RNDRW Run
Sysbench--threads=12--events=10000 FileIO--file-total-size=3g--file-test-mode=rndrw cleanup
Compare the IO performance of two servers and need to run the same thread

Lock test

The mutex test simulates all threads running concurrently at the same time.
Sysbench--threads=12 Mutex--mutex-num=1024--mutex-locks=10000--mutex-loops=10000 Run

Threading Test

Sysbench Threads--num-threads=64--thread-yields=100--thread-locks=2 Run

Mysql-sysbench Performance Test