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

測試結果：