SSD VS HDD 在INNODB 上的性能比较

由于项目后端存储的需要，需要高性能的存储设备。特此比较Fusion IO （80G）和 RAID5 的mysql性能,采用sysbench v0.4.8 来测试

 

//innodb中创建100k条数据 ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql1.sock prepare --mysql-user=terry --mysql-password=terry mysql> show table status; +--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | sbtest | InnoDB | 10 | Compact | 1000109 | 225 | 225132544 | 0 | 18399232 | 8584691712 | 1000001 | 2011-03-16 10:43:28 | NULL | NULL | utf8_general_ci | NULL | | | +--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 

 

第一次测试，innodb buffer 开的比较高 数据基本上都缓存住了

 

SSD

OLTP test statistics: queries performed: read: 1400028 write: 500010 other: 200004 total: 2100042 transactions: 100002 (2271.75 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 1900038 (43163.33 per sec.) other operations: 200004 (4543.51 per sec.) Test execution summary: total time: 44.0197s total number of events: 100002 total time taken by event execution: 703.3672 per-request statistics: min: 0.0023s avg: 0.0070s max: 0.6603s approx. 95 percentile: 0.0176s Threads fairness: events (avg/stddev): 6250.1250/55.38 execution time (avg/stddev): 43.9604/0.00 

HDD

./sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-socket=/tmp/mysql1.sock --mysql-user=terry --mysql-password=terry run sysbench v0.4.8: multi-threaded system evaluation benchmark OLTP test statistics: queries performed: read: 1400056 write: 500020 other: 200008 total: 2100084 transactions: 100004 (2324.85 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 1900076 (44172.19 per sec.) other operations: 200008 (4649.70 per sec.) Test execution summary: total time: 43.0152s total number of events: 100004 total time taken by event execution: 686.9507 per-request statistics: min: 0.0024s avg: 0.0069s max: 0.5917s approx. 95 percentile: 0.0223s Threads fairness: events (avg/stddev): 6250.2500/70.82 execution time (avg/stddev): 42.9344/0.01 

 

性能基本上差不都 ，都被cache住了。

07:30:01 AM CPU %user %nice %system %iowait %steal %idle 09:10:01 AM all 0.03 0.00 8.69 0.00 0.00 91.28 09:20:01 AM all 0.03 0.00 8.38 0.00 0.00 91.60 09:30:01 AM all 0.03 0.00 8.44 0.00 0.00 91.53 09:40:01 AM all 0.03 0.00 8.39 0.00 0.00 91.58 09:50:01 AM all 0.03 0.00 8.50 0.00 0.00 91.48 10:00:01 AM all 0.03 0.00 8.72 0.00 0.00 91.25 

没有iowait , CPU使用了1-2个核。

 

第二次测试，innodb buffer 开的很低（1k）  数据走direct_IO方式写入底层存储（innodb_flush_method=O_DIRECT）

SSD

OLTP test statistics: queries performed: read: 1400014 write: 500005 other: 200002 total: 2100021 transactions: 100001 (63.42 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 1900019 (1204.98 per sec.) other operations: 200002 (126.84 per sec.) Test execution summary: total time: 1576.8096s total number of events: 100001 total time taken by event execution: 25227.3666 per-request statistics: min: 0.0137s avg: 0.2523s max: 2.3590s approx. 95 percentile: 0.6610s Threads fairness: events (avg/stddev): 6250.0625/31.85 execution time (avg/stddev): 1576.7104/0.03 

测试期间的 SAR 数据 07:30:01 AM CPU %user %nice %system %iowait %steal %idle 10:50:01 AM all 9.23 0.00 10.09 0.07 0.00 80.60 11:00:01 AM all 1.85 0.00 9.29 1.94 0.00 86.92 11:10:01 AM all 9.03 0.00 12.64 9.70 0.00 68.63 11:20:01 AM all 8.73 0.00 12.34 9.66 0.00 69.27 11:30:01 AM all 3.46 0.00 10.10 3.80 0.00 82.65 Average: all 0.50 0.00 8.67 0.37 0.00 90.46 

IOWAIT 比较高

HDD

OLTP test statistics: queries performed: read: 1400014 write: 500005 other: 200002 total: 2100021 transactions: 100001 (38.86 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 1900019 (738.26 per sec.) other operations: 200002 (77.71 per sec.) Test execution summary: total time: 2573.6523s total number of events: 100001 total time taken by event execution: 41169.6306 per-request statistics: min: 0.0114s avg: 0.4117s max: 1.7586s approx. 95 percentile: 0.7781s Threads fairness: events (avg/stddev): 6250.0625/27.99 execution time (avg/stddev): 2573.1019/0.15

07:30:01 AM CPU %user %nice %system %iowait %steal %idle 11:00:01 AM all 1.57 0.00 0.75 7.46 0.00 90.22 11:10:01 AM all 2.40 0.00 1.14 11.18 0.00 85.28 11:20:01 AM all 2.46 0.00 1.17 11.81 0.00 84.57 11:30:01 AM all 2.43 0.00 1.16 12.06 0.00 84.35 Average: all 0.31 0.00 0.13 0.62 0.00 98.93 

IOWAIT 比较高，最高时到达了18%

 

在这组测试中，HDD的性能比FUSION IO 低了50%左右。可能是由于顺序读写FUSION IO性能没有充分体现出来。