DM数据库学习之路(十九）DM8数据库sysbench部署及压力测试

sysbench部署
1. 安装依赖

yum -y install  make automake libtool pkgconfig libaio-devel vim-common

1. 上传sysbench源代码

sysbench_tool.tar

1. 测试是否安装成功

$ /opt/sysbench/sysbench-master-dpi/src/lua

$ ./sysbench --version

sysbench 1.1.0

sysbench测试DM
1. 测试准备
  1. 生成配置文件

$cd /dmdata/sysbench_tool/sysbench-master-dpi

$ ./autogen.sh

autoreconf: Entering directory `.'

autoreconf: configure.ac: not using Gettext

autoreconf: running: aclocal -I m4

autoreconf: configure.ac: tracing

autoreconf: running: libtoolize --copy

libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, 'config'.

libtoolize: copying file 'config/ltmain.sh'

libtoolize: putting macros in AC_CONFIG_MACRO_DIRS, 'm4'.

libtoolize: copying file 'm4/libtool.m4'

libtoolize: copying file 'm4/ltoptions.m4'

libtoolize: copying file 'm4/ltversion.m4'

autoreconf: running: /usr/bin/autoconf

autoreconf: running: /usr/bin/autoheader

autoreconf: running: automake --add-missing --copy --no-force

autoreconf: Leaving directory `.'

[dmdba@dm8db01 sysbench-master-dpi]$ pwd

/dmdata/sysbench_tool/sysbench-master-dpi

此步骤会生成配置文件configure

1. 1. 执行配置文件

X86平台，仅测试DM命令如下：

$cd /dmdata/sysbench_tool/sysbench-master-dpi

$ ./configure --without-mysql --with-dm

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

/dmdata/sysbench_tool/sysbench-master-dpi/config/missing: Unknown `--is-lightweight' option

Try `/dmdata/sysbench_tool/sysbench-master-dpi/config/missing --help' for more information

configure: WARNING: 'missing' script is too old or missing

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 whether make supports the include directive... yes (GNU style)

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 whether gcc understands -c and -o together... yes

checking dependency style of gcc... gcc3

checking how to run the C preprocessor... gcc -E

checking for grep that handles long lines and -e... /usr/bin/grep

checking for egrep... /usr/bin/grep -E

checking for ANSI C header files... yes

checking for sys/types.h... yes

checking for sys/stat.h... yes

checking for stdlib.h... yes

checking for string.h... yes

checking for memory.h... yes

checking for strings.h... yes

checking for inttypes.h... yes

checking for stdint.h... yes

checking for unistd.h... yes

checking minix/config.h usability... no

checking minix/config.h presence... no

checking for minix/config.h... no

checking whether it is safe to define __EXTENSIONS__... yes

checking for gcc... (cached) gcc

checking whether we are using the GNU C compiler... (cached) yes

checking whether gcc accepts -g... (cached) yes

checking for gcc option to accept ISO C89... (cached) none needed

checking whether gcc understands -c and -o together... (cached) yes

checking dependency style of gcc... (cached) gcc3

checking for gcc option to accept ISO C99... none needed

checking how to run the C preprocessor... gcc -E

checking for a sed that does not truncate output... /usr/bin/sed

checking for C compiler vendor... gnu

checking for gcc architecture flag...

checking for x86 cpuid 0 output... 16:756e6547:6c65746e:49656e69

checking for x86 cpuid 1 output... 906ea:1020800:fffa3203:1f8bfbff

checking whether C compiler accepts -march=core2... yes

checking for gcc architecture flag... -march=core2

checking for ar... ar

checking the archiver (ar) interface... ar

checking how to print strings... printf

checking for a sed that does not truncate output... (cached) /usr/bin/sed

checking for fgrep... /usr/bin/grep -F

checking for ld used by gcc... /usr/bin/ld

checking if the linker (/usr/bin/ld) is GNU ld... yes

checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -B

checking the name lister (/usr/bin/nm -B) interface... BSD nm

checking whether ln -s works... yes

checking the maximum length of command line arguments... 3145728

checking how to convert x86_64-unknown-linux-gnu file names to x86_64-unknown-linux-gnu format... func_convert_file_noop

checking how to convert x86_64-unknown-linux-gnu file names to toolchain format... func_convert_file_noop

checking for /usr/bin/ld option to reload object files... -r

checking for objdump... objdump

checking how to recognize dependent libraries... pass_all

checking for dlltool... no

checking how to associate runtime and link libraries... printf %s\n

checking for archiver @FILE support... @

checking for strip... strip

checking for ranlib... ranlib

checking command to parse /usr/bin/nm -B output from gcc object... ok

checking for sysroot... no

checking for a working dd... /usr/bin/dd

checking how to truncate binary pipes... /usr/bin/dd bs=4096 count=1

checking for mt... no

checking if : is a manifest tool... no

checking for dlfcn.h... yes

checking for objdir... .libs

checking if gcc supports -fno-rtti -fno-exceptions... no

checking for gcc option to produce PIC... -fPIC -DPIC

checking if gcc PIC flag -fPIC -DPIC works... yes

checking if gcc static flag -static works... yes

checking if gcc supports -c -o file.o... yes

checking if gcc supports -c -o file.o... (cached) yes

checking whether the gcc linker (/usr/bin/ld -m elf_x86_64) supports shared libraries... yes

checking whether -lc should be explicitly linked in... no

checking dynamic linker characteristics... GNU/Linux ld.so

checking how to hardcode library paths into programs... immediate

checking whether stripping libraries is possible... yes

checking if libtool supports shared libraries... yes

checking whether to build shared libraries... yes

checking whether to build static libraries... yes

checking for pkg-config... yes

checking for C compiler vendor... (cached) gnu

checking whether to compile with MySQL support... no

checking whether to compile with DM support... yes

checking whether to compile with PostgreSQL support... no

checking for pkg-config... /usr/bin/pkg-config

checking pkg-config is at least version 0.9.0... yes

checking whether to build with system or bundled LuaJIT... bundled

checking whether to build with system or bundled Concurrency Kit... bundled

checking whether SHM_HUGETLB is declared... yes

checking whether O_SYNC is declared... yes

checking for the pthreads library -lpthreads... no

checking whether pthreads work without any flags... no

checking whether pthreads work with -Kthread... no

checking whether pthreads work with -kthread... no

checking for the pthreads library -llthread... no

checking whether pthreads work with -pthread... yes

checking for joinable pthread attribute... PTHREAD_CREATE_JOINABLE

checking if more special flags are required for pthreads... no

checking for sqrt in -lm... yes

checking DM C flags... ./configure: line 12735: /dm8: Is a directory

checking DM linker flags... /dm8/include/libdmdpi.a -lrt

checking if dm.h defines DM_OPT_SSL_MODE... checking libaio.h usability... yes

checking libaio.h presence... yes

checking for libaio.h... yes

checking for io_queue_init in -laio... yes

checking if io_getevents() has an old interface... no

checking for ANSI C header files... (cached) yes

checking errno.h usability... yes

checking errno.h presence... yes

checking for errno.h... yes

checking fcntl.h usability... yes

checking fcntl.h presence... yes

checking for fcntl.h... yes

checking math.h usability... yes

checking math.h presence... yes

checking for math.h... yes

checking pthread.h usability... yes

checking pthread.h presence... yes

checking for pthread.h... yes

checking sched.h usability... yes

checking sched.h presence... yes

checking for sched.h... yes

checking signal.h usability... yes

checking signal.h presence... yes

checking for signal.h... yes

checking for stdlib.h... (cached) yes

checking for string.h... (cached) yes

checking sys/aio.h usability... no

checking sys/aio.h presence... no

checking for sys/aio.h... no

checking sys/ipc.h usability... yes

checking sys/ipc.h presence... yes

checking for sys/ipc.h... yes

checking sys/time.h usability... yes

checking sys/time.h presence... yes

checking for sys/time.h... yes

checking sys/mman.h usability... yes

checking sys/mman.h presence... yes

checking for sys/mman.h... yes

checking sys/shm.h usability... yes

checking sys/shm.h presence... yes

checking for sys/shm.h... yes

checking thread.h usability... no

checking thread.h presence... no

checking for thread.h... no

checking for unistd.h... (cached) yes

checking limits.h usability... yes

checking limits.h presence... yes

checking for limits.h... yes

checking libgen.h usability... yes

checking libgen.h presence... yes

checking for libgen.h... yes

checking for off_t... yes

checking whether time.h and sys/time.h may both be included... yes

checking for thread local storage (TLS) class... _Thread_local

checking for __attribute__((format))... yes

checking for __attribute__((unused))... yes

checking for special C compiler options needed for large files... no

checking for _FILE_OFFSET_BITS value needed for large files... no

checking size of size_t... 8

checking size of bool... 1

checking for stdlib.h... (cached) yes

checking for unistd.h... (cached) yes

checking for sys/param.h... yes

checking for getpagesize... yes

checking for working mmap... yes

checking whether strerror_r is declared... yes

checking for strerror_r... yes

checking whether strerror_r returns char *... yes

checking for library containing clock_gettime... none required

checking for alarm... yes

checking for clock_gettime... yes

checking for directio... no

checking for fdatasync... yes

checking for gettimeofday... yes

checking for isatty... yes

checking for memalign... yes

checking for memset... yes

checking for posix_memalign... yes

checking for pthread_cancel... yes

checking for pthread_yield... yes

checking for setvbuf... yes

checking for sqrt... yes

checking for strdup... yes

checking for thr_setconcurrency... no

checking for valloc... yes

checking for pthread_once... yes

checking that generated files are newer than configure... done

configure: creating ./config.status

config.status: creating Makefile

config.status: creating third_party/luajit/Makefile

config.status: creating third_party/concurrency_kit/Makefile

config.status: creating src/Makefile

config.status: creating src/drivers/Makefile

config.status: creating src/drivers/mysql/Makefile

config.status: creating src/drivers/pgsql/Makefile

config.status: creating src/drivers/dm/Makefile

config.status: creating src/tests/Makefile

config.status: creating src/tests/cpu/Makefile

config.status: creating src/tests/fileio/Makefile

config.status: creating src/tests/memory/Makefile

config.status: creating src/tests/threads/Makefile

config.status: creating src/tests/mutex/Makefile

config.status: creating src/lua/Makefile

config.status: creating src/lua/internal/Makefile

config.status: creating tests/Makefile

config.status: creating tests/include/config.sh

config.status: creating snap/snapcraft.yaml

config.status: creating config/config.h

config.status: executing depfiles commands

config.status: executing libtool commands

===============================================================================

sysbench version : 1.1.0

CC : gcc

CFLAGS : -O3 -funroll-loops -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 : -L/usr/local/lib

LIBS : -laio -lm

prefix : /usr/local

bindir : ${prefix}/bin

libexecdir : ${prefix}/libexec

mandir : ${prefix}/share/man

datadir : ${prefix}/share

MySQL support : no

DM support : yes

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 :

===============================================================================

1. 1. 编译

$ cd /dmdata/sysbench_tool/sysbench-master-dpi

$ make

编译成功后，在src目录下生成可执行文件sysbench

将sysbench拷贝到src/lua目录下，然后进入/src/lua目录运行sysbench

1. 1. 环境变量设置

export DM_HOME=/dm8

export PATH=$DM_HOME/bin:$PATH:$DM_HOME/tool:$PATH:HOME/bin:/usr/local/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin

sysbench测试的时候需要依赖dm的dpi驱动

需要设置：LD_LIBRARY_PATH

1. 1. DM默认测试脚本说明

sysbench的数据库测试脚本在src/lua目录下，以oltp为前缀，脚本是lua脚本，可以自行编辑，编辑保存后不需要编译就可以直接使用。

Internal目录下的lua脚本为基础生成h文件，供后续程序编译使用。修改Internal目录下的lua脚本需要重新编译sysbench脚本。

Oltp_common.lua：最基础的测试脚本，定义了表结构、事务基本操作。其它oltp脚本都是引用oltp_common，将其中query进行组合，实现各种测试场景。最常用3个脚本如下：

oltp_read_write.lua:读写混合性测试，包含查询，更新，删除，插入操作

oltp_read_only.lua:读性能测试，包含查询操作

oltp_write_only.lua:写性能测试，包含更新、删除、插入操作

一般使用oltp_read_write.lua脚本测试数据库的oltp性能，包含了读和写操作。oltp_read_write.lua脚本qps是tps的20倍，一个transaction包含20个query操作，这20个query包含14个read，4个write,2个other.

begin:事务开始，计为1个other
point_selects:循环10次，计为10个read
simple_ranges:计为1个read
sum_ranges:计为1个read
order_ranges:计为1个read
distinct_ranger:计为1个read
index_updates: 计为1个write
non_index_updates: 计为1个write
delete_inserts:包含delete,insert操作，计为2个write
commit:事务结束，计为1个other

oltp_read_write.lua脚本说明：

require部分：引用其它lua脚本。Oltp_read_write.lua脚本中的函数定义都在oltp_common.lua脚本，所以需要首先引用。

Prepare_statements部分：对应sysbench测试执行过程中run命令的initializing worker threads部分，对数据库的操作就是建立session后准备各种操作的语句句柄和参数绑定。

Event部分：这部分是测试执行的具体步骤，对应与sysbench的测试执行过程中run命令在threads started后开始的操作。反复执行event统计各种指标。

1. DM8测试
  1. 基本测试
    1. 数据准备

创建测试表，并插入测试数据

./sysbench oltp_read_write.lua --db-driver=dm --dm-db=localhost:5236 --dm-user=SYSDBA --dm-password=SYSDBA --table_size=250000 --tables=25 --auto-inc=0 --threads=256 --time=180 --report-interval=30 prepare

说明：

oltp_read_write.lua #lua脚本

--db-driver=dm #使用DM的驱动

--dm-db=localhost:5236 #DM实例监听端口

--dm-user=SYSDBA #DM实例用户名

--dm-password=SYSDBA #DM实例密码

--table_size=250000 #每张表插入25万行条数据

--tables=25 #建25张测试表

--auto-inc=0 #表示主键是否使用自增列。0表示不使用自增列

--threads=256 #表示发起256个并发

--time=180 #表示测试总时长180秒

--report-interval=30 #表示每30秒输出一次测试进度报告

1. 1. 1. 运行测试

执行测试脚本

1. 1. 1. 清理数据

删除测试表

结论：测试环境为笔记本虚拟机，CPU2核，519TPS，10387QPS

可以根据CPU核数先估算TPS，查看机器CPU核数

# cat /proc/cpuinfo | grep "processor" | wc -l

物理机每核300-350TPS

虚拟机每核200-250TPS

1. 1. 1. 参数优化
I/O优化

DIRECT_IO = 1 #磁盘性能高，内存不足，IO密集

IO_THR_GROUPS = 128 #磁盘性能高,增加IO线程数目

检查点，减少检查点频率，减少每次刷盘数据量

CKPT_RLOG_SIZE = 0

CKPT_DIRTY_PAGES = 0

CKPT_FLUSH_RATE = 1

CKPT_INTERVAL = 300

减少UNDO_RETENTION

UNDO_RETENTION = 2

其它参数优化

ALTER SYSTEM SET 'BUFFER' =200000 SPFILE;

ALTER SYSTEM SET 'BUFFER_POOLS' =97 SPFILE;

ALTER SYSTEM SET 'FAST_POOL_PAGES’ =200000 SPFILE;

ALTER SYSTEM SET 'FAST_ROLL_PAGES’ =10000 SPFILE;

ALTER SYSTEM SET 'ENABLE_FREQROOTS' =2 SPFILE;

ALTER SYSTEM SET 'TRX_DICT_LOCK_NUM’ =1024 SPFILE;

ALTER SYSTEM SET 'MEMORY_MAGIC_CHECK’ =0 SPFILE;

ALTER SYSTEM SET 'ENABLE_MONITOR' =0 SPFILE;

ALTER SYSTEM SET 'MONITOR_TIME' =0 SPFILE;

ALTER SYSTEM SET 'PK_WITH_CLUSTER' =1 SPFILE;

ALTER SYSTEM SET 'FAST_LOGIN' =1 SPFILE;

ALTER SYSTEM SET 'BATCH_PARAM_OPT =1 SPFILE;

ALTER SYSTEM SET 'ENABLE_INDEX_FILTER' =1 SPFILE;

ALTER SYSTEM SET 'NEW_MOTION' =1 SPFILE;

ALTER SYSTEM SET 'MULTI_UPD_OPT_FLAG' =1 SPFILE;

ALTER SYSTEM SET 'DSC_TRX_CMT_LSN_SYNC’ =0 SPFILE;

ALTER SYSTEM SET 'CLT_CONST_TO_PARAM' =1 SPFILE;

--I0

ALTER SYSTEM SET 'DIRECT_IO' =1 SPFILE;

ALTER SYSTEM SET 'IO_THR_GROUPS' =128 SPFILE;

--THREADS

ALTER SYSTEM SET 'WORKER_TIIREADS' =64 SPFILE;

--WORK THRD STACK SIZE default value is 8192

ALTER SYSTEM SET 'WORK_THRD_STACK_SIZE' =8192 SPFILE;

--SESSION

ALTER SYSTEM SET 'MAX_SESSIONS' = 10000 SPFILE;

ALTER SYSTEM SET 'MAX_SESSION_STATEMENT' =20000 SPFILE;

--TRANSACTION

ALTER SYSTEM SET 'UNDO_RETENTION' =2 SPFILE;

--RLOG

ALTER SYSTEM SET 'RLOG_BUF_SIZE' =512 SPFILE;

ALTER SYSTEM SET 'RLOG_POOL_SIZE' =256 SPFILE;

ALTER SYSTEM SET 'RLOG_PARALLEL_ENABLE' =1 SPFILE;

ALTER SYSTEM SET 'RLOG_SAFE_SPACE' =128 SPFILE;

--ALTER SYSTEM SET 'RLOG_CHECK_SPACE’ =1 SPFILE;

--CHECK POINT

ALTER SYSTEM SET 'CKPT_RLOG_SIZE' =0 SPFILE;

ALTER SYSTEM SET 'CKPT_DIRTY_PAGES' =0 SPFILE;

ALTER SYSTEM SET 'CKPT_FLUSII_RATE' =1 SPFILE;

ALTER SYSTEM SET 'CKPT_INTERVAL' =300 SPFILE;

ALTER SYSTEM SET 'ENABLE_SPACELIMIT_CHECK' =1 SPFILE;

--INDEX

ALTER SYSTEM SET 'SORT_BUF_SIZE' =3 SPFILE;

ALTER SYSTEM SET 'SORT_BLK_SIZE' =1 SPFILE;

ALTER SYSTEM SET 'SORT_BUF_GLOBAL_SIZE' =100000 SPFILE;

ALTER SYSTEM SET 'SORT_FLAG' =1 SPFILE;

1. 1. 脚本测试

vi 88.sh

#!/bin/bash

workdir=$(cd `dirname $0`; pwd)

echo 'sysbench oltp_read_write'

./sysbench --db-driver=dm --dm-db=localhost:5236 --dm-user=SYSDBA --dm-password=SYSDBA --table_size=250000 --tables=25 --events=0 --time=600 --auto_inc=1 --threads=256 oltp_read_write prepare > ${workdir}/oltp_read_write_createtable.log 2>&1

./sysbench --db-driver=dm --dm-db=localhost:5236 --dm-user=SYSDBA --dm-password=SYSDBA --table_size=250000 --tables=25 --events=0 --time=600 --auto_inc=1 --threads=256 --percentile=95 --report-interval=1 oltp_read_write run > ${workdir}/oltp_read_write.log 2>&1

sleep 30

./sysbench --db-driver=dm --dm-db=localhost:5236 --dm-user=SYSDBA --dm-password=SYSDBA --table_size=250000 --tables=25 --events=0 --time=600 --auto_inc=1 --threads=256 --percentile=95 oltp_read_write cleanup > ${workdir}/oltp_read_write_cleanup.log 2>&1

测试结果：