实践练习5：对 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-Ran