首页 > 资讯 > 数据库 >MySQL中 sysbench如何使用

701

分享到

MySQL中 sysbench如何使用

2024-04-02 19:04:59 701人浏览泡泡鱼

摘要

Mysql中 sysbench如何使用，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。sysbench mysql,

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: '平均值'}
                ]
            }
        }
    ]
};

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注编程网数据库频道，感谢您对编程网的支持。

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL中 sysbench如何使用

本文链接: https://www.lsjlt.com/news/56081.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

oracle怎么查询当前用户所有的表

要查询当前用户拥有的所有表，可以使用以下 sql 命令：select * from user_tables; 如何查询当前用户拥有的所有表要查询当前用户拥有的所有表，可以使...

99+

2024-05-14

oracle
oracle怎么备份表中数据

oracle 表数据备份的方法包括：导出数据 (exp)：将表数据导出到外部文件。导入数据 (imp)：将导出文件中的数据导入表中。用户管理的备份 (umr)：允许用户控制备份和恢复过程...

99+

2024-05-14

oracle
oracle怎么做到数据实时备份

oracle 实时备份通过持续保持数据库和事务日志的副本来实现数据保护，提供快速恢复。实现机制主要包括归档重做日志和 asm 卷管理系统。它最小化数据丢失、加快恢复时间、消除手动备份任务...

99+

2024-05-14

oracle 数据丢失
oracle怎么查询所有的表空间

要查询 oracle 中的所有表空间，可以使用 sql 语句 "select tablespace_name from dba_tablespaces"，其中 dba_tabl...

99+

2024-05-14

oracle
oracle怎么创建新用户并赋予权限设置

答案：要创建 oracle 新用户，请执行以下步骤：以具有 create user 权限的用户身份登录；在 sql*plus 窗口中输入 create user identified ...

99+

2024-05-14

oracle
oracle怎么建立新用户

在 oracle 数据库中创建用户的方法：使用 sql*plus 连接数据库；使用 create user 语法创建新用户；根据用户需要授予权限；注销并重新登录以使更改生效。如何在 ...

99+

2024-05-14

oracle
oracle怎么创建新用户并赋予权限密码

本教程详细介绍了如何使用 oracle 创建一个新用户并授予其权限：创建新用户并设置密码。授予对特定表的读写权限。授予创建序列的权限。根据需要授予其他权限。如何使用 Oracle 创...

99+

2024-05-14

oracle
oracle怎么查询时间段内的数据记录表

在 oracle 数据库中查询指定时间段内的数据记录表，可以使用 between 操作符，用于比较日期或时间的范围。语法：select * from table_name wh...

99+

2024-05-14

oracle
oracle怎么查看表的分区

问题：如何查看 oracle 表的分区？步骤：查询数据字典视图 all_tab_partitions，指定表名。结果显示分区名称、上边界值和下边界值。如何查看 Oracle 表的分区...

99+

2024-05-14

oracle
oracle怎么导入dump文件

要导入 dump 文件，请先停止 oracle 服务，然后使用 impdp 命令。步骤包括：停止 oracle 数据库服务。导航到 oracle 数据泵工具目录。使用 impdp 命令导...

99+

2024-05-14

oracle