系统环境
# 查看系统版本[root@mysql8 ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) # 查看内核[root@mysql8 ~]# uname -r3.10.0-862.11.6.el7.x86_64# 查看内存[root@mysql8 ~]# free -m total used free shared buff/cache availableMem: 1982 188 811 9 983 1599Swap: 2047 0 2047# 查看物理CPU个数[root@mysql8 ~]# cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l2
sysbench介绍
sysbench是一个模块化、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。
sysbench目前可以进行如下几个方面的性能测试：
fileio - File I/O test #磁盘I/O性能
CPU -CPU performance test #CPU性能
memory -Memory functions speed test #内存性能
threads -Threads subsystem performance test #POSIX线程性能
mutex -Mutex performance test #调度程序性能
OLTP -OLTP test #数据库性能（OLTP基准测试）
sysbench的缺点：
模拟的表格结构太简单，不像tpcc-mysql那样完整的事物系统，但对于MySQL性能压缩对比还是很有用的。
下载地址
https://github.com/akopytov/sysbench/releaseshttps://github.com/akopytov/sysbench/archive/1.0.16.tar.gz
sysbench 软件安装
# 安装依赖包[root@mysql8 ~]# yum install libtool pkgconfig libaio-devel libtool automake make -y[root@mysql8 software]# tar xf 1.0.16.tar.gz[root@mysql8 software]# cd sysbench-1.0.16/[root@mysql8 sysbench-1.0.16]# lsautogen.sh configure.ac doc Makefile.am README.md rpm srcChangeLog COPYING install-sh missing README-Oracle.md scripts testsconfig debian m4 mkinstalldirs README-WIN.txt snap third_party[root@mysql8 sysbench-1.0.16]# ./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.2Automade with: automake (GNU automake) 1.13.4Configured with: autoconf (GNU Autoconf) 2.69[root@mysql8 sysbench-1.0.16]# ./configure --prefix=/sysbenchchecking build system type... x86_64-unknown-linux-gnuchecking host system type... x86_64-unknown-linux-gnuchecking target system type... x86_64-unknown-linux-gnuchecking for a BSD-compatible install... /usr/bin/install -cchecking whether build environment is sane... yeschecking for a thread-safe mkdir -p... /usr/bin/mkdir -pchecking for gawk... gawkchecking whether make sets $(MAKE)... yeschecking whether make supports nested variables... yeschecking for style of include used by make... GNUchecking for gcc... gccchecking whether the C compiler works... yeschecking for C compiler default output file name... a.outchecking for suffix of executables... checking whether we are cross compiling... nochecking for suffix of object files... ochecking whether we are using the GNU C compiler... yeschecking whether gcc accepts -g... yeschecking for gcc option to accept ISO C89... none neededchecking dependency style of gcc... gcc3checking how to run the C preprocessor... gcc -Echecking for grep that handles long lines and -e... /usr/bin/grepchecking for egrep... /usr/bin/grep -Echecking for ANSI C header files... yeschecking for sys/types.h... yeschecking for sys/stat.h... yeschecking for stdlib.h... yeschecking for string.h... yeschecking for memory.h... yeschecking for strings.h... yeschecking for inttypes.h... yeschecking for stdint.h... yeschecking for unistd.h... yeschecking minix/config.h usability... nochecking minix/config.h presence... nochecking for minix/config.h... nochecking whether it is safe to define __EXTENSIONS__... yeschecking for gcc... (cached) gccchecking whether we are using the GNU C compiler... (cached) yeschecking whether gcc accepts -g... (cached) yeschecking for gcc option to accept ISO C89... (cached) none neededchecking dependency style of gcc... (cached) gcc3checking for gcc option to accept ISO C99... -std=gnu99checking how to run the C preprocessor... gcc -Echecking whether gcc -std=gnu99 and cc understand -c and -o together... yeschecking for a sed that does not truncate output... /usr/bin/sedchecking for C compiler vendor... gnuchecking for gcc architecture flag... checking for x86 cpuid 0 output... 16:756e6547:6c65746e:49656e69checking for x86 cpuid 1 output... 506e3:2010800:fffa3203:f8bfbffchecking whether C compiler accepts -march=core2... yeschecking for gcc architecture flag... -march=core2checking for ar... archecking the archiver (ar) interface... archecking how to print strings... printfchecking for a sed that does not truncate output... (cached) /usr/bin/sedchecking for fgrep... /usr/bin/grep -Fchecking for ld used by gcc -std=gnu99... /usr/bin/ldchecking if the linker (/usr/bin/ld) is GNU ld... yeschecking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm -Bchecking the name lister (/usr/bin/nm -B) interface... BSD nmchecking whether ln -s works... yeschecking the maximum length of command line arguments... 1572864checking whether the shell understands some XSI constructs... yeschecking whether the shell understands "+="... yeschecking how to convert x86_64-unknown-linux-gnu file names to x86_64-unknown-linux-gnu format... func_convert_file_noopchecking how to convert x86_64-unknown-linux-gnu file names to toolchain format... func_convert_file_noopchecking for /usr/bin/ld option to reload object files... -rchecking for objdump... objdumpchecking how to recognize dependent libraries... pass_allchecking for dlltool... nochecking how to associate runtime and link libraries... printf %s\nchecking for archiver @FILE support... @checking for strip... stripchecking for ranlib... ranlibchecking command to parse /usr/bin/nm -B output from gcc -std=gnu99 object... okchecking for sysroot... nochecking for mt... nochecking if : is a manifest tool... nochecking for dlfcn.h... yeschecking for objdir... .libschecking if gcc -std=gnu99 supports -fno-rtti -fno-exceptions... nochecking for gcc -std=gnu99 option to produce PIC... -fPIC -DPICchecking if gcc -std=gnu99 PIC flag -fPIC -DPIC works... yeschecking if gcc -std=gnu99 static flag -static works... nochecking if gcc -std=gnu99 supports -c -o file.o... yeschecking if gcc -std=gnu99 supports -c -o file.o... (cached) yeschecking whether the gcc -std=gnu99 linker (/usr/bin/ld -m elf_x86_64) supports shared libraries... yeschecking whether -lc should be explicitly linked in... nochecking dynamic linker characteristics... GNU/Linux ld.sochecking how to hardcode library paths into programs... immediatechecking whether stripping libraries is possible... yeschecking if libtool supports shared libraries... yeschecking whether to build shared libraries... yeschecking whether to build static libraries... yeschecking for pkg-config... yeschecking for C compiler vendor... (cached) gnuchecking whether to compile with MySQL support... yeschecking whether to compile with Drizzle support... nochecking whether to compile with libattachsql support... nochecking whether to compile with Oracle support... nochecking whether to compile with PostgreSQL support... nochecking for pkg-config... /usr/bin/pkg-configchecking pkg-config is at least version 0.9.0... yeschecking whether to build with system or bundled LuaJIT... bundledchecking whether to build with system or bundled Concurrency Kit... bundledchecking whether SHM_HUGETLB is declared... yeschecking whether O_SYNC is declared... yeschecking for xsltproc... xsltprocchecking whether xsltproc works... nochecking for the pthreads library -lpthreads... nochecking whether pthreads work without any flags... nochecking whether pthreads work with -Kthread... nochecking whether pthreads work with -kthread... nochecking for the pthreads library -llthread... nochecking whether pthreads work with -pthread... yeschecking for joinable pthread attribute... PTHREAD_CREATE_JOINABLEchecking if more special flags are required for pthreads... nochecking for sqrt in -lm... yeschecking for mysql_config... /usr/bin/mysql_configchecking MySQL C flags... -I/usr/include/mysql -m64 checking MySQL linker flags... -L/usr/lib64/mysql -lmysqlclient -lpthread -lm -lrt -lssl -lcrypto -ldlchecking for ld used by GCC... /usr/bin/ld -m elf_x86_64checking if the linker (/usr/bin/ld -m elf_x86_64) is GNU ld... yeschecking for shared library run path origin... donechecking libaio.h usability... yeschecking libaio.h presence... yeschecking for libaio.h... yeschecking for io_queue_init in -laio... yeschecking if io_getevents() has an old interface... nochecking for malloc in -lumem... nochecking for malloc in -lmtmalloc... nochecking for ANSI C header files... (cached) yeschecking errno.h usability... yeschecking errno.h presence... yeschecking for errno.h... yeschecking fcntl.h usability... yeschecking fcntl.h presence... yeschecking for fcntl.h... yeschecking math.h usability... yeschecking math.h presence... yeschecking for math.h... yeschecking pthread.h usability... yeschecking pthread.h presence... yeschecking for pthread.h... yeschecking sched.h usability... yeschecking sched.h presence... yeschecking for sched.h... yeschecking signal.h usability... yeschecking signal.h presence... yeschecking for signal.h... yeschecking for stdlib.h... (cached) yeschecking for string.h... (cached) yeschecking sys/aio.h usability... nochecking sys/aio.h presence... nochecking for sys/aio.h... nochecking sys/ipc.h usability... yeschecking sys/ipc.h presence... yeschecking for sys/ipc.h... yeschecking sys/time.h usability... yeschecking sys/time.h presence... yeschecking for sys/time.h... yeschecking sys/mman.h usability... yeschecking sys/mman.h presence... yeschecking for sys/mman.h... yeschecking sys/shm.h usability... yeschecking sys/shm.h presence... yeschecking for sys/shm.h... yeschecking thread.h usability... nochecking thread.h presence... nochecking for thread.h... nochecking for unistd.h... (cached) yeschecking limits.h usability... yeschecking limits.h presence... yeschecking for limits.h... yeschecking libgen.h usability... yeschecking libgen.h presence... yeschecking for libgen.h... yeschecking for off_t... yeschecking whether time.h and sys/time.h may both be included... yeschecking for thread local storage (TLS) class... __threadchecking for __attribute__((format))... yeschecking for __attribute__((unused))... yeschecking for special C compiler options needed for large files... nochecking for _FILE_OFFSET_BITS value needed for large files... nochecking size of size_t... 8checking size of bool... 1checking for stdlib.h... (cached) yeschecking for unistd.h... (cached) yeschecking for sys/param.h... yeschecking for getpagesize... yeschecking for working mmap... yeschecking whether strerror_r is declared... yeschecking for strerror_r... yeschecking whether strerror_r returns char *... yeschecking for library containing clock_gettime... none requiredchecking for alarm... yeschecking for clock_gettime... yeschecking for directio... nochecking for fdatasync... yeschecking for gettimeofday... yeschecking for isatty... yeschecking for memalign... yeschecking for memset... yeschecking for posix_memalign... yeschecking for pthread_yield... yeschecking for setvbuf... yeschecking for sqrt... yeschecking for strdup... yeschecking for thr_setconcurrency... nochecking for valloc... yeschecking for pthread_once... yeschecking that generated files are newer than configure... doneconfigure: creating ./config.statusconfig.status: creating Makefileconfig.status: creating doc/xsl/Makefileconfig.status: creating doc/xsl/catalog.xmlconfig.status: creating doc/Makefileconfig.status: creating third_party/luajit/Makefileconfig.status: creating third_party/concurrency_kit/Makefileconfig.status: creating src/Makefileconfig.status: creating src/drivers/Makefileconfig.status: creating src/drivers/mysql/Makefileconfig.status: creating src/drivers/drizzle/Makefileconfig.status: creating src/drivers/oracle/Makefileconfig.status: creating src/drivers/pgsql/Makefileconfig.status: creating src/drivers/attachsql/Makefileconfig.status: creating src/tests/Makefileconfig.status: creating src/tests/cpu/Makefileconfig.status: creating src/tests/fileio/Makefileconfig.status: creating src/tests/memory/Makefileconfig.status: creating src/tests/threads/Makefileconfig.status: creating src/tests/mutex/Makefileconfig.status: creating src/lua/Makefileconfig.status: creating src/lua/internal/Makefileconfig.status: creating tests/Makefileconfig.status: creating tests/include/config.shconfig.status: creating snap/snapcraft.yamlconfig.status: creating config/config.hconfig.status: executing depfiles commandsconfig.status: executing libtool commands===============================================================================sysbench version : 1.0.16CC : gcc -std=gnu99CFLAGS : -O2 -funroll-loops -ggdb3 -march=core2 -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 -pthreadCPPFLAGS : -D_GNU_SOURCE -I$(top_srcdir)/src -I$(abs_top_builddir)/third_party/luajit/inc -I$(abs_top_builddir)/third_party/concurrency_kit/includeLDFLAGS : LIBS : -laio -lm EXTRA_LDFLAGS : prefix : /sysbenchbindir : ${prefix}/binlibexecdir : ${prefix}/libexecmandir : ${prefix}/share/mandatadir : ${prefix}/shareMySQL support : yesDrizzle support : noAttachSQL support : noOracle support : noPostgreSQL support : noLuaJIT : bundledLUAJIT_CFLAGS : -I$(abs_top_builddir)/third_party/luajit/incLUAJIT_LIBS : $(abs_top_builddir)/third_party/luajit/lib/libluajit-5.1.a -ldlLUAJIT_LDFLAGS : -rdynamicConcurrency Kit : bundledCK_CFLAGS : -I$(abs_top_builddir)/third_party/concurrency_kit/includeCK_LIBS : $(abs_top_builddir)/third_party/concurrency_kit/lib/libck.aconfigure flags : ===============================================================================[root@mysql8 sysbench-1.0.16]# make[root@mysql8 sysbench-1.0.16]# make install[root@mysql8 sysbench-1.0.16]# cd /sysbench/[root@mysql8 sysbench]# ll总用量 0drwxr-xr-x. 2 root root 22 2月 24 21:06 bindrwxr-xr-x. 4 root root 33 2月 24 21:06 share[root@mysql8 sysbench]# vim /etc/profileexport PATH=/sysbench/bin:$PATHroot@mysql8 sysbench]# . /etc/profile[root@mysql8 sysbench]# sysbench --versionsysbench 1.0.16 sysbench 命令与参数
[root@mysql8 sysbench]# sysbench --helpUsage: sysbench [options]... [testname] [command]Commands implemented by most tests: prepare run cleanup helpGeneral options: --threads=N number of threads to use [1] # 创建测试线程的数量，默认是1 --events=N limit for total number of events [0] # 事件最大数量，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 drivermysql 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 testSee 'sysbenchhelp' for a list of options for each test.
sysbench使用[command]常用的有：
prepare 准备测试，生成数据
run 执行测试
cleanup 清理数据
help
version
sysbench 脚本名
路径：/sysbench/share/sysbench/tests/include/oltp_legacy
[root@mysql8 oltp_legacy]# lsbulk_insert.lua delete.lua oltp.lua parallel_prepare.lua select_random_points.lua update_index.luacommon.lua insert.lua oltp_simple.lua select.lua select_random_ranges.lua update_non_index.lua
sysbench 使用的一些建议
在开始测试之前，应该首先明确：应采用针对整个系统的基准测试，还是针对MySQL的基准测试，还是二者都需要。
如果需要针对MySQL的基准测试，那么还需要明确精度方面的要求：是否需要使用生产环境的真实数据，还是使用工具生成也可以，前者实施起来更加繁琐。如果要使用真实数据，尽量使用全部数据，而不是部分数据。
基准测试要进行多次才有意义。
测试时需要注意主从同步的状态。
测试必须模拟多线程的情况，单线程情况不但无法模拟真实的效率，也无法模拟阻塞甚至死锁情况。
错误
错误1
configure: error: mysql_config executable not found
解决方法：
[root@mysql8 sysbench-1.0.16]# yum -y install mysql-devel