mysql壓力測試在京東雲ssd雲盤（tpccmysql壓測）

e71hao發表於2018-01-05
MySql
一、環境：京東雲Centos6.8 ，cpu16核,記憶體32G，SAS 3G,轉速未知 ，SSD雲盤300G
     mysql 版本是：5.7.20 ，預設rpm安裝,單例項。
     壓測工具是：tpcc-mysql5.0
mysql壓力測試在京東雲ssd雲盤
首先載入資料，載入10個warehouse,現在資料庫tpcc1000有9張表，warehouse10條記錄，stock表有100萬，item表有10萬，order_line299萬，new_orders11萬，orders30萬，  customer30萬。
  1. 現在資料庫tpcc1000有9張表，warehouse10條記錄，stock表有100萬，item表有10萬，order_line1186萬，new_orders11萬，orders118萬，  customer30萬,檢視一下實際表空間的大小：
  2. [mysql@mysql3 mysql1]$ ls -lhS tpcc1000/
  3. total 2.3G
  4. -rw-r----- 1 mysql mysql 1.6G Jan 5 18:51 order_line.ibd
  5. -rw-r----- 1 mysql mysql 352M Jan 5 18:51 stock.ibd
  6. -rw-r----- 1 mysql mysql 188M Jan 5 18:51 customer.ibd
  7. -rw-r----- 1 mysql mysql 96M Jan 5 18:51 history.ibd
  8. -rw-r----- 1 mysql mysql 68M Jan 5 18:51 orders.ibd
  9. -rw-r----- 1 mysql mysql 17M Jan 5 17:47 item.ibd
  10. -rw-r----- 1 mysql mysql 13M Jan 5 18:51 new_orders.ibd
  11. -rw-r----- 1 mysql mysql 96K Jan 5 18:51 district.ibd
  12. -rw-r----- 1 mysql mysql 96K Jan 5 18:51 warehouse.ibd
  13. -rw-r----- 1 mysql mysql 9.2K Jan 5 17:44 customer.frm
  14. -rw-r----- 1 mysql mysql 9.0K Jan 5 17:44 stock.frm
  15. -rw-r----- 1 mysql mysql 8.8K Jan 5 17:44 order_line.frm
  16. -rw-r----- 1 mysql mysql 8.8K Jan 5 17:44 district.frm
  17. -rw-r----- 1 mysql mysql 8.7K Jan 5 17:44 warehouse.frm
  18. -rw-r----- 1 mysql mysql 8.7K Jan 5 17:44 orders.frm
  19. -rw-r----- 1 mysql mysql 8.7K Jan 5 17:44 history.frm
  20. -rw-r----- 1 mysql mysql 8.5K Jan 5 17:44 item.frm
  21. -rw-r----- 1 mysql mysql 8.5K Jan 5 17:44 new_orders.frm
  22. -rw-r----- 1 mysql mysql 65 Jan 5 17:40 db.opt

二、測試一下雲盤的iops。

點選(此處)摺疊或開啟

  1. 在京東雲上面的高效雲盤測試，首先測試雲盤的SSD的iops隨機讀寫是4144
  2. [root@mysql3 data]# fio --name=myjob --filename=/dev/vdb --ioengine=libaio --direct=1 --bs=4k --rw=randrw --iodepth=32 --runtime=60
  3. myjob: (g=0): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=32
  4. fio-2.0.13
  5. Starting 1 process
  6. Jobs: 1 (f=1): [m] [100.0% done] [1408K/1332K/0K /s] [352 /333 /0 iops] [eta 00m:00s]
  7. myjob: (groupid=0, jobs=1): err= 0: pid=10136: Fri Jan 5 16:31:42 2018
  8.   read : io=999840KB, bw=16578KB/s, iops=4144 , runt= 60310msec
  9.     slat (usec): min=2 , max=191 , avg= 6.23, stdev= 3.63
  10.     clat (usec): min=177 , max=727001 , avg=3576.38, stdev=23287.21
  11.      lat (usec): min=208 , max=727011 , avg=3583.03, stdev=23287.50
  12.     clat percentiles (usec):
  13.      | 1.00th=[ 270], 5.00th=[ 302], 10.00th=[ 322], 20.00th=[ 350],
  14.      | 30.00th=[ 374], 40.00th=[ 394], 50.00th=[ 418], 60.00th=[ 438],
  15.      | 70.00th=[ 466], 80.00th=[ 502], 90.00th=[ 572], 95.00th=[ 660],
  16.      | 99.00th=[150528], 99.50th=[171008], 99.90th=[246784], 99.95th=[350208],
  17.      | 99.99th=[399360]
  18.     bw (KB/s) : min= 680, max=81632, per=100.00%, avg=16654.07, stdev=29344.48
  19.   write: io=976.74MB, bw=16584KB/s, iops=4145 , runt= 60310msec
  20.     slat (usec): min=2 , max=768 , avg= 8.32, stdev= 6.68
  21.     clat (usec): min=126 , max=730161 , avg=4122.61, stdev=24096.23
  22.      lat (usec): min=360 , max=730172 , avg=4131.36, stdev=24096.59
  23.     clat percentiles (usec):
  24.      | 1.00th=[ 482], 5.00th=[ 540], 10.00th=[ 580], 20.00th=[ 620],
  25.      | 30.00th=[ 660], 40.00th=[ 692], 50.00th=[ 724], 60.00th=[ 756],
  26.      | 70.00th=[ 788], 80.00th=[ 836], 90.00th=[ 932], 95.00th=[ 1080],
  27.      | 99.00th=[154624], 99.50th=[175104], 99.90th=[248832], 99.95th=[350208],
  28.      | 99.99th=[399360]
  29.     bw (KB/s) : min= 720, max=81520, per=100.00%, avg=16658.87, stdev=29264.19
  30.     lat (usec) : 250=0.12%, 500=40.73%, 750=36.85%, 1000=17.52%
  31.     lat (msec) : 2=2.07%, 4=0.16%, 10=0.03%, 20=0.04%, 50=0.64%
  32.     lat (msec) : 100=0.01%, 250=1.75%, 500=0.10%, 750=0.01%
  33.   cpu : usr=2.24%, sys=8.53%, ctx=142694, majf=0, minf=24
  34.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=100.0%, >=64=0.0%
  35.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  36.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  37.      issued : total=r=249960/w=250039/d=0, short=r=0/w=0/d=0

  39. Run status group 0 (all jobs):
  40.    READ: io=999840KB, aggrb=16578KB/s, minb=16578KB/s, maxb=16578KB/s, mint=60310msec, maxt=60310msec
  41.   WRITE: io=976.74MB, aggrb=16583KB/s, minb=16583KB/s, maxb=16583KB/s, mint=60310msec, maxt=60310msec

  43. Disk stats (read/write):
  44.   vdb: ios=250022/250028, merge=0/0, ticks=881619/1016273, in_queue=1902612, util=99.90%


三、所有引數不改變，預設安裝，以此作為壓力測試的基準線，進行對比分析，同時附上iostat 和壓測結果

點選(此處)摺疊或開啟

  1. 測試1 基準測試線。使用mysql5.7.20，所有的引數不改變，預設安裝，開始壓測，做為基準線。
  2. [root@mysql3 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w10 -c64 -r60 -l180 -ftpcc_mysql_20180102.log
  3. ***************************************
  4. *** ###easy### TPC-C Load Generator ***
  5. ***************************************
  6. option h with value 'localhost'
  7. option d with value 'tpcc1000'
  8. option u with value 'tpcc_user'
  9. option p with value 'tpcc_password'
  10. option w with value '10'
  11. option c with value '64'
  12. option r with value '60'
  13. option l with value '180'
  14. option f with value 'tpcc_mysql_20180102.log'
  15. <Parameters>
  16.      [server]: localhost
  17.      [port]: 3306
  18.      [DBname]: tpcc1000
  19.        [user]: tpcc_user
  20.        [pass]: tpcc_password
  21.   [warehouse]: 10
  22.  [connection]: 64
  23.      [rampup]: 60 (sec.)
  24.     [measure]: 180 (sec.)

  26. RAMP-UP TIME.(60 sec.)


  29. <Raw Results>
  30.   [0] sc:35014 lt:1 rt:0 fl:0
  31.   [1] sc:35002 lt:0 rt:0 fl:0
  32.   [2] sc:3501 lt:0 rt:0 fl:0
  33.   [3] sc:3497 lt:0 rt:0 fl:0
  34.   [4] sc:3501 lt:0 rt:0 fl:0
  35.  in 180 sec.

  37. <Raw Results2(sum ver.)>
  38.   [0] sc:35014 lt:1 rt:0 fl:0
  39.   [1] sc:35015 lt:0 rt:0 fl:0
  40.   [2] sc:3501 lt:0 rt:0 fl:0
  41.   [3] sc:3497 lt:0 rt:0 fl:0
  42.   [4] sc:3501 lt:0 rt:0 fl:0

  44. <Constraint Check> (all must be [OK])
  45.  [transaction percentage]
  46.         Payment: 43.47% (>=43.0%) [OK]
  47.    Order-Status: 4.35% (>= 4.0%) [OK]
  48.        Delivery: 4.34% (>= 4.0%) [OK]
  49.     Stock-Level: 4.35% (>= 4.0%) [OK]
  50.  [response time (at least 90% passed)]
  51.       New-Order: 100.00% [OK]
  52.         Payment: 100.00% [OK]
  53.    Order-Status: 100.00% [OK]
  54.        Delivery: 100.00% [OK]
  55.     Stock-Level: 100.00% [OK]

  57. <TpmC>
  58.                  11671.667 TpmC
  59. iostat 統計資訊
  60. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              17.86    0.00    5.91    9.87    0.02   66.34
    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    3.00    0.00    3.00   3.00   0.04
    vdb               0.00 19211.00    0.00 6432.75     0.00 202909.00    31.54    11.59    1.80    0.00    1.80   0.15  95.75



四、更改幾個核心引數，可以看到tps大幅度提高到1217(75035/60).

點選(此處)摺疊或開啟

  1. 測試2：增加引數，tps大幅增加，是有原因的，增大了記憶體
  2. innodb_buffer_pool_size = 22938M
    innodb_buffer_pool_instances = 8
    skip-name-resolve
    transaction_isolation=READ-COMMITTED
  3. innodb_log_file_size = 512M
    innodb_log_buffer_size = 128M
    innodb_log_files_in_group=5
    innodb_temp_data_file_path=ibtmp1:512M:autoextend
  4. [root@mysql3 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w10 -c64 -r60 -l180 -ftpcc_mysql_20180102.log
  5. ***************************************
  6. *** ###easy### TPC-C Load Generator ***
  7. ***************************************
  8. option h with value 'localhost'
  9. option d with value 'tpcc1000'
  10. option u with value 'tpcc_user'
  11. option p with value 'tpcc_password'
  12. option w with value '10'
  13. option c with value '64'
  14. option r with value '60'
  15. option l with value '180'
  16. option f with value 'tpcc_mysql_20180102.log'
  17. <Parameters>
  18.      [server]: localhost
  19.      [port]: 3306
  20.      [DBname]: tpcc1000
  21.        [user]: tpcc_user
  22.        [pass]: tpcc_password
  23.   [warehouse]: 10
  24.  [connection]: 64
  25.      [rampup]: 60 (sec.)
  26.     [measure]: 180 (sec.)

  28. RAMP-UP TIME.(60 sec.)

  30. MEASURING START.

  32. <Raw Results>
  33.   [0] sc:219107 lt:0 rt:0 fl:0
  34.   [1] sc:219072 lt:0 rt:0 fl:0
  35.   [2] sc:21909 lt:0 rt:0 fl:0
  36.   [3] sc:21908 lt:0 rt:0 fl:0
  37.   [4] sc:21912 lt:0 rt:0 fl:0
  38.  in 180 sec.

  40. <Raw Results2(sum ver.)>
  41.   [0] sc:219108 lt:0 rt:0 fl:0
  42.   [1] sc:219108 lt:0 rt:0 fl:0
  43.   [2] sc:21909 lt:0 rt:0 fl:0
  44.   [3] sc:21911 lt:0 rt:0 fl:0
  45.   [4] sc:21912 lt:0 rt:0 fl:0

  47. <Constraint Check> (all must be [OK])
  48.  [transaction percentage]
  49.         Payment: 43.47% (>=43.0%) [OK]
  50.    Order-Status: 4.35% (>= 4.0%) [OK]
  51.        Delivery: 4.35% (>= 4.0%) [OK]
  52.     Stock-Level: 4.35% (>= 4.0%) [OK]
  53.  [response time (at least 90% passed)]
  54.       New-Order: 100.00% [OK]
  55.         Payment: 100.00% [OK]
  56.    Order-Status: 100.00% [OK]
  57.        Delivery: 100.00% [OK]
  58.     Stock-Level: 100.00% [OK]

  60. <TpmC>
  61.                  73035.664 TpmC
iostat 統計資料
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          74.08    0.00   11.53    1.19    0.01   13.19
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     0.75    0.00    0.50     0.00     5.00    20.00     0.00    0.00    0.00    0.00   0.00   0.00
vdb               0.00  5584.00    0.00 1509.38     0.00 28306.00    37.51     1.43    0.95    0.00    0.95   0.49  73.60
五、測試3，增加引數innodb_io_capacity,按照理論，tps應該增加的，但是反而下降。
  1. 測試3：增加引數，主要增加innodb_io_capacity，按照理論，tps應該增加的，但是反而下降10000，具體檢視下，看看是有沒有鎖。
  2. innodb_io_capacity = 10000
    innodb_io_capacity_max = 20000
    innodb_flush_neighbors = 0
    innodb_write_io_threads = 8
    innodb_read_io_threads = 8
    innodb_purge_threads = 4
    innodb_page_cleaners = 4
    innodb_open_files = 65535
    innodb_max_dirty_pages_pct = 50
  3. [root@mysql3 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w10 -c64 -r60 -l180 -ftpcc_mysql_20180102.log
  4. ***************************************
  5. *** ###easy### TPC-C Load Generator ***
  6. ***************************************
  7. option h with value 'localhost'
  8. option d with value 'tpcc1000'
  9. option u with value 'tpcc_user'
  10. option p with value 'tpcc_password'
  11. option w with value '10'
  12. option c with value '64'
  13. option r with value '60'
  14. option l with value '180'
  15. option f with value 'tpcc_mysql_20180102.log'
  16. <Parameters>
  17.      [server]: localhost
  18.      [port]: 3306
  19.      [DBname]: tpcc1000
  20.        [user]: tpcc_user
  21.        [pass]: tpcc_password
  22.   [warehouse]: 10
  23.  [connection]: 64
  24.      [rampup]: 60 (sec.)
  25.     [measure]: 180 (sec.)

  27. RAMP-UP TIME.(60 sec.)

  29. MEASURING START.

  31. <Raw Results>
  32.   [0] sc:201891 lt:0 rt:0 fl:0
  33.   [1] sc:201850 lt:0 rt:0 fl:0
  34.   [2] sc:20190 lt:0 rt:0 fl:0
  35.   [3] sc:20187 lt:0 rt:0 fl:0
  36.   [4] sc:20186 lt:0 rt:0 fl:0
  37.  in 180 sec.

  39. <Raw Results2(sum ver.)>
  40.   [0] sc:201895 lt:0 rt:0 fl:0
  41.   [1] sc:201884 lt:0 rt:0 fl:0
  42.   [2] sc:20190 lt:0 rt:0 fl:0
  43.   [3] sc:20189 lt:0 rt:0 fl:0
  44.   [4] sc:20187 lt:0 rt:0 fl:0

  46. <Constraint Check> (all must be [OK])
  47.  [transaction percentage]
  48.         Payment: 43.47% (>=43.0%) [OK]
  49.    Order-Status: 4.35% (>= 4.0%) [OK]
  50.        Delivery: 4.35% (>= 4.0%) [OK]
  51.     Stock-Level: 4.35% (>= 4.0%) [OK]
  52.  [response time (at least 90% passed)]
  53.       New-Order: 100.00% [OK]
  54.         Payment: 100.00% [OK]
  55.    Order-Status: 100.00% [OK]
  56.        Delivery: 100.00% [OK]
  57.     Stock-Level: 100.00% [OK]

  59. <TpmC>
  60.                  67297.000 TpmC
iostat 的統計資訊
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          70.27    0.00   11.00    1.60    0.01   17.12
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     1.33    0.00    2.00     0.00    13.33    13.33     0.00    0.00    0.00    0.00   0.00   0.00
vdb               0.00  8142.89    0.00 2545.33     0.00 42552.89    33.44     5.40    2.12    0.00    2.12   0.31  77.98
這次tps下降了。所以我們要恢復原來的引數。把這些引數清除繼續下面的測試

六、增加併發連線到128個
  1. 測試4.引數不調整，但是增加併發連線到128個。看tps77476，沒有很大的提高，併發可以撐得住。對系統影響較小
  2. [root@mysql3 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w10 -c128 -r60 -l180 -ftpcc_mysql_20180102.log
  3. ***************************************
  4. *** ###easy### TPC-C Load Generator ***
  5. ***************************************
  6. option h with value 'localhost'
  7. option d with value 'tpcc1000'
  8. option u with value 'tpcc_user'
  9. option p with value 'tpcc_password'
  10. option w with value '10'
  11. option c with value '128'
  12. option r with value '60'
  13. option l with value '180'
  14. option f with value 'tpcc_mysql_20180102.log'
  15. <Parameters>
  16.      [server]: localhost
  17.      [port]: 3306
  18.      [DBname]: tpcc1000
  19.        [user]: tpcc_user
  20.        [pass]: tpcc_password
  21.   [warehouse]: 10
  22.  [connection]: 128
  23.      [rampup]: 60 (sec.)
  24.     [measure]: 180 (sec.)
  25. RAMP-UP TIME.(60 sec.)
  26. MEASURING START.

  28. STOPPING THREADS................................................................................................................................
  29. <Raw Results>
  30.   [0] sc:232429 lt:0 rt:0 fl:0
  31.   [1] sc:232379 lt:0 rt:0 fl:0
  32.   [2] sc:23247 lt:0 rt:0 fl:0
  33.   [3] sc:23246 lt:0 rt:0 fl:0
  34.   [4] sc:23246 lt:0 rt:0 fl:0
  35.  in 180 sec.

  37. <Raw Results2(sum ver.)>
  38.   [0] sc:232430 lt:0 rt:0 fl:0
  39.   [1] sc:232417 lt:0 rt:0 fl:0
  40.   [2] sc:23247 lt:0 rt:0 fl:0
  41.   [3] sc:23246 lt:0 rt:0 fl:0
  42.   [4] sc:23246 lt:0 rt:0 fl:0

  44. <Constraint Check> (all must be [OK])
  45.  [transaction percentage]
  46.         Payment: 43.47% (>=43.0%) [OK]
  47.    Order-Status: 4.35% (>= 4.0%) [OK]
  48.        Delivery: 4.35% (>= 4.0%) [OK]
  49.     Stock-Level: 4.35% (>= 4.0%) [OK]
  50.  [response time (at least 90% passed)]
  51.       New-Order: 100.00% [OK]
  52.         Payment: 100.00% [OK]
  53.    Order-Status: 100.00% [OK]
  54.        Delivery: 100.00% [OK]
  55.     Stock-Level: 100.00% [OK]

  57. <TpmC>
  58.                  77476.336 TpmC
  59. iostat 統計資訊
  60. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              78.48    0.00   11.64    0.77    0.00    9.11
    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.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
    vdb               0.00  6027.25    0.00 1488.25     0.00 59964.00    40.29     1.41    0.95    0.00    0.95   0.45  67.21

七、開啟二進位制日誌功能，以及刷盤方式，對tps影響都是巨大的。這個結論也可以理解，不停的寫二進位制日誌。假如整個資料庫只用innodb一種引擎，又寫二進位制日誌，又寫innodb日誌，不是重複嗎？但是線上日誌對資料庫的效能影響是絕對巨頭的，甚至可以認為是決定性的。

點選(此處)摺疊或開啟

  1. 測試5：增加引數，表示開啟二進位制日誌功能，tps效能大幅下跌到17523，我檢視binlog日誌，有個1.1Gbinlog.000001，還有一個276M的binlog.0000002日誌檔案。所有的效能損耗在寫二進位制日誌檔案上。測試6：如果sync_binlog變為0，我的tps測試結果為44691.332 TpmC 。所以說不控制的話，tps應該有提高，提高效果還是很明顯的哦。
  2. server_id=1
  3. binlog_format=row
  4. log_bin = binlog
  5. sync_binlog=1
  6. [root@mysql3 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w10 -c64 -r60 -l180 -ftpcc_mysql_20180102.log
  7. ***************************************
  8. *** ###easy### TPC-C Load Generator ***
  9. ***************************************
  10. option h with value 'localhost'
  11. option d with value 'tpcc1000'
  12. option u with value 'tpcc_user'
  13. option p with value 'tpcc_password'
  14. option w with value '10'
  15. option c with value '64'
  16. option r with value '60'
  17. option l with value '180'
  18. option f with value 'tpcc_mysql_20180102.log'

  20. <Raw Results>
  21.   [0] sc:52564 lt:5 rt:0 fl:0
  22.   [1] sc:52560 lt:0 rt:0 fl:0
  23.   [2] sc:5257 lt:0 rt:0 fl:0
  24.   [3] sc:5257 lt:0 rt:0 fl:0
  25.   [4] sc:5258 lt:0 rt:0 fl:0
  26.  in 180 sec.

  28. <Raw Results2(sum ver.)>
  29.   [0] sc:52565 lt:5 rt:0 fl:0
  30.   [1] sc:52571 lt:0 rt:0 fl:0
  31.   [2] sc:5258 lt:0 rt:0 fl:0
  32.   [3] sc:5257 lt:0 rt:0 fl:0
  33.   [4] sc:5258 lt:0 rt:0 fl:0

  35. <Constraint Check> (all must be [OK])
  36.  [transaction percentage]
  37.         Payment: 43.47% (>=43.0%) [OK]
  38.    Order-Status: 4.35% (>= 4.0%) [OK]
  39.        Delivery: 4.35% (>= 4.0%) [OK]
  40.     Stock-Level: 4.35% (>= 4.0%) [OK]
  41.  [response time (at least 90% passed)]
  42.       New-Order: 99.99% [OK]
  43.         Payment: 100.00% [OK]
  44.    Order-Status: 100.00% [OK]
  45.        Delivery: 100.00% [OK]
  46.     Stock-Level: 100.00% [OK]

  48. <TpmC>
  49.                  17523.000 TpmC
八、把資料量增加到50個warehouse.這個時候，資料量是這樣的：
現在資料庫tpcc1000有9張表，warehouse10條記錄，stock表有500萬，item表有10萬，order_line1499萬，new_orders45萬，orders150萬，  customer150萬,檢視一下實際表空間的大小3.9G：

  1. [root@mysql3 ~]# ll /data/mysql1/tpcc1000/ -hS
  2. total 3.9G
  3. -rw-r----- 1 mysql mysql 1.7G Jan 10 11:13 stock.ibd
  4. -rw-r----- 1 mysql mysql 1.1G Jan 10 11:40 order_line.ibd
  5. -rw-r----- 1 mysql mysql 920M Jan 10 11:22 customer.ibd
  6. -rw-r----- 1 mysql mysql 108M Jan 10 11:22 history.ibd
  7. -rw-r----- 1 mysql mysql 68M Jan 10 11:40 orders.ibd
  8. -rw-r----- 1 mysql mysql 20M Jan 10 11:40 new_orders.ibd
  9. -rw-r----- 1 mysql mysql 17M Jan 10 11:03 item.ibd
  10. -rw-r----- 1 mysql mysql 144K Jan 10 11:13 district.ibd
  11. -rw-r----- 1 mysql mysql 96K Jan 10 11:11 warehouse.ibd
  12. -rw-r----- 1 mysql mysql 9.2K Jan 10 11:01 customer.frm
  13. -rw-r----- 1 mysql mysql 9.0K Jan 10 11:01 stock.frm
  14. -rw-r----- 1 mysql mysql 8.8K Jan 10 11:01 order_line.frm
  15. -rw-r----- 1 mysql mysql 8.8K Jan 10 11:01 district.frm
  16. -rw-r----- 1 mysql mysql 8.7K Jan 10 11:01 warehouse.frm
  17. -rw-r----- 1 mysql mysql 8.7K Jan 10 11:01 orders.frm
  18. -rw-r----- 1 mysql mysql 8.7K Jan 10 11:01 history.frm
  19. -rw-r----- 1 mysql mysql 8.5K Jan 10 11:01 item.frm
  20. -rw-r----- 1 mysql mysql 8.5K Jan 10 11:01 new_orders.frm
  21. -rw-r----- 1 mysql mysql 67 Jan 8 16:28 db.opt
九、來做一個壓測，這裡要說明一下測試7引數配置和測試6配置一樣。從結果來看，增加資料量，對tps影響不大。主要引數如下：
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
#autocommit=0
server_id=1
binlog_format=row
log_bin                 = binlog
sync_binlog=0
  1. 測試7.增加warehouse到50個
  2. [root@mysql3 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w50 -c128 -r30 -l90 -ftpcc_mysql_20180102.log
  3. ***************************************
  4. *** ###easy### TPC-C Load Generator ***
  5. ***************************************
  6. option h with value 'localhost'
  7. option d with value 'tpcc1000'
  8. option u with value 'tpcc_user'
  9. option p with value 'tpcc_password'
  10. option w with value '50'
  11. option c with value '128'
  12. option r with value '30'
  13. option l with value '90'
  14. option f with value 'tpcc_mysql_20180102.log'
  15. <Parameters>
  16.      [server]: localhost
  17.      [port]: 3306
  18.      [DBname]: tpcc1000
  19.        [user]: tpcc_user
  20.        [pass]: tpcc_password
  21.   [warehouse]: 50
  22.  [connection]: 128
  23.      [rampup]: 30 (sec.)
  24.     [measure]: 90 (sec.)

  26. RAMP-UP TIME.(30 sec.)


  29. <Raw Results>
  30.   [0] sc:83245 lt:2 rt:0 fl:0
  31.   [1] sc:83233 lt:3 rt:0 fl:0
  32.   [2] sc:8324 lt:0 rt:0 fl:0
  33.   [3] sc:8322 lt:0 rt:0 fl:0
  34.   [4] sc:8323 lt:0 rt:0 fl:0
  35.  in 90 sec.

  37. <Raw Results2(sum ver.)>
  38.   [0] sc:83245 lt:2 rt:0 fl:0
  39.   [1] sc:83250 lt:3 rt:0 fl:0
  40.   [2] sc:8324 lt:0 rt:0 fl:0
  41.   [3] sc:8322 lt:0 rt:0 fl:0
  42.   [4] sc:8323 lt:0 rt:0 fl:0

  44. <Constraint Check> (all must be [OK])
  45.  [transaction percentage]
  46.         Payment: 43.48% (>=43.0%) [OK]
  47.    Order-Status: 4.35% (>= 4.0%) [OK]
  48.        Delivery: 4.35% (>= 4.0%) [OK]
  49.     Stock-Level: 4.35% (>= 4.0%) [OK]
  50.  [response time (at least 90% passed)]
  51.       New-Order: 100.00% [OK]
  52.         Payment: 100.00% [OK]
  53.    Order-Status: 100.00% [OK]
  54.        Delivery: 100.00% [OK]
  55.     Stock-Level: 100.00% [OK]

  57. <TpmC>
  58.                  55498.000 TpmC
  59. iostat 統計
  60. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              70.65    0.00   10.26    2.04    0.01   17.04
    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.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
    vdb               0.00 11602.88   27.00 1049.25  1300.00 100851.00    94.91     2.44    2.30    5.16    2.22   0.60  64.59
十、把warehouse 增加到500個，
把資料量增加到50個warehouse.這個時候，資料量是這樣的：
現在資料庫tpcc1000有9張表，warehouse10條記錄，stock表有5000萬，item表有10萬，order_line1.49億，new_orders450萬，orders1500萬，  customer1500萬,檢視一下實際表空間的大小38G：

  1. [root@mysql3 ~]# ll /data/mysql1/tpcc1000/ -hS
  2. total 38G
  3. -rw-r----- 1 mysql mysql 17G Jan 10 16:11 stock.ibd
  4. -rw-r----- 1 mysql mysql 11G Jan 10 19:27 order_line.ibd
  5. -rw-r----- 1 mysql mysql 8.9G Jan 10 16:52 customer.ibd
  6. -rw-r----- 1 mysql mysql 988M Jan 10 16:52 history.ibd
  7. -rw-r----- 1 mysql mysql 608M Jan 10 19:27 orders.ibd
  8. -rw-r----- 1 mysql mysql 128M Jan 10 19:27 new_orders.ibd
  9. -rw-r----- 1 mysql mysql 17M Jan 10 15:03 item.ibd
  10. -rw-r----- 1 mysql mysql 9.0M Jan 10 16:11 district.ibd
  11. -rw-r----- 1 mysql mysql 144K Jan 10 16:10 warehouse.ibd
  12. -rw-r----- 1 mysql mysql 9.2K Jan 10 15:02 customer.frm
  13. -rw-r----- 1 mysql mysql 9.0K Jan 10 15:02 stock.frm
  14. -rw-r----- 1 mysql mysql 8.8K Jan 10 15:02 order_line.frm
  15. -rw-r----- 1 mysql mysql 8.8K Jan 10 15:02 district.frm
  16. -rw-r----- 1 mysql mysql 8.7K Jan 10 15:02 warehouse.frm
  17. -rw-r----- 1 mysql mysql 8.7K Jan 10 15:02 orders.frm
  18. -rw-r----- 1 mysql mysql 8.7K Jan 10 15:02 history.frm
  19. -rw-r----- 1 mysql mysql 8.5K Jan 10 15:02 item.frm
  20. -rw-r----- 1 mysql mysql 8.5K Jan 10 15:02 new_orders.frm
  21. -rw-r----- 1 mysql mysql 67 Jan 8 16:28 db.opt
十一、先做一個測試線，看看情況有報錯，報錯如下：
1461, 42000, Can't create more than max_prepared_stmt_count statements (current value: 16382)
1040, 08004, Too many connections
這個問題比較好解決，更改兩個引數不報錯了。set global  max_prepared_stmt_count=1048576;
壓力測試結果出來了，是5700TpmC.明顯，這個結果是非常低的。問題出在哪裡呢？

  1. 測試8 資料量有38G，很大
  2. [root@mysql3 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w500 -c512 -r10 -l10 -ftpcc_mysql_20180102.log
  3. ***************************************
  4. *** ###easy### TPC-C Load Generator ***
  5. ***************************************
  6. option h with value 'localhost'
  7. option d with value 'tpcc1000'
  8. option u with value 'tpcc_user'
  9. option p with value 'tpcc_password'
  10. option w with value '500'
  11. option c with value '512'
  12. option r with value '10'
  13. option l with value '10'
  14. option f with value 'tpcc_mysql_20180102.log'
  15. <Parameters>
  16.      [server]: localhost
  17.      [port]: 3306
  18.      [DBname]: tpcc1000
  19.        [user]: tpcc_user
  20.        [pass]: tpcc_password
  21.   [warehouse]: 500
  22.  [connection]: 512
  23.      [rampup]: 10 (sec.)
  24.     [measure]: 10 (sec.)

  26. RAMP-UP TIME.(10 sec.)

  28. <Raw Results>
  29.   [0] sc:949 lt:1 rt:0 fl:0
  30.   [1] sc:865 lt:0 rt:0 fl:0
  31.   [2] sc:85 lt:0 rt:0 fl:0
  32.   [3] sc:67 lt:0 rt:0 fl:0
  33.   [4] sc:57 lt:0 rt:0 fl:0
  34.  in 10 sec.

  36. <Raw Results2(sum ver.)>
  37.   [0] sc:949 lt:1 rt:0 fl:0
  38.   [1] sc:865 lt:0 rt:0 fl:0
  39.   [2] sc:85 lt:0 rt:0 fl:0
  40.   [3] sc:67 lt:0 rt:0 fl:0
  41.   [4] sc:57 lt:0 rt:0 fl:0

  43. <Constraint Check> (all must be [OK])
  44.  [transaction percentage]
  45.         Payment: 42.74% (>=43.0%) [NG] *
  46.    Order-Status: 4.20% (>= 4.0%) [OK]
  47.        Delivery: 3.31% (>= 4.0%) [NG] *
  48.     Stock-Level: 2.82% (>= 4.0%) [NG] *
  49.  [response time (at least 90% passed)]
  50.       New-Order: 99.89% [OK]
  51.         Payment: 100.00% [OK]
  52.    Order-Status: 100.00% [OK]
  53.        Delivery: 100.00% [OK]
  54.     Stock-Level: 100.00% [OK]

  56. <TpmC>
  57.                  5700.000 TpmC
  58. iostat 統計資訊：
  59. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              14.18    0.00    4.84   63.26    0.01   17.71
    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.38    0.12    0.88     2.00    10.00    12.00     0.00    0.12    0.00    0.14   0.12   0.01
    vdb               1.25 10116.38 2605.50  615.88 204808.00 86800.00    90.52    48.55   14.97   15.37   13.28   0.31  99.99
    這個比較有意思。你對比iostat,和以前測試相比，r/s由0變成了2605，w/s由1488變成了615，avgqu-sz變成了48，等待佇列很多
    %util變成了99.99，io是滿負荷的。這說明什麼呢？寫變少了，讀大量增加。為什麼呢？因為資料量變大了，查詢耗時了。
    現在要解決的問題是，最佳化查詢語句後再進行壓力測試。

這個比較有意思。你對比iostat,和以前測試相比，r/s由0變成了2605，w/s由1488變成了615，avgqu-sz變成了48，等待佇列很多
%util變成了99.99，io是滿負荷的。這說明什麼呢？寫變少了，讀大量增加。為什麼呢？因為資料量變大了，查詢耗時了。
現在要解決的問題是，最佳化查詢語句後再進行壓力測試。

十二、我開啟了mysql的慢查詢，分析哪些語句執行慢。摘錄部分查詢結果如下：

  1. # Query_time: 4.743248 Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 0
  2. SET timestamp=1515661062;
  3. INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES(3002, 4, 138, 2870, '2018-01-11 16:57:37', 7, 1);

  5. # Query_time: 3.139345 Lock_time: 0.000102 Rows_sent: 1 Rows_examined: 3001
  6. SET timestamp=1515661062;
  7. SELECT o_id, o_entry_d, COALESCE(o_carrier_id,0) FROM orders WHERE o_w_id = 345 AND o_d_id = 8 AND o_c_id = 1069 AND o_id = (SELECT MAX(o_id) FROM orders WHERE o_w_id = 345 AND o_d_id = 8 AND o_c_id = 1069);

  9. # Query_time: 4.433704 Lock_time: 0.000241 Rows_sent: 1 Rows_examined: 3002
  10. SET timestamp=1515661062;
  11. SELECT o_id, o_entry_d, COALESCE(o_carrier_id,0) FROM orders WHERE o_w_id = 408 AND o_d_id = 7 AND o_c_id = 760 AND o_id = (SELECT MAX(o_id) FROM orders WHERE o_w_id = 408 AND o_d_id = 7 AND o_c_id = 760);

  13. # Query_time: 7.391263 Lock_time: 0.000151 Rows_sent: 1 Rows_examined: 3003
  14. SET timestamp=1515661062;
  15. SELECT o_id, o_entry_d, COALESCE(o_carrier_id,0) FROM orders WHERE o_w_id = 253 AND o_d_id = 4 AND o_c_id = 1272 AND o_id = (SELECT MAX(o_id) FROM orders WHERE o_w_id = 253 AND o_d_id = 4 AND o_c_id = 1272)
我們看到，這裡面慢查詢都是發生在表orders，有執行7秒的，有執行4秒的，3秒的，有insert語句。我把這些語句單獨拿到
mysql中執行，執行非常快。列印出執行計劃，我看到了所有的語句都走了索引，而且每個語句執行速度很快。這個會是什麼原因呢？我猜測是併發執行緒多，有鎖住情況。但是mysql的查詢是非阻塞的，插入也是非阻塞的，這又是什麼原因呢？先做個驗證：使用256個併發執行緒連線，看看有什麼表現。
我們看到，這裡面慢查詢都是發生在表orders，有執行7秒的，有執行4秒的，3秒的，有insert語句。我把這些語句單獨拿到
mysql中執行，執行非常快。列印出執行計劃，我看到了所有的語句都走了索引，而且每個語句執行速度很快。這個會是什麼原因呢？我猜測是併發執行緒多，有鎖住情況。但是mysql的查詢是非阻塞的，插入也是非阻塞的，這又是什麼原因呢？先做個驗證：使用256個併發執行緒連線，看看有什麼表現。

十三、我先後做如下壓測，後面附上tps:當庫存數量達到450的時候，效能急劇下降，不清楚mysql哪個指標受限制了。


  1.
  2. 測試9 ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w100 -c64 -r10 -l30 -ftpcc_mysql_20180104.log
  3. 33200.000 TpmC

  5. 測試10 ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w100 -c256 -r10 -l30 -ftpcc_mysql_20180104.log
  6. 32594.000 TpmC

  8. 測試11 ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w250 -c256 -r10 -l30 -ftpcc_mysql_20180104.log
  9. 30148.000 TpmC
  1. 測試12 ./tpcc_start -hlocalhost -dtpcc1000 -utpcc_user -ptpcc_password -w450 -c256 -r10 -l30 -ftpcc_mysql_20180104.log 
  2. 17996.000 TpmC
當庫存數量達到450的時候，效能急劇下降，不清楚mysql哪個指標受限制了。


最後點評：




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

相關文章