sysbench的简单安装与数据库测试

我会带着你远行 2022-08-12 00:25 435阅读 0赞

Sysbench是MySQL数据库及系统的重要压力测试工具，通过该工具可以测试出CPU、内存、IO、进程、数据库等方面的性能。

下面是使用sysbench进行MySQL数据库测试的步骤：

**第一步：sysbench软件的编译安装**

1.sysbench的安装和使用需要先安装下面相关的软件包：

\# yum install -y automake libtool zlib gcc glibc-devel  glibc-headers  glibc-kernheaders

要测试MySQL数据库性能，需要在系统先安装好MySQL数据库，本例中MySQL数据库安装路径为/usr/local/mysql/

2.由于sysbench多表压测的源码使用bazaar进行管理，因此需要首先安装bazaar工具，才能获取源码。

安装bazaar工具  
\# yum install -y bzr

3.获取sysbench源码

\# cd /usr/local/src

\# bzr branch lp:sysbench

\# ls

4.编译安装sysbench软件：

\# cd  /usr/local/src

\# cd sysbench/;   
\# ./autogen.sh   
\# ./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib  
\# make & make install  
  
  
测试安装结果：  
\# /usr/local/sysbench/bin/sysbench --help

如果能够正常显示出sysbench相同命令，则说明sysbench软件安装完毕。

如果不能够正常显示提示下面的错误，则进行相应处理：

\# /usr/local/sysbench/bin/sysbench --help  
/usr/local/sysbench/bin/sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory  
  
如果提示上面的错误，则说明链接可能存在问题，执行下面的命令查找并进行链接即可：  
\# find / -name libmysqlclient.so.18  
/usr/local/env/mysql-5.5.35-linux2.6-x86\_64/lib/libmysqlclient.so.18

\# ln -s /usr/local/env/mysql-5.5.35-linux2.6-x86\_64/lib/libmysqlclient.so.18 /usr/lib64/

**第二步：数据库测试相关用户和表准备**

sysbench在运行过程中需要连接MySQL数据库，并将测试结果存放在数据库中，所以在测试前要先创建测试相关的用户和表。

\# mysql -u root -p

mysql> create databases sbtest DEFAULT CHARACTER SET utf8;

mysql> grant all privileges on sbtest.\* to test@'127.0.0.1' identified by 'test';

mysql> flush privileges;

mysql> show grants for test@'127.0.0.1';

进行创建用户的连接测试：

\# mysql -utest -p'test'  -h127.0.0.1 -P3307 --database sbtest

注意:数据库中的5个参数都必须保证正常连通，才能在下面的数据测试中能够正常写入和测试，主机-h为127.0.0.1,端口-p为3307，用户名-u为test，密码-p为test，测试数据库--database为sbtest；其中前四个参数可以在下面进行操作时进行对应修改，测试数据库的名称已经写入到脚本中，不能够修改。建议按照这样的配置进行测试库准备。

**第三步：用sysbench进行数据准备、测试**

Sysbench通过调用parallel\_prepare.lua脚本，进行并行数据准备。数据准备就绪后，调用oltp.lua脚本进行压力测试。有大量的测试参数，可以指定不同类型的操作比例，从而控制业务模型。参数可以参考说明文档，不在赘述。

数据准备：

\# find / -name parallel\_perpare.lua

\#/usr/local/sysbench/bin/sysbench \--test=/usr/local/src/sysbench/sysbench/tests/db/parallel\_prepare.lua \--max-time=100 --oltp-dist-type=uniform --max-requests=0 --mysql-user=test --mysql-password=test --mysql-table-engine=innodb --oltp-table-size=100 --oltp-tables-count=32 --oltp-range-size=90 --oltp-point-selects=1 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 --oltp-distinct-ranges=1 --oltp-non-index-updates=10 --num-threads=20  --mysql-host=127.0.0.1 --mysql-port=3307 prepare

执行测试：

\# find / -name oltp.lua

\#/usr/local/sysbench/bin/sysbench \--test=/usr/local/src/sysbench/sysbench/tests/db/oltp.lua \--max-time=100 --oltp-dist-type=uniform --max-requests=0 --mysql-user=test --mysql-password=test --mysql-table-engine=innodb --oltp-table-size=100 --oltp-tables-count=32 --oltp-range-size=90 --oltp-point-selects=1 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 --oltp-distinct-ranges=1 --oltp-non-index-updates=10 --num-threads=20  --mysql-host=127.0.0.1 --mysql-port=3307 run

注意：在数据准备和执行测试的过程中，其他参数基本相同，但上面两个标注红色的地方是不同的，需要注意。

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

实际操作过程如下：

安装bazaar工具的过程如下

# yum install bzr
    Loaded plugins: fastestmirror, security
    Determining fastest mirrors
    base                                                                                                         | 3.7 kB     00:00     
    extras                                                                                                       | 3.4 kB     00:00     
    updates                                                                                                      | 3.4 kB     00:00     
    updates/primary_db                                                                                           | 4.7 MB     00:01     
    Setting up Install Process
    Resolving Dependencies
    --> Running transaction check
    ---> Package bzr.x86_64 0:2.1.1-2.el6 will be installed
    --> Finished Dependency Resolution
    
    Dependencies Resolved
    
    ====================================================================================================================================
     Package                     Arch                           Version                              Repository                    Size
    ====================================================================================================================================
    Installing:
     bzr                         x86_64                         2.1.1-2.el6                          base                         5.3 M
    
    Transaction Summary
    ====================================================================================================================================
    Install       1 Package(s)
    
    Total download size: 5.3 M
    Installed size: 23 M
    Is this ok [y/N]: y
    Downloading Packages:
    bzr-2.1.1-2.el6.x86_64.rpm                                                                                   | 5.3 MB     00:04     
    Running rpm_check_debug
    Running Transaction Test
    Transaction Test Succeeded
    Running Transaction
      Installing : bzr-2.1.1-2.el6.x86_64                                                                                           1/1 
      Verifying  : bzr-2.1.1-2.el6.x86_64                                                                                           1/1 
    
    Installed:
      bzr.x86_64 0:2.1.1-2.el6                                                                                                          
    
    Complete!

使用bazaar工具获取sysbench源码的过程如下：

# bzr branch lp:sysbench
    You have not informed bzr of your Launchpad ID, and you must do this to
    write to Launchpad or access private data.  See "bzr help launchpad-login".
    Branched 122 revision(s).                                                                                                          
    # ls

进行sysbench编译准备的过程如下：

# cd sysbench/; 
    # ./autogen.sh 
    ./autogen.sh: running `aclocal -I m4' 
    ./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 `autoheader' 
    ./autogen.sh: running `automake -c --foreign --add-missing' 
    configure.ac:23: installing `config/compile'
    configure.ac:11: installing `config/config.guess'
    configure.ac:11: installing `config/config.sub'
    configure.ac:16: installing `config/install-sh'
    configure.ac:16: installing `config/missing'
    sysbench/Makefile.am: installing `config/depcomp'
    ./autogen.sh: running `autoconf' 
    Libtoolized with: libtoolize (GNU libtool) 2.2.6b
    Automade with: automake (GNU automake) 1.11.1
    Configured with: autoconf (GNU Autoconf) 2.63
    #

进行sysbench编译的过程如下：

# ./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
    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... /bin/mkdir -p
    checking for gawk... gawk
    checking whether make sets $(MAKE)... yes
    checking for gcc... gcc
    checking for C compiler default output file name... a.out
    checking whether the C compiler works... yes
    checking whether we are cross compiling... no
    checking for suffix of executables... 
    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 for style of include used by make... GNU
    checking dependency style of gcc... gcc3
    checking how to run the C preprocessor... gcc -E
    checking whether gcc and cc understand -c and -o together... yes
    checking for grep that handles long lines and -e... /bin/grep
    checking for egrep... /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 a sed that does not truncate output... /bin/sed
    checking for fgrep... /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... 1966080
    checking whether the shell understands some XSI constructs... yes
    checking whether the shell understands "+="... yes
    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 ar... ar
    checking for strip... strip
    checking for ranlib... ranlib
    checking command to parse /usr/bin/nm -B output from gcc object... ok
    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... no
    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 whether __SUNPRO_C is declared... no
    checking "C Compiler version"... "gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4)"
    checking whether to compile with MySQL support... (cached) yes
    checking whether to compile with Drizzle support... (cached) yes
    checking whether to compile with Oracle support... (cached) no
    checking whether to compile with PostgreSQL support... (cached) no
    checking whether to compile with Lua support... (cached) yes
    checking whether SHM_HUGETLB is declared... yes
    checking whether O_SYNC is declared... yes
    checking for xsltproc... xsltproc
    checking whether xsltproc works... no
    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 MySQL includes... (cached) /usr/local/mysql/include
    checking MySQL libraries... (cached) /usr/local/mysql/lib
    checking for ld used by GCC... /usr/bin/ld -m elf_x86_64
    checking if the linker (/usr/bin/ld -m elf_x86_64) is GNU ld... yes
    checking for shared library run path origin... done
    checking for libdrizzle... no
    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 malloc in -lumem... no
    checking for malloc in -lmtmalloc... 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 for inline... inline
    checking for off_t... yes
    checking for size_t... yes
    checking whether time.h and sys/time.h may both be included... 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 for stdlib.h... (cached) yes
    checking for GNU libc compatible malloc... yes
    checking for stdlib.h... (cached) yes
    checking for unistd.h... (cached) 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 whether clock_gettime is declared... yes
    checking for library containing clock_gettime... -lrt
    checking for alarm... yes
    checking for directio... no
    checking for fdatasync... yes
    checking for gettimeofday... yes
    checking for lrand48... yes
    checking for drand48... yes
    checking for memalign... yes
    checking for memset... yes
    checking for mkstemp... yes
    checking for popen... yes
    checking for posix_memalign... yes
    checking for pthread_yield... yes
    checking for _setjmp... 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
    configure: creating ./config.status
    config.status: creating Makefile
    config.status: creating doc/xsl/Makefile
    config.status: creating doc/xsl/catalog.xml
    config.status: creating doc/Makefile
    config.status: creating sysbench/Makefile
    config.status: creating sysbench/drivers/Makefile
    config.status: creating sysbench/drivers/mysql/Makefile
    config.status: creating sysbench/drivers/drizzle/Makefile
    config.status: creating sysbench/drivers/oracle/Makefile
    config.status: creating sysbench/drivers/pgsql/Makefile
    config.status: creating sysbench/tests/Makefile
    config.status: creating sysbench/tests/cpu/Makefile
    config.status: creating sysbench/tests/fileio/Makefile
    config.status: creating sysbench/tests/memory/Makefile
    config.status: creating sysbench/tests/threads/Makefile
    config.status: creating sysbench/tests/mutex/Makefile
    config.status: creating sysbench/tests/db/Makefile
    config.status: creating sysbench/scripting/Makefile
    config.status: creating sysbench/scripting/lua/Makefile
    config.status: creating sysbench/scripting/lua/src/Makefile
    config.status: creating config/config.h
    config.status: executing depfiles commands
    config.status: executing libtool commands
    #

用sysbench进行数据准备的过程如下：

# /usr/local/sysbench/bin/sysbench --test=/usr/local/src/sysbench/sysbench/tests/db/parallel_prepare.lua --max-time=100 --oltp-dist-type=uniform --max-requests=0 --mysql-user=test --mysql-password=test --mysql-table-engine=innodb --oltp-table-size=100 --oltp-tables-count=32 --oltp-range-size=90 --oltp-point-selects=1 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 --oltp-distinct-ranges=1 --oltp-non-index-updates=10 --num-threads=20  --mysql-host=127.0.0.1 --mysql-port=3307 prepare
    sysbench 0.5:  multi-threaded system evaluation benchmark
    
    Creating table 'sbtest1'...
    Inserting 100 records into 'sbtest1'
    Creating table 'sbtest2'...
    Inserting 100 records into 'sbtest2'
    Creating table 'sbtest3'...
    Inserting 100 records into 'sbtest3'
    Creating table 'sbtest4'...
    Inserting 100 records into 'sbtest4'
    Creating table 'sbtest5'...
    Inserting 100 records into 'sbtest5'
    Creating table 'sbtest6'...
    Inserting 100 records into 'sbtest6'
    Creating table 'sbtest7'...
    Inserting 100 records into 'sbtest7'
    Creating table 'sbtest8'...
    Inserting 100 records into 'sbtest8'
    Creating table 'sbtest9'...
    Inserting 100 records into 'sbtest9'
    Creating table 'sbtest10'...
    Inserting 100 records into 'sbtest10'
    Creating table 'sbtest11'...
    Inserting 100 records into 'sbtest11'
    Creating table 'sbtest12'...
    Inserting 100 records into 'sbtest12'
    Creating table 'sbtest13'...
    Inserting 100 records into 'sbtest13'
    Creating table 'sbtest14'...
    Inserting 100 records into 'sbtest14'
    Creating table 'sbtest15'...
    Inserting 100 records into 'sbtest15'
    Creating table 'sbtest16'...
    Inserting 100 records into 'sbtest16'
    Creating table 'sbtest17'...
    Inserting 100 records into 'sbtest17'
    Creating table 'sbtest18'...
    Inserting 100 records into 'sbtest18'
    Creating table 'sbtest19'...
    Inserting 100 records into 'sbtest19'
    Creating table 'sbtest20'...
    Inserting 100 records into 'sbtest20'
    Creating table 'sbtest21'...
    Inserting 100 records into 'sbtest21'
    Creating table 'sbtest22'...
    Inserting 100 records into 'sbtest22'
    Creating table 'sbtest23'...
    Inserting 100 records into 'sbtest23'
    Creating table 'sbtest24'...
    Inserting 100 records into 'sbtest24'
    Creating table 'sbtest25'...
    Inserting 100 records into 'sbtest25'
    Creating table 'sbtest26'...
    Inserting 100 records into 'sbtest26'
    Creating table 'sbtest27'...
    Inserting 100 records into 'sbtest27'
    Creating table 'sbtest28'...
    Inserting 100 records into 'sbtest28'
    Creating table 'sbtest29'...
    Inserting 100 records into 'sbtest29'
    Creating table 'sbtest30'...
    Inserting 100 records into 'sbtest30'
    Creating table 'sbtest31'...
    Inserting 100 records into 'sbtest31'
    Creating table 'sbtest32'...
    Inserting 100 records into 'sbtest32'
    [root@v02c09 sysbench]#

用sysbench进行数据库测试的过程如下：

# /usr/local/sysbench/bin/sysbench --test=/usr/local/src/sysbench/sysbench/tests/db/oltp.lua --max-time=100 --oltp-dist-type=uniform --max-requests=0 --mysql-user=test --mysql-password=test --mysql-table-engine=innodb --oltp-table-size=100 --oltp-tables-count=32 --oltp-range-size=90 --oltp-point-selects=1 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 --oltp-distinct-ranges=1 --oltp-non-index-updates=10 --num-threads=20  --mysql-host=127.0.0.1 --mysql-port=3307 run
    sysbench 0.5:  multi-threaded system evaluation benchmark
    
    Running the test with following options:
    Number of threads: 20
    Random number generator seed is 0 and will be ignored
    
    
    Threads started!
    
    OLTP test statistics:
        queries performed:
            read:                            395775
            write:                           1024414
            other:                           157907
            total:                           1578096
        transactions:                        78752  (787.36 per sec.)
        deadlocks:                           403    (4.03 per sec.)
        read/write requests:                 1420189 (14199.04 per sec.)
        other operations:                    157907 (1578.75 per sec.)
    
    General statistics:
        total time:                          100.0200s
        total number of events:              78752
        total time taken by event execution: 1999.4894s
        response time:
             min:                                  5.88ms
             avg:                                 25.39ms
             max:                                435.97ms
             approx.  95 percentile:              59.85ms
    
    Threads fairness:
        events (avg/stddev):           3937.6000/124.24
        execution time (avg/stddev):   99.9745/0.01

测试结果出现后，主要关注这些指标：

transaction的大小，后面括号内为TPS值，即每秒钟能够处理的事务数量，TPS值越大，数据库的事务吞吐量越大；

response time：响应时间，需要响应时间大多为毫秒级，响应时间约小，性能越快；