linuxea:sysbench1.0.14压力测试对比


sysbench是基于LuaJIT的可脚本化多线程基准测试工具。它最常用于数据库基准测试,但也可用于创建不涉及数据库服务器的任意复杂工作负载。

  • sysbench附带了以下功能:
    oltp_*.lua:类似OLTP的数据库基准测试的集合
    fileio:文件系统级基准测试
    cpu: 简单的CPU基准测试
    memory:内存访问基准测试
    threads:基于线程的调度程序基准测试
    mutex:一个POSIX互斥体基准测试

  • 特点:
    可以统计速率和延迟,包括延迟百分点数形成简单的排序
    即使有数千个并发,开销也很低。sysbench能够每秒生成,跟踪数以万计的事件
    通过lua脚本可以实现预定义的测试条件,能够轻松完成新的基础测试

更多请访问:https://github.com/akopytov/sysbench

1,安装

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

2,zabbix-agent安装

curl -Lk https://raw.githubusercontent.com/LinuxEA-Mark/zabbix3.0.2-complete-works/master/zabbix-install/zabbix-install.sh|bash -s load 10.10.240.114

授权用户监控

GRANT SELECT ON *.* TO 'zabbix'@'127.0.0.1' IDENTIFIED BY 'password';

下载监控脚本(zabbix模板在github上)

curl -Lk https://raw.githubusercontent.com/LinuxEA-Mark/zabbix3.0.2-complete-works/master/mysql_QPTS/mariadb/install_db_status.sh|bash
  • 提供的lua脚本
[root@DS-VM-Node49 ~]# ll /usr/local/sysbench/share/sysbench/
总用量 60
-rwxr-xr-x 1 root root  1446 5月  21 14:45 bulk_insert.lua
-rw-r--r-- 1 root root 14468 5月  21 14:45 oltp_common.lua
-rwxr-xr-x 1 root root  1290 5月  21 14:45 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 5月  21 14:45 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 5月  21 14:45 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 5月  21 14:45 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 5月  21 14:45 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 5月  21 14:45 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 5月  21 14:45 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 5月  21 14:45 oltp_write_only.lua
-rwxr-xr-x 1 root root  1933 5月  21 14:45 select_random_points.lua
-rwxr-xr-x 1 root root  2132 5月  21 14:45 select_random_ranges.lua

3,创建一个表

mysql> create database db_20180528;
Query OK, 1 row affected (0.00 sec)

4,授权压测用户权限

GRANT all  ON db_20180528.* TO 'mark'@'%' identified by 'password';  

5,压测的参数

sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=10.10.240.145 \  # IP 
--mysql-port=3336 \           # 端口
--mysql-user=mark \           # 用户名
--mysql-password=password \   # 密码
--mysql-db=db_20180528 \      # 库名
--db-driver=mysql \           # 数据库类型
--tables=10 \                 # 生成 10 个测试表
--table-size=3000000 \        # 每个测试表填充数据量为 100000 
--report-interval=10 \        # 定期以秒为单位报告具有指定间隔的中间统计信息 0禁用中间报告[0]
--threads=250 \               # 线程数
--time=60 \                   # 执行时长为 120秒
prepare

其中run执行真正的压测,cleanup用来清除数据和表,一般先用prepare构建表结构,如下:

sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=10.10.240.145 \
--mysql-port=3336 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3336 \
--db-driver=mysql \
--tables=10 \
--table-size=1000000 \
--report-interval=10 \
--threads=80 \
--time=60 \
run

实际prepare的表结构可能是这样的:

MariaDB [sbtest]>  desc sbtest.sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

上面的测试命令代表的是:对mysql进行oltp基准测试,表数量10,每表行数约10w(几乎delete多少就会insert的多少),并且是非事务的只读测试,持续60s,并发线程数80个。

6,负载均衡只读压测

需要说明的选项:
我们可以把threads依次递增(成倍数的递加),或者调整my.cnf参数,比较效果。另外需要注意的是,大部分mysql中间件对事务的处理,默认都是把sql发到主库执行,所以只读测试需要加上skip-trx=on来跳过测试中的显式事务。

请注意:我们使用maxscale的Readconnroute路由负载进行测试,集群MariaDB Galera Cluster

sysbench /usr/share/sysbench/oltp_read_only.lua \
--mysql-host=10.10.240.145 \
--mysql-port=3336 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3336 \
--db-driver=mysql \
--tables=10 \
--table-size=1000000 \
--report-interval=10 \
--threads=80 \
--time=60 \
--skip-trx=on \
run

注意:先使用prepare,完成后使用run,清楚数据使用cleanup,prepare一次可以多次重复使用
结果如下:

[ 10s ] thds: 80 tps: 2050.04 qps: 28756.83 (r/w/o: 28756.83/0.00/0.00) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 2322.46 qps: 32507.76 (r/w/o: 32507.76/0.00/0.00) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 2841.30 qps: 39772.25 (r/w/o: 39772.25/0.00/0.00) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 3247.04 qps: 45465.87 (r/w/o: 45465.87/0.00/0.00) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 3294.92 qps: 46130.73 (r/w/o: 46130.73/0.00/0.00) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 3324.43 qps: 46544.00 (r/w/o: 46544.00/0.00/0.00) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            2392432
        write:                           0
        other:                           0
        total:                           2392432
    transactions:                        170888 (2845.82 per sec.)
    queries:                             2392432 (39841.44 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0471s
    total number of events:              170888

Latency (ms):
         min:                                    6.66
         avg:                                   28.09
         max:                                 2301.15
         95th percentile:                       71.83
         sum:                              4801074.58

Threads fairness:
    events (avg/stddev):           2136.1000/730.42
    execution time (avg/stddev):   60.0134/0.01

在用sysbench压的时候,在mysql后端会话里有时看到大量的query cache lock,如果使用的是uniform取样,最好把查询缓存关掉。当然如果是做两组性能对比压测,因为都受这个因素影响,关心也不大。

负载均衡的监控图指标如下

6,混合读写

混合读写我们使用maxscale的Readwritesplit读写分离进行测试,集群MariaDB Galera Cluster,逻辑主从如下:

Server changed state: server1[10.10.240.202:3306]: new_master. [Running] -> [Master, Synced, Running]
Server changed state: server2[10.10.240.203:3306]: new_slave. [Running] -> [Slave, Synced, Running]
Server changed state: server3[10.10.240.146:3306]: new_slave. [Running] -> [Slave, Synced, Running]

在1.0版本中混合读写oltp_read_write.lua这个脚本即可
我们创建db20180528_3337库进行混合读写的测试

sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=10.10.240.145 \
--mysql-port=3337 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3337 \
--db-driver=mysql \
--tables=10 \
--table-size=1000000 \
--report-interval=10 \
--threads=80 \
--time=60 \
--skip-trx=on \
run

注意:先使用prepare,完成后使用run,清楚数据使用cleanup,prepare一次可以多次重复使用
注意:如果不加oltp-skip-trx=on,那么所有查询都会发往主库,但如果在有写入的情况下使用--oltp-skip-trx=on跳过BEGIN和COMMIT

结果如下:

[ 10s ] thds: 80 tps: 132.06 qps: 2495.40 (r/w/o: 1957.85/267.63/269.92) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 161.21 qps: 2903.16 (r/w/o: 2258.92/325.62/318.62) lat (ms,95%): 1561.52 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 163.90 qps: 2948.16 (r/w/o: 2293.07/339.90/315.20) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 191.70 qps: 3450.23 (r/w/o: 2685.83/397.60/366.80) lat (ms,95%): 1401.61 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 202.30 qps: 3640.71 (r/w/o: 2832.33/431.59/376.79) lat (ms,95%): 1479.41 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 242.71 qps: 4367.04 (r/w/o: 3394.31/513.12/459.61) lat (ms,95%): 1304.21 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            154266
        write:                           22895
        other:                           21181
        total:                           198342
    transactions:                        11019  (182.90 per sec.)
    queries:                             198342 (3292.19 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.2444s
    total number of events:              11019

Latency (ms):
         min:                                    9.71
         avg:                                  436.60
         max:                                 4126.11
         95th percentile:                     1506.29
         sum:                              4810859.89

Threads fairness:
    events (avg/stddev):           137.7375/15.14
    execution time (avg/stddev):   60.1357/0.06

监控如下:

试试update

sysbench /usr/share/sysbench/oltp_update_index.lua \
--mysql-host=10.10.240.145 \
--mysql-port=3337 \
--mysql-user=mark \
--mysql-password=password \
--mysql-db=db20180528_3337 \
--db-driver=mysql \
--tables=10 \
--table-size=1000000 \
--report-interval=10 \
--threads=80 \
--time=60 \
--skip-trx=on 

注意:先使用prepare,完成后使用run,清楚数据使用cleanup,prepare一次可以多次重复使用

生成报告
执行结果

sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 80
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!
[ 10s ] thds: 80 tps: 1249.32 qps: 1249.32 (r/w/o: 0.00/482.19/767.13) lat (ms,95%): 320.17 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 80 tps: 1672.80 qps: 1672.80 (r/w/o: 0.00/646.40/1026.40) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 80 tps: 1909.94 qps: 1909.94 (r/w/o: 0.00/740.21/1169.72) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 80 tps: 2265.18 qps: 2265.18 (r/w/o: 0.00/868.45/1396.72) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 80 tps: 2587.02 qps: 2587.02 (r/w/o: 0.00/986.64/1600.37) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 80 tps: 2562.74 qps: 2562.74 (r/w/o: 0.00/990.11/1572.62) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0      //总select数量
        write:                           47181  //总update、insert、delete语句数量
        other:                           75371  //commit、unlock tables以及其他mutex的数量
        total:                           122552
    transactions:                        122552 (2040.15 per sec.) //通常需要关注的数字(TPS)
    queries:                             122552 (2040.15 per sec.)
    ignored errors:                      0      (0.00 per sec.) //忽略的错误数
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0684s  //即max-time指定的压测实际
    total number of events:              122552    //总的事件数,一般与transactions相同

Latency (ms):
         min:                                    0.34
         avg:                                   39.19  //95%的语句的平均响应时间
         max:                                 2665.16
         95th percentile:                      215.44
         sum:                              4802756.15

Threads fairness:
    events (avg/stddev):           1531.9000/148.18
    execution time (avg/stddev):   60.0345/0.02

updater监控如下:

sysbench还可以对文件系统IO测试,CPU性能测试,以及内存分配与传输速度测试这里并不做介绍,sysbench可以作为硬件参数的一个测试对比。
至于吞吐连和读写tps需要自己计算

1 分享

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

支付宝扫码赞助

支付宝扫码赞助

日期: 2018-05-30分类: Mariadb

标签: mariadb

发表评论