Linux之实验sysbench做压测之sysbench安装和MySQL只读测试

摘要

sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况

sysbench编译安装

[root@DS-VM-Node160 ~]# cd /tmp/
[root@DS-VM-Node160 /tmp]# git clone https://github.com/akopytov/sysbench.git
[root@DS-VM-Node160 /tmp]# cd sysbench/
[root@DS-VM-Node160 /tmp/sysbench]# yum install mysql mysql-devel libtool openssl-devel openssl-static mysql-community-devel -y
[root@DS-VM-Node160 /tmp/sysbench]# ./configure -V
sysbench configure 0.5
generated by GNU Autoconf 2.69

Copyright (C) 2012 Free Software Foundation, Inc.
This configure script is free software; the Free Software Foundation
gives unlimited permission to copy, distribute and modify it.
[root@DS-VM-Node160 /tmp/sysbench]#
[root@DS-VM-Node160 /tmp/sysbench]# libtoolize --force --copy
[root@DS-VM-Node160 /tmp/sysbench]# ./autogen.sh
[root@DS-VM-Node160 /tmp/sysbench]# ./configure --prefix=/usr/local/sysbench
###如果需要测试PostgreSQL、Oracle,则在configure时需要加上 --with-oracle 或者 --with-pgsql 参数
[root@DS-VM-Node160 /tmp/sysbench]# make -j `awk '/processor/{a++}END{print a}' /proc/cpuinfo` && make install
#0.5版本需要oltp.lua测试脚本
#如果是rpm包方式安装的,在 /usr/share/doc/sysbench/tests/db/ 下可找到
[root@DS-VM-Node160 /tmp/sysbench]# cp -ai sysbench/tests /usr/local/sysbench/
[root@DS-VM-Node160 /tmp/sysbench]# echo 'export PATH=/usr/local/sysbench/bin:$PATH' >/etc/profile.d/sysbench.sh
[root@DS-VM-Node160 /tmp/sysbench]# . /etc/profile.d/sysbench.sh
[root@DS-VM-Node160 /tmp/sysbench]# which sysbench && cd
/usr/local/sysbench/bin/sysbench
[root@DS-VM-Node160 /tmp/sysbench]# sysbench --version
sysbench 0.5

sysbench 使用yum安装

[root@DS-VM-Node160 /tmp]# yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm -y
[root@DS-VM-Node160 /tmp/sysbench]# yum install sysbench -y

使用sysbench测试MySQL

##创建一个压测使用的库
[root@DS-VM-Node160 ~]# mysql -uroot -p******* -h10.10.231.149 -P3317 -e 'SELECT version();'
+-----------------------+
| version()             |
+-----------------------+
| 10.0.25-MariaDB-wsrep |
+-----------------------+
[root@DS-VM-Node160 ~]# mysql -uroot -p****** -h10.10.231.149 -P3317 -e 'CREATE DATABASE sysbench;'
[root@DS-VM-Node160 ~]# mysql -uroot -p****** -h10.10.231.149 -P3317 -e "SHOW DATABASES LIKE '%sysbench%';"
+-----------------------+
| Database (%sysbench%) |
+-----------------------+
| sysbench              |
+-----------------------+
[root@DS-VM-Node160 ~]#
###创建测试帐户
[root@DS-VM-Node160 ~]# mysql -uroot -p****** -h10.10.231.149 -P3317 -e "GRANT ALL PRIVILEGES ON *.* TO 'sysbench'@'10.%.%.%' IDENTIFIED  BY 'lookback';"
[root@DS-VM-Node160 ~]# mysql -uroot -p****** -h10.10.231.149 -P3317 -e "SELECT User,Password,Host FROM mysql.user WHERE User LIKE '%sysbench%';"
+----------+-------------------------------------------+----------+
| User     | Password                                  | Host     |
+----------+-------------------------------------------+----------+
| sysbench | *153CCFAEAA83407D8DBDBFAA3D17B1A95553E60C | 10.%.%.% |
+----------+-------------------------------------------+----------+
[root@DS-VM-Node160 ~]# 

创建测试数据 表中有1kw条 估计大小有2GB

[root@DS-VM-Node160 ~]# sysbench --test=oltp --test=/usr/local/sysbench/tests/db/oltp.lua --db-driver=mysql --oltp-table-size=10000000 --mysql-db=sysbench --mysql-user=sysbench --mysql-password=lookback --mysql-host=10.10.231.149 --mysql-port=3317 prepare

###注意最后一个,一项测试开始前需要用prepare来准备好表和数据,run执行真正的压测,cleanup用来清除数据和表。
sysbench 0.5: multi-threaded system evaluation benchmark

生成表的格式

[root@DS-VM-Node160 ~]# mysql -usysbench -plookback -h10.10.231.149 -P3317 -e "SELECT * FROM sysbench.sbtest1 ORDER BY id LIMIT 5;"
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                     |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  2 | 3718516 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
|  5 | 5934152 | 05879259692-02024081996-76022818191-82933803603-81845875017-31928300264-16934042125-67052432228-92123768050-95121478647 | 79362588344-09017007031-35411714211-52054317597-26283585383 |
|  8 | 3082941 | 16663631067-14838697989-64358398406-16917073223-47713406217-78812818873-46576803224-61955865221-97956448087-49259465660 | 52468633478-82076178857-68496668227-22002343387-57481185690 |
| 11 | 3371969 | 88814841367-98767826715-60494483894-21079440118-10718401291-33837777683-38627243809-45118876335-22718290515-23426562159 | 79384524915-41356971023-74955511882-92798558059-93482034638 |
| 14 | 4988307 | 87263726817-94340135738-17557808545-78801760644-04111877554-34439976372-12880746592-22897263227-59023974220-20489960934 | 34521232779-81152534024-26776427142-43102257779-39982840334 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
[root@DS-VM-Node160 ~]# 

Creating table 'sbtest1'...
Inserting 10000000 records into 'sbtest1'

开始做OLTP只读测试 分别测试1 4 8 16 32 64线程
[root@DS-VM-Node160 ~]# for i in 1 4 8 16 32 64; do sysbench --test=oltp --test=/usr/local/sysbench/tests/db/oltp.lua --db-driver=mysql --oltp-table-size=10000000 --mysql-db=sysbench --mysql-user=sysbench --mysql-password=lookback --mysql-host=10.10.231.149 --mysql-port=3317 --max-time=60 --max-requests=0 --oltp-read-only --oltp-skip-trx  --oltp-nontrx-mode=select --oltp-dist-type=uniform --oltp-test-mode=nontrx --report-interval=10 --num-threads=$i run; sleep 10; done
###参数解释
--max-requests=0:压力测试产生请求的总数,如果以下面的max-time来记,这个值设为0
#--max-time=60 压测时间,此处设置为1分钟
#--max-requests=0 limit for total number of requests [10000] "0 是无限制"
#--oltp-read-only=on:表示不产生只读SQL,也就是使用oltp.lua时会采用只读模式。默认 off,如果设置为on,则不会产生update,delete,insert的sql。
#--oltp-nontrx-mode=select 查询类型对于非事务执行模式{select, update_key, update_nokey, insert, delete} [select]
#--num-threads 并发线程数,可以理解为模拟的客户端并发连接数
#--oltp-tables-count=10 表数量
#--oltp-reconnect-mode=STRING 重新连接模式{session(不使用重新连接。每个线程断开只在测试结束),transaction(在每次事务结束后重新连接),query(在每个SQL语句执行完重新连接),random(对于每个事务随机选择以上重新连接模式)}。默认是session
#--oltp-dist-type=uniform:指定随机取样类型,可选值有 uniform(均匀分布), Gaussian(高斯分布), special(空间分布)。默认是special
#--oltp-test-mode=nontrx:执行模式,这里是非事务式的。可选值有simple,complex,nontrx。默认是complex
    simple:简单查询,SELECT c FROM sbtest WHERE id=N
    complex (advanced transactional):事务模式在开始和结束事务之前加上begin和commit, 一个事务里可以有多个语句,如点查询、范围查询、排序查询、更新、删除、插入等,并且为了不破坏测试表的数据,该模式下一条记录删除后会在同一个事务里添加一条相同的记录。
    nontrx (non-transactional):与simple相似,但是可以进行update/insert等操作,所以如果做连续的对比压测,你可能需要重新cleanup,prepare。
#--rand-init=on:是否随机初始化数据,如果不随机化那么初始好的数据每行内容除了主键不同外其他完全相同
#--report-interval=10:表示每10s输出一次测试进度报告

注意,针对不同的选项取值就会有不同的子选项。比如oltp-dist-type=special,就有比如oltp-dist-pct=1、oltp-dist-res=50两个子选项,代表有50%的查询落在1%的行(即热点数据)上,另外50%均匀的(sample uniformly)落在另外99%的记录行上。
再比如oltp-test-mode=nontrx时, 就可以有oltp-nontrx-mode,可选值有select(默认), update_key, update_nokey, insert, delete,代表非事务式模式下使用的测试sql类型。
以上代表的是一个只读的例子,可以把num-threads依次递增(16,36,72,128,256,512),或者调整my.cnf参数,比较效果。另外需要注意的是,大部分mysql中间件对事务的处理,默认都是把sql发到主库执行,所以只读测试需要加上oltp-skip-trx=on来跳过测试中的显式事务。
ps1: 只读测试也可以使用share/tests/db/select.lua进行,但只是简单的point select。
ps2: 我在用sysbench压的时候,在mysql后端会话里有时看到大量的query cache lock,如果使用的是uniform取样,最好把查询缓存关掉。当然如果是做两组性能对比压测,因为都受这个因素影响,关心也不大。
下面是上面语句的测试结果

[root@DS-VM-Node160 ~]# for i in 1 4 8 16 32 64; do sysbench --test=oltp --test=/usr/local/sysbench/tests/db/oltp.lua --db-driver=mysql --oltp-table-size=10000000 --mysql-db=sysbench --mysql-user=sysbench --mysql-password=lookback --mysql-host=10.10.231.149 --mysql-port=3317 --max-time=60 --max-requests=0 --oltp-read-only --oltp-skip-trx  --oltp-nontrx-mode=select --oltp-dist-type=uniform --oltp-test-mode=nontrx --report-interval=10 --num-threads=$i run; sleep 10; done
sysbench 0.5:  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


Initializing worker threads...

Threads started!

[  10s] threads: 1, tps: 86.10, reads: 1205.98, writes: 344.39, response time: 12.78ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 1, tps: 80.30, reads: 1124.00, writes: 321.20, response time: 13.50ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 1, tps: 80.20, reads: 1122.70, writes: 320.80, response time: 13.72ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 1, tps: 80.30, reads: 1124.10, writes: 321.20, response time: 13.63ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 1, tps: 79.70, reads: 1117.00, writes: 319.10, response time: 13.76ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 1, tps: 78.00, reads: 1091.90, writes: 311.70, response time: 14.22ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            67858
        write:                           19388
        other:                           9694
        total:                           96940
    transactions:                        4847   (80.78 per sec.)
    read/write requests:                 87246  (1454.00 per sec.)
    other operations:                    9694   (161.56 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0040s
    total number of events:              4847
    total time taken by event execution: 59.9929s
    response time:
         min:                                 10.15ms
         avg:                                 12.38ms
         max:                                112.96ms
         approx.  95 percentile:              13.65ms

Threads fairness:
    events (avg/stddev):           4847.0000/0.00
    execution time (avg/stddev):   59.9929/0.00

sysbench 0.5:  multi-threaded system evaluation benchmark

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


Initializing worker threads...

Threads started!

[  10s] threads: 4, tps: 299.69, reads: 4199.52, writes: 1199.48, response time: 15.47ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 4, tps: 299.70, reads: 4197.30, writes: 1198.90, response time: 14.89ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 4, tps: 300.90, reads: 4207.30, writes: 1202.80, response time: 15.22ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 4, tps: 295.20, reads: 4134.80, writes: 1180.90, response time: 15.72ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 4, tps: 287.30, reads: 4025.80, writes: 1150.10, response time: 16.49ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 4, tps: 298.90, reads: 4181.90, writes: 1194.60, response time: 16.48ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            249494
        write:                           71284
        other:                           35642
        total:                           356420
    transactions:                        17821  (296.96 per sec.)
    read/write requests:                 320778 (5345.30 per sec.)
    other operations:                    35642  (593.92 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0113s
    total number of events:              17821
    total time taken by event execution: 239.9966s
    response time:
         min:                                 10.87ms
         avg:                                 13.47ms
         max:                                122.42ms
         approx.  95 percentile:              15.66ms

Threads fairness:
    events (avg/stddev):           4455.2500/9.44
    execution time (avg/stddev):   59.9992/0.00

sysbench 0.5:  multi-threaded system evaluation benchmark

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


Initializing worker threads...

Threads started!

[  10s] threads: 8, tps: 485.19, reads: 6799.80, writes: 1941.77, response time: 22.12ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 8, tps: 481.50, reads: 6744.80, writes: 1926.80, response time: 23.56ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 8, tps: 480.10, reads: 6715.70, writes: 1918.80, response time: 24.27ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 8, tps: 452.50, reads: 6340.60, writes: 1811.60, response time: 24.72ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 8, tps: 480.40, reads: 6722.80, writes: 1921.40, response time: 24.54ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 8, tps: 485.20, reads: 6796.00, writes: 1942.00, response time: 24.75ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            401198
        write:                           114628
        other:                           57314
        total:                           573140
    transactions:                        28657  (477.27 per sec.)
    read/write requests:                 515826 (8590.92 per sec.)
    other operations:                    57314  (954.55 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0432s
    total number of events:              28657
    total time taken by event execution: 480.2772s
    response time:
         min:                                 11.03ms
         avg:                                 16.76ms
         max:                                294.31ms
         approx.  95 percentile:              24.07ms

Threads fairness:
    events (avg/stddev):           3582.1250/9.12
    execution time (avg/stddev):   60.0346/0.00

sysbench 0.5:  multi-threaded system evaluation benchmark

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


Initializing worker threads...

Threads started!

[  10s] threads: 16, tps: 755.09, reads: 10587.34, writes: 3021.85, response time: 30.39ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 16, tps: 753.80, reads: 10553.58, writes: 3014.79, response time: 30.25ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 16, tps: 694.90, reads: 9728.53, writes: 2778.51, response time: 35.96ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 16, tps: 571.10, reads: 8001.37, writes: 2290.79, response time: 33.51ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 16, tps: 563.60, reads: 7883.93, writes: 2249.71, response time: 32.86ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 16, tps: 756.50, reads: 10586.50, writes: 3025.50, response time: 30.67ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            573524
        write:                           163864
        other:                           81932
        total:                           819320
    transactions:                        40966  (682.58 per sec.)
    read/write requests:                 737388 (12286.43 per sec.)
    other operations:                    81932  (1365.16 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0165s
    total number of events:              40966
    total time taken by event execution: 960.0691s
    response time:
         min:                                 12.99ms
         avg:                                 23.44ms
         max:                               2295.61ms
         approx.  95 percentile:              32.15ms

Threads fairness:
    events (avg/stddev):           2560.3750/33.23
    execution time (avg/stddev):   60.0043/0.01

sysbench 0.5:  multi-threaded system evaluation benchmark

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


Initializing worker threads...

Threads started!

[  10s] threads: 32, tps: 891.19, reads: 12504.40, writes: 3568.44, response time: 54.01ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 32, tps: 998.60, reads: 13975.92, writes: 3992.61, response time: 51.92ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 32, tps: 962.60, reads: 13476.78, writes: 3849.79, response time: 48.25ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 32, tps: 1046.50, reads: 14653.70, writes: 4187.60, response time: 46.25ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 32, tps: 1009.70, reads: 14140.21, writes: 4036.90, response time: 47.09ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 32, tps: 966.20, reads: 13520.91, writes: 3865.00, response time: 49.98ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            822920
        write:                           235120
        other:                           117560
        total:                           1175600
    transactions:                        58780  (978.89 per sec.)
    read/write requests:                 1058040 (17620.08 per sec.)
    other operations:                    117560 (1957.79 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0474s
    total number of events:              58780
    total time taken by event execution: 1920.5471s
    response time:
         min:                                 14.57ms
         avg:                                 32.67ms
         max:                                732.59ms
         approx.  95 percentile:              49.49ms

Threads fairness:
    events (avg/stddev):           1836.8750/27.39
    execution time (avg/stddev):   60.0171/0.01

sysbench 0.5:  multi-threaded system evaluation benchmark

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


Initializing worker threads...

Threads started!

[  10s] threads: 64, tps: 1094.98, reads: 15400.07, writes: 4391.91, response time: 101.24ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 64, tps: 1052.40, reads: 14737.81, writes: 4204.60, response time: 106.62ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 64, tps: 1129.60, reads: 15817.40, writes: 4522.60, response time: 98.67ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 64, tps: 956.60, reads: 13404.34, writes: 3840.78, response time: 111.78ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 64, tps: 1088.30, reads: 15224.31, writes: 4339.80, response time: 102.06ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 64, tps: 1135.50, reads: 15880.44, writes: 4535.51, response time: 101.39ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            904932
        write:                           258552
        other:                           129276
        total:                           1292760
    transactions:                        64638  (1076.69 per sec.)
    read/write requests:                 1163484 (19380.45 per sec.)
    other operations:                    129276 (2153.38 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0339s
    total number of events:              64638
    total time taken by event execution: 3841.3044s
    response time:
         min:                                 16.69ms
         avg:                                 59.43ms
         max:                               1045.31ms
         approx.  95 percentile:             103.48ms

Threads fairness:
    events (avg/stddev):           1009.9688/19.97
    execution time (avg/stddev):   60.0204/0.01

[root@DS-VM-Node160 ~]#

Linux之实验sysbench做压测之sysbench安装和MySQL只读测试
我们一般关注的用于绘图的指标主要有:
response time avg: 平均响应时间。(后面的95%的大小可以通过--percentile=98的方式去更改)
transactions: 精确的说是这一项后面的TPS 。但如果使用了-oltp-skip-trx=on,这项事务数恒为0,需要用total number of events 去除以总时间,得到tps(其实还可以分为读tps和写tps)
read/write requests: 用它除以总时间,得到吞吐量QPS
当然还有一些系统层面的cpu,io,mem相关指标

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

lookback

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: