MySQL中 sysbench如何使用
MySQL中 sysbench如何使用，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
sysbench
mysql,postgresql,oracle,cpu，内存，磁盘，效率高，可根据特点编写lua脚本
pgbench
postgresql，专业测试pg，简单高效
sysbench 安装
sysbench2.1支持系统版本，linux,windows 10 以上，windows需要开启wsl
https://github.com/akopytov/sysbench
git clone https://github.com/akopytov/sysbench.git
./autogen.sh
# Add --with-pgsql to build with PostgreSQL support
./configure
make -j
make install
sysbench /usr/local/share/sysbench/oltp_write_only.lua \
--mysql-host=skypixel-fish.mysql.database.azure.com \
--mysql-port=3306 \
--mysql-user=dbmgr@skypixel-fish \
--mysql-password=DdBZ7pFGnPQANSCh \
--mysql-db=testdb \
--db-driver=mysql \
--tables=15 \
--table-size=5000000 \
--report-interval=3 \
--threads=256 \
--time=300 \
prepare
run
prepare/run/cleanup
https://blog.csdn.net/ashic/article/details/63388358
https://blog.csdn.net/kai404/article/details/52832848
https://blog.csdn.net/reblue520/article/details/51050682
https://www.cnblogs.com/kismetv/archive/2017/09/30/7615738.html
sysbench
sysbench /usr/local/share/sysbench/oltp_write_only.lua \
--mysql-host=skypixel-fish.mysql.database.azure.com \
--mysql-port=3306 \
--mysql-user=dbmgr@skypixel-fish \
--mysql-password=DdBZ7pFGnPQANSCh \
--mysql-db=testsys \
--db-driver=mysql \
--tables=10 \
--table-size=50 \
--report-interval=3 \
--threads=256 \
--time=600 \
prepare
cleanup
oltp_read_only.lua: -- 有各种Select语句
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
oltp_read_write.lua -- select,update,delete ,insert
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1
oltp_write_only.lua -->update 和insert语句
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1
set global general_log=ON;
tailf /var/lib/mysql/mysql01.log
oltp_read_only oltp_read_write
sysbench /usr/local/share/sysbench/oltp_read_only.lua \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=Ysysljj443322! \
--mysql-db=sysbench \
--db-driver=mysql \
--tables=10 \
--table-size=5000 \
--report-interval=3 \
--threads=2 \
--time=60 \
run
threads 越大，tps,qps越大
prepare
cleanup
http://echarts.baidu.com/echarts2/doc/example/line1.html#macarons
TPS:
grep "^\[" 1.log |awk -F'(' '{print $1}'|sed -e "s/\[\ /\{'time':'/g" -e "s/\ \]//g" -e "s/:\ /':'/g" -e "s/\ /','/g" -e "s/,'$/\}/g" > 2.log
awk -F : '{print $4}' 2.log| awk -F , '{print $1","}'| awk '{for(i=1;i<=NF;i++)a[NR,i]=$i}END{for(j=1;j<=NF;j++)for(k=1;k<=NR;k++)printf k==NR?a[k,j] RS:a[k,j] FS}'
awk -F : '{print $5","}' 2.txt|wc -l
awk -F : '{print $5","}' 2.log|awk '{for(i=1;i<=NF;i++)a[NR,i]=$i}END{for(j=1;j<=NF;j++)for(k=1;k<=NR;k++)printf k==NR?a[k,j] RS:a[k,j] FS}'
QPS:
behind:
awk -F : '{print $NF ","}' behind.log |awk '{for(i=1;i<=NF;i++)a[NR,i]=$i}END{for(j=1;j<=NF;j++)for(k=1;k<=NR;k++)printf k==NR?a[k,j] RS:a[k,j] FS}'
TPS、QPS数据绘图
option = { title : { text: 'MySQL压力测试', subtext: 'only write 55 threads ' }, tooltip : { trigger: 'axis' }, legend: { data:['TPS','QPS'] }, toolbox: { show : true, feature : { mark : {show: true}, dataView : {show: true, readOnly: false}, magicType : {show: true, type: ['line', 'bar']}, restore : {show: true}, saveAsImage : {show: true} } }, calculable : true, xAxis : [ { type : 'category', boundaryGap : false, data : ['0s','3s','6s','9s','12s','15s','18s','21s','24s','27s','30s','33s','36s','39s','42s','45s','48s','51s','54s','57s','60s','63s','66s','69s','72s','75s','78s','81s','84s','87s','90s','93s','96s','99s','102s','105s','108s','111s','114s','117s','120s','123s','126s','129s','132s','135s','138s','141s','144s','147s','150s','153s','156s','159s','162s','165s','168s','171s','174s','177s','180s','183s','186s','189s','192s','195s','198s','201s','204s','207s','210s','213s','216s','219s','222s','225s','228s','231s','234s','237s','240s','243s','246s','249s','252s','255s','258s','261s','264s','267s','270s','273s','276s','279s','282s','285s','288s','291s','294s','297s','300s'] } ], yAxis : [ { type : 'value', axisLabel : { formatter: '{value} ' } } ], series : [ { name:'TPS', type:'line', data:[35.63,38.69,52.31,40.33,51.33,40.67,50.67,41.67,48.67,43.35,48.97,43.02,48.98,41.66,48.34,43.66,39.67,49.67,51.33,43.00,41.02,47.64,42.67,48.68,42.66,49.69,41.66,49.33,42.68,49.32,42.33,49.01,42.65,47.66,44.34,48.66,43.35,46.33,45.99,45.04,46.33,45.33,47.65,45.33,43.99,46.67,45.33,45.01,48.69,37.31,36.34,37.66,54.01,37.66,53.66,39.01,51.33,40.32,52.01,39.66,51.67,40.03,51.29,40.01,51.32,41.01,39.00,52.34,39.68,51.33,40.65,51.01,41.00,49.99,42.68,49.32,42.34,48.01,42.99,49.68,42.33,49.68,42.65,47.34,45.01,46.65,45.67,46.35,45.01,47.65,44.99,46.01,44.66,46.36,44.65,47.67,41.00,44.99,46.66,45.67], markPoint : { data : [ {type : 'max', name: '最大值'}, {type : 'min', name: '最小值'} ] }, markLine : { data : [ {type : 'average', name: '平均值'} ] } }, { name:'QPS', type:'line', data:[270.03,254.81,274.86,277.00,273.67,275.00,275.67,272.67,268.01,274.12,273.86,273.49,269.22,269.61,273.36,277.64,267.05,281.01,276.31,274.98,267.44,272.83,271.35,273.39,274.93,276.80,270.95,269.66,279.08,276.94,272.66,274.74,273.22,273.62,277.70,276.32,272.41,274.96,270.28,270.23,278.31,274.95,272.59,276.66,268.63,278.33,274.31,273.05,276.49,259.48,185.02,271.97,274.39,273.97,276.66,274.71,265.33,272.27,274.37,274.65,272.67,266.51,272.44,274.41,276.27,275.38,263.67,285.02,262.09,283.67,267.56,271.71,274.99,273.59,275.76,269.25,281.03,277.70,275.96,273.73,266.31,277.08,275.55,274.69,273.03,268.55,284.38,269.08,272.72,277.93,270.60,275.75,273.60,275.47,271.91,278.69,271.00,270.63,274.31,275.68], markPoint : { data : [ {type : 'max', name: '最大值'}, {type : 'min', name: '最小值'} ] }, markLine : { data : [ {type : 'average', name: '平均值'} ] } } ]};中从延时数据绘图
option = { title : { text: 'MySQL压力测试', subtext: 'read and write 170 threads ' }, tooltip : { trigger: 'axis' }, legend: { data:['Seconds_Behind_Master'] }, toolbox: { show : true, feature : { mark : {show: true}, dataView : {show: true, readOnly: false}, magicType : {show: true, type: ['line', 'bar']}, restore : {show: true}, saveAsImage : {show: true} } }, calculable : true, xAxis : [ { type : 'category', boundaryGap : false, data : ['0s','3s','6s','9s','12s','15s','18s','21s','24s','27s','30s','33s','36s','39s','42s','45s','48s','51s','54s','57s','60s','63s','66s','69s','72s','75s','78s','81s','84s','87s','90s','93s','96s','99s','102s','105s','108s','111s','114s','117s','120s','123s','126s','129s','132s','135s','138s','141s','144s','147s','150s','153s','156s','159s','162s','165s','168s','171s','174s','177s','180s','183s','186s','189s','192s','195s','198s','201s','204s','207s','210s','213s','216s','219s','222s','225s','228s','231s','234s','237s','240s','243s','246s','249s','252s','255s','258s','261s','264s','267s','270s','273s','276s','279s','282s','285s','288s','291s','294s','297s','300s'] } ], yAxis : [ { type : 'value', axisLabel : { formatter: '{value} s' } } ], series : [ { name:'Seconds_Behind_Master', type:'line', data:[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], markPoint : { data : [ {type : 'max', name: '最大值'}, {type : 'min', name: '最小值'} ] }, markLine : { data : [ {type : 'average', name: '平均值'} ] } } ]};看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。
