mysql压力测试在青云PCIE盘sysbench版本

对比测试“ 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
     
233. 
     
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
     
236. 
     
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
     
239. 
     
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
     
243. 
     
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
     
246. 
     
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
     
249. 
     
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
     
253. 
     
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
     
256. 
     
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
     
259. 
     
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
     
262. 
     
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
     
265. 
     
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)
   
4. 
   
5. Initializing worker threads...
   
6. 
   
7. Creating table 'sbtest4'...Creating table 'sbtest10'...
   
8. Creating table 'sbtest7'...
   
9. 
   
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)
   
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
   
9. 
   
10. 
    
11. Initializing worker threads...
    
12. 
    
13. Threads
    
14. 
    
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.)
    
27. 
    
28. General statistics:
    
29.     total time: 60.0109s
    
30.     total number of events: 80765
    
31. 
    
32. Latency (ms):
    
33.          min: 4.10
    
34.          avg: 14.85
    
35.          max: 384.70
    
36.          95th percentile: 30.26
    
37.          sum: 1199742.53
    
38. 
    
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)
   
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
   
9. 
   
10. 
    
11. Initializing worker threads...
    
12. 
    
13. Threads
    
14. 
    
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.)
    
29. 
    
30. General statistics:
    
31.     total time: 120.0795s
    
32.     total number of events: 163897
    
33. 
    
34. Latency (ms):
    
35.          min: 4.55
    
36.          avg: 73.23
    
37.          max: 3815.25
    
38.          95th percentile: 167.44
    
39.          sum: 12002493.01
    
40. 
    
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)
   
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
   
9. 
   
10. 
    
11. Initializing worker threads...
    
12. 
    
13. Threads
    
14. 
    
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.)
    
29. 
    
30. General statistics:
    
31.     total time: 120.1728s
    
32.     total number of events: 266746
    
33. 
    
34. Latency (ms):
    
35.          min: 4.75
    
36.          avg: 115.19
    
37.          max: 1242.44
    
38.          95th percentile: 231.53
    
39.          sum: 30725391.47
    
40. 
    
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)
   
3. 
   
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
   
8. 
   
9. 
   
10. Initializing worker threads...
    
11. 
    
12. Threads
    
13. 
    
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.)
    
28. 
    
29. General statistics:
    
30.     total time: 120.3194s
    
31.     total number of events: 240472
    
32. 
    
33. Latency (ms):
    
34.          min: 4.74
    
35.          avg: 255.60
    
36.          max: 4306.89
    
37.          95th percentile: 590.56
    
38.          sum: 61465534.91
    
39. 
    
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30393770/viewspace-2150563/