mysql壓力測試在青雲PCIE盤sysbench版本

e71hao發表於2018-01-26
MySql
對比測試“ mysql壓力測試在京東雲ssd雲盤sysbench版本 ” http://blog.itpub.net/30393770/viewspace-2150070/
一、環境：青雲Centos6.8 ，cpu16核,記憶體32G，SAS 3G,轉速未知 ，SSD雲盤300G
     mysql 版本是：5.7.20 ，預設rpm安裝,單例項。
     壓測工具是：sysbench1.0.9
mysql壓力測試在京東雲ssd雲盤fio測試4K隨機讀寫iops是

  1. [root@mysqltest1 tmp]# fio fio.conf
  2. 4k_rand_write: (g=0): rw=randwrite, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=32
  3. 4k_rand_read: (g=1): rw=randread, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=32
  4. 4k_80_rand_read: (g=2): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=32
  5. 8k_rand_write: (g=3): rw=randwrite, bs=8K-8K/8K-8K/8K-8K, ioengine=libaio, iodepth=32
  6. 8k_rand_read: (g=4): rw=randread, bs=8K-8K/8K-8K/8K-8K, ioengine=libaio, iodepth=32
  7. 8k_80_rand_read: (g=5): rw=randrw, bs=8K-8K/8K-8K/8K-8K, ioengine=libaio, iodepth=32
  8. 64k_rand_write: (g=6): rw=randwrite, bs=64K-64K/64K-64K/64K-64K, ioengine=libaio, iodepth=32
  9. 64k_rand_read: (g=7): rw=randread, bs=64K-64K/64K-64K/64K-64K, ioengine=libaio, iodepth=32
  10. 512k_seq_write: (g=8): rw=write, bs=512K-512K/512K-512K/512K-512K, ioengine=libaio, iodepth=32
  11. 512k_seq_read: (g=9): rw=read, bs=512K-512K/512K-512K/512K-512K, ioengine=libaio, iodepth=32
  12. fio-2.0.13
  13. Starting 10 threads
  14. Jobs: 1 (f=0): [_________/] [-.-% done] [0K/41650K/0K /s] [0 /81 /0 iops] [eta 02m:05s]
  15. 4k_rand_write: (groupid=0, jobs=1): err= 0: pid=15681: Fri Jan 26 20:59:57 2018
  16.   write: io=5120.2MB, bw=58351KB/s, iops=14587 , runt= 89853msec
  17.     slat (usec): min=2 , max=145070 , avg= 9.16, stdev=333.61
  18.     clat (usec): min=1 , max=944673 , avg=2182.13, stdev=10050.99
  19.      lat (usec): min=40 , max=944683 , avg=2191.83, stdev=10062.63
  20.     clat percentiles (usec):
  21.      | 1.00th=[ 70], 5.00th=[ 123], 10.00th=[ 185], 20.00th=[ 290],
  22.      | 30.00th=[ 378], 40.00th=[ 462], 50.00th=[ 548], 60.00th=[ 676],
  23.      | 70.00th=[ 900], 80.00th=[ 1432], 90.00th=[ 3664], 95.00th=[ 7456],
  24.      | 99.00th=[30848], 99.50th=[57088], 99.90th=[128512], 99.95th=[164864],
  25.      | 99.99th=[333824]
  26.     bw (KB/s) : min= 0, max=155500, per=100.00%, avg=58393.10, stdev=30564.33
  27.     lat (usec) : 2=0.01%, 4=0.01%, 20=0.01%, 50=0.06%, 100=3.13%
  28.     lat (usec) : 250=12.75%, 500=28.71%, 750=19.40%, 1000=8.75%
  29.     lat (msec) : 2=11.69%, 4=6.12%, 10=6.07%, 20=1.79%, 50=0.94%
  30.     lat (msec) : 100=0.41%, 250=0.17%, 500=0.02%, 750=0.01%, 1000=0.01%
  31.   cpu : usr=6.75%, sys=17.53%, ctx=305533, majf=18446744073709551600, minf=18446744073709538833
  32.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=106.3%, >=64=0.0%
  33.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  34.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  35.      issued : total=r=0/w=1310720/d=0, short=r=0/w=0/d=0
  36. 4k_rand_read: (groupid=1, jobs=1): err= 0: pid=15743: Fri Jan 26 20:59:57 2018
  37.   read : io=5120.2MB, bw=294369KB/s, iops=73590 , runt= 17811msec
  38.     slat (usec): min=1 , max=19630 , avg= 3.34, stdev=31.93
  39.     clat (usec): min=24 , max=62910 , avg=430.26, stdev=981.54
  40.      lat (usec): min=29 , max=62912 , avg=433.89, stdev=982.23
  41.     clat percentiles (usec):
  42.      | 1.00th=[ 95], 5.00th=[ 121], 10.00th=[ 143], 20.00th=[ 181],
  43.      | 30.00th=[ 215], 40.00th=[ 247], 50.00th=[ 278], 60.00th=[ 310],
  44.      | 70.00th=[ 354], 80.00th=[ 414], 90.00th=[ 580], 95.00th=[ 1004],
  45.      | 99.00th=[ 3632], 99.50th=[ 4320], 99.90th=[12224], 99.95th=[23936],
  46.      | 99.99th=[31360]
  47.     bw (KB/s) : min= 0, max=479984, per=96.71%, avg=284680.75, stdev=152049.46
  48.     lat (usec) : 50=0.02%, 100=1.38%, 250=39.40%, 500=46.22%, 750=6.01%
  49.     lat (usec) : 1000=1.97%
  50.     lat (msec) : 2=2.01%, 4=2.28%, 10=0.60%, 20=0.05%, 50=0.07%
  51.     lat (msec) : 100=0.01%
  52.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073709361396, majf=18446744073709551600, minf=18446744073709538425
  53.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=130.0%, >=64=0.0%
  54.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  55.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  56.      issued : total=r=1310720/w=0/d=0, short=r=0/w=0/d=0
  57. 4k_80_rand_read: (groupid=2, jobs=1): err= 0: pid=15758: Fri Jan 26 20:59:57 2018
  58.   read : io=4104.3MB, bw=146912KB/s, iops=36727 , runt= 28607msec
  59.     slat (usec): min=1 , max=70525 , avg= 4.25, stdev=110.87
  60.     clat (usec): min=19 , max=401067 , avg=617.26, stdev=2862.62
  61.      lat (usec): min=30 , max=401070 , avg=621.94, stdev=2866.92
  62.     clat percentiles (usec):
  63.      | 1.00th=[ 81], 5.00th=[ 127], 10.00th=[ 165], 20.00th=[ 217],
  64.      | 30.00th=[ 255], 40.00th=[ 290], 50.00th=[ 326], 60.00th=[ 370],
  65.      | 70.00th=[ 422], 80.00th=[ 502], 90.00th=[ 708], 95.00th=[ 1176],
  66.      | 99.00th=[ 5664], 99.50th=[11712], 99.90th=[39680], 99.95th=[56064],
  67.      | 99.99th=[96768]
  68.     bw (KB/s) : min= 0, max=351824, per=98.86%, avg=145235.61, stdev=64144.88
  69.   write: io=1015.1MB, bw=36365KB/s, iops=9090 , runt= 28607msec
  70.     slat (usec): min=2 , max=27722 , avg= 6.04, stdev=128.05
  71.     clat (usec): min=43 , max=365921 , avg=994.55, stdev=3992.50
  72.      lat (usec): min=48 , max=365924 , avg=1001.02, stdev=3996.23
  73.     clat percentiles (usec):
  74.      | 1.00th=[ 117], 5.00th=[ 187], 10.00th=[ 227], 20.00th=[ 278],
  75.      | 30.00th=[ 322], 40.00th=[ 366], 50.00th=[ 422], 60.00th=[ 498],
  76.      | 70.00th=[ 612], 80.00th=[ 772], 90.00th=[ 1192], 95.00th=[ 2384],
  77.      | 99.00th=[12864], 99.50th=[21888], 99.90th=[57600], 99.95th=[72192],
  78.      | 99.99th=[111104]
  79.     bw (KB/s) : min= 128, max=69520, per=100.00%, avg=36428.64, stdev=14015.90
  80.     lat (usec) : 20=0.01%, 50=0.09%, 100=1.74%, 250=23.87%, 500=50.12%
  81.     lat (usec) : 750=12.70%, 1000=4.14%
  82.     lat (msec) : 2=3.89%, 4=1.63%, 10=1.11%, 20=0.39%, 50=0.26%
  83.     lat (msec) : 100=0.06%, 250=0.01%, 500=0.01%
  84.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073709233054, majf=18446744073709551600, minf=18446744073709538270
  85.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=123.1%, >=64=0.0%
  86.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  87.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  88.      issued : total=r=1050665/w=260055/d=0, short=r=0/w=0/d=0
  89. 8k_rand_write: (groupid=3, jobs=1): err= 0: pid=15782: Fri Jan 26 20:59:57 2018
  90.   write: io=5120.3MB, bw=79614KB/s, iops=9951 , runt= 65857msec
  91.     slat (usec): min=2 , max=81097 , avg= 9.27, stdev=223.45
  92.     clat (usec): min=1 , max=816154 , avg=3203.88, stdev=9077.98
  93.      lat (usec): min=39 , max=816160 , avg=3213.61, stdev=9082.30
  94.     clat percentiles (usec):
  95.      | 1.00th=[ 67], 5.00th=[ 101], 10.00th=[ 151], 20.00th=[ 266],
  96.      | 30.00th=[ 382], 40.00th=[ 490], 50.00th=[ 612], 60.00th=[ 836],
  97.      | 70.00th=[ 1688], 80.00th=[ 6752], 90.00th=[ 7968], 95.00th=[11072],
  98.      | 99.00th=[34048], 99.50th=[50432], 99.90th=[88576], 99.95th=[114176],
  99.      | 99.99th=[250880]
  100.     bw (KB/s) : min= 1, max=225472, per=99.36%, avg=79101.62, stdev=32586.73
  101.     lat (usec) : 2=0.01%, 4=0.01%, 20=0.01%, 50=0.05%, 100=4.78%
  102.     lat (usec) : 250=13.67%, 500=22.52%, 750=16.18%, 1000=6.20%
  103.     lat (msec) : 2=8.31%, 4=4.06%, 10=18.59%, 20=3.56%, 50=1.57%
  104.     lat (msec) : 100=0.43%, 250=0.07%, 500=0.01%, 750=0.01%, 1000=0.01%
  105.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073709154043, majf=18446744073709551600, minf=18446744073709538133
  106.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=117.5%, >=64=0.0%
  107.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  108.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  109.      issued : total=r=0/w=655360/d=0, short=r=0/w=0/d=0
  110. 8k_rand_read: (groupid=4, jobs=1): err= 0: pid=15827: Fri Jan 26 20:59:57 2018
  111.   read : io=5120.3MB, bw=576802KB/s, iops=72096 , runt= 9090msec
  112.     slat (usec): min=2 , max=24613 , avg= 3.51, stdev=30.98
  113.     clat (usec): min=32 , max=39094 , avg=439.00, stdev=891.92
  114.      lat (usec): min=36 , max=39097 , avg=442.82, stdev=892.69
  115.     clat percentiles (usec):
  116.      | 1.00th=[ 95], 5.00th=[ 126], 10.00th=[ 151], 20.00th=[ 197],
  117.      | 30.00th=[ 237], 40.00th=[ 274], 50.00th=[ 306], 60.00th=[ 346],
  118.      | 70.00th=[ 394], 80.00th=[ 462], 90.00th=[ 628], 95.00th=[ 924],
  119.      | 99.00th=[ 3472], 99.50th=[ 4384], 99.90th=[11968], 99.95th=[22656],
  120.      | 99.99th=[31872]
  121.     bw (KB/s) : min= 1, max=818384, per=94.64%, avg=545872.89, stdev=245409.15
  122.     lat (usec) : 50=0.01%, 100=1.39%, 250=32.33%, 500=49.82%, 750=9.22%
  123.     lat (usec) : 1000=2.77%
  124.     lat (msec) : 2=2.25%, 4=1.54%, 10=0.57%, 20=0.06%, 50=0.06%
  125.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073708955600, majf=18446744073709551600, minf=18446744073709537814
  126.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=171.2%, >=64=0.0%
  127.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  128.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  129.      issued : total=r=655360/w=0/d=0, short=r=0/w=0/d=0
  130. 8k_80_rand_read: (groupid=5, jobs=1): err= 0: pid=15842: Fri Jan 26 20:59:57 2018
  131.   read : io=4113.8MB, bw=298394KB/s, iops=37297 , runt= 14117msec
  132.     slat (usec): min=2 , max=78188 , avg= 4.44, stdev=131.92
  133.     clat (usec): min=25 , max=294855 , avg=601.62, stdev=2368.68
  134.      lat (usec): min=28 , max=294859 , avg=606.45, stdev=2374.40
  135.     clat percentiles (usec):
  136.      | 1.00th=[ 100], 5.00th=[ 143], 10.00th=[ 177], 20.00th=[ 225],
  137.      | 30.00th=[ 262], 40.00th=[ 298], 50.00th=[ 334], 60.00th=[ 374],
  138.      | 70.00th=[ 422], 80.00th=[ 502], 90.00th=[ 716], 95.00th=[ 1160],
  139.      | 99.00th=[ 5344], 99.50th=[12224], 99.90th=[35584], 99.95th=[45824],
  140.      | 99.99th=[72192]
  141.     bw (KB/s) : min= 1, max=678759, per=100.00%, avg=298903.18, stdev=115771.56
  142.   write: io=1006.6MB, bw=73011KB/s, iops=9125 , runt= 14117msec
  143.     slat (usec): min=2 , max=25211 , avg= 7.66, stdev=169.94
  144.     clat (usec): min=53 , max=200702 , avg=1024.20, stdev=3821.23
  145.      lat (usec): min=58 , max=200716 , avg=1031.48, stdev=3826.15
  146.     clat percentiles (usec):
  147.      | 1.00th=[ 151], 5.00th=[ 203], 10.00th=[ 235], 20.00th=[ 278],
  148.      | 30.00th=[ 318], 40.00th=[ 362], 50.00th=[ 410], 60.00th=[ 478],
  149.      | 70.00th=[ 596], 80.00th=[ 772], 90.00th=[ 1256], 95.00th=[ 2544],
  150.      | 99.00th=[14272], 99.50th=[24448], 99.90th=[51456], 99.95th=[69120],
  151.      | 99.99th=[109056]
  152.     bw (KB/s) : min=13852, max=132304, per=100.00%, avg=73149.21, stdev=21764.62
  153.     lat (usec) : 50=0.03%, 100=0.79%, 250=23.16%, 500=52.33%, 750=12.16%
  154.     lat (usec) : 1000=4.02%
  155.     lat (msec) : 2=4.16%, 4=1.62%, 10=0.96%, 20=0.43%, 50=0.29%
  156.     lat (msec) : 100=0.04%, 250=0.01%, 500=0.01%
  157.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073708882383, majf=18446744073709551600, minf=18446744073709537694
  158.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=140.6%, >=64=0.0%
  159.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  160.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  161.      issued : total=r=526535/w=128825/d=0, short=r=0/w=0/d=0
  162. 64k_rand_write: (groupid=6, jobs=1): err= 0: pid=15859: Fri Jan 26 20:59:57 2018
  163.   write: io=5121.1MB, bw=147939KB/s, iops=2310 , runt= 35453msec
  164.     slat (usec): min=6 , max=36057 , avg=21.32, stdev=164.54
  165.     clat (usec): min=1 , max=1077.5K, avg=13861.11, stdev=40752.02
  166.      lat (usec): min=82 , max=1077.5K, avg=13882.05, stdev=40751.74
  167.     clat percentiles (usec):
  168.      | 1.00th=[ 109], 5.00th=[ 163], 10.00th=[ 374], 20.00th=[ 7392],
  169.      | 30.00th=[ 7840], 40.00th=[ 7904], 50.00th=[ 7968], 60.00th=[ 8032],
  170.      | 70.00th=[ 8768], 80.00th=[12608], 90.00th=[23168], 95.00th=[39680],
  171.      | 99.00th=[87552], 99.50th=[152576], 99.90th=[544768], 99.95th=[1056768],
  172.      | 99.99th=[1056768]
  173.     bw (KB/s) : min= 10, max=177181, per=98.61%, avg=145887.94, stdev=38610.32
  174.     lat (usec) : 2=0.01%, 100=0.44%, 250=8.17%, 500=2.15%, 750=1.83%
  175.     lat (usec) : 1000=0.99%
  176.     lat (msec) : 2=1.03%, 4=1.18%, 10=55.41%, 20=14.26%, 50=12.19%
  177.     lat (msec) : 100=1.48%, 250=0.49%, 500=0.22%, 750=0.10%, 1000=0.01%
  178.     lat (msec) : 2000=0.09%
  179.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073708777240, majf=18446744073709551600, minf=18446744073709537615
  180.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=112.6%, >=64=0.0%
  181.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  182.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  183.      issued : total=r=0/w=81920/d=0, short=r=0/w=0/d=0
  184. 64k_rand_read: (groupid=7, jobs=1): err= 0: pid=15890: Fri Jan 26 20:59:57 2018
  185.   read : io=5121.1MB, bw=3157.9MB/s, iops=50524 , runt= 1622msec
  186.     slat (usec): min=4 , max=1046 , avg= 6.77, stdev= 8.92
  187.     clat percentiles (usec):
  188.      | 1.00th=[ 0], 5.00th=[ 0], 10.00th=[ 0], 20.00th=[ 0],
  189.      | 30.00th=[ 0], 40.00th=[ 0], 50.00th=[ 0], 60.00th=[ 0],
  190.      | 70.00th=[ 0], 80.00th=[ 0], 90.00th=[ 0], 95.00th=[ 0],
  191.      | 99.00th=[ 0], 99.50th=[ 0], 99.90th=[ 0], 99.95th=[ 0],
  192.      | 99.99th=[ 0]
  193.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073708741760, majf=18446744073709551600, minf=18446744073709537406
  194.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=100.0%, >=64=0.0%
  195.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  196.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  197.      issued : total=r=81951/w=0/d=0, short=r=0/w=0/d=0
  198. 512k_seq_write: (groupid=8, jobs=1): err= 0: pid=15891: Fri Jan 26 20:59:57 2018
  199.   write: io=3780.0MB, bw=193410KB/s, iops=376 , runt= 20013msec
  200.     slat (usec): min=64 , max=22107 , avg=276.70, stdev=450.24
  201.     clat (usec): min=602 , max=1422.1K, avg=84639.19, stdev=88630.58
  202.      lat (usec): min=758 , max=1423.6K, avg=84912.13, stdev=88645.57
  203.     clat percentiles (msec):
  204.      | 1.00th=[ 8], 5.00th=[ 25], 10.00th=[ 39], 20.00th=[ 49],
  205.      | 30.00th=[ 57], 40.00th=[ 64], 50.00th=[ 69], 60.00th=[ 76],
  206.      | 70.00th=[ 84], 80.00th=[ 97], 90.00th=[ 129], 95.00th=[ 178],
  207.      | 99.00th=[ 529], 99.50th=[ 709], 99.90th=[ 1139], 99.95th=[ 1401],
  208.      | 99.99th=[ 1418]
  209.     bw (KB/s) : min= 85, max=205824, per=97.49%, avg=188546.65, stdev=31213.27
  210.     lat (usec) : 750=0.08%, 1000=0.07%
  211.     lat (msec) : 2=0.21%, 4=0.21%, 10=1.29%, 20=1.85%, 50=17.48%
  212.     lat (msec) : 100=60.99%, 250=15.79%, 500=1.39%, 750=0.57%, 1000=0.24%
  213.     lat (msec) : 2000=0.24%
  214.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073708738428, majf=18446744073709551600, minf=18446744073709536444
  215.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.2%, 32=135.6%, >=64=0.0%
  216.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  217.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  218.      issued : total=r=0/w=7529/d=0, short=r=0/w=0/d=0
  219. 512k_seq_read: (groupid=9, jobs=1): err= 0: pid=15915: Fri Jan 26 20:59:57 2018
  220.   read : io=5120.0MB, bw=3784.2MB/s, iops=7568 , runt= 1353msec
  221.     slat (usec): min=23 , max=13263 , avg=58.10, stdev=141.77
  222.     clat percentiles (usec):
  223.      | 1.00th=[ 0], 5.00th=[ 0], 10.00th=[ 0], 20.00th=[ 0],
  224.      | 30.00th=[ 0], 40.00th=[ 0], 50.00th=[ 0], 60.00th=[ 0],
  225.      | 70.00th=[ 0], 80.00th=[ 0], 90.00th=[ 0], 95.00th=[ 0],
  226.      | 99.00th=[ 0], 99.50th=[ 0], 99.90th=[ 0], 99.95th=[ 0],
  227.      | 99.99th=[ 0]
  228.   cpu : usr=0.00%, sys=0.00%, ctx=18446744073708730103, majf=18446744073709551600, minf=18446744073709536812
  229.   IO depths : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.2%, 32=99.7%, >=64=0.0%
  230.      submit : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
  231.      complete : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%
  232.      issued : total=r=10240/w=0/d=0, short=r=0/w=0/d=0

  234. Run status group 0 (all jobs):
  235.   WRITE: io=5120.2MB, aggrb=58350KB/s, minb=58350KB/s, maxb=58350KB/s, mint=89853msec, maxt=89853msec

  237. Run status group 1 (all jobs):
  238.    READ: io=5120.2MB, aggrb=294368KB/s, minb=294368KB/s, maxb=294368KB/s, mint=17811msec, maxt=17811msec

  240. Run status group 2 (all jobs):
  241.    READ: io=4104.3MB, aggrb=146912KB/s, minb=146912KB/s, maxb=146912KB/s, mint=28607msec, maxt=28607msec
  242.   WRITE: io=1015.1MB, aggrb=36364KB/s, minb=36364KB/s, maxb=36364KB/s, mint=28607msec, maxt=28607msec

  244. Run status group 3 (all jobs):
  245.   WRITE: io=5120.3MB, aggrb=79613KB/s, minb=79613KB/s, maxb=79613KB/s, mint=65857msec, maxt=65857msec

  247. Run status group 4 (all jobs):
  248.    READ: io=5120.3MB, aggrb=576801KB/s, minb=576801KB/s, maxb=576801KB/s, mint=9090msec, maxt=9090msec

  250. Run status group 5 (all jobs):
  251.    READ: io=4113.8MB, aggrb=298394KB/s, minb=298394KB/s, maxb=298394KB/s, mint=14117msec, maxt=14117msec
  252.   WRITE: io=1006.6MB, aggrb=73010KB/s, minb=73010KB/s, maxb=73010KB/s, mint=14117msec, maxt=14117msec

  254. Run status group 6 (all jobs):
  255.   WRITE: io=5121.1MB, aggrb=147938KB/s, minb=147938KB/s, maxb=147938KB/s, mint=35453msec, maxt=35453msec

  257. Run status group 7 (all jobs):
  258.    READ: io=5121.1MB, aggrb=3157.9MB/s, minb=3157.9MB/s, maxb=3157.9MB/s, mint=1622msec, maxt=1622msec

  260. Run status group 8 (all jobs):
  261.   WRITE: io=3780.0MB, aggrb=193410KB/s, minb=193410KB/s, maxb=193410KB/s, mint=20013msec, maxt=20013msec

  263. Run status group 9 (all jobs):
  264.    READ: io=5120.0MB, aggrb=3784.2MB/s, minb=3784.2MB/s, maxb=3784.2MB/s, mint=1353msec, maxt=1353msec

  266. Disk stats (read/write):
  267.   vdd: ios=4952095/2768934, merge=0/0, ticks=2107333/7598007, in_queue=9703486, util=98.77%



  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. 插入資料：
  2. [root@mysqltest1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/mysql/mysql1/mysql.sock --db-driver=mysql --mysql-host=localhost --mysql-port=3307 --mysql-db=sbtest --mysql-user=root --mysql-password= --table_size=10000000 --tables=10 --threads=20 --time=60 --report-interval=30 prepare
  3. sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

  5. Initializing worker threads...

  7. Creating table 'sbtest4'...Creating table 'sbtest10'...
  8. Creating table 'sbtest7'...

  10. Creating table 'sbtest5'...
  11. Creating table 'sbtest8'...
  12. Creating table 'sbtest6'...
  13. Creating table 'sbtest2'...
  14. Creating table 'sbtest3'...
  15. Creating table 'sbtest1'...
  16. Creating table 'sbtest9'...
  17. Inserting 10000000 records into 'sbtest5'
  18. Inserting 10000000 records into 'sbtest6'
  19. Inserting 10000000 records into 'sbtest10'
  20. Inserting 10000000 records into 'sbtest8'
  21. Inserting 10000000 records into 'sbtest1'
  22. Inserting 10000000 records into 'sbtest7'
  23. Inserting 10000000 records into 'sbtest2'
  24. Inserting 10000000 records into 'sbtest9'
  25. Inserting 10000000 records into 'sbtest3'
  26. Inserting 10000000 records into 'sbtest4'
  27. Creating a secondary index on 'sbtest5'...
  28. Creating a secondary index on 'sbtest6'...
  29. Creating a secondary index on 'sbtest9'...
  30. Creating a secondary index on 'sbtest3'...
  31. Creating a secondary index on 'sbtest8'...
  32. Creating a secondary index on 'sbtest2'...
  33. Creating a secondary index on 'sbtest4'...
  34. Creating a secondary index on 'sbtest1'...
  35. Creating a secondary index on 'sbtest10'...
  36. Creating a secondary index on 'sbtest7'...
看看資料量多少

  1. [root@mysqltest1 mysql1]# ls sbtest/ -lh
  2. total 24G
  3. -rw-r----- 1 mysql mysql 61 Jan 26 20:13 db.opt
  4. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest10.frm
  5. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest10.ibd
  6. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest1.frm
  7. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest1.ibd
  8. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest2.frm
  9. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest2.ibd
  10. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest3.frm
  11. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest3.ibd
  12. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest4.frm
  13. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest4.ibd
  14. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest5.frm
  15. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest5.ibd
  16. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest6.frm
  17. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest6.ibd
  18. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest7.frm
  19. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest7.ibd
  20. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest8.frm
  21. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest8.ibd
  22. -rw-r----- 1 mysql mysql 8.5K Jan 26 20:26 sbtest9.frm
  23. -rw-r----- 1 mysql mysql 2.4G Jan 26 20:30 sbtest9.ibd


二、測試1，首先做一個基準測試，慢慢往上加併發連線。10個表，1000萬資料，20個執行緒，60秒。tps是1345

  1. 測試1
  2. [root@mysqltest1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/mysql/mysql1/mysql.sock --db-driver=mysql --mysql-host=localhost --mysql-port=3307 --mysql-db=sbtest --mysql-user=root --mysql-password= --table_size=10000000 --tables=10 --threads=20 --time=60 --report-interval=30 run
  3. sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

  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: 1342.29 qps: 26855.91 (r/w/o: 18800.00/5370.71/2685.20) lat (ms,95%): 29.19 err/s: 0.00 reconn/s: 0.00
  16. [ 60s ] thds: 20 tps: 1349.15 qps: 26982.87 (r/w/o: 18887.91/5396.65/2698.31) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
  17. SQL statistics:
  18.     queries performed:
  19.         read: 1130710
  20.         write: 323060
  21.         other: 161530
  22.         total: 1615300
  23.     transactions: 80765 (1345.79 per sec.)
  24.     queries: 1615300 (26915.77 per sec.)
  25.     ignored errors: 0 (0.00 per sec.)
  26.     reconnects: 0 (0.00 per sec.)

  28. General statistics:
  29.     total time: 60.0109s
  30.     total number of events: 80765

  32. Latency (ms):
  33.          min: 4.10
  34.          avg: 14.85
  35.          max: 384.70
  36.          95th percentile: 30.26
  37.          sum: 1199742.53

  39. Threads fairness:
  40.     events (avg/stddev): 4038.2500/37.53
  41.     execution time (avg/stddev): 59.9871/0.00
三、測試2 tps 1364，這個和京東雲SSD上的測試很不一樣呀。增加執行緒了，tps也沒有什麼變化。我要重啟下mysql，再做一遍測試tps為1695，增加了一些。

  1. 測試2併發執行緒變為100個
  2. [root@mysqltest1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/mysql/mysql1/mysql.sock --db-driver=mysql --mysql-host=localhost --mysql-port=3307 --mysql-db=sbtest --mysql-user=root --mysql-password= --table_size=10000000 --tables=10 --threads=100 --time=120 --report-interval=30 run
  3. sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

  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: 1761.53 qps: 35277.53 (r/w/o: 24702.08/7049.14/3526.32) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
  16. [ 60s ] thds: 100 tps: 1260.24 qps: 25208.81 (r/w/o: 17643.48/5044.88/2520.44) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
  17. [ 90s ] thds: 100 tps: 1187.10 qps: 23728.35 (r/w/o: 16612.43/4741.61/2374.30) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
  18. [ 120s ] thds: 100 tps: 1250.60 qps: 25011.38 (r/w/o: 17507.52/5002.92/2500.94) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
  19. SQL statistics:
  20.     queries performed:
  21.         read: 2294558
  22.         write: 655588
  23.         other: 327794
  24.         total: 3277940
  25.     transactions: 163897 (1364.83 per sec.)
  26.     queries: 3277940 (27296.64 per sec.)
  27.     ignored errors: 0 (0.00 per sec.)
  28.     reconnects: 0 (0.00 per sec.)

  30. General statistics:
  31.     total time: 120.0795s
  32.     total number of events: 163897

  34. Latency (ms):
  35.          min: 4.55
  36.          avg: 73.23
  37.          max: 3815.25
  38.          95th percentile: 167.44
  39.          sum: 12002493.01

  41. Threads fairness:
  42.     events (avg/stddev): 1638.9700/24.21
  43.     execution time (avg/stddev): 120.0249/0.0
四、測試3，執行緒達到256個，tps結果為2219，相比測試2的1695tps，tps還是上升了的。併發執行緒翻倍，再往上升

  1. 測試3
  2. [root@mysqltest1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/mysql/mysql1/mysql.sock --db-driver=mysql --mysql-host=localhost --mysql-port=3307 --mysql-db=sbtest --mysql-user=root --mysql-password= --table_size=10000000 --tables=10 --threads=256 --time=120 --report-interval=30 run
  3. sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

  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: 2223.58 qps: 44595.77 (r/w/o: 31228.84/8911.41/4455.52) lat (ms,95%): 231.53 err/s: 0.00 reconn/s: 0.00
  16. [ 60s ] thds: 256 tps: 2253.39 qps: 45062.21 (r/w/o: 31550.19/9005.08/4506.94) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
  17. [ 90s ] thds: 256 tps: 2208.14 qps: 44187.36 (r/w/o: 30926.61/8844.51/4416.24) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
  18. [ 120s ] thds: 256 tps: 2198.21 qps: 43939.04 (r/w/o: 30751.77/8792.71/4394.56) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00
  19. SQL statistics:
  20.     queries performed:
  21.         read: 3734444
  22.         write: 1066984
  23.         other: 533492
  24.         total: 5334920
  25.     transactions: 266746 (2219.65 per sec.)
  26.     queries: 5334920 (44392.96 per sec.)
  27.     ignored errors: 0 (0.00 per sec.)
  28.     reconnects: 0 (0.00 per sec.)

  30. General statistics:
  31.     total time: 120.1728s
  32.     total number of events: 266746

  34. Latency (ms):
  35.          min: 4.75
  36.          avg: 115.19
  37.          max: 1242.44
  38.          95th percentile: 231.53
  39.          sum: 30725391.47

  41. Threads fairness:
  42.     events (avg/stddev): 1041.9766/19.98
  43.     execution time (avg/stddev): 120.0211/0.02
五、測試4，tps為1998，有下降，看來應該達到極限了。

  1. [root@mysqltest1 mysql]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-socket=/mysql/mysql1/mysql.sock --db-driver=mysql --mysql-host=localhost --mysql-port=3307 --mysql-db=sbtest --mysql-user=root --mysql-password= --table_size=10000000 --tables=10 --threads=512 --time=120 --report-interval=30 run
  2. sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

  4. Running the test with following options:
  5. Number of threads: 512
  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: 512 tps: 2111.91 qps: 42531.42 (r/w/o: 29796.60/8494.11/4240.72) lat (ms,95%): 549.52 err/s: 0.00 reconn/s: 0.00
  15. [ 60s ] thds: 512 tps: 2282.45 qps: 45658.59 (r/w/o: 31952.92/9140.64/4565.04) lat (ms,95%): 467.30 err/s: 0.00 reconn/s: 0.00
  16. [ 90s ] thds: 512 tps: 1797.69 qps: 35934.39 (r/w/o: 25157.74/7181.34/3595.31) lat (ms,95%): 733.00 err/s: 0.00 reconn/s: 0.00
  17. [ 120s ] thds: 512 tps: 1805.02 qps: 36013.97 (r/w/o: 25211.13/7198.12/3604.72) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
  18. SQL statistics:
  19.     queries performed:
  20.         read: 3366608
  21.         write: 961888
  22.         other: 480944
  23.         total: 4809440
  24.     transactions: 240472 (1998.58 per sec.)
  25.     queries: 4809440 (39971.60 per sec.)
  26.     ignored errors: 0 (0.00 per sec.)
  27.     reconnects: 0 (0.00 per sec.)

  29. General statistics:
  30.     total time: 120.3194s
  31.     total number of events: 240472

  33. Latency (ms):
  34.          min: 4.74
  35.          avg: 255.60
  36.          max: 4306.89
  37.          95th percentile: 590.56
  38.          sum: 61465534.91

  40. Threads fairness:
  41.     events (avg/stddev): 469.6719/13.57
  42.     execution time (avg/stddev): 120.0499/0.05




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

相關文章