【sysbench】mysql性能测试

1.mysql sysbench 安装部署

tar xvf sysbench-0.4.12.16.tar.gz
cd sysbench-0.4.12.16
./configure
make && make install 
[root@es4 sysbench-0.4.12.16]# sysbench version
sysbench 0.4.12.10

--进行Sysench OLTP 测试-混合读写
mysql -uroot -prootroot
create database sbtest;
exit;

[root@es4 oltp]# sysbench --test=oltp help
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

oltp options:
  --oltp-test-mode=STRING                  test type to use {simple,complex,nontrx,sp} [complex]
  --oltp-reconnect-mode=STRING             reconnect mode {session,transaction,query,random} [session]
  --oltp-sp-name=STRING                    name of store procedure to call in SP test mode []
  --oltp-read-only=[on|off]                generate only 'read' queries (do not modify database) [off]
  --oltp-avoid-deadlocks=[on|off]          generate update keys in increasing order to avoid deadlocks [off]
  --oltp-skip-trx=[on|off]                 skip BEGIN/COMMIT statements [off]
  --oltp-range-size=N                      range size for range queries [100]
  --oltp-point-selects=N                   number of point selects [10]
  --oltp-use-in-statement=N                Use IN-statement with 10 PK lookups per query [0]
  --oltp-use-filter=[on|off]               Use filters in range queries [off]
  --oltp-simple-ranges=N                   number of simple ranges [1]
  --oltp-sum-ranges=N                      number of sum ranges [1]
  --oltp-order-ranges=N                    number of ordered ranges [1]
  --oltp-distinct-ranges=N                 number of distinct ranges [1]
  --oltp-index-updates=N                   number of index update [1]
  --oltp-non-index-updates=N               number of non-index updates [1]
  --oltp-nontrx-mode=STRING                mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
  --oltp-auto-inc=[on|off]                 whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
  --oltp-connect-delay=N                   time in microseconds to sleep after connection to database [10000]
  --oltp-user-delay-min=N                  minimum time in microseconds to sleep after each request [0]
  --oltp-user-delay-max=N                  maximum time in microseconds to sleep after each request [0]
  --oltp-table-comment-string=STRING       comment on table []
  --oltp-table-name=STRING                 name of test table [sbtest]
  --oltp-table-size=N                      number of records in test table [10000]
  --oltp-dist-type=STRING                  random numbers distribution {uniform,gaussian,special} [special]
  --oltp-dist-iter=N                       number of iterations used for numbers generation [12]
  --oltp-dist-pct=N                        percentage of values to be treated as 'special' (for special distribution) [1]
  --oltp-dist-res=N                        percentage of 'special' values to use (for special distribution) [75]
  --oltp-point-select-mysql-handler=[on|off]Use MySQL HANDLER for point select [off]
  --oltp-point-select-all-cols=[on|off]    select all columns for the point-select query [off]
  --oltp-secondary=[on|off]                Use a secondary index in place of the PRIMARY index [off]
  --oltp-num-partitions=N                  Number of partitions used for test table [0]
  --oltp-num-tables=N                      Number of test tables [1]
  --oltp-use-range=[on|off]                Use range partitions [off]
  --oltp-use-ndb-disk-data=[on|off]        Use disk storage for payload data [off]

General database options:

  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)
  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]


Compiled-in database drivers:
  mysql - MySQL driver

mysql options:
  --mysql-host=[LIST,...]       MySQL server host [localhost]
  --mysql-port=N                MySQL server port [3306]
  --mysql-socket=STRING         MySQL socket
  --mysql-user=STRING           MySQL user [sbtest]
  --mysql-password=STRING       MySQL password []
  --mysql-db=STRING             MySQL database name [sbtest]
  --mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
  --mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]
  --mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]
  --myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]
  --mysql-create-options=STRING additional options passed to CREATE TABLE []

2.数据准备。

sysbench --test=oltp        \
--mysql-host=192.168.1.11   \
--mysql-port=3306           \
--mysql-user=root           \
--mysql-password=rootroot   \
--mysql-db=sbtest           \
--mysql-table-engine=innodb \
--mysql-engine-trx=yes      \
--mysql-ssl=no              \
--oltp-test-mode=complex    \
--oltp-num-tables=10        \
--oltp-table-size=1000000   \
--db-driver=mysql           \
--oltp-table-name=mytest    \
--report-interval=10 prepare

sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

Creating table 'mytest'...
Creating table 'mytest6'...
Creating table 'mytest3'...
Creating table 'mytest1'...
Creating table 'mytest5'...
Creating table 'mytest2'...
Creating table 'mytest4'...
Creating table 'mytest8'...
Creating table 'mytest9'...
Creating table 'mytest7'...
Creating 1000000 records in table 'mytest6'...
Creating 1000000 records in table 'mytest5'...
Creating 1000000 records in table 'mytest2'...
Creating 1000000 records in table 'mytest3'...
Creating 1000000 records in table 'mytest4'...
Creating 1000000 records in table 'mytest1'...
Creating 1000000 records in table 'mytest'...
Creating 1000000 records in table 'mytest7'...
Creating 1000000 records in table 'mytest9'...
Creating 1000000 records in table 'mytest8'...


"root@localhost Tue Jul 16 12:00:01 2024 12:00:01 [sbtest]">show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| mytest           |
| mytest1          |
| mytest2          |
| mytest3          |
| mytest4          |
| mytest5          |
| mytest6          |
| mytest7          |
| mytest8          |
| mytest9          |
+------------------+
10 rows in set (0.01 sec)

"root@localhost Tue Jul 16 12:00:19 2024 12:00:19 [sbtest]">select * from mytest1 limit 10;
+----+---+---+----------------------------------------------------+
| id | k | c | pad                                                |
+----+---+---+----------------------------------------------------+
|  1 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  2 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  3 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  4 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  5 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  6 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  7 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  8 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  9 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 10 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+----+---+---+----------------------------------------------------+
10 rows in set (0.00 sec)

"root@localhost Tue Jul 16 12:00:24 2024 12:00:24 [sbtest]">select * from mytest2 limit 10;
+----+---+---+----------------------------------------------------+
| id | k | c | pad                                                |
+----+---+---+----------------------------------------------------+
|  1 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  2 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  3 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  4 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  5 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  6 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  7 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  8 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
|  9 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 10 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+----+---+---+----------------------------------------------------+
10 rows in set (0.04 sec)

3.运行压力测试 

sysbench --test=oltp        \
--mysql-host=192.168.1.11   \
--mysql-port=3306           \
--mysql-user=root           \
--mysql-password=rootroot   \
--mysql-db=sbtest           \
--mysql-table-engine=innodb \
--mysql-engine-trx=yes      \
--mysql-ssl=no              \
--oltp-test-mode=complex    \
--oltp-num-tables=10        \
--oltp-table-size=1000000   \
--db-driver=mysql           \
--oltp-table-name=mytest    \
--report-interval=10        \
run >>/root/mytest.log 

4.检查输出日志

[root@es4 ~]# cat mytest.log 
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Using 10 test tables
Threads started!
[  10s] Intermediate results: 1 threads, tps: 177.995478, reads/s: 2491.936688, writes/s: 711.981911 response time: 6.791385ms (95%)
[  20s] Intermediate results: 1 threads, tps: 176.498844, reads/s: 2470.983821, writes/s: 705.995377 response time: 6.016000ms (95%)
[  30s] Intermediate results: 1 threads, tps: 182.297365, reads/s: 2552.163108, writes/s: 729.189459 response time: 5.962217ms (95%)
[  40s] Intermediate results: 1 threads, tps: 171.499694, reads/s: 2400.995711, writes/s: 685.998775 response time: 6.756913ms (95%)
[  50s] Intermediate results: 1 threads, tps: 178.500608, reads/s: 2499.008516, writes/s: 714.002433 response time: 6.086643ms (95%)
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           40000
        other:                           20000
        total:                           200000
    transactions:                        10000  (178.35 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 180000 (3210.32 per sec.)
    other operations:                    20000  (356.70 per sec.)

General statistics:
    total time:                          56.0691s
    total number of events:              10000
    total time taken by event execution: 56.0025
    response time:
         min:                                  4.71ms
         avg:                                  5.60ms
         max:                                 93.10ms
         approx.  95 percentile:               6.15ms

Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   56.0025/0.00

事务:178/s 
读写请求:3210/s 
最小响应时间:4.7ms 
平均响应时间:5.6ms 
最大响应时间:93.10ms 

5.数据清理 

sysbench --test=oltp        \
--mysql-host=192.168.1.11   \
--mysql-port=3306           \
--mysql-user=root           \
--mysql-password=rootroot   \
--mysql-db=sbtest           \
--mysql-table-engine=innodb \
--mysql-ssl=no              \
--oltp-table-name=mytest    \
--oltp-num-tables=10        \
cleanup 

sysbench 0.4.12.10:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Dropping table 'mytest'...
Dropping table 'mytest1'...
Dropping table 'mytest2'...
Dropping table 'mytest3'...
Dropping table 'mytest4'...
Dropping table 'mytest5'...
Dropping table 'mytest6'...
Dropping table 'mytest7'...
Dropping table 'mytest8'...
Dropping table 'mytest9'...
Done.

6.总结 

sysbench 可以测试 ：CPU,数据库(MYSQL)，内存，文件IO,线程，闩锁。