mysql壓力測試在京東雲ssd雲盤sysbench版本

e71hao發表於2018-01-11
MySql
一、環境：京東雲Centos6.8 ，cpu16核,記憶體32G，SAS 3G,轉速未知 ，SSD雲盤300G
     mysql 版本是：5.7.20 ，預設rpm安裝,單例項。
     壓測工具是：sysbench1.0.9
mysql壓力測試在京東雲ssd雲盤fio測試4K隨機讀寫iops是4144.

  1. mysql參數列基本配置：
  2. innodb_buffer_pool_size = 22938M
  3. innodb_buffer_pool_instances = 8
  4. skip-name-resolve
  5. transaction_isolation=READ-COMMITTED
  6. innodb_log_file_size = 512M
  7. innodb_log_buffer_size = 128M
  8. innodb_log_files_in_group=5
  9. innodb_temp_data_file_path=ibtmp1:512M:autoextend
  10. innodb_flush_log_at_trx_commit = 1
  11. innodb_doublewrite=on
  12. sync_binlog=0
  13. max_prepared_stmt_count=1048576
  14. max_connections = 600
  15. max_connect_errors = 1000000
  16. max_allowed_packet = 32M
  17. open_files_limit = 65535
  18. skip_name_resolve = 1
首先載入資料，23G,10個表，每個表的資料是1000萬。
  1. [root@mysql3 data]# ls -lhS mysql1/sbtest/
  2. total 23G
  3. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest10.ibd
  4. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest1.ibd
  5. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest2.ibd
  6. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest3.ibd
  7. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest4.ibd
  8. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest5.ibd
  9. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest6.ibd
  10. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest7.ibd
  11. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest8.ibd
  12. -rw-r----- 1 mysql mysql 2.3G Jan 11 20:00 sbtest9.ibd
  13. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest10.frm
  14. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest1.frm
  15. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest2.frm
  16. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest3.frm
  17. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest4.frm
  18. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest5.frm
  19. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest6.frm
  20. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest7.frm
  21. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest8.frm
  22. -rw-r----- 1 mysql mysql 8.5K Jan 11 19:55 sbtest9.frm
  23. -rw-r----- 1 mysql mysql 61 Jan 11 19:32 db.opt
二、測試1，首先做一個基準測試，慢慢往上加併發連線。10個表，1000萬資料，20個執行緒，60秒。tps是774
  1. 測試1
  2. [root@mysql3 tmp]# 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=10000000 --tables=10 --threads=20 --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: 20
  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: 20 tps: 667.42 qps: 13357.70 (r/w/o: 9351.99/2670.21/1335.50) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
  16. [ 60s ] thds: 20 tps: 880.89 qps: 17618.79 (r/w/o: 12332.59/3524.41/1761.79) lat (ms,95%): 40.37 err/s: 0.00 reconn/s: 0.00
  17. SQL statistics:
  18.     queries performed:
  19.         read: 650580
  20.         write: 185880
  21.         other: 92940
  22.         total: 929400
  23.     transactions: 46470 (774.21 per sec.)
  24.     queries: 929400 (15484.29 per sec.)
  25.     ignored errors: 0 (0.00 per sec.)
  26.     reconnects: 0 (0.00 per sec.)

  28. General statistics:
  29.     total time: 60.0203s
  30.     total number of events: 46470

  32. Latency (ms):
  33.          min: 7.81
  34.          avg: 25.82
  35.          max: 368.26
  36.          95th percentile: 52.89
  37.          sum: 1199957.04

  39. Threads fairness:
  40.     events (avg/stddev): 2323.5000/12.11
  41.     execution time (avg/stddev): 59.9979/0.00
三、測試2

  1. 測試2 併發執行緒變為100個，tps值增加了到1577.
  2. [root@mysql3 tmp]# 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=10000000 --tables=10 --threads=100 --time=120 --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: 100
  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: 100 tps: 1579.48 qps: 31645.01 (r/w/o: 22156.90/6325.84/3162.27) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
  16. [ 60s ] thds: 100 tps: 1509.51 qps: 30185.61 (r/w/o: 21131.66/6034.96/3018.98) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
  17. [ 90s ] thds: 100 tps: 1593.80 qps: 31871.44 (r/w/o: 22306.36/6377.47/3187.60) lat (ms,95%): 121.08 err/s: 0.00 reconn/s: 0.00
  18. [ 120s ] thds: 100 tps: 1625.77 qps: 32518.43 (r/w/o: 22764.28/6502.93/3251.22) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
  19. SQL statistics:
  20.     queries performed:
  21.         read: 2651026
  22.         write: 757436
  23.         other: 378718
  24.         total: 3787180
  25.     transactions: 189359 (1577.34 per sec.)
  26.     queries: 3787180 (31546.82 per sec.)
  27.     ignored errors: 0 (0.00 per sec.)
  28.     reconnects: 0 (0.00 per sec.)

  30. General statistics:
  31.     total time: 120.0477s
  32.     total number of events: 189359

  34. Latency (ms):
  35.          min: 7.24
  36.          avg: 63.38
  37.          max: 403.67
  38.          95th percentile: 118.92
  39.          sum: 12001058.69

  41. Threads fairness:
  42.     events (avg/stddev): 1893.5900/17.58
  43.     execution time (avg/stddev): 120.0106/0.01
  44. iostat的一次統計
  45. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              77.75    0.00   11.25    1.11    0.00    9.90
    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
    vda               0.00     0.00    0.00    0.12     0.00     1.00     8.00     0.00    7.00    0.00    7.00   7.00   0.09
    vdb               0.00  7379.75  505.75  938.50 31545.00 66056.00    67.58     1.93    1.34    0.84    1.60   0.42  60.99
四、測試3，執行緒達到256個，tps結果為1740，相比測試2的1577tps，上升不明顯。併發執行緒翻倍。再往上升是不是就是瓶頸了呢？
  1. 測試3 貼出測試結果
  2. [root@mysql3 tmp]# 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=10000000 --tables=10 --threads=256 --time=120 --report-interval=30 run
  3. SQL statistics:
  4.     queries performed:
  5.         read: 2926574
  6.         write: 836164
  7.         other: 418082
  8.         total: 4180820
  9.     transactions: 209041 (1740.35 per sec.)
  10.     queries: 4180820 (34807.06 per sec.)
  11.     ignored errors: 0 (0.00 per sec.)
  12.     reconnects: 0 (0.00 per sec.)

  14. General statistics:
  15.     total time: 120.1109s
  16.     total number of events: 209041


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

相關文章