环境准备
机器信息
| 机器类型 | 虚拟机 |
|---|---|
| IP | 10.201.0.170, 10.201.0.171, 10.201.0.172, 10.201.0.173 |
| 网卡名 | eth0 |
| OS | CentOS Linux release 7.9.2009 |
| CPU | 4C |
| 内存 | 总内存 16G，可用内存 15G |
| 磁盘1 | 磁盘 /dev/vda 200G |
| 磁盘2 | 磁盘 /dev/vdb 300G |
机器划分
| 角色 | 机器 | 备注 |
|---|---|---|
| 性能测试客户端 | 10.201.0.170 | |
| OBSERVER | 10.201.0.171 | OceanBase 数据库 zone1 |
| 10.201.0.172 | OceanBase 数据库 zone2 | |
| 10.201.0.173 | OceanBase 数据库 zone3 | |
| OBPROXY | 10.201.0.171 | OceanBase 访问反向代理 |
| 10.201.0.172 | OceanBase 访问反向代理 | |
| 10.201.0.173 | OceanBase 访问反向代理 |
测试内容
使用 sysbench 测试 OceanBase 的 MySQL 租户性能
测试规格
1个 db，12张表，每张表是普通表，数据量 10w 。
安装sysbench
下载链接：
https://github.com/akopytov/sysbench/releases/tag/1.0.20
编译安装：
# yum -y install automake libtool
# yum -y install mariadb.x86_64 mariadb-libs.x86_64 mariadb-devel.x86_64
# unzip -q /mnt/db/tools/sysbench-1.0.20.zip
[root@obdeployer sysbench-1.0.20]# ./autogen.sh
./autogen.sh: running `libtoolize --copy --force'
libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.
libtoolize: copying file `config/ltmain.sh'
libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.
libtoolize: copying file `m4/libtool.m4'
libtoolize: copying file `m4/ltoptions.m4'
libtoolize: copying file `m4/ltsugar.m4'
libtoolize: copying file `m4/ltversion.m4'
libtoolize: copying file `m4/lt~obsolete.m4'
./autogen.sh: running `aclocal -I m4'
./autogen.sh: running `autoheader'
./autogen.sh: running `automake -c --foreign --add-missing'
configure.ac:59: installing 'config/ar-lib'
configure.ac:45: installing 'config/compile'
configure.ac:27: installing 'config/config.guess'
configure.ac:27: installing 'config/config.sub'
configure.ac:32: installing 'config/install-sh'
configure.ac:32: installing 'config/missing'
src/Makefile.am: installing 'config/depcomp'
parallel-tests: installing 'config/test-driver'
./autogen.sh: running `autoconf'
Libtoolized with: libtoolize (GNU libtool) 2.4.2
Automade with: automake (GNU automake) 1.13.4
Configured with: autoconf (GNU Autoconf) 2.69
[root@obdeployer sysbench-1.0.20]# ./configure --prefix=/usr/sysbench/ --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/ --with-mysql
checking build system type... x86_64-unknown-linux-gnu
checking host system type... x86_64-unknown-linux-gnu
checking target system type... x86_64-unknown-linux-gnu
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking for style of include used by make... GNU
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking dependency style of gcc... gcc3
checking how to run the C preprocessor... gcc -E
……
……
……
config.status: creating config/config.h
config.status: executing depfiles commands
config.status: executing libtool commands
===============================================================================
sysbench version : 1.0.20
CC : gcc -std=gnu99
CFLAGS : -O2 -funroll-loops -ggdb3 -march=corei7 -Wall -Wextra -Wpointer-arith -Wbad-function-cast -Wstrict-prototypes -Wnested-externs -Wno-format-zero-length -Wundef -Wstrict-prototypes -Wmissing-prototypes -Wmissing-declarations -Wredundant-decls -Wcast-align -Wvla -pthread
CPPFLAGS : -D_GNU_SOURCE -I$(top_srcdir)/src -I$(abs_top_builddir)/third_party/luajit/inc -I$(abs_top_builddir)/third_party/concurrency_kit/include
LDFLAGS :
LIBS : -lm
EXTRA_LDFLAGS :
prefix : /usr/sysbench
bindir : ${prefix}/bin
libexecdir : ${prefix}/libexec
mandir : ${prefix}/share/man
datadir : ${prefix}/share
MySQL support : yes
Drizzle support : no
AttachSQL support : no
Oracle support : no
PostgreSQL support : no
LuaJIT : bundled
LUAJIT_CFLAGS : -I$(abs_top_builddir)/third_party/luajit/inc
LUAJIT_LIBS : $(abs_top_builddir)/third_party/luajit/lib/libluajit-5.1.a -ldl
LUAJIT_LDFLAGS : -rdynamic
Concurrency Kit : bundled
CK_CFLAGS : -I$(abs_top_builddir)/third_party/concurrency_kit/include
CK_LIBS : $(abs_top_builddir)/third_party/concurrency_kit/lib/libck.a
configure flags :
===============================================================================
[root@obdeployer sysbench-1.0.20]# make
Making all in doc
make[1]: 进入目录“/usr/sysbench-1.0.20/doc”
Making all in xsl
make[2]: 进入目录“/usr/sysbench-1.0.20/doc/xsl”
make[2]: 对“all”无需做任何事。
make[2]: 离开目录“/usr/sysbench-1.0.20/doc/xsl”
make[2]: 进入目录“/usr/sysbench-1.0.20/doc”
touch manual.html
make[2]: 离开目录“/usr/sysbench-1.0.20/doc”
make[1]: 离开目录“/usr/sysbench-1.0.20/doc”
Making all in third_party/luajit
make[1]: 进入目录“/usr/sysbench-1.0.20/third_party/luajit”
make -C ./luajit clean
make[2]: 进入目录“/usr/sysbench-1.0.20/third_party/luajit/luajit”
make -C src clean
make[3]: 进入目录“/usr/sysbench-1.0.20/third_party/luajit/luajit/src”
rm -f luajit libluajit.a libluajit.so host/minilua host/buildvm lj_vm.S lj_bcdef.h lj_ffdef.h lj_libdef.h lj_recdef.h lj_folddef.h host/buildvm_arch.h jit/vmdef.lua *.o host/*.o *.obj *.lib *.exp *.dll *.exe *.manifest *.pdb *.ilk
make[3]: 离开目录“/usr/sysbench-1.0.20/third_party/luajit/luajit/src”
make[2]: 离开目录“/usr/sysbench-1.0.20/third_party/luajit/luajit”
……
……
……
libtool: link: gcc -std=gnu99 -Wall -Wextra -Wpointer-arith -Wbad-function-cast -Wstrict-prototypes -Wnested-externs -Wno-format-zero-length -Wundef -Wstrict-prototypes -Wmissing-prototypes -Wmissing-declarations -Wredundant-decls -Wcast-align -Wvla -pthread -O2 -funroll-loops -ggdb3 -march=corei7 -rdynamic -o sysbench sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o sb_histogram.o sb_rand.o sb_thread.o sb_barrier.o sb_lua.o sb_util.o sb_counter.o tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/usr/lib64/mysql/ -lmysqlclient_r /usr/sysbench-1.0.20/third_party/luajit/lib/libluajit-5.1.a -ldl /usr/sysbench-1.0.20/third_party/concurrency_kit/lib/libck.a -lm -pthread
make[2]: 离开目录“/usr/sysbench-1.0.20/src”
make[1]: 离开目录“/usr/sysbench-1.0.20/src”
Making all in tests
make[1]: 进入目录“/usr/sysbench-1.0.20/tests”
make[1]: 对“all”无需做任何事。
make[1]: 离开目录“/usr/sysbench-1.0.20/tests”
make[1]: 进入目录“/usr/sysbench-1.0.20”
make[1]: 对“all-am”无需做任何事。
make[1]: 离开目录“/usr/sysbench-1.0.20”
[root@obdeployer sysbench-1.0.20]# make install
Making install in doc
make[1]: 进入目录“/usr/sysbench-1.0.20/doc”
Making install in xsl
make[2]: 进入目录“/usr/sysbench-1.0.20/doc/xsl”
make[3]: 进入目录“/usr/sysbench-1.0.20/doc/xsl”
make[3]: 对“install-exec-am”无需做任何事。
make[3]: 对“install-data-am”无需做任何事。
make[3]: 离开目录“/usr/sysbench-1.0.20/doc/xsl”
make[2]: 离开目录“/usr/sysbench-1.0.20/doc/xsl”
make[2]: 进入目录“/usr/sysbench-1.0.20/doc”
make[3]: 进入目录“/usr/sysbench-1.0.20/doc”
make[3]: 对“install-exec-am”无需做任何事。
/usr/bin/mkdir -p '/usr/sysbench/share/doc/sysbench'
/usr/bin/install -c -m 644 manual.html '/usr/sysbench/share/doc/sysbench'
make[3]: 离开目录“/usr/sysbench-1.0.20/doc”
make[2]: 离开目录“/usr/sysbench-1.0.20/doc”
make[1]: 离开目录“/usr/sysbench-1.0.20/doc”
Making install in third_party/luajit
make[1]: 进入目录“/usr/sysbench-1.0.20/third_party/luajit”
make[2]: 进入目录“/usr/sysbench-1.0.20/third_party/luajit”
make[2]: 对“install-exec-am”无需做任何事。
make[2]: 对“install-data-am”无需做任何事。
make[2]: 离开目录“/usr/sysbench-1.0.20/third_party/luajit”
make[1]: 离开目录“/usr/sysbench-1.0.20/third_party/luajit”
Making install in third_party/concurrency_kit
make[1]: 进入目录“/usr/sysbench-1.0.20/third_party/concurrency_kit”
make[2]: 进入目录“/usr/sysbench-1.0.20/third_party/concurrency_kit”
make[2]: 对“install-exec-am”无需做任何事。
make[2]: 对“install-data-am”无需做任何事。
make[2]: 离开目录“/usr/sysbench-1.0.20/third_party/concurrency_kit”
make[1]: 离开目录“/usr/sysbench-1.0.20/third_party/concurrency_kit”
Making install in src
make[1]: 进入目录“/usr/sysbench-1.0.20/src”
Making install in drivers
make[2]: 进入目录“/usr/sysbench-1.0.20/src/drivers”
Making install in mysql
make[3]: 进入目录“/usr/sysbench-1.0.20/src/drivers/mysql”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/drivers/mysql”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/drivers/mysql”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/drivers/mysql”
make[3]: 进入目录“/usr/sysbench-1.0.20/src/drivers”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/drivers”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/drivers”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/drivers”
make[2]: 离开目录“/usr/sysbench-1.0.20/src/drivers”
Making install in tests
make[2]: 进入目录“/usr/sysbench-1.0.20/src/tests”
Making install in cpu
make[3]: 进入目录“/usr/sysbench-1.0.20/src/tests/cpu”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/tests/cpu”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/tests/cpu”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/tests/cpu”
Making install in fileio
make[3]: 进入目录“/usr/sysbench-1.0.20/src/tests/fileio”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/tests/fileio”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/tests/fileio”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/tests/fileio”
Making install in memory
make[3]: 进入目录“/usr/sysbench-1.0.20/src/tests/memory”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/tests/memory”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/tests/memory”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/tests/memory”
Making install in threads
make[3]: 进入目录“/usr/sysbench-1.0.20/src/tests/threads”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/tests/threads”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/tests/threads”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/tests/threads”
Making install in mutex
make[3]: 进入目录“/usr/sysbench-1.0.20/src/tests/mutex”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/tests/mutex”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/tests/mutex”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/tests/mutex”
make[3]: 进入目录“/usr/sysbench-1.0.20/src/tests”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/tests”
make[4]: 对“install-exec-am”无需做任何事。
make[4]: 对“install-data-am”无需做任何事。
make[4]: 离开目录“/usr/sysbench-1.0.20/src/tests”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/tests”
make[2]: 离开目录“/usr/sysbench-1.0.20/src/tests”
Making install in lua
make[2]: 进入目录“/usr/sysbench-1.0.20/src/lua”
Making install in internal
make[3]: 进入目录“/usr/sysbench-1.0.20/src/lua/internal”
make install-am
make[4]: 进入目录“/usr/sysbench-1.0.20/src/lua/internal”
make[5]: 进入目录“/usr/sysbench-1.0.20/src/lua/internal”
make[5]: 对“install-exec-am”无需做任何事。
make[5]: 对“install-data-am”无需做任何事。
make[5]: 离开目录“/usr/sysbench-1.0.20/src/lua/internal”
make[4]: 离开目录“/usr/sysbench-1.0.20/src/lua/internal”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/lua/internal”
make[3]: 进入目录“/usr/sysbench-1.0.20/src/lua”
make[4]: 进入目录“/usr/sysbench-1.0.20/src/lua”
make[4]: 对“install-exec-am”无需做任何事。
/usr/bin/mkdir -p '/usr/sysbench/share/sysbench'
/usr/bin/install -c -m 644 oltp_common.lua '/usr/sysbench/share/sysbench'
/usr/bin/mkdir -p '/usr/sysbench/share/sysbench'
/usr/bin/install -c bulk_insert.lua oltp_delete.lua oltp_insert.lua oltp_read_only.lua oltp_read_write.lua oltp_point_select.lua oltp_update_index.lua oltp_update_non_index.lua oltp_write_only.lua select_random_points.lua select_random_ranges.lua '/usr/sysbench/share/sysbench'
make[4]: 离开目录“/usr/sysbench-1.0.20/src/lua”
make[3]: 离开目录“/usr/sysbench-1.0.20/src/lua”
make[2]: 离开目录“/usr/sysbench-1.0.20/src/lua”
Making install in .
make[2]: 进入目录“/usr/sysbench-1.0.20/src”
make[3]: 进入目录“/usr/sysbench-1.0.20/src”
/usr/bin/mkdir -p '/usr/sysbench/bin'
/bin/sh ../libtool --mode=install /usr/bin/install -c sysbench '/usr/sysbench/bin'
libtool: install: /usr/bin/install -c sysbench /usr/sysbench/bin/sysbench
make[3]: 对“install-data-am”无需做任何事。
make[3]: 离开目录“/usr/sysbench-1.0.20/src”
make[2]: 离开目录“/usr/sysbench-1.0.20/src”
make[1]: 离开目录“/usr/sysbench-1.0.20/src”
Making install in tests
make[1]: 进入目录“/usr/sysbench-1.0.20/tests”
make[2]: 进入目录“/usr/sysbench-1.0.20/tests”
make[2]: 对“install-exec-am”无需做任何事。
make INSTALL_TO_DIR="/usr/sysbench/share/sysbench/tests" install_test_files
make[3]: 进入目录“/usr/sysbench-1.0.20/tests”
make[3]: 离开目录“/usr/sysbench-1.0.20/tests”
/usr/bin/mkdir -p '/usr/sysbench/share/sysbench/tests'
/usr/bin/install -c test_run.sh '/usr/sysbench/share/sysbench/tests'
make[2]: 离开目录“/usr/sysbench-1.0.20/tests”
make[1]: 离开目录“/usr/sysbench-1.0.20/tests”
make[1]: 进入目录“/usr/sysbench-1.0.20”
make[2]: 进入目录“/usr/sysbench-1.0.20”
make[2]: 对“install-exec-am”无需做任何事。
make[2]: 对“install-data-am”无需做任何事。
make[2]: 离开目录“/usr/sysbench-1.0.20”
make[1]: 离开目录“/usr/sysbench-1.0.20”
[root@obdeployer sysbench-1.0.20]# cp /usr/sysbench/bin/sysbench /usr/local/bin/
[root@obdeployer sysbench-1.0.20]# cd /usr/sysbench
[root@obdeployer sysbench]# ll
总用量 0
drwxr-xr-x 2 root root 22 3月 28 09:51 bin
drwxr-xr-x 4 root root 33 3月 28 09:51 share
[root@obdeployer sysbench]# bin/sysbench -help
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
FATAL: Cannot find benchmark '-help': no such built-in test, file or module
[root@obdeployer sysbench]# bin/sysbench --help
Usage:
sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
General options:
--threads=N number of threads to use [1]
--events=N limit for total number of events [0]
--time=N limit for total execution time in seconds [10]
--forced-shutdown=STRING number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
--thread-stack-size=SIZE size of stack per thread [64K]
--rate=N average transactions rate. 0 for unlimited rate [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
--report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
--debug[=on|off] print more debugging info [off]
--validate[=on|off] perform validation checks where possible [off]
--help[=on|off] print help and exit [off]
--version[=on|off] print version and exit [off]
--config-file=FILENAME File containing command line options
--tx-rate=N deprecated alias for --rate [0]
--max-requests=N deprecated alias for --events [0]
--max-time=N deprecated alias for --time [0]
--num-threads=N deprecated alias for --threads [1]
Pseudo-Random Numbers Generator options:
--rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
--rand-spec-iter=N number of iterations used for numbers generation [12]
--rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1]
--rand-spec-res=N percentage of 'special' values to use (for special distribution) [75]
--rand-seed=N seed for random number generator. When 0, the current time is used as a RNG seed. [0]
--rand-pareto-h=N parameter h for pareto distribution [0.2]
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]
--percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
--histogram[=on|off] print latency histogram in report [off]
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]
Compiled-in database drivers:
mysql - MySQL driver
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]
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
See 'sysbench <testname> help' for a list of options for each test.
参数设置
OS参数
[root@obdeployer usr]# sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ff>$x/rps_cpus; done' [root@obdeployer usr]# sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries" [root@obdeployer usr]# sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
OceanBase 参数
SYS 租户参数
MySQL [oceanbase]> alter system set enable_auto_leader_switch=false; Query OK, 0 rows affected (1.940 sec) MySQL [oceanbase]> alter system set enable_one_phase_commit=false; Query OK, 0 rows affected (0.085 sec) MySQL [oceanbase]> alter system set enable_monotonic_weak_read = true; Query OK, 0 rows affected (0.082 sec) MySQL [oceanbase]> alter system set weak_read_version_refresh_interval='5s'; Query OK, 0 rows affected (0.758 sec) MySQL [oceanbase]> alter system _ob_minor_merge_schedule_interval='5s'; Query OK, 0 rows affected (0.320 sec) MySQL [oceanbase]> alter system set memory_limit_percentage = 90; Query OK, 0 rows affected (0.324 sec) MySQL [oceanbase]> alter system set memstore_limit_percentage = 55; Query OK, 0 rows affected (0.110 sec) MySQL [oceanbase]> alter system set freeze_trigger_percentage = 70; Query OK, 0 rows affected (1.403 sec) MySQL [oceanbase]> alter system set minor_freeze_times = 50; Query OK, 0 rows affected (0.888 sec) MySQL [oceanbase]> alter system set minor_warm_up_duration_time = 0; Query OK, 0 rows affected (0.447 sec) MySQL [oceanbase]> MySQL [oceanbase]> alter system set merge_thread_count = 32; Query OK, 0 rows affected (0.131 sec) MySQL [oceanbase]> alter system set minor_merge_concurrency = 8; Query OK, 0 rows affected (0.152 sec) MySQL [oceanbase]> alter system set _mini_merge_concurrency = 4; Query OK, 0 rows affected (0.119 sec)
PROXY 参数
MySQL [oceanbase]> alter proxyconfig set proxy_mem_limited='4G'; --防止 oom，可根据实际环境动态调整 Query OK, 0 rows affected (0.004 sec) MySQL [oceanbase]> alter proxyconfig set enable_compression_protocol=false; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set work_thread_num=32; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set enable_compression_protocol=false; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set enable_metadb_used=false; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set enable_standby=false; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set enable_strict_stat_time=false; Query OK, 0 rows affected (0.002 sec) MySQL [oceanbase]> alter proxyconfig set use_local_dbconfig=true; Query OK, 0 rows affected (0.004 sec)
租户参数
MySQL [oceanbase]> set global ob_timestamp_service='GTS' ; Query OK, 0 rows affected (0.178 sec) MySQL [oceanbase]> set global autocommit=ON; Query OK, 0 rows affected (0.014 sec) MySQL [oceanbase]> set global ob_query_timeout=36000000000; Query OK, 0 rows affected (0.364 sec) MySQL [oceanbase]> set global ob_trx_timeout=36000000000; Query OK, 0 rows affected (0.832 sec) MySQL [oceanbase]> set global max_allowed_packet=67108864; Query OK, 0 rows affected (0.253 sec) MySQL [oceanbase]> set global ob_sql_work_area_percentage=100; Query OK, 0 rows affected (0.169 sec) MySQL [oceanbase]> set global parallel_max_servers=800; Query OK, 0 rows affected, 1 warning (0.277 sec) MySQL [oceanbase]> set global parallel_servers_target=800; Query OK, 0 rows affected (0.238 sec)
数据准备
准备数据库账户
MySQL [oceanbase]> create database sysbenchdb; Query OK, 1 row affected (0.125 sec) MySQL [oceanbase]> grant all privileges on sysbenchdb.* to u_sysbench identified by '123456'; Query OK, 0 rows affected (0.291 sec)
初始化表和数据
[admin@obdeployer sysbench]$ sysbench --test=./oltp_read_only.lua --mysql-host=10.201.0.171 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obce-3zones:my_test_obtenant:u_sysbench" --mysql-password=123456 --tables=12 --table_size=100000 --threads=4 --time=300 --report-interval=60 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 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) Initializing worker threads... Creating table 'sbtest2'... Creating table 'sbtest1'... Creating table 'sbtest4'... Creating table 'sbtest3'... Inserting 100000 records into 'sbtest2' Inserting 100000 records into 'sbtest4' Inserting 100000 records into 'sbtest1' Inserting 100000 records into 'sbtest3' Creating a secondary index on 'sbtest2'... Creating a secondary index on 'sbtest4'... Creating a secondary index on 'sbtest1'... Creating a secondary index on 'sbtest3'... Creating table 'sbtest5'... Inserting 100000 records into 'sbtest5' Creating table 'sbtest6'... Inserting 100000 records into 'sbtest6' Creating table 'sbtest7'... Creating table 'sbtest8'... Inserting 100000 records into 'sbtest7' Inserting 100000 records into 'sbtest8' Creating a secondary index on 'sbtest5'... Creating a secondary index on 'sbtest6'... Creating a secondary index on 'sbtest7'... Creating a secondary index on 'sbtest8'... Creating table 'sbtest10'... Inserting 100000 records into 'sbtest10' Creating table 'sbtest9'... Inserting 100000 records into 'sbtest9' Creating table 'sbtest11'... Inserting 100000 records into 'sbtest11' Creating table 'sbtest12'... Inserting 100000 records into 'sbtest12' Creating a secondary index on 'sbtest10'... Creating a secondary index on 'sbtest9'... Creating a secondary index on 'sbtest11'... Creating a secondary index on 'sbtest12'...
SYSBENCH 参数说明：
-
--tables：指定表的数量。 -
--table_size：指定表的数据量。 -
--threads：指定并发数。 -
--mysql-ignore-errors：指定忽略的错误号，忽略后就继续跑。否则，报错就中断。 -
--time: 指定运行时间。 -
--report-interval：报告间隔。
实际执行时，对于这次测试环境–threads不能配置太大，不然会报错：FATAL: mysql_drv_query() returned error 4030 (Over tenant memory limits) for query ‘INSERT INTO sbtestx
场景测试
只读场景
[admin@obdeployer sysbench]$ sysbench --test=./oltp_read_only.lua --mysql-host=10.201.0.171 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obce-3zones:my_test_obtenant:u_sysbench" --mysql-password=123456 --tables=12 --table_size=100000 --threads=4 --time=300 --report-interval=60 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 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: 4 Report intermediate results every 60 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 60s ] thds: 4 tps: 48.05 qps: 673.08 (r/w/o: 673.08/0.00/0.00) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 4 tps: 42.30 qps: 592.21 (r/w/o: 592.21/0.00/0.00) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00 [ 180s ] thds: 4 tps: 41.20 qps: 576.88 (r/w/o: 576.88/0.00/0.00) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00 [ 240s ] thds: 4 tps: 41.45 qps: 580.10 (r/w/o: 580.10/0.00/0.00) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00 [ 300s ] thds: 4 tps: 36.98 qps: 517.80 (r/w/o: 517.80/0.00/0.00) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 176442 write: 0 other: 0 total: 176442 transactions: 12603 (41.99 per sec.) queries: 176442 (587.87 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.1328s total number of events: 12603 Latency (ms): min: 11.89 avg: 95.23 max: 1342.23 95th percentile: 196.89 sum: 1200160.83 Threads fairness: events (avg/stddev): 3150.7500/84.00 execution time (avg/stddev): 300.0402/0.05
dooba监控资源
Gallery Shown: 6 / Valid: 6 / Total: 6 ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┌─ TIME-TENANT ────┐┌─ SQL COUNT ───────────────────────────────────┐┌─ SQL RT ─────────────────────────────────┐ │ TIME TENANT ││ SEL. INS. UPD. DEL. REP. CMT. ROL. ││ SEL. INS. UPD. DEL. REP. CMT. │ │ 0:19:14 1001 ││ 152 1 2 1 0 6 0 ││ 4.37 33.81 5.38 5.16 0.00 49.53 │ │ 11:30:30 1001 ││ 0 32 61 32 0 126 0 ││ 0.00 1.52 3.16 5.60 0.00 23.81 │ │ 11:30:29 1001 ││ 0 24 53 24 0 102 0 ││ 0.00 1.37 5.48 3.49 0.00 34.80 │ │ 11:30:28 1001 ││ 0 33 64 33 0 131 0 ││ 0.00 1.28 5.46 5.23 0.00 22.42 │ │ 11:30:27 1001 ││ 0 27 52 26 0 106 0 ││ 0.00 1.50 5.61 5.26 0.00 27.73 │ │ 11:30:25 1001 ││ 0 20 40 19 0 80 0 ││ 0.00 1.79 4.92 7.46 0.00 29.09 │ │ 11:30:24 1001 ││ 0 21 46 22 0 90 0 ││ 0.00 1.81 8.15 7.23 0.00 29.76 │ │ 11:30:23 1001 ││ 0 31 60 32 0 124 0 ││ 0.00 1.38 5.16 3.24 0.00 25.68 │ │ 11:30:21 1001 ││ 0 21 43 20 0 86 0 ││ 0.00 1.28 3.95 5.44 0.00 41.31 │ │ 11:30:20 1001 ││ 0 26 53 27 0 107 0 ││ 0.00 2.60 6.03 6.31 0.00 27.70 │ │ 11:30:19 1001 ││ 0 31 60 31 0 123 0 ││ 0.00 1.24 4.32 4.53 0.00 27.14 │ │ 11:30:17 1001 ││ 0 29 61 28 0 119 0 ││ 0.00 1.50 3.79 3.32 0.00 26.26 │ │ 11:30:16 1001 ││ 0 31 64 32 0 129 0 ││ 0.00 1.18 3.75 3.99 0.00 23.34 │ │ 11:30:15 1001 ││ 0 23 45 23 0 93 0 ││ 0.00 1.12 4.96 3.82 0.00 36.07 │ │ 11:30:13 1001 ││ 0 35 69 35 0 139 0 ││ 0.00 1.17 3.85 6.06 0.00 23.41 │ │ 11:30:12 1001 ││ 0 33 66 32 0 134 0 ││ 0.00 1.19 2.74 4.08 0.00 24.68 │ └──────────────────┘└───────────────────────────────────────────────┘└──────────────────────────────────────────┘ ┌─ RPC ────────────┐┌─ MEMORY(T) ──────────────┐┌─ IOPS ─────────────────────────────┐ │ FAIL NET FRAME ││ ⊿ACTIVE TOTAL PCT. ││ SES. IOR IOR-SZ IOW IOW-SZ │ │ 0 1.70 0.01 ││ -299549 1.12G 33.9% ││ 3 43 882.0K 0 0 │ │ 0 1.26 0.00 ││ 0 382.0M 11.3% ││ 9 14 248.3K 0 0 │ │ 0 0.74 0.00 ││ 0 382.0M 11.3% ││ 9 15 257.3K 0 0 │ │ 0 1.04 0.00 ││ 0 382.0M 11.3% ││ 8 24 422.9K 0 0 │ │ 0 1.18 0.00 ││ 0 382.0M 11.3% ││ 8 21 364.9K 0 0 │ │ 0 1.20 0.00 ││ 0 382.0M 11.3% ││ 8 16 285.1K 0 0 │ │ 0 1.56 0.00 ││ 0 382.0M 11.3% ││ 9 18 309.4K 0 0 │ │ 0 1.43 0.00 ││ 0 382.0M 11.3% ││ 9 14 240.9K 0 0 │ │ 0 0.99 0.00 ││ 0 382.0M 11.3% ││ 9 14 254.0K 0 0 │ │ 0 1.01 0.00 ││ 0 382.0M 11.3% ││ 9 21 383.6K 0 0 │ │ 0 1.08 0.00 ││ 0 382.0M 11.3% ││ 9 18 314.4K 0 0 │ │ 0 1.72 0.00 ││ 0 382.0M 11.3% ││ 8 14 241.3K 0 0 │ │ 0 2.20 0.01 ││ 0 382.0M 11.3% ││ 8 17 316.1K 0 0 │ │ 0 1.76 0.00 ││ 0 382.0M 11.3% ││ 9 11 207.9K 0 0 │ │ 0 1.30 0.00 ││ 0 382.0M 11.3% ││ 9 23 399.5K 0 0 │ │ 0 1.65 0.00 ││ 0 382.0M 11.3% ││ 8 14 260.6K 0 0 │ └──────────────────┘└──────────────────────────┘└────────────────────────────────────┘
SQLPage Shown: 3 / Valid: 3 / Total: 3 ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┌─ zone1:10.201.0.171:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:31:53 ─┐ │ Active Sess: 3 CPU: 1.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 83.3% Cache-Loc Hit: 0.0% Cache-Row Hit: 0.0% │ │ IO-R Cnt: 0 IO-R Size: 15.8K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 50 1.50 1 15.21 2 3.33 1 3.14 0 0.00 4 40.59 54 0 │ │ 0 0.00 1 1.00 3 4.20 1 1.00 0 0.00 7 214 7 0 │ │ 0 0.00 4 1.17 5 4.43 4 2.00 0 0.00 15 74.42 15 0 │ │ 0 0.00 13 0.94 13 1.78 13 2.18 0 0.00 40 32.38 40 0 │ │ 0 0.00 12 1.18 18 4.44 11 1.69 0 0.00 43 24.76 43 0 │ │ 0 0.00 7 1.11 21 4.15 7 1.44 0 0.00 35 27.53 35 0 │ │ 0 0.00 14 0.89 20 2.46 14 1.05 0 0.00 49 20.14 49 0 │ │ 0 0.00 4 1.67 21 2.10 3 2.00 0 0.00 30 23.38 30 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ┌─ zone2:10.201.0.172:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:31:53 ─┐ │ Active Sess: 3 CPU: 0.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 55.6% Cache-Loc Hit: 0.0% Cache-Row Hit: 0.0% │ │ IO-R Cnt: 3 IO-R Size: 53.9K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 51 5.53 1 16.50 2 6.05 1 6.20 0 0.00 4 36.86 55 0 │ │ 0 0.00 3 1.25 6 6.38 3 8.00 0 0.00 13 83.59 13 0 │ │ 0 0.00 5 4.00 8 8.73 4 11.17 0 0.00 19 90.12 19 0 │ │ 0 0.00 8 1.36 19 7.72 9 9.67 0 0.00 36 27.71 36 0 │ │ 0 0.00 8 1.18 20 9.00 8 6.45 0 0.00 37 18.40 38 0 │ │ 0 0.00 11 1.27 12 1.44 11 13.53 0 0.00 36 26.57 36 0 │ │ 0 0.00 8 1.18 21 7.00 8 2.36 0 0.00 38 29.86 37 0 │ │ 0 0.00 9 1.17 15 5.00 9 1.83 0 0.00 33 27.67 34 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ┌─ zone3:10.201.0.173:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:31:53 ─┐ │ Active Sess: 3 CPU: 3.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 33.3% Cache-Loc Hit: 0.0% Cache-Row Hit: 0.0% │ │ IO-R Cnt: 3 IO-R Size: 57.0K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 51 5.97 1 21.39 2 6.23 1 5.72 0 0.00 4 44.33 55 0 │ │ 0 0.00 2 1.00 3 6.00 2 36.00 0 0.00 7 129 7 0 │ │ 0 0.00 2 1.00 11 7.20 3 6.50 0 0.00 17 37.09 17 0 │ │ 0 0.00 4 4.67 20 5.50 4 10.00 0 0.00 29 28.53 29 0 │ │ 0 0.00 12 1.29 24 5.24 12 4.29 0 0.00 50 29.94 49 0 │ │ 0 0.00 7 1.44 20 7.04 7 11.22 0 0.00 34 31.48 34 0 │ │ 0 0.00 8 1.00 22 6.18 8 2.55 0 0.00 39 27.10 39 0 │ │ 0 0.00 15 1.45 18 5.08 14 5.95 0 0.00 47 36.97 48 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── HOST: observer01:2883 RUMC: 3 2022-03-28 11:31:53
纯写场景
[admin@obdeployer sysbench]$ sysbench --test=./oltp_write_only.lua --mysql-host=10.201.0.171 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obce-3zones:my_test_obtenant:u_sysbench" --mysql-password=123456 --tables=12 --table_size=100000 --threads=4 --time=300 --report-interval=60 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 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: 4 Report intermediate results every 60 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 60s ] thds: 4 tps: 24.63 qps: 98.58 (r/w/o: 0.00/98.58/0.00) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 4 tps: 24.23 qps: 96.98 (r/w/o: 0.00/96.98/0.00) lat (ms,95%): 404.61 err/s: 0.00 reconn/s: 0.00 [ 180s ] thds: 4 tps: 25.72 qps: 102.93 (r/w/o: 0.00/102.93/0.00) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00 [ 240s ] thds: 4 tps: 26.43 qps: 105.60 (r/w/o: 0.00/105.60/0.00) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00 [ 300s ] thds: 4 tps: 27.77 qps: 111.12 (r/w/o: 0.00/111.12/0.00) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 30924 other: 0 total: 30924 transactions: 7731 (25.76 per sec.) queries: 30924 (103.04 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.1118s total number of events: 7731 Latency (ms): min: 28.18 avg: 155.25 max: 1921.17 95th percentile: 350.33 sum: 1200220.71 Threads fairness: events (avg/stddev): 1932.7500/12.58 execution time (avg/stddev): 300.0552/0.03
dooba监控资源
Gallery Shown: 6 / Valid: 6 / Total: 6 ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┌─ TIME-TENANT ────┐┌─ SQL COUNT ───────────────────────────────────┐┌─ SQL RT ─────────────────────────────────┐ │ TIME TENANT ││ SEL. INS. UPD. DEL. REP. CMT. ROL. ││ SEL. INS. UPD. DEL. REP. CMT. │ │ 0:19:18 1001 ││ 156 7 13 6 0 28 0 ││ 4.25 7.79 5.12 4.98 0.00 35.03 │ │ 11:35:04 1001 ││ 208 14 27 14 0 56 0 ││ 4.49 1.25 4.76 4.79 0.00 38.83 │ │ 11:35:02 1001 ││ 216 15 34 16 0 66 0 ││ 4.45 1.45 2.70 3.81 0.00 30.88 │ │ 11:35:01 1001 ││ 211 16 28 15 0 59 0 ││ 4.90 1.23 5.14 5.02 0.00 36.02 │ │ 11:35:00 1001 ││ 261 16 35 16 0 69 0 ││ 4.09 1.27 3.51 6.41 0.00 23.78 │ │ 11:34:58 1001 ││ 109 10 16 10 0 36 0 ││ 6.57 1.67 8.04 5.07 0.00 78.77 │ │ 11:34:57 1001 ││ 143 10 23 10 0 44 0 ││ 3.46 1.83 5.18 10.16 0.00 61.20 │ │ 11:34:56 1001 ││ 210 14 29 14 0 58 0 ││ 6.34 1.35 2.44 5.19 0.00 29.93 │ │ 11:34:54 1001 ││ 263 19 36 19 0 76 0 ││ 3.44 1.40 4.53 5.49 0.00 23.60 │ │ 11:34:53 1001 ││ 249 17 34 17 0 69 0 ││ 4.32 1.81 3.32 2.77 0.00 26.31 │ │ 11:34:52 1001 ││ 217 15 32 15 0 63 0 ││ 4.29 1.31 4.16 2.59 0.00 33.49 │ │ 11:34:51 1001 ││ 194 13 29 13 0 56 0 ││ 4.86 1.21 5.20 8.09 0.00 35.10 │ │ 11:34:49 1001 ││ 209 15 29 15 0 61 0 ││ 3.61 1.38 4.08 6.35 0.00 36.37 │ │ 11:34:48 1001 ││ 214 13 28 13 0 55 0 ││ 9.13 1.38 5.14 4.67 0.00 25.01 │ │ 11:34:47 1001 ││ 132 11 18 11 0 42 0 ││ 5.71 10.41 8.60 10.24 0.00 45.56 │ │ 11:34:45 1001 ││ 239 15 34 15 0 66 0 ││ 4.49 1.26 3.54 4.23 0.00 27.40 │ └──────────────────┘└───────────────────────────────────────────────┘└──────────────────────────────────────────┘ ┌─ RPC ────────────┐┌─ MEMORY(T) ──────────────┐┌─ IOPS ─────────────────────────────┐ │ FAIL NET FRAME ││ ⊿ACTIVE TOTAL PCT. ││ SES. IOR IOR-SZ IOW IOW-SZ │ │ 0 1.56 0.00 ││ -531883 442.0M 13.1% ││ 5 45 790.5K 0 0 │ │ 0 0.86 0.00 ││ 0 434.0M 12.8% ││ 9 61 1.0M 0 0 │ │ 0 1.25 0.00 ││ 1.5M 434.0M 12.8% ││ 9 69 1.2M 0 0 │ │ 0 1.99 0.00 ││ 0 432.0M 12.8% ││ 9 78 1.3M 0 0 │ │ 0 1.36 0.00 ││ 0 432.0M 12.8% ││ 8 83 1.4M 0 0 │ │ 0 1.40 0.00 ││ 0 432.0M 12.8% ││ 9 32 569.9K 0 0 │ │ 0 2.01 0.01 ││ 0 432.0M 12.8% ││ 9 47 815.4K 0 0 │ │ 0 1.58 0.00 ││ 0 432.0M 12.8% ││ 9 81 1.4M 0 0 │ │ 0 0.70 0.00 ││ 0 432.0M 12.8% ││ 9 81 1.4M 0 0 │ │ 0 1.47 0.00 ││ 0 432.0M 12.8% ││ 9 77 1.3M 0 0 │ │ 0 0.97 0.00 ││ 0 432.0M 12.8% ││ 9 67 1.1M 0 0 │ │ 0 1.57 0.01 ││ 0 432.0M 12.8% ││ 9 65 1.1M 0 0 │ │ 0 1.74 0.00 ││ 0 432.0M 12.8% ││ 8 45 803.7K 0 0 │ │ 0 1.41 0.00 ││ 0 432.0M 12.8% ││ 9 64 1.1M 0 0 │ │ 0 1.32 0.00 ││ 0 432.0M 12.8% ││ 9 51 894.0K 0 0 │ │ 0 1.77 0.00 ││ 0 432.0M 12.8% ││ 9 80 1.4M 0 0 │ └──────────────────┘└──────────────────────────┘└────────────────────────────────────┘ ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── HOST: observer01:2883 RUMC: 3 2022-03-28 11:35:04
SQLPage Shown: 3 / Valid: 3 / Total: 3 ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┌─ zone1:10.201.0.171:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:35:17 ─┐ │ Active Sess: 3 CPU: 5.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 64.3% Cache-Loc Hit: 0.0% Cache-Row Hit: 0.0% │ │ IO-R Cnt: 19 IO-R Size: 334.9K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 52 1.57 2 6.33 4 3.24 2 3.05 0 0.00 9 35.32 61 0 │ │ 76 1.86 3 1.00 10 1.57 3 3.80 0 0.00 18 52.38 95 0 │ │ 59 1.96 3 1.25 8 1.55 3 1.75 0 0.00 14 43.74 74 0 │ │ 92 1.35 3 0.75 10 2.31 3 4.50 0 0.00 16 21.76 107 0 │ │ 90 1.90 3 0.75 11 1.33 3 1.25 0 0.00 17 56.87 109 0 │ │ 74 2.06 7 1.44 8 1.73 7 1.44 0 0.00 22 31.14 96 0 │ │ 66 1.77 1 3.50 5 1.14 1 1.00 0 0.00 8 213 75 0 │ │ 52 2.55 3 1.00 10 1.31 3 1.00 0 0.00 16 26.43 68 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ┌─ zone2:10.201.0.172:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:35:17 ─┐ │ Active Sess: 3 CPU: 5.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 82.4% Cache-Loc Hit: 0.0% Cache-Row Hit: 0.0% │ │ IO-R Cnt: 9 IO-R Size: 173.5K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 53 5.53 2 5.40 4 5.88 2 5.83 0 0.00 9 30.56 62 0 │ │ 47 4.06 4 1.67 9 4.08 4 1.17 0 0.00 18 27.75 65 0 │ │ 19 8.20 3 1.00 3 17.00 3 1.40 0 0.00 11 78.73 30 0 │ │ 64 3.81 5 1.00 7 7.10 5 1.29 0 0.00 18 32.12 83 0 │ │ 58 3.63 7 1.22 12 11.19 7 9.11 0 0.00 26 17.76 85 0 │ │ 62 4.44 6 1.62 8 3.27 6 11.75 0 0.00 21 47.56 82 0 │ │ 27 8.36 6 2.12 9 2.83 6 7.75 0 0.00 21 33.64 49 0 │ │ 66 7.18 3 0.80 4 3.17 3 7.20 0 0.00 12 26.38 79 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ┌─ zone3:10.201.0.173:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:35:17 ─┐ │ Active Sess: 3 CPU: 17.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 68.3% Cache-Loc Hit: 0.0% │ │ Cache-Row Hit: 0.0% IO-R Cnt: 28 IO-R Size: 502.3K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 53 5.63 2 8.21 4 6.28 2 5.96 0 0.00 9 37.64 62 0 │ │ 76 7.16 6 2.89 9 12.00 6 9.33 0 0.00 22 34.03 99 0 │ │ 55 12.83 1 1.00 6 14.50 1 1.00 0 0.00 10 126 64 0 │ │ 67 6.83 7 1.00 13 5.06 7 4.33 0 0.00 28 23.19 95 0 │ │ 79 5.12 6 1.38 9 4.17 7 2.33 0 0.00 22 28.10 102 0 │ │ 87 5.24 2 1.00 13 5.59 2 1.00 0 0.00 18 34.83 106 0 │ │ 44 5.76 3 1.00 6 5.33 3 5.50 0 0.00 13 99.33 57 0 │ │ 44 10.75 3 1.25 5 17.43 3 4.50 0 0.00 11 48.13 55 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── HOST: observer01:2883 RUMC: 3 2022-03-28 11:35:17
读写混合场景
[admin@obdeployer sysbench]$ sysbench --test=./oltp_read_write.lua --mysql-host=10.201.0.171 --mysql-port=2883 --mysql-db=sysbenchdb --mysql-user="obce-3zones:my_test_obtenant:u_sysbench" --mysql-password=123456 --tables=12 --table_size=100000 --threads=4 --time=300 --report-interval=60 --db-driver=mysql --db-ps-mode=disable --skip-trx=on --mysql-ignore-errors=6002,6004,4012,2013,4016,1062 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: 4 Report intermediate results every 60 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 60s ] thds: 4 tps: 14.25 qps: 257.16 (r/w/o: 200.12/57.03/0.02) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 4 tps: 12.87 qps: 231.82 (r/w/o: 180.32/51.50/0.00) lat (ms,95%): 612.21 err/s: 0.00 reconn/s: 0.00 [ 180s ] thds: 4 tps: 13.97 qps: 251.13 (r/w/o: 195.27/55.87/0.00) lat (ms,95%): 530.08 err/s: 0.00 reconn/s: 0.00 [ 240s ] thds: 4 tps: 13.92 qps: 250.47 (r/w/o: 194.87/55.60/0.00) lat (ms,95%): 520.62 err/s: 0.00 reconn/s: 0.00 [ 300s ] thds: 4 tps: 13.80 qps: 248.45 (r/w/o: 193.25/55.20/0.00) lat (ms,95%): 539.71 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 57848 write: 16527 other: 1 total: 74376 transactions: 4132 (13.76 per sec.) queries: 74376 (247.72 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.2441s total number of events: 4132 Latency (ms): min: 69.26 avg: 290.56 max: 1889.70 95th percentile: 549.52 sum: 1200582.02 Threads fairness: events (avg/stddev): 1033.0000/7.58 execution time (avg/stddev): 300.1455/0.06
dooba监控资源
Gallery Shown: 6 / Valid: 6 / Total: 6 ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┌─ TIME-TENANT ────┐┌─ SQL COUNT ───────────────────────────────────┐┌─ SQL RT ─────────────────────────────────┐ │ TIME TENANT ││ SEL. INS. UPD. DEL. REP. CMT. ROL. ││ SEL. INS. UPD. DEL. REP. CMT. │ │ 0:19:30 1001 ││ 14 2 0 0 0 3 0 ││ 5.45 209 0.00 0.00 0.00 826 │ │ 11:18:23 1001 ││ 839 0 0 0 0 0 0 ││ 3.11 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:22 1001 ││ 711 0 0 0 0 0 0 ││ 3.26 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:20 1001 ││ 765 0 0 0 0 0 0 ││ 2.88 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:19 1001 ││ 742 0 0 0 0 0 0 ││ 3.02 0.00 0.00 0.00 0.00 28.48 │ │ 11:18:18 1001 ││ 824 0 0 0 0 0 0 ││ 2.99 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:17 1001 ││ 750 0 0 0 0 0 0 ││ 2.92 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:15 1001 ││ 707 0 0 0 0 0 0 ││ 2.94 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:14 1001 ││ 732 0 0 0 0 0 0 ││ 3.23 0.00 0.00 0.00 0.00 7.51 │ │ 11:18:13 1001 ││ 687 0 0 0 0 0 0 ││ 3.82 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:11 1001 ││ 746 0 0 0 0 0 0 ││ 3.09 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:10 1001 ││ 681 0 0 0 0 0 0 ││ 2.95 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:09 1001 ││ 759 0 0 0 0 0 0 ││ 3.17 0.00 0.00 0.00 0.00 12.01 │ │ 11:18:07 1001 ││ 510 0 0 0 0 0 0 ││ 4.56 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:06 1001 ││ 485 0 0 0 0 0 0 ││ 5.15 0.00 0.00 0.00 0.00 0.00 │ │ 11:18:05 1001 ││ 589 0 0 0 0 0 0 ││ 4.22 0.00 0.00 0.00 0.00 71.97 │ └──────────────────┘└───────────────────────────────────────────────┘└──────────────────────────────────────────┘ ┌─ RPC ────────────┐┌─ MEMORY(T) ──────────────┐┌─ IOPS ─────────────────────────────┐ │ FAIL NET FRAME ││ ⊿ACTIVE TOTAL PCT. ││ SES. IOR IOR-SZ IOW IOW-SZ │ │ 0 2.27 0.01 ││ -661073 2.32G 70.3% ││ 6 6 225.2K 0 0 │ │ 0 1.51 0.00 ││ 0 364.0M 10.8% ││ 9 203 3.5M 0 0 │ │ 0 0.68 0.00 ││ 0 364.0M 10.8% ││ 9 149 2.5M 0 0 │ │ 0 1.79 0.00 ││ 0 364.0M 10.8% ││ 9 181 3.1M 0 0 │ │ 0 0.85 0.01 ││ 0 364.0M 10.8% ││ 9 159 2.7M 0 0 │ │ 0 0.95 0.01 ││ 0 364.0M 10.8% ││ 9 193 3.3M 0 0 │ │ 0 1.66 0.01 ││ 0 364.0M 10.8% ││ 9 160 2.7M 0 0 │ │ 0 1.23 0.00 ││ 0 364.0M 10.8% ││ 9 185 3.2M 0 0 │ │ 0 1.83 0.00 ││ 0 364.0M 10.8% ││ 9 152 2.6M 0 0 │ │ 0 1.00 0.01 ││ 0 364.0M 10.8% ││ 9 178 3.0M 0 0 │ │ 0 1.52 0.01 ││ 0 364.0M 10.8% ││ 8 189 3.2M 0 0 │ │ 0 1.08 0.00 ││ 0 364.0M 10.8% ││ 8 159 2.7M 0 0 │ │ 0 1.53 0.00 ││ 0 364.0M 10.8% ││ 8 208 3.5M 0 0 │ │ 0 2.68 0.00 ││ 0 364.0M 10.8% ││ 9 129 2.2M 0 0 │ │ 0 3.65 0.01 ││ 0 364.0M 10.8% ││ 9 123 2.1M 0 0 │ │ 0 1.24 0.00 ││ 0 364.0M 10.8% ││ 9 158 2.6M 0 0 │ └──────────────────┘└──────────────────────────┘└────────────────────────────────────┘ ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── HOST: observer01:2883 RUMC: 3 2022-03-28 11:18:24
SQLPage Shown: 3 / Valid: 3 / Total: 3 ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┌─ zone1:10.201.0.171:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:19:08 ─┐ │ Active Sess: 3 CPU: 5.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 75.6% Cache-Loc Hit: 0.0% Cache-Row Hit: 0.0% │ │ IO-R Cnt: 47 IO-R Size: 823.0K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 13 1.57 0 161 0 0.00 0 0.00 0 0.00 0 767 13 0 │ │ 177 1.48 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 177 0 │ │ 217 1.35 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 217 0 │ │ 166 1.58 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 166 0 │ │ 86 1.22 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 86 0 │ │ 214 1.02 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 214 0 │ │ 179 1.14 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 179 0 │ │ 141 2.89 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 141 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ┌─ zone2:10.201.0.172:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:19:08 ─┐ │ Active Sess: 3 CPU: 23.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 68.8% Cache-Loc Hit: 0.0% │ │ Cache-Row Hit: 0.0% IO-R Cnt: 54 IO-R Size: 960.7K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 13 5.43 0 203 0 0.00 0 0.00 0 0.00 0 587 14 0 │ │ 222 5.71 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 222 0 │ │ 196 4.30 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 196 0 │ │ 223 4.96 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 223 0 │ │ 120 16.06 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 120 0 │ │ 180 6.29 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 180 0 │ │ 179 7.45 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 179 0 │ │ 164 7.60 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 163 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ┌─ zone3:10.201.0.173:2882 ───────────────────────────────────────────────────────────────── 2022-03-28 11:19:08 ─┐ │ Active Sess: 3 CPU: 20.0% Cache-BI Hit: 100.0% Cache-Blk Hit: 80.9% Cache-Loc Hit: 0.0% │ │ Cache-Row Hit: 0.0% IO-R Cnt: 35 IO-R Size: 610.2K IO-W Cnt: 0 IO-W Size: 0 │ │ SSC SSRT SIC SIRT SUC SURT SDC SDRT SRC SRRT TCC TCRT SLC SRC │ │ 13 5.63 1 244 0 0.00 0 0.00 0 0.00 1 896 14 0 │ │ 210 5.23 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 211 0 │ │ 198 5.45 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 198 0 │ │ 184 5.12 0 0.00 0 0.00 0 0.00 0 0.00 0 28.00 184 0 │ │ 129 10.75 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 128 0 │ │ 169 8.31 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 169 0 │ │ 159 5.19 0 0.00 0 0.00 0 0.00 0 0.00 0 0.00 160 0 │ │ 207 4.20 0 0.00 0 0.00 0 0.00 0 0.00 0 30.00 207 0 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── HOST: observer01:2883 RUMC: 3 2022-03-28 11:19:08
今天的文章实践练习5：对 OceanBase 做性能测试「建议收藏」分享到此就结束了，感谢您的阅读。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/65640.html
