mysql單例項壓力測試在青雲

e71hao發表於2018-01-26
MySql單例
一、首先來看下環境變數配置
環境：青雲Centos6.8 ，cpu16核,記憶體32G，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萬。

二、看下iops測測試結果

  1. [root@i-62ujmh37 tmp]# fio --name=myjob --filename=/dev/vdc --ioengine=libaio --direct=1 --bs=4k --rw=randrw --iodepth=32 --runtime=60
  2. myjob: (g=0): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=32
  3. fio-2.0.13
  4. Starting 1 process
  5. Jobs: 1 (f=1): [m] [100.0% done] [7448K/7397K/0K /s] [1862 /1849 /0 iops] [eta 00m:00s]
  6. myjob: (groupid=0, jobs=1): err= 0: pid=4667: Thu Jan 25 18:54:26 2018
  7.   read : io=564764KB, bw=9411.1KB/s, iops=2352 , runt= 60005msec
  8.     slat (usec): min=1 , max=1062 , avg= 6.69, stdev= 6.25
  9.     clat (usec): min=190 , max=206187 , avg=3074.65, stdev=3056.48
  10.      lat (usec): min=236 , max=206191 , avg=3081.71, stdev=3057.06
  11.     clat percentiles (usec):
  12.      | 1.00th=[ 964], 5.00th=[ 1256], 10.00th=[ 1432], 20.00th=[ 1672],
  13.      | 30.00th=[ 1864], 40.00th=[ 2064], 50.00th=[ 2320], 60.00th=[ 2672],
  14.      | 70.00th=[ 3184], 80.00th=[ 3888], 90.00th=[ 5216], 95.00th=[ 6816],
  15.      | 99.00th=[12352], 99.50th=[17792], 99.90th=[39168], 99.95th=[48384],
  16.      | 99.99th=[71168]
  17.     bw (KB/s) : min= 1336, max=12432, per=100.00%, avg=9414.79, stdev=2020.19
  18.   write: io=564484KB, bw=9407.3KB/s, iops=2351 , runt= 60005msec
  19.     slat (usec): min=2 , max=504 , avg= 7.79, stdev= 5.37
  20.     clat (msec): min=1 , max=223 , avg=10.51, stdev= 7.51
  21.      lat (msec): min=1 , max=223 , avg=10.52, stdev= 7.51
  22.     clat percentiles (msec):
  23.      | 1.00th=[ 4], 5.00th=[ 5], 10.00th=[ 6], 20.00th=[ 8],
  24.      | 30.00th=[ 8], 40.00th=[ 9], 50.00th=[ 10], 60.00th=[ 11],
  25.      | 70.00th=[ 12], 80.00th=[ 13], 90.00th=[ 16], 95.00th=[ 19],
  26.      | 99.00th=[ 37], 99.50th=[ 59], 99.90th=[ 100], 99.95th=[ 119],
  27.      | 99.99th=[ 217]
  28.     bw (KB/s) : min= 1264, max=11936, per=100.00%, avg=9412.71, stdev=2000.40
  29.     lat (usec) : 250=0.01%, 500=0.01%, 750=0.18%, 1000=0.46%
  30.     lat (msec) : 2=17.62%, 4=23.73%, 10=36.28%, 20=19.88%, 50=1.49%
  31.     lat (msec) : 100=0.30%, 250=0.05%
  32.   cpu : usr=1.92%, sys=5.85%, ctx=234648, majf=0, minf=23
  33.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=100.0%, >=64=0.0%
  34.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  35.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  36.      issued : total=r=141191/w=141121/d=0, short=r=0/w=0/d=0

  38. Run status group 0 (all jobs):
  39.    READ: io=564764KB, aggrb=9411KB/s, minb=9411KB/s, maxb=9411KB/s, mint=60005msec, maxt=60005msec
  40.   WRITE: io=564484KB, aggrb=9407KB/s, minb=9407KB/s, maxb=9407KB/s, mint=60005msec, maxt=60005msec

  42. Disk stats (read/write):
  43.   vdc: ios=141141/141057, merge=0/0, ticks=432417/1480780, in_queue=1913429, util=99.92%


三、先做個基準測試
    1. 測試1 基準測試線。使用mysql5.7.20，所有的引數不改變，預設安裝，開始壓測，做為基準線。
  2. [root@mysqltest1 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc -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 'tpcc'
  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]: tpcc
  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.   10, 1651(5):3.249|27.343, 1637(1):1.030|32.043, 163(0):0.879|8.304, 164(0):7.105|12.379, 165(0):5.897|12.352
  32.   20, 1574(4):2.149|14.264, 1571(2):0.709|7.167, 159(0):0.423|1.258, 159(0):3.833|14.516, 160(0):5.345|8.108
  33.   30, 1658(6):2.728|8.668, 1659(2):0.738|6.814, 165(0):0.309|2.194, 167(0):3.659|6.411, 165(0):5.908|9.110
  34.   40, 1512(6):2.547|14.762, 1520(0):0.626|4.169, 153(1):3.510|5.191, 150(0):3.918|8.439, 152(0):4.119|7.959
  35.   50, 1578(1):1.987|7.124, 1563(1):1.223|5.834, 157(0):0.570|1.876, 161(0):5.550|7.431, 156(0):5.508|11.684
  36.   60, 1590(4):2.171|17.251, 1604(1):1.187|6.352, 160(0):1.237|3.819, 156(0):3.954|5.426, 159(0):5.458|6.938
  37.   70, 1720(4):2.272|11.161, 1715(5):0.960|7.698, 170(0):0.339|1.274, 167(0):6.051|9.696, 173(0):6.795|7.291
  38.   80, 1418(3):2.259|6.570, 1426(9):1.909|11.341, 143(0):0.309|13.511, 147(0):2.942|3.469, 143(0):6.345|8.970
  39.   90, 1450(4):1.913|7.101, 1436(3):1.321|11.278, 144(2):1.416|6.241, 144(0):3.930|6.874, 143(0):4.731|6.620
  40.  100, 1519(1):2.238|7.276, 1526(2):1.505|35.629, 153(1):0.477|37.459, 151(0):4.468|8.978, 150(0):6.499|10.274
  41.  110, 1646(4):2.130|8.150, 1644(3):1.332|6.713, 164(0):0.322|0.780, 164(0):4.432|8.986, 164(0):5.371|5.991
  42.  120, 1562(5):2.726|10.341, 1555(5):1.379|11.628, 156(0):0.369|1.065, 155(0):8.170|13.778, 157(0):7.203|12.479
  43.  130, 1587(4):2.289|8.842, 1596(0):1.114|4.765, 160(0):0.698|4.218, 159(0):6.088|8.410, 162(0):5.383|7.278
  44.  140, 1605(7):3.445|45.388, 1602(3):1.095|9.531, 160(0):0.356|0.593, 161(0):4.479|15.637, 156(0):8.222|11.534
  45.  150, 1703(4):2.330|8.000, 1703(3):1.025|7.850, 170(0):3.808|4.917, 171(0):5.223|6.879, 171(0):5.580|6.209
  46.  160, 1557(6):2.612|7.549, 1548(2):0.846|6.777, 156(0):0.461|4.569, 156(0):7.600|10.140, 156(0):5.399|12.391
  47.  170, 1569(4):2.435|44.083, 1565(1):0.421|5.988, 155(0):1.447|3.780, 159(0):4.637|5.733, 157(0):4.363|9.454
  48.  180, 1551(3):2.579|6.450, 1552(5):1.003|15.898, 157(1):0.782|5.290, 152(0):4.564|5.552, 156(0):5.417|6.808

  50. STOPPING THREADS................................................................

  52. <Raw Results>
  53.   [0] sc:28375 lt:75 rt:0 fl:0
  54.   [1] sc:28375 lt:48 rt:0 fl:0
  55.   [2] sc:2840 lt:5 rt:0 fl:0
  56.   [3] sc:2843 lt:0 rt:0 fl:0
  57.   [4] sc:2845 lt:0 rt:0 fl:0
  58.  in 180 sec.

  60. <Raw Results2(sum ver.)>
  61.   [0] sc:28375 lt:75 rt:0 fl:0
  62.   [1] sc:28386 lt:48 rt:0 fl:0
  63.   [2] sc:2840 lt:5 rt:0 fl:0
  64.   [3] sc:2843 lt:0 rt:0 fl:0
  65.   [4] sc:2846 lt:0 rt:0 fl:0

  67. <Constraint Check> (all must be [OK])
  68.  [transaction percentage]
  69.         Payment: 43.46% (>=43.0%) [OK]
  70.    Order-Status: 4.35% (>= 4.0%) [OK]
  71.        Delivery: 4.35% (>= 4.0%) [OK]
  72.     Stock-Level: 4.35% (>= 4.0%) [OK]
  73.  [response time (at least 90% passed)]
  74.       New-Order: 99.74% [OK]
  75.         Payment: 99.83% [OK]
  76.    Order-Status: 99.82% [OK]
  77.        Delivery: 100.00% [OK]
  78.     Stock-Level: 100.00% [OK]

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

  1. 測試2：增加引數，tps大幅增加，是有原因的，增大了記憶體
  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

[root@mysqltest1 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc   -utpcc_user -ptpcc_password -w10 -c64 -r60 -l180 -ftpcc_mysql_20180102.log
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value 'localhost'
option d with value 'tpcc'
option u with value 'tpcc_user'
option p with value 'tpcc_password'
option w with value '10'
option c with value '64'
option r with value '60'
option l with value '180'
option f with value 'tpcc_mysql_20180102.log'

     [server]: localhost
     [port]: 3306
     [DBname]: tpcc
       [user]: tpcc_user
       [pass]: tpcc_password
  [warehouse]: 10
 [connection]: 64
     [rampup]: 60 (sec.)
    [measure]: 180 (sec.)


RAMP-UP TIME.(60 sec.)


MEASURING START.


  10, 6797(50):3.566|34.997, 6812(13):1.667|26.716, 681(1):0.727|46.715, 669(0):6.232|28.022, 680(1):7.795|29.600
  20, 7363(42):3.960|35.934, 7364(7):1.657|23.432, 736(0):0.649|3.526, 745(0):5.957|14.961, 735(0):11.048|19.886
  30, 6567(39):3.867|29.925, 6562(13):1.757|28.273, 656(4):0.901|46.593, 654(0):6.298|17.111, 658(1):7.324|29.403
  40, 6931(48):3.961|17.779, 6935(9):1.732|17.332, 694(2):0.768|17.727, 697(0):5.827|23.856, 692(1):10.553|25.895
  50, 7172(43):4.009|16.367, 7168(6):1.711|10.755, 717(2):0.741|14.210, 715(0):5.359|17.750, 716(0):6.791|10.342
  60, 6984(38):3.642|24.669, 6975(12):1.956|47.930, 697(1):0.533|7.859, 699(0):8.608|21.001, 697(2):9.319|25.703
  70, 6833(34):3.684|21.376, 6839(6):1.775|10.699, 685(0):0.821|1.745, 677(0):5.189|11.146, 688(2):8.791|31.218
  80, 6995(33):3.663|22.010, 7003(7):1.920|15.106, 700(1):0.574|17.128, 706(0):5.970|18.297, 697(2):9.337|24.947
  90, 6802(60):4.537|27.083, 6794(9):1.880|19.869, 679(3):0.731|20.736, 681(0):7.712|27.681, 682(2):7.436|24.312
 100, 6620(55):4.384|24.184, 6621(6):1.649|24.546, 663(2):0.715|18.528, 658(0):5.182|12.912, 660(0):7.009|11.943
 110, 7269(35):3.622|28.158, 7263(9):1.547|31.907, 727(1):0.843|20.193, 731(0):7.123|18.813, 729(0):6.869|14.293
 120, 6674(54):4.337|16.877, 6683(13):1.860|16.686, 668(2):0.801|17.818, 661(0):5.807|18.693, 667(1):7.451|21.928
 130, 6646(53):4.292|23.235, 6654(10):2.074|15.955, 663(1):0.803|7.577, 665(0):7.030|15.668, 665(0):8.167|17.875
 140, 6612(36):3.934|25.515, 6604(9):1.581|26.323, 662(2):1.075|12.032, 662(0):9.507|28.782, 661(2):12.012|29.070
 150, 6649(34):3.892|24.229, 6649(9):1.702|21.179, 665(1):0.735|15.010, 657(0):6.528|13.859, 666(1):6.976|21.376
 160, 6917(51):4.508|23.431, 6910(11):1.705|18.645, 691(1):0.621|9.835, 701(0):6.499|26.578, 691(1):7.996|26.723
 170, 7080(39):3.996|21.482, 7086(12):1.645|35.592, 708(3):0.915|12.897, 708(0):7.169|20.410, 707(2):7.942|38.435
 180, 6923(41):3.989|24.458, 6927(8):1.529|26.785, 692(3):0.845|24.342, 690(0):6.383|20.761, 690(2):10.043|30.742


STOPPING THREADS................................................................



  [0] sc:123049  lt:785  rt:0  fl:0 
  [1] sc:123680  lt:169  rt:0  fl:0 
  [2] sc:12354  lt:30  rt:0  fl:0 
  [3] sc:12376  lt:0  rt:0  fl:0 
  [4] sc:12361  lt:20  rt:0  fl:0 
 in 180 sec.



  [0] sc:123051  lt:785  rt:0  fl:0 
  [1] sc:123693  lt:169  rt:0  fl:0 
  [2] sc:12354  lt:30  rt:0  fl:0 
  [3] sc:12377  lt:0  rt:0  fl:0 
  [4] sc:12362  lt:20  rt:0  fl:0 


(all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 99.37%  [OK]
        Payment: 99.86%  [OK]
   Order-Status: 99.76%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 99.84%  [OK]



                 41278.000 TpmC

五、測試3，增加引數innodb_io_capacity,按照理論，tps應該增加的.但是反而下降。看來還是不要增加這個引數，使用預設就好。

  1. innodb_io_capacity = 10000
    innodb_io_capacity_max = 20000
  2. Order-Status: 99.66% [OK]
  3.        Delivery: 100.00% [OK]
  4.     Stock-Level: 99.72% [OK]

  6. <TpmC>
  7.                  38760.668 TpmC



六、增加併發連線到128個
測試4.引數不調整，但是增加併發連線到128個。看tps，沒有很大的提高，併發可以撐得住。對系統影響較小。


七、開啟二進位制日誌功能，以及刷盤方式，對tps影響都是巨大的。這個結論也可以理解，不停的寫二進位制日誌。
  1. 測試5：增加引數，表示開啟二進位制日誌功能，tps效能大幅下跌到24410
  3. server_id=1
  4. binlog_format=row
  5. log_bin = binlog
  6. sync_binlog=1

  7. [root@mysqltest1 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc -utpcc_user -ptpcc_password -w10 -c128 -r60 -l180 -ftpcc_mysql_20180102.log
  8. ***************************************
  9. *** ###easy### TPC-C Load Generator ***
  10. ***************************************
  11. option h with value 'localhost'
  12. option d with value 'tpcc'
  13. option u with value 'tpcc_user'
  14. option p with value 'tpcc_password'
  15. option w with value '10'
  16. option c with value '128'
  17. option r with value '60'
  18. option l with value '180'
  19. option f with value 'tpcc_mysql_20180102.log'
  20. <Parameters>
  21.      [server]: localhost
  22.      [port]: 3306
  23.      [DBname]: tpcc
  24.        [user]: tpcc_user
  25.        [pass]: tpcc_password
  26.   [warehouse]: 10
  27.  [connection]: 128
  28.      [rampup]: 60 (sec.)
  29.     [measure]: 180 (sec.)

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

  33. MEASURING START.

  35.   10, 3931(13):2.979|44.132, 3922(10):1.766|29.830, 394(2):0.654|26.905, 391(0):5.904|24.202, 395(0):6.776|22.796
  36.   20, 4099(11):2.765|17.267, 4093(10):2.040|23.696, 410(1):0.653|38.430, 408(0):5.730|7.003, 408(0):7.204|17.955
  37.   30, 4256(22):3.837|11.348, 4257(7):1.669|33.908, 424(3):0.761|27.601, 428(0):8.500|42.095, 426(0):7.010|14.613
  38.   40, 3990(18):3.424|25.064, 4000(8):1.897|26.505, 400(1):1.507|5.007, 399(0):4.940|10.248, 400(0):7.112|12.905
  39.   50, 4070(12):3.275|30.116, 4059(5):2.109|23.192, 406(1):0.858|19.380, 406(0):5.231|17.642, 407(0):5.631|18.623
  40.   60, 3970(22):4.004|37.077, 3973(11):1.982|27.547, 398(2):1.161|11.440, 397(0):6.875|18.915, 398(1):9.022|24.565
  41.   70, 4217(15):3.268|27.619, 4220(5):1.634|6.306, 421(4):2.497|20.613, 422(0):5.012|8.885, 420(0):7.031|17.698
  42.   80, 4105(7):2.948|17.698, 4094(14):1.663|16.398, 411(1):0.485|39.413, 411(0):3.772|6.978, 411(0):7.311|19.056
  43.   90, 3799(15):3.314|22.506, 3800(8):2.093|28.474, 380(0):1.203|3.083, 380(0):5.032|9.728, 380(0):6.807|12.282
  44.  100, 4128(11):3.165|29.583, 4124(12):2.048|27.292, 412(0):0.513|2.879, 411(0):6.831|12.137, 412(0):5.145|8.175
  45.  110, 4076(14):3.106|18.833, 4077(13):1.986|21.053, 408(2):0.931|19.721, 408(0):6.692|35.645, 408(1):4.777|23.266
  46.  120, 3819(18):3.802|46.336, 3830(9):2.146|37.159, 382(0):0.537|1.274, 384(0):7.491|13.327, 381(0):7.922|11.783
  47.  130, 4505(10):2.687|15.030, 4506(3):1.462|8.646, 452(0):0.720|4.742, 452(0):5.712|15.139, 451(0):5.531|14.615
  48.  140, 3819(13):2.744|12.246, 3818(6):1.610|26.568, 381(2):0.623|17.489, 380(0):8.341|27.408, 381(0):5.743|8.571
  49.  150, 4164(11):2.998|9.588, 4157(9):1.683|24.101, 416(0):0.603|4.809, 416(0):5.463|17.228, 418(0):7.802|15.564
  50.  160, 4181(20):3.546|21.682, 4176(9):2.130|28.635, 418(2):0.610|23.357, 418(0):5.041|11.110, 418(2):7.689|34.054
  51.  170, 4191(12):2.952|19.072, 4200(7):1.517|17.071, 419(0):0.390|0.735, 418(0):4.655|21.160, 418(2):5.307|31.790
  52.  180, 3911(18):3.501|12.336, 3903(2):1.930|5.965, 392(2):0.787|21.876, 393(0):5.736|15.081, 392(2):12.077|21.485

  54. STOPPING THREADS................................................................................................................................

  56. <Raw Results>
  57.   [0] sc:72969 lt:262 rt:0 fl:0
  58.   [1] sc:73061 lt:148 rt:0 fl:0
  59.   [2] sc:7301 lt:23 rt:0 fl:0
  60.   [3] sc:7322 lt:0 rt:0 fl:0
  61.   [4] sc:7316 lt:8 rt:0 fl:0
  62.  in 180 sec.

  64. <Raw Results2(sum ver.)>
  65.   [0] sc:72969 lt:262 rt:0 fl:0
  66.   [1] sc:73072 lt:148 rt:0 fl:0
  67.   [2] sc:7301 lt:23 rt:0 fl:0
  68.   [3] sc:7322 lt:0 rt:0 fl:0
  69.   [4] sc:7316 lt:8 rt:0 fl:0

  71. <Constraint Check> (all must be [OK])
  72.  [transaction percentage]
  73.         Payment: 43.47% (>=43.0%) [OK]
  74.    Order-Status: 4.35% (>= 4.0%) [OK]
  75.        Delivery: 4.35% (>= 4.0%) [OK]
  76.     Stock-Level: 4.35% (>= 4.0%) [OK]
  77.  [response time (at least 90% passed)]
  78.       New-Order: 99.64% [OK]
  79.         Payment: 99.80% [OK]
  80.    Order-Status: 99.69% [OK]
  81.        Delivery: 100.00% [OK]
  82.     Stock-Level: 99.89% [OK]

  84. <TpmC>
  85.                  24410.334 TpmC
來看下二進位制日誌有多少吧：
  1. [root@i-62ujmh37 tmp]# ls /var/lib/mysql/ -lh
  2. total 4.5G
  3. -rw-r----- 1 mysql mysql 56 Jan 26 17:29 auto.cnf
  4. -rw-r----- 1 mysql mysql 1.1G Jan 26 19:15 binlog.000001
  5. -rw-r----- 1 mysql mysql 359M Jan 26 19:16 binlog.000002
  6. -rw-r----- 1 mysql mysql 32 Jan 26 19:15 binlog.index
八、把資料量增加到50個warehouse.這個時候，資料量是這樣的：
現在資料庫tpcc1000有9張表，warehouse10條記錄，stock表有500萬，item表有10萬，order_line1499萬，new_orders45萬，orders150萬，  customer150萬,檢視一下實際表空間的大小3.9G：

  1. [root@mysqltest1 mysql]# ll tpcc -h
  2. total 3.9G
  3. -rw-r----- 1 mysql mysql 9.2K Jan 26 19:25 customer.frm
  4. -rw-r----- 1 mysql mysql 920M Jan 26 19:36 customer.ibd
  5. -rw-r----- 1 mysql mysql 61 Jan 26 17:29 db.opt
  6. -rw-r----- 1 mysql mysql 8.8K Jan 26 19:25 district.frm
  7. -rw-r----- 1 mysql mysql 144K Jan 26 19:31 district.ibd
  8. -rw-r----- 1 mysql mysql 8.7K Jan 26 19:25 history.frm
  9. -rw-r----- 1 mysql mysql 108M Jan 26 19:36 history.ibd
  10. -rw-r----- 1 mysql mysql 8.5K Jan 26 19:25 item.frm
  11. -rw-r----- 1 mysql mysql 17M Jan 26 19:27 item.ibd
  12. -rw-r----- 1 mysql mysql 8.5K Jan 26 19:25 new_orders.frm
  13. -rw-r----- 1 mysql mysql 20M Jan 26 19:50 new_orders.ibd
  14. -rw-r----- 1 mysql mysql 8.8K Jan 26 19:25 order_line.frm
  15. -rw-r----- 1 mysql mysql 1.1G Jan 26 19:50 order_line.ibd
  16. -rw-r----- 1 mysql mysql 8.7K Jan 26 19:25 orders.frm
  17. -rw-r----- 1 mysql mysql 68M Jan 26 19:50 orders.ibd
  18. -rw-r----- 1 mysql mysql 9.0K Jan 26 19:25 stock.frm
  19. -rw-r----- 1 mysql mysql 1.7G Jan 26 19:33 stock.ibd
  20. -rw-r----- 1 mysql mysql 8.7K Jan 26 19:25 warehouse.frm
  21. -rw-r----- 1 mysql mysql 96K Jan 26 19:31 warehouse.ibd
九、來做一個壓測，這裡要說明一下測試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. 測試6：資料增加到50個warehouse,tps下降不大39869
  2. [root@mysqltest1 tpcc-mysql]# ./tpcc_start -hlocalhost -dtpcc -utpcc_user -ptpcc_password -w50 -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 'tpcc'
  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 '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]: tpcc
  19.        [user]: tpcc_user
  20.        [pass]: tpcc_password
  21.   [warehouse]: 50
  22.  [connection]: 128
  23.      [rampup]: 60 (sec.)
  24.     [measure]: 180 (sec.)

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

  28. MEASURING START.

  30.   10, 5964(44):3.567|27.346, 5971(16):1.473|37.696, 598(3):0.772|37.976, 590(0):8.275|74.734, 599(0):6.947|34.844
  31.   20, 6617(40):4.034|28.260, 6602(25):2.080|25.773, 661(2):0.796|18.819, 661(0):8.825|25.459, 656(0):7.561|19.514
  32.   30, 6924(24):3.234|21.948, 6927(20):1.683|21.595, 692(3):0.946|32.696, 695(0):7.829|27.759, 694(0):5.541|8.940
  33.   40, 6534(29):3.403|21.478, 6529(18):1.507|45.933, 655(0):1.020|4.497, 650(0):7.921|21.139, 655(1):8.349|38.484
  34.   50, 6434(43):3.904|18.625, 6434(12):1.843|16.771, 641(1):0.583|9.335, 648(0):7.573|15.584, 637(0):5.727|19.737
  35.   60, 6805(16):3.143|11.632, 6800(18):1.566|34.346, 681(1):0.809|25.468, 676(0):12.476|27.746, 687(0):6.361|19.708
  36.   70, 6517(26):3.433|31.223, 6516(19):1.840|21.537, 652(0):0.871|2.926, 652(0):8.516|21.407, 652(0):5.401|11.429
  37.   80, 6860(27):3.178|26.813, 6858(21):1.485|21.634, 686(1):1.155|23.202, 689(0):6.507|27.872, 686(0):6.928|10.673
  38.   90, 6817(35):3.539|17.073, 6823(11):1.869|20.432, 682(1):0.603|9.020, 677(0):6.157|10.596, 681(0):6.873|11.045
  39.  100, 6775(36):3.540|19.590, 6777(18):1.701|32.672, 677(2):1.846|9.935, 685(0):13.251|58.494, 673(1):6.690|29.498
  40.  110, 6846(27):3.664|15.460, 6848(10):1.686|9.462, 683(1):0.482|5.604, 682(0):6.576|33.713, 685(0):7.309|10.518
  41.  120, 6766(30):3.420|15.297, 6757(22):1.890|14.911, 680(1):1.731|5.218, 682(0):11.678|31.201, 683(0):6.862|11.376
  42.  130, 6732(36):4.010|17.140, 6712(12):1.677|43.282, 671(1):0.628|20.758, 666(0):8.972|15.452, 672(1):6.561|29.093
  43.  140, 6665(24):3.183|18.097, 6681(14):1.381|23.611, 667(1):0.622|7.625, 669(0):10.305|25.056, 666(0):7.474|10.014
  44.  150, 6829(31):3.540|16.230, 6835(8):1.672|19.174, 684(4):1.254|37.092, 686(0):10.967|17.614, 682(1):5.912|23.496
  45.  160, 6478(42):4.048|37.318, 6469(21):1.796|33.043, 644(1):0.888|7.388, 640(0):7.508|15.716, 645(1):6.955|20.289
  46.  170, 6504(36):4.007|48.731, 6509(13):1.710|28.571, 654(3):0.902|14.536, 646(0):9.516|22.571, 655(1):5.545|25.944
  47.  180, 6540(31):3.513|31.792, 6519(12):1.993|28.497, 653(0):0.439|0.880, 668(0):7.844|27.393, 651(1):7.018|21.431

  49. STOPPING THREADS................................................................................................................................

  51. <Raw Results>
  52.   [0] sc:119030 lt:577 rt:0 fl:0
  53.   [1] sc:119277 lt:290 rt:0 fl:0
  54.   [2] sc:11935 lt:26 rt:0 fl:0
  55.   [3] sc:11962 lt:0 rt:0 fl:0
  56.   [4] sc:11952 lt:7 rt:0 fl:0
  57.  in 180 sec.

  59. <Raw Results2(sum ver.)>
  60.   [0] sc:119030 lt:577 rt:0 fl:0
  61.   [1] sc:119295 lt:290 rt:0 fl:0
  62.   [2] sc:11935 lt:26 rt:0 fl:0
  63.   [3] sc:11962 lt:0 rt:0 fl:0
  64.   [4] sc:11952 lt:7 rt:0 fl:0

  66. <Constraint Check> (all must be [OK])
  67.  [transaction percentage]
  68.         Payment: 43.47% (>=43.0%) [OK]
  69.    Order-Status: 4.35% (>= 4.0%) [OK]
  70.        Delivery: 4.35% (>= 4.0%) [OK]
  71.     Stock-Level: 4.35% (>= 4.0%) [OK]
  72.  [response time (at least 90% passed)]
  73.       New-Order: 99.52% [OK]
  74.         Payment: 99.76% [OK]
  75.    Order-Status: 99.78% [OK]
  76.        Delivery: 100.00% [OK]
  77.     Stock-Level: 99.94% [OK]

  79. <TpmC>
  80.                  39869.000 TpmC





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

相關文章