MySQL压测工具--sysbench

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

SysBench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况。它主要包括以下几种方式的测试: 

1、cpu性能 
2、磁盘io性能 
3、调度程序性能 
4、内存分配及传输速度 
5、POSIX线程性能 
6、数据库性能(OLTP基准测试)  
1、安装
安装方法1:yum
#sudo yum -y install sysbench

安装方法2:二进制包
#yum -y install make automake libtool pkgconfig libaio-devel vim-common
#yum -y install mariadb-devel
#yum -y install postgresql-devel
#./autogen.sh
#./configure
#make
#make install

2、General syntax
The general command line syntax for sysbench is:
sysbench [options]... [testname] [command]
  • testname is an optional name of a built-in test (e.g. fileio, memory, cpu, etc.), or a name of one of the bundled Lua scripts (e.g. oltp_read_only), or a path to a custom Lua script. If no test name is specified on the command line (and thus, there is no command too, as in that case it would be parsed as a testname), or the test name is a dash ("-"), then sysbench expects a Lua script to execute on its standard input.
  • command is an optional argument that will be passed by sysbench to the built-in test or script specified with testname.command defines the action that must be performed by the test. The list of available commands depends on a particular test. Some tests also implement their own custom commands.
    Below is a description of typical test commands and their purpose:
    • prepare: performs preparative actions for those tests which need them, e.g. creating the necessary files on disk for thefileio test, or filling the test database for database benchmarks.
    • run: runs the actual test specified with the testname argument. This command is provided by all tests.
    • cleanup: removes temporary data after the test run in those tests which create one.
    • help: displays usage information for the test specified with the testname argument. This includes the full list of commands provided by the test, so it should be used to get the available commands.
  • options is a list of zero or more command line options starting with '--'. As with commands, the sysbench testname help command should be used to describe available options provided by a particular test.
    See General command line options for a description of general options provided by sysbench itself.
You can use   sysbench --help   to display the general command line syntax and options.

3、MySQL测试:
[root@172-16-10-204 /usr/local/share/sysbench]
#sysbench --test=oltp_insert.lua --mysql-host=127.0.0.1 --mysql-socket=/data/mysql/run/mysql.sock --mysql-db=test --mysql-user=newdba --tables=1 --table-size=5000000 --threads=1500 --report-interval=1 --time=10 --mysql-password=yuelei66 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0-76eeace (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 5000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

[root@172-16-10-204 /usr/local/share/sysbench]
#sysbench --test=oltp_insert.lua --mysql-host=127.0.0.1 --mysql-socket=/data/mysql/run/mysql.sock --mysql-db=test --mysql-user=newdba --tables=1 --table-size=5000000 --threads=1500 --report-interval=1 --time=10 --mysql-password=yuelei66 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0-76eeace (using bundled LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 1s ] thds: 1500 tps: 1014.63 qps: 1014.63 (r/w/o: 0.00/1014.63/0.00) lat (ms,95%): 995.51 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1500 tps: 3261.88 qps: 3261.88 (r/w/o: 0.00/3261.88/0.00) lat (ms,95%): 1589.90 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1500 tps: 5367.33 qps: 5367.33 (r/w/o: 0.00/5367.33/0.00) lat (ms,95%): 1648.20 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1500 tps: 3507.11 qps: 3507.11 (r/w/o: 0.00/3507.11/0.00) lat (ms,95%): 977.74 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1500 tps: 6685.48 qps: 6685.48 (r/w/o: 0.00/6685.48/0.00) lat (ms,95%): 1069.86 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1500 tps: 6392.54 qps: 6392.54 (r/w/o: 0.00/6392.54/0.00) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1500 tps: 6156.24 qps: 6156.24 (r/w/o: 0.00/6156.24/0.00) lat (ms,95%): 746.32 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1500 tps: 7873.09 qps: 7873.09 (r/w/o: 0.00/7873.09/0.00) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1500 tps: 6311.22 qps: 6313.22 (r/w/o: 0.00/6313.22/0.00) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1500 tps: 6545.22 qps: 6543.22 (r/w/o: 0.00/6543.22/0.00) lat (ms,95%): 601.29 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           54405
        other:                           0
        total:                           54405
    transactions:                        54405  (5133.03 per sec.)
    queries:                             54405  (5133.03 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      5133.0279
    time elapsed:                        10.5990s
    total number of events:              54405

Latency (ms):
         min:                                  3.36
         avg:                                279.83
         max:                               4505.81
         95th percentile:                    960.30
         sum:                            15224111.99

Threads fairness:
    events (avg/stddev):           36.2700/6.32
    execution time (avg/stddev):   10.1494/0.11

[root@172-16-10-204 /usr/local/share/sysbench]
#sysbench --test=oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-socket=/data/mysql/run/mysql.sock --mysql-db=test --mysql-user=newdba --tables=1 --table-size=5000000 --threads=1500 --report-interval=1 --time=10 --mysql-password=yuelei66 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0-76eeace (using bundled LuaJIT 2.1.0-beta3)

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


Initializing worker threads...

Threads started!

[ 1s ] thds: 1500 tps: 17.14 qps: 5214.99 (r/w/o: 3956.14/0.00/1258.85) lat (ms,95%): 1109.09 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1500 tps: 169.16 qps: 5539.21 (r/w/o: 5200.89/0.00/338.32) lat (ms,95%): 2120.76 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1500 tps: 286.12 qps: 5839.38 (r/w/o: 5267.15/0.00/572.23) lat (ms,95%): 3040.14 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1500 tps: 334.88 qps: 5908.95 (r/w/o: 5239.19/0.00/669.77) lat (ms,95%): 3982.86 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1500 tps: 387.77 qps: 6935.90 (r/w/o: 6161.36/0.00/774.54) lat (ms,95%): 5033.35 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1500 tps: 556.79 qps: 8732.39 (r/w/o: 7618.81/0.00/1113.58) lat (ms,95%): 6026.41 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1500 tps: 527.97 qps: 8097.57 (r/w/o: 7040.62/0.00/1056.94) lat (ms,95%): 6835.96 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1500 tps: 588.31 qps: 9055.04 (r/w/o: 7878.42/0.00/1176.63) lat (ms,95%): 7895.16 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1500 tps: 609.69 qps: 10273.50 (r/w/o: 9054.12/0.00/1219.38) lat (ms,95%): 8484.79 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1500 tps: 608.94 qps: 9583.36 (r/w/o: 8503.34/0.00/1080.03) lat (ms,95%): 7215.39 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 1500 tps: 963.83 qps: 8534.32 (r/w/o: 7570.49/0.00/963.83) lat (ms,95%): 5033.35 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            76384
        write:                           0
        other:                           10912
        total:                           87296
    transactions:                        5456   (462.84 per sec.)
    queries:                             87296  (7405.38 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      462.8360
    time elapsed:                        11.7882s
    total number of events:              5456

Latency (ms):
         min:                                  3.39
         avg:                               2960.73
         max:                              11392.48
         95th percentile:                   7086.63
         sum:                            16153720.07

Threads fairness:
    events (avg/stddev):           3.6373/1.30
    execution time (avg/stddev):   10.7691/0.44

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
canal 消息中间件 关系型数据库
Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
【9月更文挑战第1天】Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
831 4
|
7天前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
47 11
|
1月前
|
关系型数据库 MySQL 测试技术
【赵渝强老师】MySQL的基准测试与sysbench
本文介绍了MySQL数据库的基准测试及其重要性,并详细讲解了如何使用sysbench工具进行测试。内容涵盖sysbench的安装、基本使用方法,以及具体测试MySQL数据库的步骤,包括创建测试数据库、准备测试数据、执行测试和清理测试数据。通过这些步骤,可以帮助读者掌握如何有效地评估MySQL数据库的性能。
|
2月前
|
SQL 分布式计算 关系型数据库
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
Hadoop-21 Sqoop 数据迁移工具 简介与环境配置 云服务器 ETL工具 MySQL与Hive数据互相迁移 导入导出
91 3
|
3月前
|
SQL 缓存 关系型数据库
MySQL高级篇——性能分析工具
MySQL的慢查询日志,用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long-query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为 10,意思是运行10秒以上(不含10秒)的语句,认为是超出了我们的最大忍耐时间值。它的主要作用是,帮助我们发现那些执行时间特别长的 SOL 查询,并且有针对性地进行优化,从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候,检查一下慢查询日志,找到那些慢查询,对解决问题很有帮助。
MySQL高级篇——性能分析工具
|
3月前
|
安全 关系型数据库 MySQL
Navicat工具设置MySQL权限的操作指南
通过上述步骤,您可以使用Navicat有效地为MySQL数据库设置和管理用户权限,确保数据库的安全性和高效管理。这个过程简化了数据库权限管理,使其既直观又易于操作。
463 4
|
4月前
|
SQL 关系型数据库 MySQL
在Linux中,mysql 数据备份工具有哪些?
在Linux中,mysql 数据备份工具有哪些?
|
4月前
|
SQL 存储 关系型数据库
MySQL备份:mydumper 备份恢复工具生产实战
MySQL备份:mydumper 备份恢复工具生产实战
|
4月前
|
关系型数据库 MySQL 数据库
MySQL回滚工具:binlog 闪回工具 MyFlash工具
MySQL回滚工具:binlog 闪回工具 MyFlash工具
|
4月前
|
关系型数据库 MySQL OLTP
性能工具之 MySQL OLTP Sysbench BenchMark 测试示例
【8月更文挑战第6天】使用 pt-query-digest 工具分析 MySQL 慢日志性能工具之 MySQL OLTP Sysbench BenchMark 测试示例
325 0
性能工具之 MySQL OLTP Sysbench BenchMark 测试示例

推荐镜像

更多