mysql叢集壓力測試在京東雲盤：sysbench版本

e71hao發表於2018-01-12
MySql
一、環境：京東雲Centos6.8 ，cpu16核,記憶體32G，SAS 3G,轉速未知 
     mysql 版本是：5.7.20 ，預設rpm安裝,mysql非同步複製，一個slave測試。
     壓測工具是：sysbench1.0.9
mysql壓力測試在京東雲ssd雲盤fio測試4K隨機讀寫iops是
提前說下結論是：可以看到256個執行緒的tps是742。半同步複製是1287，非同步複製是1685，單例項是2419.
二、測試1 首先測試一下iops

三、測試2 預設安裝，所有引數不改變，做一個單例項的基準測試：
資料為500萬，10個表，總數量為5000萬，表的容量為12G
  1. 測試1 單例項，引數為
  2. log-bin=binlog
    sync_binlog = 1
    binlog_format = row
    innodb_buffer_pool_size = 22938M
    innodb_buffer_pool_instances = 8
    skip-name-resolve
    transaction_isolation=READ-COMMITTED
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 128M
    innodb_log_files_in_group=5
    innodb_temp_data_file_path=ibtmp1:512M:autoextend
    innodb_flush_log_at_trx_commit = 1
    innodb_doublewrite=on
    max_prepared_stmt_count=1048576
    max_connections = 600
    max_connect_errors = 1000000
    max_allowed_packet = 32M
    open_files_limit    = 65535
    skip_name_resolve = 1
  3. [root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 runsysbench 1.0.9 (using system LuaJIT 2.0.4)

  5. Running the test with following options:
  6. Number of threads: 256
  7. Report intermediate results every 30 second(s)
  8. Initializing random number generator from current time


  11. Initializing worker threads...

  13. Threads

  15. [ 30s ] thds: 256 tps: 2401.44 qps: 48176.55 (r/w/o: 33731.45/9633.93/4811.17) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
  16. [ 60s ] thds: 256 tps: 2441.54 qps: 48779.20 (r/w/o: 34141.87/9754.42/4882.91) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
  17. SQL statistics:
  18.     queries performed:
  19.         read: 2038078
  20.         write: 582308
  21.         other: 291154
  22.         total: 2911540
  23.     transactions: 145577 (2419.01 per sec.)
  24.     queries: 2911540 (48380.22 per sec.)
  25.     ignored errors: 0 (0.00 per sec.)
  26.     reconnects: 0 (0.00 per sec.)

  28. General statistics:
  29.     total time: 60.1788s
  30.     total number of events: 145577

  32. Latency (ms):
  33.          min: 9.05
  34.          avg: 105.58
  35.          max: 532.87
  36.          95th percentile: 186.54
  37.          sum: 15370510.44

  39. Threads fairness:
  40.     events (avg/stddev): 568.6602/20.63
  41.     execution time (avg/stddev): 60.0411/0.03




  1. 測試2：普通非同步複製 ，我們看到tps為1399。執行緒數為100.
  2. master引數如下：
  3. server_id=1
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    log-bin=binlog
    sync_binlog = 1
    binlog_format = row
    innodb_buffer_pool_size = 22938M
    innodb_buffer_pool_instances = 8
    skip-name-resolve
    transaction_isolation=READ-COMMITTED
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 128M
    innodb_log_files_in_group=5
    innodb_temp_data_file_path=ibtmp1:512M:autoextend
    innodb_flush_log_at_trx_commit = 1
    innodb_doublewrite=on
    max_prepared_stmt_count=1048576
    max_connections = 600
    max_connect_errors = 1000000
    max_allowed_packet = 32M
    open_files_limit    = 65535
    skip_name_resolve = 1
slave 引數如下：
server_id=2
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
relay_log=relay
relay_log_purge=0
innodb_buffer_pool_size = 22938M
innodb_buffer_pool_instances = 8
skip-name-resolve
transaction_isolation=READ-COMMITTED
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_log_files_in_group=5
innodb_temp_data_file_path=ibtmp1:512M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite=on
max_prepared_stmt_count=1048576
max_connections = 600
max_connect_errors = 1000000
max_allowed_packet = 32M
open_files_limit    = 65535
skip_name_resolve = 1
slow_query_log=on
slow_query_log_file=slowquery.log
long_query_time=3
log_queries_not_using_indexes = ON
  1. [root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=100 --time=60 --report-interval=30 run
  2. sysbench 1.0.9 (using system LuaJIT 2.0.4)

  4. Running the test with following options:
  5. Number of threads: 100
  6. Report intermediate results every 30 second(s)
  7. Initializing random number generator from current time


  10. Initializing worker threads...

  12. Threads

  14. [ 30s ] thds: 100 tps: 1418.73 qps: 28423.55 (r/w/o: 19902.03/5680.74/2840.79) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00
  15. [ 60s ] thds: 100 tps: 1388.45 qps: 27774.89 (r/w/o: 19441.72/5556.30/2776.87) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
  16. SQL statistics:
  17.     queries performed:
  18.         read: 1180690
  19.         write: 337340
  20.         other: 168670
  21.         total: 1686700
  22.     transactions: 84335 (1399.41 per sec.)
  23.     queries: 1686700 (27988.21 per sec.)
  24.     ignored errors: 0 (0.00 per sec.)
  25.     reconnects: 0 (0.00 per sec.)

  27. General statistics:
  28.     total time: 60.2631s
  29.     total number of events: 84335

  31. Latency (ms):
  32.          min: 8.62
  33.          avg: 71.20
  34.          max: 514.88
  35.          95th percentile: 121.08
  36.          sum: 6004886.80

  38. Threads fairness:
  39.     events (avg/stddev): 843.3500/17.92
  40.     execution time (avg/stddev): 60.0489/0.03

  1. 測試3 普通非同步複製，增加併發數到256，tps 變為827。反覆幾次都是這樣的數值。增加了非同步複製，tps下降很嚴重。
  2. [root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=30 --report-interval=30 run
  3. sysbench 1.0.9 (using system LuaJIT 2.0.4)
  4. [ 30s ] thds: 256 tps: 820.98 qps: 16543.16 (r/w/o: 11587.44/3306.96/1648.75) lat (ms,95%): 746.32 err/s: 0.00 reconn/s: 0.00
  5. SQL statistics:
  6.     queries performed:
  7.         read: 356748
  8.         write: 101928
  9.         other: 50964
  10.         total: 509640
  11.     transactions: 25482 (827.69 per sec.)
  12.     queries: 509640 (16553.86 per sec.)
  13.     ignored errors: 0 (0.00 per sec.)
  14.     reconnects: 0 (0.00 per sec.)

  16. General statistics:
  17.     total time: 30.7837s
  18.     total number of events: 25482

  20. Latency (ms):
  21.          min: 9.42
  22.          avg: 302.09
  23.          max: 6031.80
  24.          95th percentile: 746.32
  25.          sum: 7697901.37

  27. Threads fairness:
  28.     events (avg/stddev): 99.5391/53.50
  29.     execution time (avg/stddev): 30.0699/0.06


  1. 測試4 半同步 master slave引數沒有變化，變化的由原來的非同步複製變成半同步。tps是1287。剛開始我以為我搞錯了，測試了幾遍，發現半同步複製，很穩定，tps在1200多。但是非同步複製，tps才827.後來我又測試了幾遍非同步複製，穩定在900多tps。沒有道理呀！非同步複製，需要等待從庫響應才能提交，tps應該更少的呀。但是，反而更多。這個是為什麼呢？

  3. [root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 run 
    sysbench 1.0.9 (using system LuaJIT 2.0.4)


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




    Initializing worker threads...


    Threads started!


    [ 30s ] thds: 256 tps: 1367.81 qps: 27486.91 (r/w/o: 19250.05/5492.84/2744.02) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
    [ 60s ] thds: 256 tps: 1227.47 qps: 24575.83 (r/w/o: 17200.88/4920.04/2454.90) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
    SQL statistics:
        queries performed:
            read:                            1094058
            write:                           312588
            other:                           156294
            total:                           1562940
        transactions:                        78147  (1287.63 per sec.)
        queries:                             1562940 (25752.63 per sec.)
        ignored errors:                      0      (0.00 per sec.)
        reconnects:                          0      (0.00 per sec.)


    General statistics:
        total time:                          60.6886s
        total number of events:              78147


    Latency (ms):
             min:                                 10.64
             avg:                                196.74
             max:                               2116.58
             95th percentile:                    344.08
             sum:                            15374511.74


    Threads fairness:
        events (avg/stddev):           305.2617/18.94
        execution time (avg/stddev):   60.0567/0.05



  1. 測試5 非同步複製，重啟後再次測試，tps為1685.看來重啟之後測試的值比測試3高很多。比半同步複製tps高點。這才正常。
  2. [root@mysql1 ~]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 runsysbench 1.0.9 (using system LuaJIT 2.0.4)

  4. Running the test with following options:
  5. Number of threads: 256
  6. Report intermediate results every 30 second(s)
  7. Initializing random number generator from current time


  10. Initializing worker threads...

  12. Threads

  14. [ 30s ] thds: 256 tps: 1854.50 qps: 37237.93 (r/w/o: 26079.43/7440.98/3717.52) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
  15. [ 60s ] thds: 256 tps: 1533.39 qps: 30644.69 (r/w/o: 21446.68/6132.50/3065.52) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
  16. SQL statistics:
  17.     queries performed:
  18.         read: 1429932
  19.         write: 408552
  20.         other: 204276
  21.         total: 2042760
  22.     transactions: 102138 (1685.63 per sec.)
  23.     queries: 2042760 (33712.64 per sec.)
  24.     ignored errors: 0 (0.00 per sec.)
  25.     reconnects: 0 (0.00 per sec.)

  27. General statistics:
  28.     total time: 60.5918s
  29.     total number of events: 102138

  31. Latency (ms):
  32.          min: 13.28
  33.          avg: 150.66
  34.          max: 1693.21
  35.          95th percentile: 282.25
  36.          sum: 15388303.96

  38. Threads fairness:
  39.     events (avg/stddev): 398.9766/23.66
  40.     execution time (avg/stddev): 60.1106/0.08


  1. 測試6 組複製3個節點，引數和非同步複製一樣。採取的單主模式。我們來看下期中一個節點的引數：
  2. server_id=1
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    sync_binlog = 1
    innodb_buffer_pool_size = 22938M
    innodb_buffer_pool_instances = 8
    skip-name-resolve

  3. transaction_isolation=READ-COMMITTED
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 128M
    innodb_log_files_in_group=5
    innodb_temp_data_file_path=ibtmp1:512M:autoextend
    innodb_flush_log_at_trx_commit = 1
    innodb_doublewrite=on
    max_prepared_stmt_count=1048576
    max_connections = 600
    max_connect_errors = 1000000
    max_allowed_packet = 32M
    open_files_limit    = 65535
    skip_name_resolve = 1

  4. gtid_mode=on
    enforce_gtid_consistency=on
    master_info_repository=table
    relay_log_info_repository=table
    binlog_checksum=none
    log_slave_updates=on
    log_bin=binlog
    binlog_format=row
    relay_log=relay-log
    log_timestamps=SYSTEM


    transaction_write_set_extraction=XXHASH64
    loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
    loose-group_replication_start_on_boot=off
    loose-group_replication_local_address="10.1.3.24:24901"
    loose-group_replication_group_seeds="10.1.3.24:24901,10.1.3.25:24902,10.1.3.23:24903"
    loose-group_replication_bootstrap_group=off

    可以看到100個執行緒的tps是683。半同步複製是1287，非同步複製是1685，單例項是2419.

  6. [root@mysql1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=100 --time=60 --report-interval=30 run
  7. sysbench 1.0.9 (using system LuaJIT 2.0.4)

  9. Running the test with following options:
  10. Number of threads: 100
  11. Report intermediate results every 30 second(s)
  12. Initializing random number generator from current time


  15. Initializing worker threads...

  17. Threads

  19. [ 30s ] thds: 100 tps: 142.46 qps: 2912.41 (r/w/o: 2041.02/211.02/660.37) lat (ms,95%): 580.02 err/s: 0.00 reconn/s: 0.00
  20. [ 60s ] thds: 100 tps: 1232.57 qps: 24625.49 (r/w/o: 17236.89/1869.27/5519.33) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
  21. SQL statistics:
  22.     queries performed:
  23.         read: 578424
  24.         write: 62418
  25.         other: 185478
  26.         total: 826320
  27.     transactions: 41316 (683.42 per sec.)
  28.     queries: 826320 (13668.46 per sec.)
  29.     ignored errors: 0 (0.00 per sec.)
  30.     reconnects: 0 (0.00 per sec.)

  32. General statistics:
  33.     total time: 60.4528s
  34.     total number of events: 41316

  36. Latency (ms):
  37.          min: 8.61
  38.          avg: 145.81
  39.          max: 22889.55
  40.          95th percentile: 356.70
  41.          sum: 6024263.73

  43. Threads fairness:
  44.     events (avg/stddev): 413.1600/14.56
  45.     execution time (avg/stddev): 60.2426/0.21

  1. 測試7 在上面測試基礎上，加引數set global innodb_flush_log_at_trx_commit=0;
    因為組複製這個是安全的。
  2. [root@mysql1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table_size=5000000 --tables=10 --threads=256 --time=60 --report-interval=30 run
  3. sysbench 1.0.9 (using system LuaJIT 2.0.4)

  5. Running the test with following options:
  6. Number of threads: 256
  7. Report intermediate results every 30 second(s)
  8. Initializing random number generator from current time


  11. Initializing worker threads...

  13. Threads

  15. [ 30s ] thds: 256 tps: 1131.73 qps: 22796.67 (r/w/o: 15963.64/2456.99/4376.04) lat (ms,95%): 623.33 err/s: 0.00 reconn/s: 0.00
  16. [ 60s ] thds: 256 tps: 705.84 qps: 14103.58 (r/w/o: 9872.01/1562.96/2668.61) lat (ms,95%): 612.21 err/s: 0.00 reconn/s: 0.00
  17. SQL statistics:
  18.     queries performed:
  19.         read: 775334
  20.         write: 120639
  21.         other: 211647
  22.         total: 1107620
  23.     transactions: 55381 (914.03 per sec.)
  24.     queries: 1107620 (18280.60 per sec.)
  25.     ignored errors: 0 (0.00 per sec.)
  26.     reconnects: 0 (0.00 per sec.)

  28. General statistics:
  29.     total time: 60.5878s
  30.     total number of events: 55381

  32. Latency (ms):
  33.          min: 3.90
  34.          avg: 278.94
  35.          max: 12261.22
  36.          95th percentile: 623.33
  37.          sum: 15448099.10

  39. Threads fairness:
  40.     events (avg/stddev): 216.3320/7.12
  41.     execution time (avg/stddev): 60.3441/0.12




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30393770/viewspace-2150102/，如需轉載，請註明出處，否則將追究法律責任。

相關文章