平平淡淡从从容容

0%

MySQL基准测试

发表于 分类于 2.2k 2 分钟

孙子曰：夫用兵之法，全国为上，破国次之；全军为上，破军次之；全旅为上，破旅次之；全卒为上，破卒次之；全伍为上，破伍次之。是故百战百胜，非善之善者也；不战而屈人之兵，善之善者也。故上兵伐谋，其次伐交，其次伐兵，其下攻城。攻城之法，为不得已。修橹贲温，具器械，三月而后成；距堙，又三月而后已。将不胜其忿而蚁附之，杀士卒三分之一，而城不拔者，此攻之灾也。故善用兵者，屈人之兵而非战也，拔人之城而非攻也，毁人之国而非久也，必以全争于天下，故兵不顿而利可全，此谋攻之法也。 – 《孙子兵法·谋攻篇》

一、基础

      数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可对比的测试。它可理解为针对系统的一种压力测试，用于掌握系统行为或重新系统的某个状态，但它不关心业务逻辑，更加简单、直接、易于测试，数据可以由工具生成，不要求真实；而压力测试一般考虑业务逻辑(如购物车业务)，要求真实的数据。

      通过对数据库进行基准测试，可分析在当前的配置下(包括硬件配置、OS、数据库设置等)数据库的性能表现，找出MySQL的性能阈值，并根据实际系统的要求调整配置和策略。常见的数据库指标包括：

  • TPS ：Transactions Per Second ，即数据库每秒执行的事务数，以 commit 成功次数为准。
  • QPS ：Queries Per Second ，即数据库每秒执行的 SQL 数（含 insert、select、update、delete 等）。
  • RT ：Response Time，即响应时间，包括平均响应时间、最小响应时间、最大响应时间、时间百分比等，其中时间百分比参考意义较大，如前95%的请求的最大响应时间。
  • Concurrency Threads ：，即并发量，是指每秒可处理的查询请求的数量。

      sysbench是常用的数据库基准测试工具，具有可跨平台性，支持多线程和多种数据库服务。

二、使用

  1. 安装MySQL(Mac下)

    • 源码安装
    • DMG安装
    • brew安装
      • brew search mysql，查找可安装的MySQL
      • brew install mysql@5.7
      • mysql_secure_installation设置密码
        • Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
        • mysql.server start
      • 重试mysql_secure_installation

  2. 安装sysbench

    • brew install sysbench

  3. 使用

    • 系统自带基准测试脚本：cd /usr/local/Cellar/sysbench/1.0.20_1/share/sysbench

      • bulk_insert.lua
      • oltp_common.lua
      • oltp_delete.lua
      • oltp_insert.lua
      • oltp_point_select.lua
      • oltp_read_only.lua
      • oltp_read_write.lua
      • oltp_update_index.lua
      • oltp_update_non_index.lua
      • oltp_write_only.lua
      • select_random_points.lua
      • select_random_ranges.lua
      • tests目录

    • sysbench命令说明sysbench --help

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      62
      63
      64
      65
      66
      67
      68
      69
      70
      Usage:
        sysbench [options]... [testname] [command]

      Commands implemented by most tests: prepare run cleanup help

      General options:
        --threads=N                     number of threads to use [1]
        --events=N                      limit for total number of events [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 driver

      mysql 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 test

      See 'sysbench <testname> help' for a list of options for each test.

    • 创建基准测试数据库

      • mysql -uroot -p
      • create database sbtest

    • 准备基准测试数据

      • sysbench oltp_common.lua –time=300 –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=root –mysql-password=your_password –mysql-db=sbtest –table-size=1000000 –tables=10 –threads=32 –events=999999999 prepare
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

      Initializing worker threads...

      Creating table 'sbtest4'...
      Creating table 'sbtest10'...
      Creating table 'sbtest1'...
      Creating table 'sbtest9'...
      Creating table 'sbtest2'...
      Creating table 'sbtest7'...
      Creating table 'sbtest6'...
      Creating table 'sbtest8'...
      Creating table 'sbtest5'...
      Creating table 'sbtest3'...
      Inserting 1000000 records into 'sbtest6'
      Inserting 1000000 records into 'sbtest5'
      Inserting 1000000 records into 'sbtest7'
      Inserting 1000000 records into 'sbtest4'
      Inserting 1000000 records into 'sbtest10'
      Inserting 1000000 records into 'sbtest9'
      Inserting 1000000 records into 'sbtest8'
      Inserting 1000000 records into 'sbtest2'
      Inserting 1000000 records into 'sbtest1'
      Inserting 1000000 records into 'sbtest3'


      Creating a secondary index on 'sbtest9'...
      Creating a secondary index on 'sbtest2'...
      Creating a secondary index on 'sbtest4'...
      Creating a secondary index on 'sbtest5'...
      Creating a secondary index on 'sbtest6'...
      Creating a secondary index on 'sbtest10'...
      Creating a secondary index on 'sbtest1'...
      Creating a secondary index on 'sbtest7'...
      Creating a secondary index on 'sbtest3'...
      Creating a secondary index on 'sbtest8'...

    • 执行基准测试

      • sysbench oltp_read_write.lua –time=300 –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=root –mysql-password=your_password –mysql-db=sbtest –table-size=1000000 –tables=10 –threads=16 –events=999999999 –report-interval=10 run
        • 可逐步增加线程数、数据量等选项设置，以对比不同参数下系统性能
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

      Running the test with following options:
      Number of threads: 16
      Report intermediate results every 10 second(s)
      Initializing random number generator from current time


      Initializing worker threads...

      Threads started!

      [ 10s ] thds: 16 tps: 106.12 qps: 2144.94 (r/w/o: 1504.83/426.27/213.83) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
      [ 20s ] thds: 16 tps: 105.60 qps: 2115.90 (r/w/o: 1480.30/424.40/211.20) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
      [ 30s ] thds: 16 tps: 99.60 qps: 1991.07 (r/w/o: 1392.68/399.29/199.10) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
      ...
      ...
      ...

      SQL statistics:
          queries performed:
              read:                            546266
              write:                           156076
              other:                           78038
              total:                           780380
          transactions:                        39019  (129.99 per sec.)
          queries:                             780380 (2599.80 per sec.)
          ignored errors:                      0      (0.00 per sec.)
          reconnects:                          0      (0.00 per sec.)

      General statistics:
          total time:                          300.1671s
          total number of events:              39019

      Latency (ms):
               min:                                   13.93
               avg:                                  123.02
               max:                                  997.45
               95th percentile:                      262.64
               sum:                              4800286.12

      Threads fairness:
          events (avg/stddev):           2438.6875/11.76
          execution time (avg/stddev):   300.0179/0.03

    • 清理数据(调整参数时执行下数据清理)

      • sysbench oltp_read_write.lua –time=300 –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=root –mysql-password=Playcrab –mysql-db=sbtest –table-size=1000000 –tables=10 –threads=16 –events=999999999 –report-interval=10 cleanup
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

      Dropping table 'sbtest1'...
      Dropping table 'sbtest2'...
      Dropping table 'sbtest3'...
      Dropping table 'sbtest4'...
      Dropping table 'sbtest5'...
      Dropping table 'sbtest6'...
      Dropping table 'sbtest7'...
      Dropping table 'sbtest8'...
      Dropping table 'sbtest9'...
      Dropping table 'sbtest10'...

三、参考

  1. 参考一
  2. 参考二
  3. 参考三
  4. 参考四
  5. 参考五