How to use MySQL sysbench!

Sysbench of MySQL stress test

Installation of 1.sysbench

Download Sysbench Source Package http://down1.chinaunix.net/distfiles/sysbench-0.4.10.tar.gz

After downloading, there will be a sysbench-0.4.10.tar.gz compressed package.

Generate directory sysbench-0.4.10 after decompression

Enter the sysbench-0.4.10 directory

[Email protected] home]# cd/home/sysbench-0.4.10

Compiling and installing Sysbench

[Email protected] sysbench-0.4.10]#/configure--with-mysql-includes=/usr/include/mysql--with-mysql-libs=/usr/ Lib && make && make install

Checking build system Type ... X86_64-unknown-linux-gnu

Checking host system Type ... X86_64-unknown-linux-gnu

Checking target system Type ... X86_64-unknown-linux-gnu

Checking for a bsd-compatible install .../usr/bin/install-c

Checking whether build environment is sane ... yes

Checking for a thread-safe mkdir-p .../bin/mkdir-p

Checking for gawk ... gawk

TIP:

WITH-MYSQL-INCLUDES specifies the path of the Mysql-devel identified above,

With-mysql-libs is the path to the MySQL installation,

# These two parameters didn't make sense at the time of compilation and installation, the error has been

Error handling during installation

(1). drv_mysql.c:32:19:error:mysql.h:no such file or directory

Parameter with-mysql-includes, with-mysql-libs not accurate

(2)./usr/bin/ld:cannot Find-lmysqlclient_r

Collect2:ld returned 1 exit status

MAKE[2]: * * * [sysbench] Error 1

MAKE[2]: Leaving directory '/home/sysbench-0.4.10/sysbench '

MAKE[1]: * * * [all-recursive] Error 1

MAKE[1]: Leaving directory '/home/sysbench-0.4.10/sysbench '

Make: * * * [all-recursive] Error 1

Under Installation: Rpm-ivh mysql-shared-5.6.22-1.el6.x86_64.rpm

Use of 2.sysbench

Sysbench items that can be tested are

Performance testing of the CPU

Memory Performance Test

Disk IO energy-absorbing test

OLTP system Testing

The most important is the disk IO energy-absorbing and OLTP tests.

Here we focus on disk IO and OLTP testing.

===== Disk IO performance test ==============

IO test is divided into three stages, file generation, test execution, clean-up test files

First generate the files you want to test

Sysbench--test=fileio--num-threads=2--file-num=16--file-total-size=30g--file-test-mode=rndrw--file-rw-ratio=2 Prepare

[Email protected] ~]# sysbench--test=fileio--num-threads=2--file-num=16--file-total-size=30g--file-test-mode= RNDRW--file-rw-ratio=2 Prepare

Sysbench 0.4.10:multi-threaded System Evaluation Benchmark

Files, 1966080Kb, 30720Mb total

Creating files for the test ...

Then perform the test

Sysbench--test=fileio--num-threads=2--file-num=16--file-total-size=30g--file-test-mode=rndrw--file-rw-ratio=2 Run

[Email protected] ~]# sysbench--test=fileio--num-threads=2--file-num=16--file-total-size=30g--file-test-mode= RNDRW--file-rw-ratio=2 Run

Sysbench 0.4.10:multi-threaded System Evaluation Benchmark

Running the test with following options:

Number of Threads:2

Extra File Open flags:0

Files, 1.875Gb each

30Gb Total File size

Block size 16Kb

Number of random requests for random io:10000

Read/write ratio for combined random IO test:2.00

Periodic FSYNC enabled, calling FSYNC () each requests.

Calling Fsync () at the end of test, Enabled.

Using synchronous I/O mode

Doing Random r/w Test

Threads started!

Done.

Operations performed:6667 Read, 3333 Write, and the other = 11600 Total

Read 104.17Mb written 52.078Mb Total transferred 156.25Mb (2.0936MB/SEC)

133.99 Requests/sec executed

Test Execution Summary:

Total time:74.6328s

Total number of events:10000

Total time taken by event execution:142.3936

Per-request Statistics:

Min:0.01ms

Avg:14.24ms

Max:305.56ms

Approx. Percentile:37.48ms

Threads Fairness:

Events (Avg/stddev): 5000.0000/12.00

Execution Time (Avg/stddev): 71.1968/0.09

The most important thing to pay attention to is the throughput (2.0936MB/SEC),

Requests per second (133.99 requests/sec executed)

95% of requests are time-consuming (approx. PERCENTILE:37.48MS)

Final cleanup of test files

Sysbench--test=fileio--num-threads=2--file-num=16--file-total-size=30g--file-test-mode=rndrw--file-rw-ratio=2 Cleanup

=====OLTP System Performance Test ==============

OTLP performance testing is divided into two stages.

1. Preparation phase

2. Implementation phase

--Preparation phase

[Email protected] ~]# sysbench--TEST=OLTP--mysql-table-engine=innodb--oltp-table-size=4000000--mysql-socket=/var /lib/mysql/mysql.sock--mysql-db=test--mysql-user=root [email protected] Prepare

Sysbench 0.4.10:multi-threaded System Evaluation Benchmark

No DB drivers specified, using MySQL

Creating table ' Sbtest ' ...

Creating 4000000 Records in table ' Sbtest ' ...

--implementation phase

[Email protected] ~]# sysbench--num-threads=8--max-requests=4000000--test=oltp--mysql-table-engine=innodb-- oltp-table-size=4000000--mysql-socket=/var/lib/mysql/mysql.sock--mysql-db=test--mysql-user=root--[email Protected] Run

Sysbench 0.4.10:multi-threaded System Evaluation Benchmark

No DB drivers specified, using MySQL

Warning:preparing of "BEGIN" is unsupported, using emulation

(Last message repeated 7 times)

Running the test with following options:

Number of Threads:8

Doing OLTP test.

Running Mixed OLTP Test

Using Special Distribution (iterations, 1 pct of values is returned in the PCT cases)

Using "BEGIN" for starting transactions

Using auto_inc on the ID column

Maximum number of requests for OLTP test are limited to 4000000

Threads started!

Done.

OLTP Test Statistics:

Queries performed:

read:56000000

write:20000000

other:8000000

total:84000000

transactions:4000000 (125.37 per sec.)

Deadlocks:0 (0.00 per Sec.)

Read/write requests:76000000 (2381.99 per sec.)

Other operations:8000000 (250.74 per sec.)

Test Execution Summary:

Total time:31906.1255s

Total number of events:4000000

Total time taken by event execution:255224.4528

Per-request Statistics:

Min:2.69ms

Avg:63.81ms

Max:5557.09ms

Approx. Percentile:178.90ms

Threads Fairness:

Events (Avg/stddev): 500000.0000/2282.56

Execution Time (Avg/stddev): 31903.0566/0.09

This output contains a

QPS (transactions:4000000 (125.37 per sec)),

TPS (transactions:4000000 (125.37 per sec.))

95 of requests are time-consuming at 178.90ms:approx. Percentile:178.90ms

Reference Links:

Http://blog.chinaunix.net/uid-177564-id-3715242.html

Http://blog.chinaunix.net/uid-20639775-id-2955761.html

Http://www.anbob.com/archives/2177.html

http://imysql.cn/node/312

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1597221

How to use MySQL sysbench!