mysql 数据库 poor IO performance.从DELL 815(6disk raid 5,oel5.5) 迁移至 HP DL380 (14disk raid 1+0,rhl5.8)
当时的IO 表现
avg-cpu: %user %nice %system %iowait %steal %idle
4.55 0.00 1.30 0.25 0.00 93.90
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 5.00 0.00 5.00 0.00 40.00 16.00 0.00 0.30 0.30 0.15
cciss/c0d0p1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p2
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p3
0.00 5.00 0.00 5.00 0.00 40.00 16.00 0.00 0.30 0.30 0.15
cciss/c0d0p4
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p5
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p6
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d1 0.00 1199.00 4.50 2289.00 66.00 13952.00 12.22 0.88 0.38 0.37 85.35
cciss/c0d1p1
0.00 1199.00 4.50 2289.00 66.00 13952.00 12.22 0.88 0.38 0.37 85.35
Average: DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
Average: dev104-0 34.67 0.00 20727.64 597.80 0.02 0.51 0.12 0.40
Average: dev104-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: dev104-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: dev104-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: dev104-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: dev104-5 34.67 0.00 20727.64 597.80 0.02 0.51 0.12 0.40
Average: dev104-6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Average: dev104-16 2676.38 7654.27 29005.03 13.70 2.47 0.92 0.37 98.04
Average: dev104-17 2676.38 7654.27 29005.03 13.70 2.47 0.92 0.37 98.04%util 接近90%. 临时修改sync_binlog=0 问题解决 (源库sync_binlog=1 没有出现这种问题)
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 9094.00 0.00 136.00 0.00 36920.00 542.94 0.05 0.35 0.10 1.40
cciss/c0d0p1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p2
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p3
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p4
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p5
0.00 9094.00 0.00 136.00 0.00 36920.00 542.94 0.05 0.35 0.10 1.40
cciss/c0d0p6
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d1 0.00 575.00 25.00 18.00 400.00 2372.00 128.93 0.17 4.03 3.81 16.40
cciss/c0d1p1
0.00 575.00 25.00 18.00 400.00 2372.00 128.93 0.17 4.03 3.81 16.40
05:43:19 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util
05:43:21 PM dev104-0 1.01 0.00 72.36 72.00 0.00 0.00 0.00 0.00
05:43:21 PM dev104-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
05:43:21 PM dev104-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
05:43:21 PM dev104-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
05:43:21 PM dev104-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
05:43:21 PM dev104-5 1.01 0.00 72.36 72.00 0.00 0.00 0.00 0.00
05:43:21 PM dev104-6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
05:43:21 PM dev104-16 52.76 1165.83 5089.45 118.55 0.25 4.77 4.68 24.67
05:43:21 PM dev104-17 52.76 1165.83 5089.45 118.55 0.25 4.77 4.68 24.67%util,tps,均大幅下降。应用类型为快速插入commit 操作。
测试对比DELL815, HP DL380 IO 表现:
DELL PowerEdge R815
[root@db-2-2 iozone]# iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data/mysql/iozone/log.xls -t 2 -C |tee /data/mysql/iozone/iozone.log
Iozone: Performance Test of File I/O
Version $Revision: 3.315 $
Compiled for 64 bit mode.
Build: linux
Contributors:William Norcott, Don Capps, Isom Crawford, Kirby Collins
Al Slater, Scott Rhine, Mike Wisner, Ken Goss
Steve Landherr, Brad Smith, Mark Kelly, Dr. Alain CYR,
Randy Dunlap, Mark Montague, Dan Million, Gavin Brebner,
Jean-Marc Zucconi, Jeff Blomberg, Benny Halevy,
Erik Habbinga, Kris Strecker, Walter Wong, Joshua Root.
Run began: Tue Jan 29 11:11:51 2013
Record Size 4096 KB
File size set to 2097152 KB
Excel chart generation enabled
Include fsync in write timing
Include close in write timing
Command line used: iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data/mysql/iozone/log.xls -t 2 -C
Output is in Kbytes/sec
Time Resolution = 0.000001 seconds.
Processor cache size set to 1024 Kbytes.
Processor cache line size set to 32 bytes.
File stride size set to 17 * record size.
Throughput test with 2 processes
Each process writes a 2097152 Kbyte file in 4096 Kbyte records
Children see throughput for 2 initial writers = 197313.41 KB/sec
Parent sees throughput for 2 initial writers = 174735.45 KB/sec
Min throughput per process = 87504.14 KB/sec
Max throughput per process = 109809.27 KB/sec
Avg throughput per process = 98656.70 KB/sec
Min xfer = 1671168.00 KB
Child[0] xfer count = 1671168.00 KB, Throughput = 87504.14 KB/sec
Child[1] xfer count = 2097152.00 KB, Throughput = 109809.27 KB/sec
Children see throughput for 2 rewriters = 203248.12 KB/sec
Parent sees throughput for 2 rewriters = 203053.04 KB/sec
Min throughput per process = 101543.17 KB/sec
Max throughput per process = 101704.95 KB/sec
Avg throughput per process = 101624.06 KB/sec
Min xfer = 2097152.00 KB
Child[0] xfer count = 2097152.00 KB, Throughput = 101543.17 KB/sec
Child[1] xfer count = 2097152.00 KB, Throughput = 101704.95 KB/sec
Children see throughput for 2 readers = 2397158.38 KB/sec
Parent sees throughput for 2 readers = 2391863.63 KB/sec
Min throughput per process = 1176860.12 KB/sec
Max throughput per process = 1220298.25 KB/sec
Avg throughput per process = 1198579.19 KB/sec
Min xfer = 2023424.00 KB
Child[0] xfer count = 2023424.00 KB, Throughput = 1176860.12 KB/sec
Child[1] xfer count = 2097152.00 KB, Throughput = 1220298.25 KB/sec
Children see throughput for 2 re-readers = 2600163.88 KB/sec
Parent sees throughput for 2 re-readers = 2592894.05 KB/sec
Min throughput per process = 1271696.50 KB/sec
Max throughput per process = 1328467.38 KB/sec
Avg throughput per process = 1300081.94 KB/sec
Min xfer = 2011136.00 KB
Child[0] xfer count = 2011136.00 KB, Throughput = 1271696.50 KB/sec
Child[1] xfer count = 2097152.00 KB, Throughput = 1328467.38 KB/sec
"Throughput report Y-axis is type of test X-axis is number of processes"
"Record size = 4096 Kbytes "
"Output is in Kbytes/sec"
" Initial write " 197313.41
" Rewrite " 203248.12
" Read " 2397158.38
" Re-read " 2600163.88—————————————————————–
HP ProLiant DL380p Gen8
[root@DCB-SRV-0225 data1]# iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data1/iozone/log.xls -t 2 -C |tee /data1/iozone/iozone.log
Iozone: Performance Test of File I/O
Version $Revision: 3.315 $
Compiled for 64 bit mode.
Build: linux
Contributors:William Norcott, Don Capps, Isom Crawford, Kirby Collins
Al Slater, Scott Rhine, Mike Wisner, Ken Goss
Steve Landherr, Brad Smith, Mark Kelly, Dr. Alain CYR,
Randy Dunlap, Mark Montague, Dan Million, Gavin Brebner,
Jean-Marc Zucconi, Jeff Blomberg, Benny Halevy,
Erik Habbinga, Kris Strecker, Walter Wong, Joshua Root.
Run began: Tue Jan 29 10:50:05 2013
Record Size 4096 KB
File size set to 2097152 KB
Excel chart generation enabled
Include fsync in write timing
Include close in write timing
Command line used: iozone -i 0 -i 1 -r 4096 -s 2G -Recb /data1/iozone/log.xls -t 2 -C
Output is in Kbytes/sec
Time Resolution = 0.000001 seconds.
Processor cache size set to 1024 Kbytes.
Processor cache line size set to 32 bytes.
File stride size set to 17 * record size.
Throughput test with 2 processes
Each process writes a 2097152 Kbyte file in 4096 Kbyte records
Children see throughput for 2 initial writers = 372381.80 KB/sec
Parent sees throughput for 2 initial writers = 371527.79 KB/sec
Min throughput per process = 185643.42 KB/sec
Max throughput per process = 186738.38 KB/sec
Avg throughput per process = 186190.90 KB/sec
Min xfer = 2084864.00 KB
Child[0] xfer count = 2084864.00 KB, Throughput = 185643.42 KB/sec
Child[1] xfer count = 2097152.00 KB, Throughput = 186738.38 KB/sec
Children see throughput for 2 rewriters = 560498.03 KB/sec
Parent sees throughput for 2 rewriters = 560283.33 KB/sec
Min throughput per process = 280219.81 KB/sec
Max throughput per process = 280278.22 KB/sec
Avg throughput per process = 280249.02 KB/sec
Min xfer = 2097152.00 KB
Child[0] xfer count = 2097152.00 KB, Throughput = 280219.81 KB/sec
Child[1] xfer count = 2097152.00 KB, Throughput = 280278.22 KB/sec
Children see throughput for 2 readers = 7649346.50 KB/sec
Parent sees throughput for 2 readers = 7612895.95 KB/sec
Min throughput per process = 3796191.50 KB/sec
Max throughput per process = 3853155.00 KB/sec
Avg throughput per process = 3824673.25 KB/sec
Min xfer = 2076672.00 KB
Child[0] xfer count = 2076672.00 KB, Throughput = 3796191.50 KB/sec
Child[1] xfer count = 2097152.00 KB, Throughput = 3853155.00 KB/sec
Children see throughput for 2 re-readers = 8173770.00 KB/sec
Parent sees throughput for 2 re-readers = 8132688.68 KB/sec
Min throughput per process = 4063641.50 KB/sec
Max throughput per process = 4110128.50 KB/sec
Avg throughput per process = 4086885.00 KB/sec
Min xfer = 2084864.00 KB
Child[0] xfer count = 2097152.00 KB, Throughput = 4110128.50 KB/sec
Child[1] xfer count = 2084864.00 KB, Throughput = 4063641.50 KB/sec
"Throughput report Y-axis is type of test X-axis is number of processes"
"Record size = 4096 Kbytes "
"Output is in Kbytes/sec"
" Initial write " 372381.80
" Rewrite " 560498.03
" Read " 7649346.50
" Re-read " 8173770.00
iozone test complete.所有指数 HP 都秒杀dell 了.
参考 ext3 and sync-binlog do not play well together。 看来在不同的OS版本上,mysql之于ext3的performance仍然是存在区别的。
