MySQL压力测试/sysbench使用

比眉伴天荒 2022-02-18 14:47 230阅读 0赞

今天进行了MySQL pxc集群的压测，一般在做项目的时候DBA需要根据业务自定义数据库使用类型，所以在决定阶段，DBA就要根据对业务的预估进行数据库集群的筛选，同时也要对数据库进行压测。

压测的目的是为了测试该机器在哪些条件下数据库压力较大，数据库在该机器的瓶颈大概在哪里。而不是说，我要对比那个数据库性能更好，所谓的性能都是在已经定义好物理机的情况下去找更合适的。所以新的物理机理论上在选择和安装新数据库时都要进行压测。MySQL自带有压测工具，我觉得不好用。所以用的是目前主流的MySQL压测工具 sysbench。

这里还是多说一句，对数据库的选择是根据业务来的，不是说我某个集群性能更好我就要选择他，比如支付系统，不管半同步性能比pxc高多少，都没有PXC的数据强一致性更能保障数据一致，所以这时候不能能因为半同步性能更好而放弃PXC。

当更具业务定义好数据库类型时，就可以安装数据库并进行压测了，主要步骤如下：

1、安装数据库并新建压测用户并授权。

2、安装sysbench，本次我用的时centos7，直接yum安装就行了，官网上有安装命令，比网络上的什么二进制编译、yum编译更加靠谱，所以我也不写啥命令了，去官网看吧

3、写好sysbench执行命令进行压测

sysbench压测工具使用方式：

sysbench不同版本命令不一样，不要拿着网上的别人的命令进行敲打，严谨的方式是 sysbench --help查看当前版本的命令格式，如我使用的版本：

[root@d8555732f478 sysbench]# sysbench testname --help
    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
      pgsql - PostgreSQL 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]
    
    pgsql options:
      --pgsql-host=STRING     PostgreSQL server host [localhost]
      --pgsql-port=N          PostgreSQL server port [5432]
      --pgsql-user=STRING     PostgreSQL user [sbtest]
      --pgsql-password=STRING PostgreSQL password []
      --pgsql-db=STRING       PostgreSQL database name [sbtest]
    
    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.

在使用sysbench命令时，需要执行压测方式，该方式是一个脚本文件，如果是yum安装的，大多数在/usr/share/sysbench文件夹下，例：

[root@d8555732f478 sysbench]# ls
    bulk_insert.lua  oltp_delete.lua  oltp_point_select.lua  oltp_read_write.lua    oltp_update_non_index.lua  select_random_points.lua  tests
    oltp_common.lua  oltp_insert.lua  oltp_read_only.lua     oltp_update_index.lua  oltp_write_only.lua        select_random_ranges.lua
    [root@d8555732f478 sysbench]# pwd 
    /usr/share/sysbench

看名字也只要这些脚本干嘛的，insert就是压测插入，read压测只读，read\_write压测读写，大概是7分读3分写的比例，我们常规使用读写脚本就行了，如果是读写分离的库进行只读或者只写的，可以单独压测读和写

有了启动参数、压测方式脚本、启动命令，那么我们就要开始进行压测了，本次压测命令：

sysbench --mysql-host=45.59.111.111 --mysql-port=33061 --mysql-user=testuser --mysql-password=123456 \
    --mysql-db=testdb --report-interval=10 --time=120 --tables=4 --table-size=10000000 --threads=1 \
    /usr/share/sysbench/oltp_read_write.lua prepare

所以我们常规用到的参数就是：新建的数据库、用户及其密码、报告时间（10秒一次）、执行压测时间（120秒）、压测表数量（4张）、表数据量（1千万）、线程（1）、执行压测方式脚本（读写），最后加上准备命令（prepare）

这里我要说一下 sysbench \[options\]... \[testname\] \[command\]  这里的command，

command命令有三个：prepare、run、cleanup

分别对应的是准备表及其数据、执行压测、清理数据，由于我们在数据库只新建了库没有新建表，所以在参数指定表属性的时候是sysbench自己去新建并且完成指定属性的，所以在压测前我们都要执行prepare命令

等待准备完成后，在执行：

sysbench --mysql-host=45.59.113.211 --mysql-port=33061 --mysql-user=testuser --mysql-password=123456 \
    --mysql-db=testdb --report-interval=10 --time=120 --tables=4 --table-size=10000000 --threads=1 \
    /usr/share/sysbench/oltp_read_write.lua run

run命令进行压测，这时候就会每隔10秒输出相关压测数据，

最后执行cleanup清除结果：

--------------------

压测方式，默认新建的表大概是4个字段，我们定义了4张表、1000000万数据级，压测这几个属性不变，之后线程从1开始，进行：1、5、10、20、50、100、200、500、1000这几组进行测试，尽量找到性能衰减点。在线程找到衰减点之后，可以试着慢慢调整表数量、表大小等，更换另一个参数需要prepare一次。掌控数据库在该机上的性能瓶颈，对于后期DBA数据库维护有极大的帮助。

比如我这次开了一个线程报告出的结果：

[root@d8555732f478 sysbench]# sysbench --mysql-host=45.59.111.111 --mysql-port=33061 --mysql-user=testuser --mysql-password=123456 \
    > --mysql-db=testdb --report-interval=10 --time=120 --tables=4 --table-size=10000000 --threads=1 \
    > /usr/share/sysbench/oltp_read_write.lua run
    sysbench 1.0.17 (using system LuaJIT 2.0.4)
    
    Running the test with following options:
    Number of threads: 1
    Report intermediate results every 10 second(s)
    Initializing random number generator from current time
    
    
    Initializing worker threads...
    
    Threads started!
    
    [ 10s ] thds: 1 tps: 12.06 qps: 242.03 (r/w/o: 169.56/23.33/49.14) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
    [ 20s ] thds: 1 tps: 18.15 qps: 363.61 (r/w/o: 254.74/37.80/71.08) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
    [ 30s ] thds: 1 tps: 18.09 qps: 361.11 (r/w/o: 252.57/37.18/71.36) lat (ms,95%): 130.13 err/s: 0.00 reconn/s: 0.00
    [ 40s ] thds: 1 tps: 23.92 qps: 478.01 (r/w/o: 334.49/47.54/95.98) lat (ms,95%): 90.78 err/s: 0.00 reconn/s: 0.00
    [ 50s ] thds: 1 tps: 21.30 qps: 426.56 (r/w/o: 298.77/42.30/85.49) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
    [ 60s ] thds: 1 tps: 25.00 qps: 499.74 (r/w/o: 349.73/51.10/98.91) lat (ms,95%): 81.48 err/s: 0.00 reconn/s: 0.00
    [ 70s ] thds: 1 tps: 23.70 qps: 474.82 (r/w/o: 332.61/45.20/97.00) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
    [ 80s ] thds: 1 tps: 24.20 qps: 484.30 (r/w/o: 338.80/48.90/96.60) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
    [ 90s ] thds: 1 tps: 24.30 qps: 484.18 (r/w/o: 338.79/47.70/97.70) lat (ms,95%): 90.78 err/s: 0.00 reconn/s: 0.00
    [ 100s ] thds: 1 tps: 25.20 qps: 505.81 (r/w/o: 354.21/49.40/102.20) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
    [ 110s ] thds: 1 tps: 29.00 qps: 578.40 (r/w/o: 404.70/58.30/115.40) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
    [ 120s ] thds: 1 tps: 29.70 qps: 594.91 (r/w/o: 416.71/58.10/120.10) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
    SQL statistics:
        queries performed:
            read:                            38458
            write:                           5469
            other:                           11013
            total:                           54940
        transactions:                        2747   (22.88 per sec.)   --TPS性能
        queries:                             54940  (457.68 per sec.)  --QPS性能
        ignored errors:                      0      (0.00 per sec.)
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:
        total time:                          120.0385s
        total number of events:              2747
    
    Latency (ms):
             min:                                    7.22
             avg:                                   43.68
             max:                                  857.01
             95th percentile:                       94.10
             sum:                               119989.56
    
    Threads fairness:
        events (avg/stddev):           2747.0000/0.00
        execution time (avg/stddev):   119.9896/0.00