Solve the problem of slow dump data in MySQL for DW business bitsCN.com
Problem background:
DBA in Beijing reported that DW recently pulled data from MySQL and found that data pulling was slow. at that time, it was switched. After analysis by the DBA and the business party, the data pulling speed in a slave database was significantly slower than that in the master database.
After detailed communication with the DBA board bridge, I checked the system-level information captured by the board bridge and found that the iostat comparison was very obvious, and I/O scheduling algorithm was generally suspected. Using pt-summary, we found that the kernel version and hard disk scheduling are different:
Comparison of active/standby hardware environments:
Kernel | 2.6.32-220.17.1.tb619.el6.x86 _ 64 2.6.18-164. el5
Sda | [deadline] 128 [cfq] 128
Under the team of Banqiao, we pulled DW and tested it again.
The original sda hard disk I/O scheduling policy is cfq:
$ Cat/sys/block/sda/queue/schedue
Noop anticipatory deadline [cfq]
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm % util
Sda 4.95 21017.82 1697.03 144.55 53600.00 83889.11 74.66 39.44 16.24 0.54 99.11
Sda 4.00 7135.00 196.00 470.00 6112.00 153664.00 239.90 71.69 122.19 1.50 100.10
Sda 5.00 173.00 1567.00 276.00 49544.00 14152.00 34.56 19.00 9.87 0.54 100.10
Sda 6.00 240.00 1317.00 206.00 41704.00 6600.00 31.72 21.21 14.13 0.66 100.10
Sda 5.00 123.00 1956.00 54.00 61872.00 1288.00 31.42 18.25 9.14 0.50 100.10
Sda 6.00 3368.00 1515.00 85.00 47880.00 27544.00 47.14 22.12 13.61 0.63 100.10
Sda 6.00 190.00 1664.00 66.00 52720.00 2288.00 31.80 19.19 11.24 0.58 100.10
Sda 9.00 533.00 999.00 1329.00 30960.00 54736.00 36.81 18.79 7.68 0.43 100.10
Sda 18.00 466.00 1771.00 864.00 54032.00 36336.00 34.30 13.07 5.38 0.38 100.10
Sda 4.95 95.05 1401.98 15.84 44435.64 641.58 31.79 21.46 14.08 0.70 99.11
Sda 13.00 291.00 1639.00 67.00 50296.00 3128.00 31.32 16.82 10.70 0.59 100.10
Sda 4.00 191.00 1512.00 17.00 47792.00 1136.00 32.00 23.93 15.50 0.65 100.10
Sda 8.00 108.00 1699.00 52.00 53792.00 1280.00 31.45 25.18 13.85 0.57 100.10
Sda 7.00 143.00 1429.00 27.00 45344.00 1824.00 32.40 18.71 13.19 0.69 100.10
Sda 13.00 186.00 990.00 19.00 30888.00 1176.00 31.78 18.06 18.11 0.99 100.10
Sda 3.00 102.00 763.00 12.00 24184.00 1232.00 32.79 16.64 20.77 1.29 100.10
Change the I/O scheduling policy of the hard disk sda to deadline for comparison:
$ Sudo su-c "echo deadline | sudo tee/sys/block/sda/queue/sched"
Deadline
Or
$ Echo deadline | sudo tee/sys/block/sda/queue/scheduler
Deadline
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm % util
Sda 31.00 208.00 4088.00 372.00 128432.00 11120.00 31.29 10.40 2.33 0.22 100.10
Sda 28.00 193.00 4173.00 360.00 132024.00 11016.00 31.56 9.12 2.01 0.22 100.10
Sda 37.00 125.00 4503.00 317.00 142472.00 10048.00 31.64 9.13 1.89 0.21 100.10
Sda 30.00 266.00 4452.00 414.00 141072.00 12040.00 31.47 8.68 1.78 0.21 100.10
Sda 44.00 171.00 4629.00 450.00 146568.00 18064.00 32.41 8.74 1.72 0.20 100.10
Sda 32.00 239.00 4660.00 560.00 147328.00 18456.00 31.76 9.84 1.89 0.19 100.10
Sda 30.00 330.00 4004.00 463.00 125808.00 13072.00 31.09 9.63 2.16 0.22 100.10
Sda 38.00 122.00 4730.00 358.00 149680.00 10392.00 31.46 8.71 1.72 0.20 100.10
Sda 29.00 408.00 3897.00 813.00 122632.00 22760.00 30.87 9.48 2.01 0.21 100.10
Sda 27.72 115.84 3687.13 282.18 116586.14 9655.45 31.80 9.19 2.32 0.25 99.11
Sda 30.00 259.00 3629.00 739.00 114144.00 26616.00 32.23 10.55 2.40 0.23 100.10
Sda 34.00 206.00 4608.00 190.00 145232.00 3272.00 30.95 9.47 1.98 0.21 100.10
Sda 34.00 190.00 4327.00 449.00 136304.00 11696.00 30.99 9.40 1.96 0.21 100.10
Sda 41.00 229.00 4559.00 389.00 144408.00 11464.00 31.50 8.93 1.81 0.20 100.10
The comparison data intuitively reflects the features of CFQ and DEADLINE:
1. CFQ sorts the I/O addresses to reduce the disk seek time and satisfy as many I/O requests as possible. The data in rrqm/s and wrqm/s is obvious.
2. I/O requests from CFQ may not be satisfied, but may starve to death. What we see here is not starvation, but await's obviously long.
3. DEADLINE is more suitable for DB than CFQ. Rsec/s and wsec/s are larger than CFQ, that is, higher I/O throughput.
Through feedback from DW colleagues, the speed of the application is significantly faster, which indicates that it is indeed effective. This is an old machine, and the newly installed machine has been set as DEADLINE by OPS personnel.
BitsCN.com
