We have a combined application/MySQL-server which has started to crumble. Right now, it gets stuck on copying a 125 million row MySQL MyISAM table (INSERT INTO a_copy SELECT * FROM a
with KEYS DISABLED
on a_copy
). We've benchmarked the job of which this query is a part to well under an hour on production data in a cloned production VM. However, when running this job in production, the copy query has been running for well over 12 hours without finishing, randomly making every MySQL query slower than molasses (60+ second, without any locks).
Output from iostat
yyyy@xxxx:~$ iostat -mxdc 10
Linux 2.6.32-5-686 (xxxx) 12/24/14 _i686_ (4 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
5.24 0.00 1.34 13.43 0.00 80.00
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 1.84 232.18 68.24 468.50 1.88 2.74 17.62 47.19 87.87 0.69 36.79
avg-cpu: %user %nice %system %iowait %steal %idle
0.77 0.00 2.26 27.92 0.00 69.05
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 6.70 6.10 317.30 208.20 1.37 0.83 8.56 140.26 173.99 1.90 100.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.81 0.00 2.58 31.64 0.00 64.97
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 5.00 11.50 372.80 242.80 1.56 1.00 8.54 146.50 321.34 1.62 100.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.17 0.00 1.65 39.42 0.00 58.76
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 3.00 23.40 226.80 618.00 0.94 2.50 8.34 145.54 171.94 1.18 100.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.22 0.00 1.77 32.23 0.00 65.78
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 5.70 22.50 282.10 491.80 1.18 2.02 8.45 145.72 182.10 1.29 100.00
How should I intepret this?