安装
在github上有安装说明：https://github.com/akopytov/sysbench
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash sudo apt -y install sysbench
准备数据
root@scutech:/mysql/data# mysqladmin create sbtest root@scutech:/mysql/data# sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=10000 --oltp-tables-count=10 --oltp-test-mode=complex --time=120 --report-interval=10 prepare --mysql-user=root --mysql-password=dingjia scutech@scutech:/mysql/data/sbtest$ ll total 102572 drwxr-x--- 2 mysql mysql 4096 Mar 4 21:03 ./ drwxr-xr-x 12 mysql mysql 4096 Mar 4 21:03 ../ -rw-r----- 1 mysql mysql 65 Mar 4 21:03 db.opt -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest1.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:03 sbtest1.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest10.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest10.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest2.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest2.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest3.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest3.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest4.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest4.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest5.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest5.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest6.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest6.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest7.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest7.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest8.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest8.ibd -rw-r----- 1 mysql mysql 8632 Mar 4 21:03 sbtest9.frm -rw-r----- 1 mysql mysql 10485760 Mar 4 21:04 sbtest9.ibd scutech@scutech:/mysql/data/sbtest$ du -sh . 101M .
从docker中运行
准备工作
在mysql的客户端中运行
CREATE SCHEMA sbtest; CREATE USER sbtest@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON sbtest.* to sbtest@'%';
造数据的工作
time docker run \ --rm=true \ --name=sb-prepare \ severalnines/sysbench \ sysbench \ --db-driver=mysql \ --oltp-table-size=10000000 \ --oltp-tables-count=24 \ --threads=1 \ --mysql-host=192.168.87.205 \ --mysql-port=3306 \ --mysql-user=sbtest \ --mysql-password=password \ /usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua \ run
清除把run 改成cleanup
运行结果
queries performed: read: 0 write: 89760 other: 48 total: 89808 transactions: 1 (0.00 per sec.) queries: 89808 (21.72 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 4134.4101s total number of events: 1 Latency (ms): min: 4134408.88 avg: 4134408.88 max: 4134408.88 95th percentile: 100000.00 sum: 4134408.88 Threads fairness: events (avg/stddev): 1.0000/0.00 execution time (avg/stddev): 4134.4089/0.00 real 68m58.429s user 0m0.732s sys 0m0.287s
数据大小
[root@base-test-01 sbtest]# du -sh .
55G .
可以认为一个小时生成了50G的内容
查看帮助
--name=sb-run \ severalnines/sysbench \ sysbench /usr/share/sysbench/oltp_common.lua help
从docker中运行 the benchmark for MySQL:
$ docker run \ --name=sb-run \ severalnines/sysbench \ sysbench \ --db-driver=mysql \ --report-interval=2 \ --mysql-table-engine=innodb \ --oltp-table-size=100000 \ --oltp-tables-count=24 \ --threads=64 \ --time=99999 \ --mysql-host=10.0.0.51 \ --mysql-port=3306 \ --mysql-user=sbtest \ --mysql-password=password \ /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \ run
服务器测试
测试环境配置
服务器 DELL PowerEdge R820
CPU Intel® Xeon® CPU E5-4650 v2 @ 2.40GHz 4个10核
内存 256G DDR-3
存储 1111.25GB 71862.50GB SATA SSD
操作系统 Centos 7.7 64bit
内核 3.10.0-1062.el7.x86_64
造数据
用140个线程造2000个表的数据
scutech@infokist:/md10$ sysbench oltp_common --tables=2000 --table-size=1000000 --mysql-user=root --mysql-password=dingjia --mysql-host=192.168.87.178 prepare --threads=140
生成数据近500G
[oracle@localhost ~]$ sudo du -sh /u01/mysql/data/sbtest/ 469G /u01/mysql/data/sbtest/ [oracle@localhost ~]$
造数据时服务器的压力如下：
如果需要清除数据用下面的命令，清除10个表的数据：
sysbench oltp_common cleanup --mysql-user=root --mysql-password=dingjia --mysql-host=192.168.87.178 --tables=10
测试run
开始测试
sysbench oltp_read_write --tables=1000 --table-size=1000000 --mysql-user=root --mysql-password=dingjia --mysql-host=192.168.87.178 --threads=80 --report-interval=10 --time=1000 run
运行时出错，提示：
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"
修改参数后解决
mysql> set persist max_prepared_stmt_count=100000; Query OK, 0 rows affected (0.00 sec) mysql> show global status like 'com_stmt%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Com_stmt_execute | 1883907 | | Com_stmt_close | 720080 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 820240 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Com_stmt_reprepare | 0 | +-------------------------+---------+ 7 rows in set (0.01 sec)
sysbench提示的性能如下
[ 520s ] thds: 80 tps: 1000.19 qps: 20014.08 (r/w/o: 14012.02/4001.68/2000.39) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00 [ 530s ] thds: 80 tps: 1000.00 qps: 20001.64 (r/w/o: 14001.56/4000.09/1999.99) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00 [ 540s ] thds: 80 tps: 1003.10 qps: 20054.76 (r/w/o: 14035.98/4012.59/2006.20) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00 [ 550s ] thds: 80 tps: 1000.71 qps: 20015.13 (r/w/o: 14010.49/4003.23/2001.41) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00 [ 1000s ] thds: 80 tps: 997.28 qps: 19940.16 (r/w/o: 13957.16/3988.53/1994.47) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 13978370 write: 3993820 other: 1996910 total: 19969100 transactions: 998455 (997.15 per sec.) queries: 19969100 (19942.94 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 1001.3063s total number of events: 998455 Latency (ms): min: 9.92 avg: 80.12 max: 902.25 95th percentile: 116.80 sum: 79998913.63 Threads fairness: events (avg/stddev): 12480.6875/823.73 execution time (avg/stddev): 999.9864/0.05
每秒的tps是1000,每秒大约2万个sql语句
从enterprise monitor 中截取的数据如下：
在top观察的mysqld进程的res随着业务量上来后的运行不断的增加。
和同一机器Oracle的测试对比
oracle用swingbench的测试
项目 MySQL测试结果 Oracle测试结果
TPS 1000 10000
qps 20000 146463
Transactions 997 6151.5
感觉MySQL的innodb明显没有Oracle吞吐量大呀！
而且My取消binlog和把innodb_flush_log_at_trx_commit为1也没有看到性能有什么变化。
