mysql-cluster(7)——基准测试

浅浅的花香味﹌ 2022-05-23 08:10 248阅读 0赞

mysql-cluster系列：

1.  [mysql-cluster(1)——简介][mysql-cluster_1]
2.  [mysql-cluster(2)——部署][mysql-cluster_2]
3.  [mysql-cluster(3)——集群管理和维护][mysql-cluster_3]
4.  [mysql-cluster(4)——创建内存表和磁盘表][mysql-cluster_4]
5.  [mysql-cluster(5)——jdbc][mysql-cluster_5_jdbc]
6.  [mysql-cluster(6)——配置详解][mysql-cluster_6]
7.  [mysql-cluster(7)——基准测试][mysql-cluster_7]
8.  [mysql-cluster(8)——最佳实践][mysql-cluster_8]
9.  [mysql-cluster(9)——NDB和InnoDB区别][mysql-cluster_9_NDB_InnoDB]
10. [mysql-cluster(10)——注意事项][mysql-cluster_10]

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

本文目录：

*  1 mysql-cluster部署和配置
 *  2 测试
    
     *  2.1 只读
     *  2.2 混合读写
     *  2.3 只更新
 *  附录
    
     *  测试参考
     *  集群

# 1 mysql-cluster部署和配置 #

见附录。

# 2 测试 #

准备库：

mysql -u root
    create database sbtest;

## 2.1 只读 ##

sysbench --test=/opt/mysql/sysbench/share/tests/db/oltp.lua \
    --mysql-host=10.5.11.87 --mysql-port=3306 --mysql-user=root \
    --mysql-db=sbtest1 --oltp-tables-count=10 --oltp-table-size=100000 \
    --mysql-table-engine=ndbcluster
    --report-interval=10 --oltp-dist-type=uniform --rand-init=on --max-requests=0 \
    --oltp-test-mode=nontrx --oltp-nontrx-mode=select \
    --oltp-read-only=on --oltp-skip-trx=on \
    --max-time=120 --num-threads=12 \
    [prepare|run|cleanup]

注意最后一行，一项测试开始前需要用prepare来准备好表和数据，run执行真正的压测，cleanup用来清除数据和表。实际prepare的表结构：

mysql> desc sbtest1.sbtest1;

+-------+------------------+------+-----+---------+----------------+
    | Field | Type             | Null | Key | Default | Extra          |
    +-------+------------------+------+-----+---------+----------------+
    | id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | k     | int(10) unsigned | NO   | MUL | 0       |                |
    | c     | char(120)        | NO   |     |         |                |
    | pad   | char(60)         | NO   |     |         |                |
    +-------+------------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)

上面的测试命令代表的是：对mysql进行oltp基准测试，表数量10，每表行数约50w（几乎delete多少就会insert的多少），并且是非事务的只读测试，持续120s，并发线程数12。

需要说明的选项：

*  –mysql-db=sbtest1：测试使用的目标数据库，这个库名要事先创建
 *  –oltp-tables-count=10：产生表的数量
 *  –oltp-table-size=500000：每个表产生的记录行数
 *  –oltp-dist-type=uniform：指定随机取样类型，可选值有 uniform(均匀分布), Gaussian(高斯分布), special(空间分布)。默认是special
 *  –oltp-read-only=off：表示不止产生只读SQL，也就是使用oltp.lua时会采用读写混合模式。默认 off，如果设置为on，则不会产生update,delete,insert的sql。
 *  –oltp-test-mode=nontrx ：执行模式，这里是非事务式的。可选值有simple,complex,nontrx。默认是complex  
    
    
     *  simple：简单查询，SELECT c FROM sbtest WHERE id=N
     *  complex (advanced transactional)：事务模式在开始和结束事务之前加上begin和commit， 一个事务里可以有多个语句，如点查询、范围查询、排序查询、更新、删除、插入等，并且为了不破坏测试表的数据，该模式下一条记录删除后会在同一个事务里添加一条相同的记录。
     *  nontrx (non-transactional)：与simple相似，但是可以进行update/insert等操作，所以如果做连续的对比压测，你可能需要重新cleanup,prepare。
 *  –oltp-skip-trx=\[on|off\]：省略begin/commit语句。默认是off
 *  –rand-init=on：是否随机初始化数据，如果不随机化那么初始好的数据每行内容除了主键不同外其他完全相同
 *  –num-threads=12： 并发线程数，可以理解为模拟的客户端并发连接数
 *  –report-interval=10：表示每10s输出一次测试进度报告
 *  –max-requests=0：压力测试产生请求的总数，如果以下面的max-time来记，这个值设为0
 *  –max-time=120：压力测试的持续时间，这里是2分钟。  
    注意，针对不同的选项取值就会有不同的子选项。比如oltp-dist-type=special，就有比如oltp-dist-pct=1、oltp-dist-res=50两个子选项，代表有50%的查询落在1%的行（即热点数据）上，另外50%均匀的(sample uniformly)落在另外99%的记录行上。

以上代表的是一个只读的例子，可以把num-threads依次递增（16,36,72,128,256,512），或者调整my.cnf参数，比较效果。另外需要注意的是，大部分mysql中间件对事务的处理，默认都是把sql发到主库执行，所以只读测试需要加上oltp-skip-trx=on来跳过测试中的显式事务。

ps1: 只读测试也可以使用share/tests/db/select.lua进行，但只是简单的point select。  
ps2: 我在用sysbench压的时候，在mysql后端会话里有时看到大量的query cache lock，如果使用的是uniform取样，最好把查询缓存关掉。当然如果是做两组性能对比压测，因为都受这个因素影响，关心也不大。

结果：

[  10s] threads: 12, tps: 0.00, reads: 34442.35, writes: 0.00, response time: 7.07ms (95%), errors: 0.00, reconnects:  0.00
    [  20s] threads: 12, tps: 0.00, reads: 34396.41, writes: 0.00, response time: 7.19ms (95%), errors: 0.00, reconnects:  0.00
    [  30s] threads: 12, tps: 0.00, reads: 34396.00, writes: 0.00, response time: 7.24ms (95%), errors: 0.00, reconnects:  0.00
    [  40s] threads: 12, tps: 0.00, reads: 34393.29, writes: 0.00, response time: 7.28ms (95%), errors: 0.00, reconnects:  0.00
    [  50s] threads: 12, tps: 0.00, reads: 34377.81, writes: 0.00, response time: 7.32ms (95%), errors: 0.00, reconnects:  0.00
    [  60s] threads: 12, tps: 0.00, reads: 34374.00, writes: 0.00, response time: 7.32ms (95%), errors: 0.00, reconnects:  0.00
    [  70s] threads: 12, tps: 0.00, reads: 34405.10, writes: 0.00, response time: 7.33ms (95%), errors: 0.00, reconnects:  0.00
    [  80s] threads: 12, tps: 0.00, reads: 34429.50, writes: 0.00, response time: 7.27ms (95%), errors: 0.00, reconnects:  0.00
    [  90s] threads: 12, tps: 0.00, reads: 34327.79, writes: 0.00, response time: 7.17ms (95%), errors: 0.00, reconnects:  0.00
    [ 100s] threads: 12, tps: 0.00, reads: 34434.41, writes: 0.00, response time: 7.20ms (95%), errors: 0.00, reconnects:  0.00
    [ 110s] threads: 12, tps: 0.00, reads: 34385.81, writes: 0.00, response time: 7.35ms (95%), errors: 0.00, reconnects:  0.00
    [ 120s] threads: 12, tps: 0.00, reads: 34327.00, writes: 0.00, response time: 7.29ms (95%), errors: 0.00, reconnects:  0.00
    OLTP test statistics:
        queries performed:
            read:                            4126948                                            //总select数量
            write:                           0                                                        //总update、insert、delete语句数量
            other:                           0                                                        //commit、unlock tables以及其他mutex的数量
            total:                           4126948
        transactions:                        0      (0.00 per sec.)                        //通常需要关注的数字(TPS)
        read/write requests:                 4126948 (34389.65 per sec.)
        other operations:                    0      (0.00 per sec.)
        ignored errors:                      0      (0.00 per sec.)                        //忽略的错误数
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:
        total time:                          120.0055s                                          //即max-time指定的压测时间
        total number of events:              294782                                     //总的事件数，一般与transactions相同
        total time taken by event execution: 1439.5910s                        //所有事务耗时相加(不考虑并行因素)
        response time:
             min:                                  2.76ms                                            //最短耗时
             avg:                                  4.88ms                                            //平均耗时
             max:                                 31.23ms                                          //最长耗时
             approx.  95 percentile:               7.25ms                                  //95%的语句的平均耗时
    
    Threads fairness:
        events (avg/stddev):           24565.1667/628.73
        execution time (avg/stddev):   119.9659/0.00

## 2.2 混合读写 ##

sysbench --test=/opt/mysql/sysbench/share/tests/db/oltp.lua \
    --mysql-host=10.5.11.87 --mysql-port=3306 --mysql-user=root \
    --mysql-db=sbtest1 --oltp-tables-count=10 --oltp-table-size=100000 \
    --mysql-table-engine=ndbcluster
    --report-interval=10 --oltp-dist-type=uniform --rand-init=on --max-requests=0 \
    --oltp-read-only=off--oltp-skip-trx=on \
    --max-time=120 --num-threads=12 \
    --mysql-ignore-errors=1062 \
    [prepare|run|cleanup]

结果：

[  10s] threads: 12, tps: 1442.09, reads: 20199.76, writes: 5768.76, response time: 14.52ms (95%), errors: 0.00, reconnects:  0.00
    [  20s] threads: 12, tps: 1532.00, reads: 21452.10, writes: 6130.00, response time: 12.20ms (95%), errors: 0.00, reconnects:  0.00
    [  30s] threads: 12, tps: 1471.00, reads: 20589.21, writes: 5882.80, response time: 13.07ms (95%), errors: 0.00, reconnects:  0.00
    [  40s] threads: 12, tps: 1452.00, reads: 20333.07, writes: 5808.39, response time: 13.37ms (95%), errors: 0.00, reconnects:  0.00
    [  50s] threads: 12, tps: 1468.30, reads: 20550.73, writes: 5871.71, response time: 12.96ms (95%), errors: 0.00, reconnects:  0.00
    [  60s] threads: 12, tps: 1466.80, reads: 20538.50, writes: 5867.50, response time: 12.81ms (95%), errors: 0.00, reconnects:  0.00
    [  70s] threads: 12, tps: 1465.30, reads: 20516.10, writes: 5862.50, response time: 12.92ms (95%), errors: 0.00, reconnects:  0.00
    [  80s] threads: 12, tps: 1467.80, reads: 20547.00, writes: 5869.50, response time: 12.71ms (95%), errors: 0.00, reconnects:  0.00
    [  90s] threads: 12, tps: 1472.90, reads: 20622.20, writes: 5893.60, response time: 12.91ms (95%), errors: 0.00, reconnects:  0.00
    [ 100s] threads: 12, tps: 1458.80, reads: 20422.30, writes: 5833.20, response time: 12.88ms (95%), errors: 0.00, reconnects:  0.00
    [ 110s] threads: 12, tps: 1407.90, reads: 19710.30, writes: 5632.70, response time: 13.74ms (95%), errors: 0.00, reconnects:  0.00
    [ 120s] threads: 12, tps: 1464.60, reads: 20504.70, writes: 5858.30, response time: 12.73ms (95%), errors: 0.00, reconnects:  0.00
    OLTP test statistics:
        queries performed:
            read:                            2459898
            write:                           702828
            other:                           351414
            total:                           3514140
        transactions:                        175707 (1464.15 per sec.)
        read/write requests:                 3162726 (26354.65 per sec.)
        other operations:                    351414 (2928.29 per sec.)
        ignored errors:                      0      (0.00 per sec.)
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:
        total time:                          120.0064s
        total number of events:              175707
        total time taken by event execution: 1439.7651s
        response time:
             min:                                  3.66ms
             avg:                                  8.19ms
             max:                                167.03ms
             approx.  95 percentile:              13.01ms
    
    Threads fairness:
        events (avg/stddev):           14642.2500/47.12
        execution time (avg/stddev):   119.9804/0.00

## 2.3 只更新 ##

如果基准测试的时候，你只想比较两个项目的update（或insert）效率，那可以不使用oltp脚本，而直接改用update\_index.lua：

sysbench --test=/opt/mysql/sysbench/share/tests/db/update_index.lua \
    --mysql-host=10.5.11.87 --mysql-port=3306 --mysql-user=root \
    --mysql-db=sbtest1 --oltp-tables-count=10 --oltp-table-size=100000 \
    --mysql-table-engine=ndbcluster
    --report-interval=10 --rand-init=on --max-requests=0 \
    --oltp-read-only=off --max-time=120 --num-threads=128 \
    [ prepare | run | cleanup ]

此时像oltp-read-only=off许多参数都失效了。需要说明的是这里 (非)索引更新，不是where条件根据索引去查找更新，而是更新索引列上的值。

结果：

[  10s] threads: 128, tps: 0.00, reads: 0.00, writes: 12593.15, response time: 32.18ms (95%), errors: 0.00, reconnects:  0.00
    [  20s] threads: 128, tps: 0.00, reads: 0.00, writes: 8833.69, response time: 47.22ms (95%), errors: 0.00, reconnects:  0.00
    [  30s] threads: 128, tps: 0.00, reads: 0.00, writes: 7456.37, response time: 54.60ms (95%), errors: 0.00, reconnects:  0.00
    [  40s] threads: 128, tps: 0.00, reads: 0.00, writes: 6939.23, response time: 59.56ms (95%), errors: 0.00, reconnects:  0.00
    [  50s] threads: 128, tps: 0.00, reads: 0.00, writes: 6737.47, response time: 61.28ms (95%), errors: 0.00, reconnects:  0.00
    [  60s] threads: 128, tps: 0.00, reads: 0.00, writes: 7139.32, response time: 59.49ms (95%), errors: 0.00, reconnects:  0.00
    [  70s] threads: 128, tps: 0.00, reads: 0.00, writes: 7373.28, response time: 59.42ms (95%), errors: 0.00, reconnects:  0.00
    [  80s] threads: 128, tps: 0.00, reads: 0.00, writes: 7981.12, response time: 56.49ms (95%), errors: 0.00, reconnects:  0.00
    [  90s] threads: 128, tps: 0.00, reads: 0.00, writes: 8565.80, response time: 53.91ms (95%), errors: 0.00, reconnects:  0.00
    [ 100s] threads: 128, tps: 0.00, reads: 0.00, writes: 9895.16, response time: 47.52ms (95%), errors: 0.00, reconnects:  0.00
    [ 110s] threads: 128, tps: 0.00, reads: 0.00, writes: 10928.57, response time: 43.34ms (95%), errors: 0.00, reconnects:  0.00
    OLTP test statistics:
        queries performed:
            read:                            0
            write:                           1062559
            other:                           0
            total:                           1062559
        transactions:                        0      (0.00 per sec.)
        read/write requests:                 1062559 (8854.30 per sec.)
        other operations:                    0      (0.00 per sec.)
        ignored errors:                      0      (0.00 per sec.)
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:
        total time:                          120.0049s
        total number of events:              1062559
        total time taken by event execution: 15358.2838s
        response time:
             min:                                  0.18ms
             avg:                                 14.45ms
             max:                                426.33ms
             approx.  95 percentile:              50.52ms
    
    Threads fairness:
        events (avg/stddev):           8301.2422/107.61
        execution time (avg/stddev):   119.9866/0.00

# 附录 #

## 测试参考 ##

（1）使用sysbench对mysql压力测试（ [https://segmentfault.com/a/1190000004866961][https_segmentfault.com_a_1190000004866961]）  
（2）MySQL Cluster 7.4 GA: 200 Million QPS, Active-Active Geographic Replication and more（[https://dev.mysql.com/tech-resources/articles/mysql-cluster-7.4.html][https_dev.mysql.com_tech-resources_articles_mysql-cluster-7.4.html]）  
（3）MySQL Cluster初步测试结果汇总图示报告（[http://blog.csdn.net/mchdba/article/details/10524599][http_blog.csdn.net_mchdba_article_details_10524599]）

## 集群 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">ip</th> 
   <th align="left">用途</th> 
   <th align="left">机器配置</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">10.5.11.85</td> 
   <td align="left">MGM节点</td> 
   <td align="left">8核 2.30GHz 8G</td> 
  </tr> 
  <tr> 
   <td align="left">10.5.11.86</td> 
   <td align="left">NDB节点和SQL节点</td> 
   <td align="left">8核 2.30GHz 8G</td> 
  </tr> 
  <tr> 
   <td align="left">10.5.11.87</td> 
   <td align="left">NDB节点和SQL节点</td> 
   <td align="left">8核 2.30GHz 8G</td> 
  </tr> 
 </tbody> 
</table>

MGM节点配置（/opt/mysql/data/config.ini）：

[NDBD DEFAULT]
    NoOfReplicas=2
    DataMemory=2G
    IndexMemory=1G
    NoOfFragmentLogFiles = 160
    
    #mgm
    [NDB_MGMD]
    nodeid=1
    hostname=10.5.11.85
    datadir=/opt/mysql/data
    
    #ndb
    [NDBD]
    nodeid=2
    hostname=10.5.11.86
    datadir=/opt/mysql/data
    [NDBD]
    nodeid=4
    hostname=10.5.11.87
    datadir=/opt/mysql/data
    
    #api
    [MySQLD]
    nodeid=3
    hostname=10.5.11.87
    [MySQLD]
    nodeid=5
    hostname=10.5.11.86
    [MySQLD]

NDB节点和SQL节点配置（/etc/my.cnf）：

[MYSQLD]
    user=root
    character_set_server=utf8
    ndbcluster
    ndb-connectstring=10.5.11.85
    default-storage-engine=ndbcluster
    datadir=/opt/mysql/data
    basedir=/usr/local/mysql
    [MYSQL_CLUSTER]
    ndb-connectstring=10.5.11.85
    ndb-cluster-connection-pool=1000

[mysql-cluster_1]: https://blog.csdn.net/weitry/article/details/80579706
[mysql-cluster_2]: https://blog.csdn.net/weitry/article/details/80579769
[mysql-cluster_3]: https://blog.csdn.net/weitry/article/details/80579795
[mysql-cluster_4]: https://blog.csdn.net/weitry/article/details/80579817
[mysql-cluster_5_jdbc]: https://blog.csdn.net/weitry/article/details/80579824
[mysql-cluster_6]: https://blog.csdn.net/weitry/article/details/80579838
[mysql-cluster_7]: https://blog.csdn.net/weitry/article/details/80579897
[mysql-cluster_8]: https://blog.csdn.net/weitry/article/details/80579925
[mysql-cluster_9_NDB_InnoDB]: https://blog.csdn.net/weitry/article/details/80579975
[mysql-cluster_10]: https://blog.csdn.net/weitry/article/details/80579988
[https_segmentfault.com_a_1190000004866961]: https://segmentfault.com/a/1190000004866961
[https_dev.mysql.com_tech-resources_articles_mysql-cluster-7.4.html]: https://dev.mysql.com/tech-resources/articles/mysql-cluster-7.4.html
[http_blog.csdn.net_mchdba_article_details_10524599]: http://blog.csdn.net/mchdba/article/details/10524599