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 sysbench2，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.lua3,创建一个表
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 | | | | -
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.02updater监控如下：
sysbench还可以对文件系统IO测试，CPU性能测试，以及内存分配与传输速度测试这里并不做介绍，sysbench可以作为硬件参数的一个测试对比。至于吞吐连和读写tps需要自己计算
