乘风原创程序

  • MySQL基准测试工具sysbench的安装测试教程
  • 2020/5/13 10:12:03
  • 下文主要给大家带来MySQL基准测试工具sysbench的安装测试教程,希望这些内容能够带给大家实际用处,这也是我编辑MySQL基准测试工具sysbench的安装测试教程这篇文章的主要目的。好了,废话不多说,大家直接看下文吧。

    环境:CentOS6.5,MySQL5.7.22
    1.下载  sysbench下载地址
    2.安装

    tar -zxvf sysbench-1.1.0.tar.gz
    yum install automake libtool –y   #安装依赖包
    cd sysbench-0.4.12.14
    ./autogen.sh
    ./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib --with-mysql
    export LD_LIBRARY_PATH=/usr/local/mysql/include #这里换成机器中mysql路径下的include
    make
    make install
    sysbench --version  # 检验安装是否成功

    3.简介
    sysbench是跨平台的基准测试工具,支持多线程,支持多种数据库,主要包括以下几种测试:

    • CPU性能
    • 磁盘io性能
    • 调度程序性能
    • 内存分配及传输速度
    • POSIX线程性能
    • 数据库性能(基于OLTP的基本测试)
      4.sysbench语法   sysbench [options] [testname] [command]
    • command sysbench要执行的命令,包括prepare,run,cleanup。
    • testname 指定要进行的测试
    • options

      • --mysql-host MySQL云服务器主机名
      • --mysql-port MySQL云服务器端口,默认为3306
      • --mysql-user MySQL用户名
      • --mysql-password MySQL密码
      • --oltp-test-mode 执行模式,默认为complex
      • --oltp-tables-size 测试表的大小
      • --oltp-tables-count 测试表的数量
      • --threads 客户端的并发连接
      • --time 测试执行的时间
        5.测试实例
    • CPU测试
      • --cpu-max-prime=N upper limit for primes generator [10000]
        sysbench    --cpu-max-prime=20000 --report-interval=10
    • fileio测试
      *4个线程,随机读写,每10s输出一次结果
      [root@slave1 sysbench-master]# sysbench fileio  help
      sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
      fileio options:
      --file-num=N                  number of files to create [128]   #需要创建的文件数,默认为128
      --file-block-size=N          block size to use in all IO operations [16384]
      --file-total-size=SIZE        total size of files to create [2G]   创建的文件总大小
      --file-test-mode=STRING      test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}  #读写方式
      --file-io-mode=STRING        file operations mode {sync,async,mmap} [sync] # 文件操作模式{sync(同步),async(异步),fastmmap(快速map映射),slowmmap(慢map映射)}。默认是sync
      --file-async-backlog=N        number of asynchronous operatons to queue per thread [128] 
      --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []   #使用额外的标志来打开文件{sync,dsync,direct} 。默认为空
      --file-fsync-freq=N          do fsync() after this number of requests (0 - don't use fsync()) [100]
      --file-fsync-all[=on|off]    do fsync() after each write operation [off]
      --file-fsync-end[=on|off]    do fsync() at the end of test [on]
      --file-fsync-mode=STRING      which method to use for synchronization {fsync, fdatasync} [fsync]  #使用哪种方法进行同步{fsync, fdatasync}。默认是fsync
      --file-merged-requests=N      merge at most this number of IO requests if possible (0 - don't merge) [0] #如果可以,合并最多的IO请求数(0–表示不合并)。默认是0
      --file-rw-ratio=N            reads/writes ratio for combined test [1.5] #测试时的读写比例。默认是1.5
    [root@slave1 sysbench-master]# sysbench --threads=4 --file-test-mode=rndwr --report-interval=10 fileio prepare  #准备阶段
    [root@slave1 sysbench-master]# sysbench --threads=4 --time=60 --report-interval=10 --file-test-mode=rndrw fileio run
    sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
    
    Running the test with following options:
    Number of threads: 4
    Report intermediate results every 10 second(s)
    Initializing random number generator from current time
    
    Extra file open flags: (none)
    128 files, 16MiB each
    2GiB total file size
    Block size 16KiB
    Number of IO requests: 0
    Read/Write ratio for combined random IO test: 1.50
    Periodic FSYNC enabled, calling fsync() each 100 requests.
    Calling fsync() at the end of test, Enabled.
    Using synchronous I/O mode
    Doing random r/w test
    Initializing worker threads...
    
    Threads started!
    
    [ 10s ] reads: 3.65 MiB/s writes: 2.44 MiB/s fsyncs: 494.49/s latency (ms,95%): 30.265
    [ 20s ] reads: 3.00 MiB/s writes: 2.00 MiB/s fsyncs: 412.60/s latency (ms,95%): 34.954
    [ 30s ] reads: 3.28 MiB/s writes: 2.19 MiB/s fsyncs: 444.19/s latency (ms,95%): 35.589
    [ 40s ] reads: 3.19 MiB/s writes: 2.13 MiB/s fsyncs: 434.41/s latency (ms,95%): 38.247
    [ 50s ] reads: 3.00 MiB/s writes: 2.00 MiB/s fsyncs: 410.90/s latency (ms,95%): 37.565
    [ 60s ] reads: 3.09 MiB/s writes: 2.06 MiB/s fsyncs: 419.19/s latency (ms,95%): 35.589
    
    Throughput:
            read:  IOPS=204.22 3.19 MiB/s (3.35 MB/s)
            write: IOPS=136.15 2.13 MiB/s (2.23 MB/s)
            fsync: IOPS=442.70
    
    Latency (ms):
            min:                                  0.00
            avg:                                  5.14
            max:                                310.70
            95th percentile:                    35.59
            sum:                            239996.51
    
    [root@slave1 sysbench-master]# sysbench --threads=4 --time=60 --report-interval=10 --file-test-mode=rndrw fileio cleanup  #清理文件
    sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
    
    Removing test files...
    • 内存测试
      • 测试4个线程,60秒,每10秒输出一次报告信息,总内存大小100G,随机访问模式
    [root@slave1 sysbench-master]# sysbench --threads=4 --time=60 --report-interval=10 --memory-total-size=100G --memory-access-mode=rnd memory run
    sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
    
    Running the test with following options:
    Number of threads: 4
    Report intermediate results every 10 second(s)
    Initializing random number generator from current time
    
    Running memory speed test with the following options:
      block size: 1KiB
      total size: 102400MiB
      operation: write
      scope: global
    
    Initializing worker threads...
    
    Threads started!
    
    [ 10s ] 671.96 MiB/sec
    [ 20s ] 672.65 MiB/sec
    [ 30s ] 667.18 MiB/sec
    [ 40s ] 675.73 MiB/sec
    [ 50s ] 676.82 MiB/sec
    Total operations: 41341475 (689021.00 per second)
    
    40372.53 MiB transferred (672.87 MiB/sec)
    
    Throughput:
        events/s (eps):                      689021.0010
        time elapsed:                        60.0003s
        total number of events:              41341475
    
    Latency (ms):
            min:                                    0.00
            avg:                                    0.01
            max:                                  20.25
            95th percentile:                        0.00
            sum:                              218390.97
    
    Threads fairness:
        events (avg/stddev):          10335368.7500/59009.67
        execution time (avg/stddev):  54.5977/0.14
    • MySQL测试
      • 压测实例,4张表,每张表10W条记录,压测120秒
    sysbench --db-driver=mysql --time=120 --threads=4 --report-interval=10 --mysql-host=localhost --mysql-port=2433 --mysql-socket=/data/mysqldata/mysql.sock --mysql-user=7roaddba --mysql-password=y%6bm@3o_q1a --mysql-db=inc --tables=4 --table-size=100000 oltp_read_write prepare  ##准备
    
    sysbench  --db-driver=mysql --time=120 --threads=4 --report-interval=10 --mysql-host=localhost --mysql-port=3306 --mysql-socket=/data/mysqldata/mysql.sock --mysql-user=7roaddba --mysql-password=y%6bm@3o_q1a --mysql-db=inc --tables=4 --table-size=100000 oltp_read_write run  ##测试
    sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
    
    Running the test with following options:
    Number of threads: 4
    Report intermediate results every 10 second(s)
    Initializing random number generator from current time
    
    Initializing worker threads...
    
    Threads started!
    
    [ 10s ] thds: 4 tps: 111.37 qps: 2233.72 (r/w/o: 1564.69/445.88/223.14) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
    [ 20s ] thds: 4 tps: 197.50 qps: 3950.22 (r/w/o: 2764.42/790.80/395.00) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00
    [ 30s ] thds: 4 tps: 211.10 qps: 4222.06 (r/w/o: 2955.87/843.99/422.20) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
    [ 40s ] thds: 4 tps: 213.70 qps: 4272.79 (r/w/o: 2991.39/854.00/427.40) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
    [ 50s ] thds: 4 tps: 211.80 qps: 4239.27 (r/w/o: 2967.15/848.41/423.71) lat (ms,95%): 43.39 err/s: 0.10 reconn/s: 0.00
    [ 60s ] thds: 4 tps: 223.10 qps: 4459.15 (r/w/o: 3121.36/891.59/446.19) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
    [ 70s ] thds: 4 tps: 190.40 qps: 3811.24 (r/w/o: 2667.73/762.71/380.80) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00
    [ 80s ] thds: 4 tps: 183.00 qps: 3660.05 (r/w/o: 2561.97/732.09/366.00) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00
    [ 90s ] thds: 4 tps: 211.60 qps: 4226.63 (r/w/o: 2958.32/845.11/423.20) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00
    [ 100s ] thds: 4 tps: 213.30 qps: 4272.92 (r/w/o: 2991.64/854.58/426.69) lat (ms,95%): 39.65 err/s: 0.10 reconn/s: 0.00
    [ 110s ] thds: 4 tps: 212.51 qps: 4249.12 (r/w/o: 2974.88/849.22/425.01) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
    [ 120s ] thds: 4 tps: 183.00 qps: 3660.19 (r/w/o: 2561.80/732.40/366.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
    SQL statistics:
        queries performed:
            read:                            330820  #总select数量
            write:                          94513   # 总update,insert,delete数量
            other:                          47258 ##commit,unlock tables 数量
            total:                          472591
        transactions:                        23628  (196.88 per sec.)  #通常需要关注的数字(TPS)
        queries:                            472591 (3937.94 per sec.) #
        ignored errors:                      2      (0.02 per sec.) # 忽略的错误数
        reconnects:                          0      (0.00 per sec.)
    
    Throughput:
        events/s (eps):                      196.8840
        time elapsed:                        120.0098s
        total number of events:              23628   #总的事件数
    
    Latency (ms):
            min:                                    6.03
            avg:                                  20.31
            max:                                  847.12
            95th percentile:                      44.98
            sum:                              479909.87
    
    Threads fairness:
        events (avg/stddev):          5907.0000/60.54

       execution time (avg/stddev):  119.9775/0.00

    对于以上关于MySQL基准测试工具sysbench的安装测试教程,大家是不是觉得非常有帮助。如果需要了解更多内容,请继续关注我们的行业资讯,相信你会喜欢上这些内容的。