本文以sysbench为出发点，先从Lua/LuaJIT到sysbench概念上进行初步的认识，然后到编译安装和使用，最后介绍了怎么来实现CPU、内存、文件IO、数据库各种模式的测试。总共4大块循序渐进来了解sysbench的概念、安装、使用、系统资源及MySQL和PostreSQL数据库场景的测试。
1 关于sysbench
在介绍sysbench之前，先了解下Lua语言和LuaJIT两个概念
Lua
什么是Lua? Lua是一种强大、高效、轻量级、可嵌入的脚本语言。它支持过程式编程、面向对象编程、函数式编程、数据驱动编程和数据描述。
Lua结合了简单的过程语法和基于关联数组和可扩展语义的强大数据描述结构。Lua是动态类型的，通过使用基于寄存器的虚拟机解释字节码来运行，并具有自动内存管理和增量垃圾收集，使其非常适合配置、脚本编制和快速原型。
Lua从何而来? Lua是由 PUC-Rio (巴西里约热内卢天主教大学)的一个团队设计、实现和维护的。Lua在大学中的 Tecgraf 实验室出生并长大，这里的前身是PUC-Rio计算机图形技术集团。Lua现在位于里约大学计算机科学系LabLua实验室。
名字有什么意义? “Lua”(发音为LOO-ah)在葡萄牙语中意为“月亮”。更具体地说，“Lua”是一个名字，是地球月球的名字，也是语言的名字。
为什么选择Lua?
- Lua是一种经过验证的健壮语言 Lua已被用于许多工业应用，特别是嵌入式系统(和游戏(例如魔兽世界和愤怒的小鸟)。Lua是目前游戏中最主要的脚本语言。
- Lua速度很快 Lua在性能方面享有当之无愧的声誉。宣称“和Lua一样快”是其他脚本语言的愿望。几个基准测试显示Lua是解释型脚本语言领域中最快的语言。Lua不仅在经过微调的基准程序中速度很快，在现实生活中也是如此。大部分大型应用程序都是用Lua编写的。 如果您需要更快的速度，可以尝试 LuaJIT，这是一个使用即时编译器的Lua的独立实现。
- Lua是可移植的 Lua是以一个小包的形式发布的，可以在所有具有标准C编译器的平台上开箱即用。Lua可以在各种Unix和Windows上运行，在移动设备(运行Android、iOS、BREW、Symbian、Windows Phone)上运行，在嵌入式微处理器(如ARM和Rabbit，用于乐高MindStorms等应用程序)上运行，在IBM大型机上运行等等。
- Lua是可嵌入的 Lua是一个快速的语言引擎，占用空间小，可以很容易地嵌入到应用程序中。Lua有一个简单且有良好文档的API，允许与其他语言编写的代码进行强集成。用其他语言编写的库来扩展Lua是很容易的。用Lua扩展用其他语言编写的程序也很容易。
- Lua简单但功能强大 Lua设计中的一个基本概念是为实现特性提供元机制，而不是直接在语言中提供一系列特性。例如，尽管Lua不是纯粹的面向对象语言，但它确实为实现类和继承提供了元机制。Lua的元机制带来了概念经济，并保持语言较小，同时允许以非常规的方式扩展语义。
- Lua很小 将Lua添加到应用程序中不会使其膨胀。Lua 5.4.4的tarball包含源代码和文档，压缩值为353K，未压缩值为1.3M。源代码包含大约30000行c语言。在64位Linux下，用所有标准Lua库构建的Lua解释器占用281K, Lua库占用468K。
- Lua是免费的 Lua是免费的开源软件，在一个非常自由的许可证(著名的MIT许可证)下发布。它可以用于任何目的，包括商业目的，绝对免费。下载并使用即可。
LuaJIT
LuaJIT是Lua编程语言的即时编译器(JIT)，基本兼容大部分的平台。
LuaJIT已经成功地用作游戏、设备、网络和图形应用程序、数值模拟、交易平台和许多其他专业应用程序的脚本中间件。它的范围从嵌入式设备、智能手机、台式机到服务器群。它结合了高灵活性、高性能和无与伦比的低内存占用。
LuaJIT从2005年开始不断发展。它被广泛认为是最快的动态语言实现之一。自第一次发布以来，它在许多跨语言基准测试上的表现都超过了其他动态语言——通常是相当大的差距。
对于LuaJIT 2.0，整个VM从头开始重写，并对性能进行了不懈的优化。它结合了用汇编语言编写的高速解释器和最先进的JIT编译器。
创新的跟踪编译器集成了高级的、基于SSA的优化和高度调优的代码生成后端。与动态语言相关的开销的大量减少使它能够进入传统上为脱机静态语言编译器保留的性能范围。
sysbench介绍
sysbench是一个基于LuaJIT的可脚本多线程基准测试工具。它最常用于数据库基准测试，但也可用于创建不涉及数据库服务器的任意复杂工作负载。
Sysbench附带了以下捆绑的基准测试:
oltp_*.lua:类似oltp的数据库基准的集合fileio:文件系统级基准测试cpu:简单的cpu基准测试memory:内存访问基准threads:基于线程的调度器基准mutex: POSIX互斥基准
特性
- 关于速率和延迟的大量统计数据是可用的，包括延迟百分比和直方图;
- 即使有数千个并发线程，开销也很低。Sysbench能够每秒生成和跟踪数亿个事件;
- 通过在用户提供的Lua脚本中实现预定义的钩子，可以轻松创建新的基准测试;
- 可以用作通用的Lua解释器，只需使用
#!/usr/bin/sysbench替换#!/usr/bin/lua。
2 sysbench安装
二进制安装
在Linux上下载和安装sysbench最简单的方法是使用 packagecloud 托管的二进制包存储库。每个sysbench版本都会自动更新存储库。目前可以使用x86_64、i386和aarch64二进制文件。
有多种下载和安装sysbench包的方法，请参见 https://packagecloud.io/akopytov/sysbench/install 。
从sysbench 1.0开始，不再支持原生Windows版本。目前在Windows环境下，sysbench的获取方式推荐使用Windows 10自带的Windows Subsystem for Linux。此处以WSL下的Ubuntu为例
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench
如果是RHEL/CentOS系统可以参考如下命令进行联机在线安装:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
源码安装
一般针对于不能连网的内网、定制安装或是在没有二进制包可用的架构上选择源码编译安装sysbench。
- 编译安装依赖包
--RHEL/CentOS
yum -y install make automake libtool pkgconfig libaio-devel
# 支持MySQL，在RHEL/CentOS 5以mysql-devel替换
yum -y install mariadb-devel openssl-devel
# 支持PostgreSQL support
yum -y install postgresql-devel
- 通过 https://github.com/akopytov/sysbench/releases 下载源码
# ls -lh sysbench-1.0.20.tar.gz
-rw-r--r-- 1 root root 1.5M Apr 27 17:12 sysbench-1.0.20.tar.gz
# tar -zxf sysbench-1.0.20.tar.gz
# ls sysbench-1.0.20
autogen.sh COPYING m4 README.md scripts third_party
ChangeLog debian Makefile.am README-Oracle.md snap
config doc missing README-WIN.txt src
configure.ac install-sh mkinstalldirs rpm tests
- 构建和安装
# 解压安装包并cd到源码目录，运行autogen.sh，生成configure目录
./autogen.sh
# 增加--with-pgsql可以构建支持PostgreSQL，具体可参考./configure -help
# ./configure
# make -j
# make install
上面的代码将在默认情况下构建支持MySQL的sysbench。如果你的MySQL头文件和库在非标准的位置(在PATH中找不到mysql_config)，你可以在 ./configure中使用--with-mysql-includes和--with-mysql-libs选项显式地指定它们。
要在不支持MySQL的情况下编译sysbench，使用--without-mysql。如果没有可用的数据库驱动程序，与数据库相关的脚本将无法工作，但其他基准测试将正常工作。
$ sysbench
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Reading the script from the standard input:
- CentOS7.7下安装sysbench版本
1.0.20,支持MySQL和PostgreSQL,此处PostgreSQL路径/u01/app/pgsql
# yum install maraidb mariadb-devel
--不安装MySQL和MySQL Dev包，make时会出现如下报错：
drv_mysql.c:37:19: fatal error: mysql.h: No such file or directory
#include
# ./autogen.sh
...
# ./configure --prefix=/usr/local/sysbench --with-mysql
--with-pgsql --with-pgsql-includes=/u01/app/pgsql/include --with-pgsql-libs=/u01/app/pgsql/lib
...
# make -j
...
# make install
...
# ls /usr/local/sysbench/
bin share
# export LD_LIBRARY_PATH=/u01/app/pgsql/lib:$LD_LIBRARY_PATH
# /usr/local/sysbench/bin/sysbench --version
sysbench 1.0.20
3 sysbench使用
sysbench的命令行语法如下:
sysbench [options]... [testname] [command]
testname是一个内置测试的可选名称(例如，fileio，memory，cpu等)，或者一个捆绑的Lua脚本的名称(例如oltp_read_only)，或者一个自定义Lua脚本的路径。如果在命令行上没有指定测试名(因此，也没有命令，因为在这种情况下，它将被解析为testname)，或者测试名是破虚号("-")，那么sysbench期望在其标准输入上执行Lua脚本。command是一个可选参数，将由sysbench传递给内置测试或用testname指定的脚本。命令定义测试必须执行的动作。可用命令的列表取决于特定的测试。一些测试还实现了它们自己的自定义命令。 下面是典型的测试命令及其用途的描述:prepare:为那些需要它们的测试执行准备操作，例如在磁盘上为文件测试创建必要的文件，或为数据库基准测试填充测试数据库。run:运行testname参数指定的实际测试。所有测试都提供此命令。cleanup:在创建临时数据的测试运行后删除临时数据。help:显示使用参数 testname 指定的测试的使用信息。这包括测试提供的完整命令列表，因此应该使用它来获取可用的命令。
options是一个包含0个或多个以“--”开头的命令行选项的列表。与命令一样，应该使用sysbench testname help命令描述特定测试提供的可用选项。
您可以使用sysbench --help来显示一般的命令行语法和选项。
命令行选项
支持的常用选项及其说明和默认值如下表所示:
| 选项 | 描述 | 默认值 |
|---|---|---|
| --threads | 创建1的工作线程总数 | 1 |
| --events | 请求总数的限制。0(默认值)表示没有限制 | 0 |
| --time | 总执行时间限制，以秒为单位。0表示不限制 | 10 |
| --warmup-time | 在启用统计信息运行实际基准测试之前，在禁用统计信息的情况下执行事件这么多秒。当您希望从统计数据中排除基准测试运行的初始阶段时，这非常有用。在许多基准测试中，初始时间并不具有代表性，因为CPU/数据库/页面和其他缓存需要一些时间来预热 | 0 |
| --rate | 这个数字指定了所有线程平均每秒钟应该执行多少事件(事务)。0(默认)表示无限速率，即事件以尽可能快的速度执行 | 0 |
| --thread-init-timeout | 工作线程初始化的等待时间，单位为秒 | 30 |
| --thread-stack-size | 每个线程的堆栈大小 | 32k |
| --report-interval | 以间隔时间为秒定期上报中间统计信息。注意，此选项生成的统计信息是按间隔计算的，而不是累积的。0禁用中间报告 | 0 |
| --debug | 打印更多的调试信息 | off |
| --validate | 在可能的情况下对测试结果进行验证 | off |
| --help | 打印通用语法或指定测试的帮助并退出 | off |
| --verbosity | 详细级别(0 -只有关键消息，5 -调试) | 4 |
| --percentile | sysbench测量所有已处理请求的执行时间，以显示最小、平均和最大执行时间等统计信息。对于大多数基准测试，知道一个匹配某个百分比的请求执行时间值也是有用的(例如，95%百分比意味着我们应该放弃最长请求的5%，并从剩余的请求中选择最大值)。此选项允许指定查询执行次数的百分位数用于计数 | 95 |
| --luajit-cmd | 执行一个LuaJIT控制命令。这个选项相当于luajit -j |
随机数字选项
sysbench提供了许多算法来生成随机数，这些随机数根据给定的概率分布进行分布。下表列出了可用于控制这些算法的选项。
| 选项 | 描述 | 默认值 |
|---|---|---|
| --rand-type | 随机型随机数分布{uniform, gaussian, special, pareto, zipfian}默认使用。基准测试脚本可以选择使用默认分布，或者显式地指定它，即覆盖默认发行版。 | special |
| --rand-seed | 随机数生成器的种子。当为0时，使用当前时间作为RNG种子。 | 0 |
| rand-spec-iter | 特殊分布的迭代次数 | 12 |
| rand-spec-pct | 在特殊分布中的特殊值占整个范围的百分比 | 1 |
| rand- specres | 用于特殊分布的“special”值的百分比 | 75 |
| --rand-pareto-h | 帕累托分布的形状参数 | 0.2 |
| --rand-zipfian-exp | Zipfian分布的形状参数(theta) | 0.8 |
命令参考
sysbench --help命令结果如下
- 通用数据库选项
General database options:
--db-driver=STRING specifies database driver to use ('help' to get list of available drivers) [mysql]
--db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
--db-debug[=on|off] print database-specific debug information [off]
- 其中默认支持的mysql
mysql options:
--mysql-host=[LIST,...] MySQL server host [localhost]
--mysql-port=[LIST,...] MySQL server port [3306]
--mysql-socket=[LIST,...] MySQL socket
--mysql-user=STRING MySQL user [sbtest]
--mysql-password=STRING MySQL password []
--mysql-db=STRING MySQL database name [sbtest]
--mysql-ssl[=on|off] use SSL connections, if available in the client library [off]
--mysql-ssl-cipher=STRING use specific cipher for SSL connections []
--mysql-compression[=on|off] use compression, if available in the client library [off]
--mysql-debug[=on|off] trace all client library calls [off]
--mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
--mysql-dry-run[=on|off] Dry run, pretend that all MySQL client API calls are successful without executing them [off]
4 sysbench测试
下面将讲述怎么通过sysbench对cpu、内存、文件IO及数据库进行测试，主要通过调用sysbench命令和相关的参数来实现。
CPU测试
- 通过
sysbench cpu help，CPU主要是通过对素数的运行来检查CPU的运行统计，默认素数总上限为10000。素数就是除1和它自身之外，不能被其他自然数整除的数， 也叫质数
# sysbench cpu help
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
cpu options:
--cpu-max-prime=N upper limit for primes generator [10000]
- 测试命令及结果输出如下：
# sysbench --threads=32 --time=30 --report-interval=2 cpu run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 32
Report intermediate results every 2 second(s)
Initializing random number generator from current time
Prime numbers limit: 10000
Initializing worker threads...
Threads started!
[ 2s ] thds: 32 eps: 28914.97 lat (ms,95%): 1.12
[ 4s ] thds: 32 eps: 28985.22 lat (ms,95%): 1.12
[ 6s ] thds: 32 eps: 28952.48 lat (ms,95%): 1.12
[ 8s ] thds: 32 eps: 28985.91 lat (ms,95%): 1.12
[ 10s ] thds: 32 eps: 28947.79 lat (ms,95%): 1.12
[ 12s ] thds: 32 eps: 28974.74 lat (ms,95%): 1.12
[ 14s ] thds: 32 eps: 28974.41 lat (ms,95%): 1.12
[ 16s ] thds: 32 eps: 28988.54 lat (ms,95%): 1.12
[ 18s ] thds: 32 eps: 28984.86 lat (ms,95%): 1.12
[ 20s ] thds: 32 eps: 28989.35 lat (ms,95%): 1.12
[ 22s ] thds: 32 eps: 28981.76 lat (ms,95%): 1.12
[ 24s ] thds: 32 eps: 28959.09 lat (ms,95%): 1.12
[ 26s ] thds: 32 eps: 28996.98 lat (ms,95%): 1.10
[ 28s ] thds: 32 eps: 28995.49 lat (ms,95%): 1.10
CPU speed:
events per second: 28972.84
General statistics:
total time: 30.0014s
total number of events: 869283
Latency (ms):
min: 1.10
avg: 1.10
max: 7.43
95th percentile: 1.12
sum: 959709.65
Threads fairness:
events (avg/stddev): 27165.0938/84.41
execution time (avg/stddev): 29.9909/0.00
内存测试
- 内存测试主要包括如下参数，块大小（默认1K）、内存总大小（默认100G）、没存访问的区域、是否启用大页、内存的操作（默认写）、内存访问模式（默认顺序）
# sysbench memory help
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
memory options:
--memory-block-size=SIZE size of memory block for test [1K]
--memory-total-size=SIZE total size of data to transfer [100G]
--memory-scope=STRING memory access scope {global,local} [global]
--memory-hugetlb[=on|off] allocate memory from HugeTLB pool [off]
--memory-oper=STRING type of memory operations {read, write, none} [write]
--memory-access-mode=STRING memory access mode {seq,rnd} [seq]
- 4个并行顺序测试内存块为8K，总时间30秒，每秒输出其结果
# sysbench --threads=4 --time=30 --report-interval=1 memory --memory-block-size=8k --memory-access-mode=seq run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 4
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Running memory speed test with the following options:
block size: 8KiB
total size: 102400MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
[ 1s ] 2903.00 MiB/sec
[ 2s ] 2896.92 MiB/sec
[ 3s ] 2884.34 MiB/sec
[ 4s ] 2874.30 MiB/sec
[ 5s ] 2872.00 MiB/sec
[ 6s ] 2856.93 MiB/sec
[ 7s ] 2888.23 MiB/sec
[ 8s ] 2913.26 MiB/sec
[ 9s ] 2858.52 MiB/sec
[ 10s ] 2871.02 MiB/sec
[ 11s ] 2854.56 MiB/sec
[ 12s ] 2887.48 MiB/sec
[ 13s ] 2893.52 MiB/sec
[ 14s ] 2903.86 MiB/sec
[ 15s ] 2902.05 MiB/sec
[ 16s ] 2886.12 MiB/sec
[ 17s ] 2891.93 MiB/sec
[ 18s ] 2902.01 MiB/sec
[ 19s ] 2866.51 MiB/sec
[ 20s ] 2893.74 MiB/sec
[ 21s ] 2880.06 MiB/sec
[ 22s ] 2855.68 MiB/sec
[ 23s ] 2875.77 MiB/sec
[ 24s ] 2901.61 MiB/sec
[ 25s ] 2909.31 MiB/sec
[ 26s ] 2889.32 MiB/sec
[ 27s ] 2908.84 MiB/sec
[ 28s ] 2927.31 MiB/sec
[ 29s ] 2875.35 MiB/sec
Total operations: 11087394 (369556.82 per second)
86620.27 MiB transferred (2887.16 MiB/sec)
General statistics:
total time: 30.0002s
total number of events: 11087394
Latency (ms):
min: 0.00
avg: 0.01
max: 0.39
95th percentile: 0.02
sum: 114753.86
Threads fairness:
events (avg/stddev): 2771848.5000/55556.57
execution time (avg/stddev): 28.6885/0.11
文件IO测试
sysbench在一定程度上可以通过fileio来测试磁盘的性能。可以通过sysbench fileio help来查看测试文件IO有哪些参数。
- 其中函数
fsync()是指将同步内存中所有已修改的文件数据到储存设备。 - 必选参数
file-test-mode用来指定测试模式，其中顺序写、顺序读、顺序读写、随机读、随机写、随机读写。 - 场景：准备两个文件各5GB大小，4个并行顺序读写60秒，测试完成后移除文件
--准备文件
# sysbench --threads=4 --time=60 --report-interval=5 fileio --file-num=2 --file-total-size=10G --file-test-mode=seqwr prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
2 files, 5242880Kb each, 10240Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Creating file test_file.1
10737418240 bytes written in 21.12 seconds (484.91 MiB/sec).
# ls -lh test_file.*
-rw------- 1 root root 5.0G Jul 4 10:20 test_file.0
-rw------- 1 root root 5.0G Jul 4 10:20 test_file.1
--进行测试每5秒输出结果
# sysbench --threads=4 --time=60 --report-interval=5 fileio --file-num=2 --file-total-size=10G --file-test-mode=seqwr run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Extra file open flags: (none)
2 files, 5GiB each
10GiB total file size
Block size 16KiB
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential write (creation) test
Initializing worker threads...
Threads started!
[ 5s ] reads: 0.00 MiB/s writes: 275.40 MiB/s fsyncs: 352.25/s latency (ms,95%): 0.234
[ 10s ] reads: 0.00 MiB/s writes: 269.34 MiB/s fsyncs: 344.61/s latency (ms,95%): 0.226
[ 15s ] reads: 0.00 MiB/s writes: 254.94 MiB/s fsyncs: 326.40/s latency (ms,95%): 0.266
[ 20s ] reads: 0.00 MiB/s writes: 261.37 MiB/s fsyncs: 334.38/s latency (ms,95%): 0.247
[ 25s ] reads: 0.00 MiB/s writes: 272.24 MiB/s fsyncs: 348.61/s latency (ms,95%): 0.243
[ 30s ] reads: 0.00 MiB/s writes: 263.19 MiB/s fsyncs: 337.00/s latency (ms,95%): 0.266
[ 35s ] reads: 0.00 MiB/s writes: 239.12 MiB/s fsyncs: 305.99/s latency (ms,95%): 0.318
[ 40s ] reads: 0.00 MiB/s writes: 323.71 MiB/s fsyncs: 414.20/s latency (ms,95%): 0.158
[ 45s ] reads: 0.00 MiB/s writes: 594.19 MiB/s fsyncs: 760.61/s latency (ms,95%): 0.084
[ 50s ] reads: 0.00 MiB/s writes: 639.52 MiB/s fsyncs: 818.95/s latency (ms,95%): 0.073
[ 55s ] reads: 0.00 MiB/s writes: 612.18 MiB/s fsyncs: 782.84/s latency (ms,95%): 0.078
File operations:
reads/s: 0.00
writes/s: 24651.33
fsyncs/s: 493.16
Throughput:
read, MiB/s: 0.00
written, MiB/s: 385.18
General statistics:
total time: 60.0027s
total number of events: 1508789
Latency (ms):
min: 0.00
avg: 0.16
max: 36.39
95th percentile: 0.12
sum: 239474.07
Threads fairness:
events (avg/stddev): 377197.2500/2496.98
execution time (avg/stddev): 59.8685/0.00
--移除文件
# sysbench --threads=4 --time=60 --report-interval=5 fileio --file-num=2 --file-total-size=10G --file-test-mode=seqwr cleanup
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Removing test files...
- 场景：单文件 16并发 20G 8k数据块 随机读写 执行20秒 debug输出
--准备文件
# sysbench --threads=16 --time=20 --test=fileio --file-num=1 --file-block-size=8192 --file-total-size=20G --file-test-mode=rndrw --verbosity=5 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
1 files, 20971520Kb each, 20480Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
21474836480 bytes written in 58.80 seconds (348.32 MiB/sec).
--fileio测试
# sysbench --threads=16 --time=20 --test=fileio --file-num=1 --file-block-size=8192 --file-total-size=20G --file-test-mode=rndrw --verbosity=5 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Initializing random number generator from current time
Extra file open flags: (none)
1 files, 20GiB each
20GiB total file size
Block size 8KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
DEBUG: Opening file: test_file.0
Initializing worker threads...
DEBUG: Worker thread (#0) started
DEBUG: Worker thread (#0) initialized
DEBUG: Worker thread (#1) started
DEBUG: Worker thread (#1) initialized
DEBUG: Worker thread (#2) started
DEBUG: Worker thread (#2) initialized
DEBUG: Worker thread (#3) started
DEBUG: Worker thread (#3) initialized
DEBUG: Worker thread (#4) started
DEBUG: Worker thread (#4) initialized
DEBUG: Worker thread (#5) started
DEBUG: Worker thread (#5) initialized
DEBUG: Worker thread (#6) started
DEBUG: Worker thread (#6) initialized
DEBUG: Worker thread (#8) started
DEBUG: Worker thread (#8) initialized
DEBUG: Worker thread (#7) started
DEBUG: Worker thread (#7) initialized
DEBUG: Worker thread (#9) started
DEBUG: Worker thread (#9) initialized
DEBUG: Worker thread (#10) started
DEBUG: Worker thread (#10) initialized
DEBUG: Worker thread (#11) started
DEBUG: Worker thread (#11) initialized
DEBUG: Worker thread (#12) started
DEBUG: Worker thread (#14) started
DEBUG: Worker thread (#14) initialized
DEBUG: Worker thread (#13) started
DEBUG: Worker thread (#13) initialized
DEBUG: Worker thread (#15) started
DEBUG: Worker thread (#12) initialized
DEBUG: Worker thread (#15) initialized
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.
File operations:
reads/s: 43209.38
writes/s: 28806.79
fsyncs/s: 720.92
Throughput:
read, MiB/s: 337.57
written, MiB/s: 225.05
General statistics:
total time: 20.0018s
total number of events: 1454994
Latency (ms):
min: 0.00
avg: 0.22
max: 17.11
95th percentile: 1.70
sum: 319313.16
Threads fairness:
events (avg/stddev): 90937.1250/578.91
execution time (avg/stddev): 19.9571/0.00
--移除文件
# sysbench --threads=16 --time=20 --test=fileio --file-num=1 --file-block-size=8192 --file-total-size=20G --file-test-mode=rndrw --verbosity=5 cleanup
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Removing test files...
上述介绍了怎么来测试系统的基本资源CPU、内存和IO，接下来我们针对于数据库进行测试。
数据库测试模块
- 查看测试lua脚本
# ls -lrt share/sysbench/
total 120
-rwxr-xr-x 1 root root 2118 Apr 27 17:49 select_random_ranges.lua
-rwxr-xr-x 1 root root 1919 Apr 27 17:49 select_random_points.lua
-rwxr-xr-x 1 root root 1440 Apr 27 17:49 oltp_write_only.lua
-rwxr-xr-x 1 root root 1127 Apr 27 17:49 oltp_update_non_index.lua
-rwxr-xr-x 1 root root 1118 Apr 27 17:49 oltp_update_index.lua
-rwxr-xr-x 1 root root 1824 Apr 27 17:49 oltp_read_write.lua
-rwxr-xr-x 1 root root 1649 Apr 27 17:49 oltp_read_only.lua
-rwxr-xr-x 1 root root 1265 Apr 27 17:49 oltp_point_select.lua
-rwxr-xr-x 1 root root 2415 Apr 27 17:49 oltp_insert.lua
-rwxr-xr-x 1 root root 1290 Apr 27 17:49 oltp_delete.lua
-rw-r--r-- 1 root root 14369 Apr 27 17:49 oltp_common.lua
-rwxr-xr-x 1 root root 1452 Apr 27 17:49 bulk_insert.lua
drwxr-xr-x 4 root root 20480 Apr 27 17:49 tests
- oltp_common.lua
# more share/sysbench/oltp_common.lua
-- Copyright (C) 2006-2018 Alexey Kopytov
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
-- -----------------------------------------------------------------------------
-- Common code for OLTP benchmarks.
-- -----------------------------------------------------------------------------
function init()
assert(event ~= nil,
"this script is meant to be included by other OLTP scripts and " ..
"should not be called directly.")
end
if sysbench.cmdline.command == nil then
error("Command is required. Supported commands: prepare, prewarm, run, " ..
"cleanup, help")
end
-- Command line options
sysbench.cmdline.options = {
table_size =
{"Number of rows per table", 10000},
range_size =
{"Range size for range SELECT queries", 100},
tables =
{"Number of tables", 1},
point_selects =
{"Number of point SELECT queries per transaction", 10},
simple_ranges =
{"Number of simple range SELECT queries per transaction", 1},
sum_ranges =
{"Number of SELECT SUM() queries per transaction", 1},
order_ranges =
{"Number of SELECT ORDER BY queries per transaction", 1},
distinct_ranges =
{"Number of SELECT DISTINCT queries per transaction", 1},
index_updates =
{"Number of UPDATE index queries per transaction", 1},
non_index_updates =
{"Number of UPDATE non-index queries per transaction", 1},
delete_inserts =
{"Number of DELETE/INSERT combinations per transaction", 1},
range_selects =
{"Enable/disable all range SELECT queries", true},
auto_inc =
{"Use AUTO_INCREMENT column as Primary Key (for MySQL), " ..
"or its alternatives in other DBMS. When disabled, use " ..
"client-generated IDs", true},
skip_trx =
{"Don't start explicit transactions and execute all queries " ..
"in the AUTOCOMMIT mode", false},
secondary =
{"Use a secondary index in place of the PRIMARY KEY", false},
create_secondary =
{"Create a secondary index in addition to the PRIMARY KEY", true},
mysql_storage_engine =
{"Storage engine, if MySQL is used", "innodb"},
pgsql_variant =
{"Use this PostgreSQL variant when running with the " ..
"PostgreSQL driver. The only currently supported " ..
"variant is 'redshift'. When enabled, " ..
"create_secondary is automatically disabled, and " ..
"delete_inserts is set to 0"}
}
-- Prepare the dataset. This command supports parallel execution, i.e. will
-- benefit from executing with --threads > 1 as long as --tables > 1
function cmd_prepare()
...
-- Preload the dataset into the server cache. This command supports parallel
-- execution, i.e. will benefit from executing with --threads > 1 as long as
-- --tables > 1
--
-- PS. Currently, this command is only meaningful for MySQL/InnoDB benchmarks
function cmd_prewarm()
...
...
MySQL测试
- MySQL数据库准备，版本为8.0.30
# su - mysql
$ mysqld &
...
$ mysql -uroot -proot2023
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 8.0.30-22 Percona Server (GPL), Release 22, Revision 7e301439b65
Copyright (c) 2009-2022 Percona LLC and/or its affiliates
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
- 准备连接用户和数据库
test
--创建可以远程连接的root用户
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> create user 'root'@'%' identified with mysql_native_password by 'root2023';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
--准备test数据库
$ mysql -h -P3306 -uroot -proot2023
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 16
Server version: 8.0.30-22 Percona Server (GPL), Release 22, Revision 7e301439b65
Copyright (c) 2009-2022 Percona LLC and/or its affiliates
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
- 场景：4张表，每张表个10万行，16线程 - 点查 oltp_point_select
--表准备
# cd share/sysbench
# sysbench --threads=16 --time=60 --report-interval=1 oltp_common.lua --mysql-host=66.3.125.125 --mysql-port=3306 --mysql-user=root --mysql-password=root2023
--mysql-db=test --tables=4 --table-size=100000 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest2'...
Creating table 'sbtest1'...
Creating table 'sbtest4'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest3'
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest2'...
--连接数据库确认
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sbtest1 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
+----------------+
4 rows in set (0.00 sec)
mysql> select count(*) from test1;
ERROR 1146 (42S02): Table 'test.test1' doesn't exist
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)
mysql> desc sbtest2;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| k | int | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+-----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show create table sbtest3;
+---------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------+
| sbtest3 | CREATE TABLE `sbtest3` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_3` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from sbtest4 limit 5;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 50303 | 18034632456-32298647298-82351096178-60420120042-90070228681-93395382793-96740777141-18710455882-88896678134-41810932745 | 43683718329-48150560094-43449649167-51455516141-06448225399 |
| 2 | 50450 | 69708345057-48265944193-91002879830-11554672482-35576538285-03657113365-90301319612-18462263634-56608104414-27254248188 | 05603373460-16140454933-50476449060-04937808333-32421752305 |
| 3 | 50940 | 04940100707-79196214653-69899219520-16993366471-57789899414-92525293502-62890351877-80720493586-13749767421-91023911663 | 67126402466-45286595358-58204691523-62355769941-06673789329 |
| 4 | 49974 | 00224676656-38003697255-44184401985-68851502665-32480635416-18445090402-63317915144-45481704416-01982644758-64592914637 | 77377062042-10834277926-21379893425-70248821993-50926412030 |
| 5 | 49825 | 62628403457-54953268488-99818997311-93464095661-99772044635-92164943209-17521494984-21945783042-38536244491-28083888401 | 55908867377-87072757393-05794355403-55852568630-13599576089 |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)
--60秒点查测试
# sysbench --threads=16 --time=60 --report-interval=1 oltp_point_select --mysql-host=66.3.125.125 --mysql-port=3306 --mysql-user=root --mysql-password=root2023
--mysql-db=test --tables=4 --table-size=100000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
--此处结果内容主要包含: 时间计数(1s意为1秒)、thds为线程数、tps为每秒事务数、qps为每秒查询数、r/w/o分别代表每秒的读/写/其他操作数
--lat(ms, 95%)时95%操作的耗时-单位为毫秒、err/s是每秒的错误数、reconn/s是每秒的重连数
[ 1s ] thds: 16 tps: 26905.31 qps: 26905.31 (r/w/o: 26905.31/0.00/0.00) lat (ms,95%): 0.65 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 16 tps: 29389.04 qps: 29389.04 (r/w/o: 29389.04/0.00/0.00) lat (ms,95%): 0.72 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 16 tps: 30498.78 qps: 30498.78 (r/w/o: 30498.78/0.00/0.00) lat (ms,95%): 0.68 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 16 tps: 22776.97 qps: 22776.97 (r/w/o: 22776.97/0.00/0.00) lat (ms,95%): 1.27 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 16 tps: 30643.61 qps: 30643.61 (r/w/o: 30643.61/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 16 tps: 22722.25 qps: 22722.25 (r/w/o: 22722.25/0.00/0.00) lat (ms,95%): 1.21 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 16 tps: 31585.17 qps: 31585.17 (r/w/o: 31585.17/0.00/0.00) lat (ms,95%): 0.64 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 16 tps: 30296.85 qps: 30296.85 (r/w/o: 30296.85/0.00/0.00) lat (ms,95%): 0.68 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 16 tps: 23705.46 qps: 23705.46 (r/w/o: 23705.46/0.00/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 30879.22 qps: 30879.22 (r/w/o: 30879.22/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 16 tps: 22564.61 qps: 22564.61 (r/w/o: 22564.61/0.00/0.00) lat (ms,95%): 0.95 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 16 tps: 30561.08 qps: 30561.08 (r/w/o: 30561.08/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 16 tps: 28261.06 qps: 28261.06 (r/w/o: 28261.06/0.00/0.00) lat (ms,95%): 0.73 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 16 tps: 21548.61 qps: 21548.61 (r/w/o: 21548.61/0.00/0.00) lat (ms,95%): 1.18 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 27673.56 qps: 27673.56 (r/w/o: 27673.56/0.00/0.00) lat (ms,95%): 0.74 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 16 tps: 20636.60 qps: 20636.60 (r/w/o: 20636.60/0.00/0.00) lat (ms,95%): 1.27 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 16 tps: 27885.83 qps: 27885.83 (r/w/o: 27885.83/0.00/0.00) lat (ms,95%): 0.73 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 16 tps: 28684.66 qps: 28685.66 (r/w/o: 28685.66/0.00/0.00) lat (ms,95%): 0.74 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 16 tps: 25480.74 qps: 25479.74 (r/w/o: 25479.74/0.00/0.00) lat (ms,95%): 0.84 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 30483.03 qps: 30483.03 (r/w/o: 30483.03/0.00/0.00) lat (ms,95%): 0.68 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 16 tps: 22042.51 qps: 22042.51 (r/w/o: 22042.51/0.00/0.00) lat (ms,95%): 1.25 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 16 tps: 30604.82 qps: 30604.82 (r/w/o: 30604.82/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 16 tps: 29589.29 qps: 29589.29 (r/w/o: 29589.29/0.00/0.00) lat (ms,95%): 0.70 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 16 tps: 24282.99 qps: 24282.99 (r/w/o: 24282.99/0.00/0.00) lat (ms,95%): 0.86 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 31246.92 qps: 31246.92 (r/w/o: 31246.92/0.00/0.00) lat (ms,95%): 0.64 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 16 tps: 22876.61 qps: 22876.61 (r/w/o: 22876.61/0.00/0.00) lat (ms,95%): 1.08 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 16 tps: 31529.39 qps: 31529.39 (r/w/o: 31529.39/0.00/0.00) lat (ms,95%): 0.64 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 16 tps: 29517.51 qps: 29517.51 (r/w/o: 29517.51/0.00/0.00) lat (ms,95%): 0.69 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 16 tps: 26343.56 qps: 26343.56 (r/w/o: 26343.56/0.00/0.00) lat (ms,95%): 0.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 31113.37 qps: 31113.37 (r/w/o: 31113.37/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 16 tps: 24066.86 qps: 24066.86 (r/w/o: 24066.86/0.00/0.00) lat (ms,95%): 0.83 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 16 tps: 30315.40 qps: 30315.40 (r/w/o: 30315.40/0.00/0.00) lat (ms,95%): 0.68 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 16 tps: 29995.12 qps: 29995.12 (r/w/o: 29995.12/0.00/0.00) lat (ms,95%): 0.69 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 16 tps: 23322.28 qps: 23322.28 (r/w/o: 23322.28/0.00/0.00) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 30676.67 qps: 30677.67 (r/w/o: 30677.67/0.00/0.00) lat (ms,95%): 0.65 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 16 tps: 23831.21 qps: 23831.21 (r/w/o: 23831.21/0.00/0.00) lat (ms,95%): 1.01 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 16 tps: 31442.98 qps: 31441.98 (r/w/o: 31441.98/0.00/0.00) lat (ms,95%): 0.64 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 16 tps: 29257.83 qps: 29257.83 (r/w/o: 29257.83/0.00/0.00) lat (ms,95%): 0.69 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 16 tps: 24508.15 qps: 24508.15 (r/w/o: 24508.15/0.00/0.00) lat (ms,95%): 0.99 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 30816.09 qps: 30816.09 (r/w/o: 30816.09/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 16 tps: 23236.84 qps: 23236.84 (r/w/o: 23236.84/0.00/0.00) lat (ms,95%): 0.95 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 16 tps: 29849.10 qps: 29849.10 (r/w/o: 29849.10/0.00/0.00) lat (ms,95%): 0.70 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 16 tps: 29130.52 qps: 29130.52 (r/w/o: 29130.52/0.00/0.00) lat (ms,95%): 0.73 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 16 tps: 23340.17 qps: 23340.17 (r/w/o: 23340.17/0.00/0.00) lat (ms,95%): 0.86 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 29494.52 qps: 29494.52 (r/w/o: 29494.52/0.00/0.00) lat (ms,95%): 0.69 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 16 tps: 22966.11 qps: 22966.11 (r/w/o: 22966.11/0.00/0.00) lat (ms,95%): 0.83 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 16 tps: 30129.21 qps: 30129.21 (r/w/o: 30129.21/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 16 tps: 29165.29 qps: 29165.29 (r/w/o: 29165.29/0.00/0.00) lat (ms,95%): 0.70 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 16 tps: 23268.56 qps: 23268.56 (r/w/o: 23268.56/0.00/0.00) lat (ms,95%): 0.97 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 30788.20 qps: 30788.20 (r/w/o: 30788.20/0.00/0.00) lat (ms,95%): 0.65 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 16 tps: 19082.94 qps: 19082.94 (r/w/o: 19082.94/0.00/0.00) lat (ms,95%): 2.03 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 16 tps: 29830.84 qps: 29830.84 (r/w/o: 29830.84/0.00/0.00) lat (ms,95%): 0.72 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 16 tps: 29476.39 qps: 29476.39 (r/w/o: 29476.39/0.00/0.00) lat (ms,95%): 0.70 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 16 tps: 23153.58 qps: 23153.58 (r/w/o: 23153.58/0.00/0.00) lat (ms,95%): 0.95 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 29153.81 qps: 29153.81 (r/w/o: 29153.81/0.00/0.00) lat (ms,95%): 0.70 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 16 tps: 23450.91 qps: 23450.91 (r/w/o: 23450.91/0.00/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 16 tps: 30307.30 qps: 30307.30 (r/w/o: 30307.30/0.00/0.00) lat (ms,95%): 0.67 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 16 tps: 29204.35 qps: 29204.35 (r/w/o: 29204.35/0.00/0.00) lat (ms,95%): 0.70 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 16 tps: 21223.78 qps: 21223.78 (r/w/o: 21223.78/0.00/0.00) lat (ms,95%): 1.18 err/s: 0.00 reconn/s: 0.00
--SQL统计信息，SQL的读/写/其他/总计次数、总事务数及每秒事务数、总查询数及每秒查询数、忽略错误数据、重新数据
SQL statistics:
queries performed:
read: 1637657
write: 0
other: 0
total: 1637657
transactions: 1637657 (27292.23 per sec.)
queries: 1637657 (27292.23 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
--通用统计信息：总时间、总操作数
General statistics:
total time: 60.0026s
total number of events: 1637657
--耗时信息(单位毫秒)：最小、平均、最大、95%耗时、总耗时
Latency (ms):
min: 0.35
avg: 0.59
max: 19.81
95th percentile: 0.75
sum: 959077.57
Threads fairness:
events (avg/stddev): 102353.5625/5226.65
execution time (avg/stddev): 59.9423/0.00
- 场景：4张表，每张表个10万行，16线程 - 读写 oltp_read_write
--60秒读写
# sysbench --threads=16 --time=60 --report-interval=1 oltp_read_write --mysql-host=66.3.125.125 --mysql-port=3306 --mysql-user=root --mysql-password=root2023
--mysql-db=test --tables=4 --table-size=100000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
--此处结果内容主要包含: 时间计数(1s意为1秒)、thds为线程数、tps为每秒事务数、qps为每秒查询数、r/w/o分别代表每秒的读/写/其他操作数
--lat(ms, 95%)时95%操作的耗时-单位为毫秒、err/s是每秒的错误数、reconn/s是每秒的重连数
[ 1s ] thds: 16 tps: 850.05 qps: 17191.55 (r/w/o: 12053.34/3422.15/1716.06) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 16 tps: 811.05 qps: 16189.10 (r/w/o: 11340.77/3227.22/1621.11) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 16 tps: 1101.64 qps: 22027.85 (r/w/o: 15419.00/4404.57/2204.28) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 16 tps: 1075.49 qps: 21530.73 (r/w/o: 15073.81/4305.95/2150.97) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 16 tps: 801.10 qps: 16045.97 (r/w/o: 11236.38/3208.39/1601.20) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 16 tps: 1082.76 qps: 21662.30 (r/w/o: 15165.71/4330.06/2166.53) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 16 tps: 761.08 qps: 15211.66 (r/w/o: 10649.16/3042.33/1520.17) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 16 tps: 1076.98 qps: 21531.62 (r/w/o: 15056.74/4319.92/2154.96) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 16 tps: 1066.95 qps: 21302.97 (r/w/o: 14914.28/4254.79/2133.90) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 681.07 qps: 13618.31 (r/w/o: 9528.91/2730.26/1359.13) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 16 tps: 1112.85 qps: 22264.06 (r/w/o: 15586.94/4448.41/2228.71) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 16 tps: 752.84 qps: 15131.86 (r/w/o: 10579.81/3045.37/1506.69) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 16 tps: 1098.32 qps: 21904.34 (r/w/o: 15351.44/4356.26/2196.64) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 16 tps: 970.85 qps: 19442.90 (r/w/o: 13609.83/3891.38/1941.69) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 773.18 qps: 15459.62 (r/w/o: 10828.53/3084.72/1546.36) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 16 tps: 1062.01 qps: 21222.24 (r/w/o: 14842.17/4256.05/2124.02) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 16 tps: 712.95 qps: 14246.94 (r/w/o: 9980.26/2840.79/1425.89) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 16 tps: 1053.95 qps: 21143.99 (r/w/o: 14800.29/4235.80/2107.90) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 16 tps: 1101.93 qps: 22045.65 (r/w/o: 15432.05/4409.73/2203.86) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 759.16 qps: 15124.24 (r/w/o: 10587.27/3019.65/1517.32) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 16 tps: 1095.75 qps: 21946.00 (r/w/o: 15366.50/4388.00/2191.50) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 16 tps: 756.07 qps: 15209.36 (r/w/o: 10630.95/3065.27/1513.14) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 16 tps: 1082.06 qps: 21555.26 (r/w/o: 15097.88/4293.25/2164.13) lat (ms,95%): 19.65 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 16 tps: 990.90 qps: 19767.95 (r/w/o: 13841.57/3944.59/1981.79) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 16 tps: 744.12 qps: 14928.48 (r/w/o: 10450.74/2989.50/1488.25) lat (ms,95%): 54.83 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 16 tps: 1037.85 qps: 20736.04 (r/w/o: 14510.93/4150.41/2074.70) lat (ms,95%): 21.89 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 16 tps: 667.88 qps: 13342.61 (r/w/o: 9335.33/2670.52/1336.76) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 16 tps: 1070.99 qps: 21434.72 (r/w/o: 15009.80/4282.94/2141.97) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 16 tps: 1070.16 qps: 21409.29 (r/w/o: 14990.30/4279.66/2139.33) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 774.04 qps: 15489.75 (r/w/o: 10836.52/3105.15/1548.07) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 16 tps: 1107.76 qps: 22127.17 (r/w/o: 15494.62/4418.04/2214.52) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 16 tps: 777.29 qps: 15545.86 (r/w/o: 10872.10/3117.18/1556.59) lat (ms,95%): 47.47 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 16 tps: 1080.79 qps: 21628.88 (r/w/o: 15144.12/4323.18/2161.59) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 16 tps: 984.89 qps: 19685.82 (r/w/o: 13780.48/3935.57/1969.78) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 16 tps: 791.22 qps: 15754.48 (r/w/o: 11018.13/3154.90/1581.45) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 16 tps: 1067.87 qps: 21341.44 (r/w/o: 14931.21/4273.49/2136.74) lat (ms,95%): 19.29 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 16 tps: 752.13 qps: 15124.64 (r/w/o: 10607.85/3012.52/1504.26) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 16 tps: 982.76 qps: 19580.31 (r/w/o: 13690.72/3925.06/1964.53) lat (ms,95%): 32.53 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 16 tps: 1110.14 qps: 22319.86 (r/w/o: 15629.00/4469.57/2221.28) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 680.97 qps: 13617.32 (r/w/o: 9541.52/2713.86/1361.93) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 16 tps: 1111.87 qps: 22208.31 (r/w/o: 15549.12/4436.46/2222.73) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 16 tps: 805.03 qps: 16108.54 (r/w/o: 11267.38/3231.11/1610.05) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 16 tps: 1045.95 qps: 20910.98 (r/w/o: 14639.28/4179.80/2091.90) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 16 tps: 1094.33 qps: 21819.55 (r/w/o: 15269.58/4364.31/2185.66) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 16 tps: 783.97 qps: 15710.35 (r/w/o: 10996.55/3141.87/1571.94) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 16 tps: 1038.84 qps: 20800.89 (r/w/o: 14565.82/4157.38/2077.69) lat (ms,95%): 20.00 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 16 tps: 857.18 qps: 17127.60 (r/w/o: 11987.52/3425.72/1714.36) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 16 tps: 775.87 qps: 15554.33 (r/w/o: 10891.13/3111.47/1551.73) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 16 tps: 1065.00 qps: 21241.01 (r/w/o: 14862.01/4249.00/2130.00) lat (ms,95%): 18.95 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 671.06 qps: 13512.29 (r/w/o: 9480.90/2690.26/1341.13) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 16 tps: 1084.98 qps: 21700.59 (r/w/o: 15188.71/4340.92/2170.96) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 16 tps: 884.84 qps: 17622.83 (r/w/o: 12318.78/3536.36/1767.68) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 16 tps: 979.12 qps: 19590.36 (r/w/o: 13714.65/3916.47/1959.24) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 16 tps: 1097.01 qps: 21962.12 (r/w/o: 15383.09/4384.02/2195.01) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 16 tps: 780.05 qps: 15610.91 (r/w/o: 10919.63/3131.18/1560.09) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 16 tps: 1085.05 qps: 21662.94 (r/w/o: 15161.66/4332.19/2169.09) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 16 tps: 906.00 qps: 18179.06 (r/w/o: 12727.04/3639.01/1813.01) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 16 tps: 943.95 qps: 18825.03 (r/w/o: 13173.32/3764.81/1886.90) lat (ms,95%): 25.74 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 16 tps: 1110.87 qps: 22218.32 (r/w/o: 15554.12/4441.46/2222.73) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 796.00 qps: 15868.00 (r/w/o: 11099.00/3177.00/1592.00) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
--SQL统计信息，SQL的读/写/其他/总计次数、总事务数及每秒事务数、总查询数及每秒查询数、忽略错误数据、重新数据
SQL statistics:
queries performed:
read: 787738
write: 225068
other: 112534
total: 1125340
transactions: 56267 (937.50 per sec.)
queries: 1125340 (18750.05 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
--通用统计信息：总时间、总操作数
General statistics:
total time: 60.0162s
total number of events: 56267
--耗时信息(单位毫秒)：最小、平均、最大、95%耗时、总耗时
Latency (ms):
min: 10.73
avg: 17.06
max: 311.71
95th percentile: 28.67
sum: 960075.64
Threads fairness:
events (avg/stddev): 3516.6875/178.13
execution time (avg/stddev): 60.0047/0.00
--清理
# sysbench --threads=16 --time=60 --report-interval=1 oltp_read_write --mysql-host=66.3.125.125 --mysql-port=3306 --mysql-user=root --mysql-password=root2023
--mysql-db=test --tables=4 --table-size=100000 cleanup
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
- 关闭数据库
$ mysqladmin shutdown -uroot -proot2023
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
PostgreSQL测试
- 准备数据库 - 版本12.6
# su - postgres
$ pg_ctl -D /data/pgsql -l logfile start
waiting for server to start.... done
server started
$ ps -ef|grep postgres
root 12818 27446 0 15:43 pts/3 00:00:00 su - postgres
postgres 12820 12818 0 15:43 pts/3 00:00:00 -bash
postgres 13393 1 0 15:43 ? 00:00:00 /u01/app/pgsql/bin/postgres -D /data/pgsql
postgres 13404 13393 0 15:43 ? 00:00:00 postgres: checkpointer
postgres 13405 13393 0 15:43 ? 00:00:00 postgres: background writer
postgres 13406 13393 0 15:43 ? 00:00:00 postgres: walwriter
postgres 13407 13393 0 15:43 ? 00:00:00 postgres: autovacuum launcher
postgres 13408 13393 0 15:43 ? 00:00:00 postgres: stats collector
postgres 13409 13393 0 15:43 ? 00:00:00 postgres: logical replication launcher
$ psql
psql (12.6)
Type "help" for help.
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
- 准备连接用户和数据库
test
--创建测试数据库
postgres=# create database test;
CREATE DATABASE
postgres=# l test
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------+----------+----------+-------------+-------------+-------------------
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
--创建用户sysbench
postgres=# create user sysbench superuser login password 'sysbench2023';
CREATE ROLE
postgres=# du sysbench
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
sysbench | Superuser | {}
postgres=# du+ sysbench
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
sysbench | Superuser | {} |
--连接数据库
$ psql -h
-p 5432 -U sysbench -W test
Password:
psql (12.6)
Type "help" for help.
test=# l+ test
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7953 kB | pg_default |
(1 row)
- 场景：1张表，每张表个100万行，8线程 - 范围查 select_random_ranges
--准备表
# sysbench --threads=8 --time=30 --report-interval=1 --db-driver=pgsql select_random_ranges --pgsql-host=66.3.125.125 --pgsql-port=5432
--pgsql-user=sysbench --pgsql-password=sysbench2023 --pgsql-db=test --tables=2 --table-size=1000000 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest2'...
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest2'
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...
--数据库中确认表和数据库
test=# dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | sbtest1 | table | sysbench
public | sbtest2 | table | sysbench
(2 rows)
test=# select count(*) from sbtest1;
count
---------
1000000
(1 row)
test=# d sbtest2;
Table "public.sbtest2"
Column | Type | Collation | Nullable | Default
--------+----------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('sbtest2_id_seq'::regclass)
k | integer | | not null | 0
c | character(120) | | not null | ''::bpchar
pad | character(60) | | not null | ''::bpchar
Indexes:
"sbtest2_pkey" PRIMARY KEY, btree (id)
"k_2" btree (k)
--30秒性能测试select_random_ranges
# sysbench --threads=8 --time=30 --report-interval=1 --db-driver=pgsql select_random_ranges --pgsql-host=66.3.125.125 --pgsql-port=5432
--pgsql-user=sysbench --pgsql-password=sysbench2023 --pgsql-db=test --tables=2 --table-size=1000000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 8 tps: 7841.46 qps: 7841.46 (r/w/o: 7841.46/0.00/0.00) lat (ms,95%): 1.32 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 6670.35 qps: 6670.35 (r/w/o: 6670.35/0.00/0.00) lat (ms,95%): 1.93 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 8134.65 qps: 8134.65 (r/w/o: 8134.65/0.00/0.00) lat (ms,95%): 1.27 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 8 tps: 7081.10 qps: 7081.10 (r/w/o: 7081.10/0.00/0.00) lat (ms,95%): 1.58 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 8 tps: 5487.50 qps: 5487.50 (r/w/o: 5487.50/0.00/0.00) lat (ms,95%): 2.97 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 8 tps: 8246.95 qps: 8246.95 (r/w/o: 8246.95/0.00/0.00) lat (ms,95%): 1.21 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 8 tps: 6584.11 qps: 6584.11 (r/w/o: 6584.11/0.00/0.00) lat (ms,95%): 1.86 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 8 tps: 7932.69 qps: 7932.69 (r/w/o: 7932.69/0.00/0.00) lat (ms,95%): 1.37 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 8 tps: 7800.66 qps: 7800.66 (r/w/o: 7800.66/0.00/0.00) lat (ms,95%): 1.37 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 8 tps: 5476.66 qps: 5477.65 (r/w/o: 5477.65/0.00/0.00) lat (ms,95%): 3.02 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 8 tps: 8400.74 qps: 8399.74 (r/w/o: 8399.74/0.00/0.00) lat (ms,95%): 1.18 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 8 tps: 6334.65 qps: 6334.65 (r/w/o: 6334.65/0.00/0.00) lat (ms,95%): 2.07 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 8 tps: 8075.97 qps: 8075.97 (r/w/o: 8075.97/0.00/0.00) lat (ms,95%): 1.27 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 8 tps: 8019.73 qps: 8019.73 (r/w/o: 8019.73/0.00/0.00) lat (ms,95%): 1.30 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 8 tps: 6337.34 qps: 6337.34 (r/w/o: 6337.34/0.00/0.00) lat (ms,95%): 1.93 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 8 tps: 7875.31 qps: 7875.31 (r/w/o: 7875.31/0.00/0.00) lat (ms,95%): 1.32 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 8 tps: 6478.87 qps: 6478.87 (r/w/o: 6478.87/0.00/0.00) lat (ms,95%): 1.86 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 8 tps: 8029.60 qps: 8029.60 (r/w/o: 8029.60/0.00/0.00) lat (ms,95%): 1.27 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 8 tps: 7956.06 qps: 7956.06 (r/w/o: 7956.06/0.00/0.00) lat (ms,95%): 1.32 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 6338.23 qps: 6338.23 (r/w/o: 6338.23/0.00/0.00) lat (ms,95%): 1.73 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 8 tps: 8189.96 qps: 8189.96 (r/w/o: 8189.96/0.00/0.00) lat (ms,95%): 1.25 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 8 tps: 6520.26 qps: 6520.26 (r/w/o: 6520.26/0.00/0.00) lat (ms,95%): 2.30 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 8 tps: 7717.71 qps: 7717.71 (r/w/o: 7717.71/0.00/0.00) lat (ms,95%): 1.42 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 8 tps: 7514.65 qps: 7514.65 (r/w/o: 7514.65/0.00/0.00) lat (ms,95%): 1.47 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 8 tps: 6393.39 qps: 6393.39 (r/w/o: 6393.39/0.00/0.00) lat (ms,95%): 1.79 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 8 tps: 7502.14 qps: 7502.14 (r/w/o: 7502.14/0.00/0.00) lat (ms,95%): 1.34 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 8 tps: 6642.10 qps: 6642.10 (r/w/o: 6642.10/0.00/0.00) lat (ms,95%): 1.73 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 8 tps: 6712.77 qps: 6712.77 (r/w/o: 6712.77/0.00/0.00) lat (ms,95%): 1.70 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 8 tps: 7284.62 qps: 7284.62 (r/w/o: 7284.62/0.00/0.00) lat (ms,95%): 1.47 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 216072
write: 0
other: 0
total: 216072
transactions: 216072 (7201.36 per sec.)
queries: 216072 (7201.36 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0025s
total number of events: 216072
Latency (ms):
min: 0.71
avg: 1.11
max: 32.75
95th percentile: 1.55
sum: 239635.53
Threads fairness:
events (avg/stddev): 27009.0000/1684.57
execution time (avg/stddev): 29.9544/0.00
- 场景：1张表，每张表个100万行，8线程 - 删除 oltp_delete
--30秒删除
# sysbench --threads=8 --time=30 --report-interval=1 --db-driver=pgsql oltp_delete --pgsql-host=66.3.125.125 --pgsql-port=5432
--pgsql-user=sysbench
--pgsql-password=sysbench2023 --pgsql-db=test --tables=2 --table-size=1000000 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 8
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 1s ] thds: 8 tps: 7744.15 qps: 7744.15 (r/w/o: 0.00/6740.73/1003.43) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 8 tps: 9215.60 qps: 9215.60 (r/w/o: 0.00/5947.10/3268.50) lat (ms,95%): 0.84 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 8 tps: 10104.84 qps: 10104.84 (r/w/o: 0.00/4939.37/5165.48) lat (ms,95%): 1.16 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 8 tps: 14614.81 qps: 14614.81 (r/w/o: 0.00/5427.30/9187.51) lat (ms,95%): 0.77 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 8 tps: 14388.68 qps: 14388.68 (r/w/o: 0.00/4331.40/10057.28) lat (ms,95%): 0.80 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 8 tps: 10472.62 qps: 10472.62 (r/w/o: 0.00/2805.17/7667.45) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 8 tps: 10004.27 qps: 10004.27 (r/w/o: 0.00/2546.58/7457.69) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 8 tps: 13070.38 qps: 13070.38 (r/w/o: 0.00/3226.09/9844.29) lat (ms,95%): 0.97 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 8 tps: 11780.44 qps: 11780.44 (r/w/o: 0.00/2801.87/8978.57) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 8 tps: 12733.67 qps: 12733.67 (r/w/o: 0.00/2996.98/9736.69) lat (ms,95%): 0.86 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 8 tps: 12010.92 qps: 12010.92 (r/w/o: 0.00/2803.15/9207.77) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 8 tps: 15161.94 qps: 15161.94 (r/w/o: 0.00/3490.53/11671.42) lat (ms,95%): 0.74 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 8 tps: 10689.91 qps: 10689.91 (r/w/o: 0.00/2503.21/8186.70) lat (ms,95%): 2.00 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 8 tps: 16073.40 qps: 16073.40 (r/w/o: 0.00/3584.54/12488.86) lat (ms,95%): 0.72 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 8 tps: 15359.51 qps: 15359.51 (r/w/o: 0.00/3469.44/11890.07) lat (ms,95%): 0.73 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 8 tps: 12378.46 qps: 12378.46 (r/w/o: 0.00/2664.67/9713.79) lat (ms,95%): 1.04 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 8 tps: 15772.29 qps: 15772.29 (r/w/o: 0.00/3540.19/12232.10) lat (ms,95%): 0.73 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 8 tps: 12836.29 qps: 12836.29 (r/w/o: 0.00/2755.63/10080.66) lat (ms,95%): 0.95 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 8 tps: 15181.45 qps: 15181.45 (r/w/o: 0.00/3345.34/11836.11) lat (ms,95%): 0.78 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 14645.16 qps: 14645.16 (r/w/o: 0.00/3289.38/11355.78) lat (ms,95%): 0.81 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 8 tps: 11932.81 qps: 11932.81 (r/w/o: 0.00/2600.40/9332.42) lat (ms,95%): 1.14 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 8 tps: 15366.80 qps: 15366.80 (r/w/o: 0.00/3234.12/12132.69) lat (ms,95%): 0.74 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 8 tps: 12664.64 qps: 12664.64 (r/w/o: 0.00/2694.77/9969.86) lat (ms,95%): 0.95 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 8 tps: 15692.08 qps: 15692.08 (r/w/o: 0.00/3371.59/12320.49) lat (ms,95%): 0.73 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 8 tps: 15511.77 qps: 15511.77 (r/w/o: 0.00/3196.16/12315.61) lat (ms,95%): 0.74 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 8 tps: 11559.28 qps: 11559.28 (r/w/o: 0.00/2390.26/9169.01) lat (ms,95%): 1.18 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 8 tps: 15146.52 qps: 15146.52 (r/w/o: 0.00/3204.99/11941.53) lat (ms,95%): 0.77 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 8 tps: 12715.10 qps: 12715.10 (r/w/o: 0.00/2659.86/10055.24) lat (ms,95%): 0.97 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 8 tps: 14726.89 qps: 14726.89 (r/w/o: 0.00/2961.58/11765.31) lat (ms,95%): 0.78 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 102728
other: 292209
total: 394937
transactions: 394937 (13162.74 per sec.)
queries: 394937 (13162.74 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0025s
total number of events: 394937
Latency (ms):
min: 0.32
avg: 0.61
max: 163.82
95th percentile: 0.86
sum: 239730.10
Threads fairness:
events (avg/stddev): 49367.1250/594.29
execution time (avg/stddev): 29.9663/0.00
--数据库中确认数据
test=# select now(), count(*) from sbtest1 union all select now(), count(*) from sbtest2;
now | count
-------------------------------+--------
2023-07-04 16:27:22.569512+08 | 984107
2023-07-04 16:27:22.569512+08 | 984167
(2 rows)
test=# select now(), count(*) from sbtest1 union all select now(), count(*) from sbtest2;
now | count
------------------------------+--------
2023-07-04 16:27:25.86696+08 | 979129
2023-07-04 16:27:25.86696+08 | 979150
(2 rows)
--清理表和数据
# sysbench --threads=8 --time=30 --report-interval=1 --db-driver=pgsql oltp_delete --pgsql-host=66.3.125.125 --pgsql-port=5432
--pgsql-user=sysbench --pgsql-password=sysbench2023 --pgsql-db=test --tables=2 --table-size=1000000 cleanup
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
test=# dt
Did not find any relations.
- 关闭数据库
$ pg_ctl -D /data/pgsql status
pg_ctl: server is running (PID: 13393)
/u01/app/pgsql/bin/postgres "-D" "/data/pgsql"
$ pg_ctl -D /data/pgsql stop
waiting for server to shut down.... done
server stopped
总结
本文围绕找sysbench对其进行了介绍、安装和使用，已经对基础软硬件进行相关的测试。选用sysbench版本号是最新的1.0.20，文中重点介绍了对MySQL和PostgreSQL数据库进行场景场景的性能测试。
