1

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?

2

2 Answers 2

1

EDIT: While the error is identified correctly, this solution does not help. When MySQL ADD PRIMARY KEYs, it again copies the table akin to INSERT INTO a_copy SELECT * FROM a, which leaves as at the same problem again. Check my other response in this thread.

So, I've figured it out, through watching the file sizes in /var/lib/mysql/<mydb>/a*, where the index file(.MYI) had a significant and growing file size.

DISABLE KEYS only disables non-unique keys in general, and leaves primary keys active in particular. Basically, the 125M-row INSERT INTO cased 125M index insertions on a joint PRIMARY KEY which manifests as random writes to disk which, in turn, is the most expensive thing you can do with a disk.

So, my solution is to DROP PRIMARY KEY on the a_copy table during copy, and then ADD PRIMARY KEY once it's done.

0

The best solution I found, and settled with in production, was found at http://www.mysqldiary.com/if-you-copy-a-myisam-table-with-primary-key-don%E2%80%99t-forget-to-order-the-rows-first/ .

Using SHOW INDEX IN a WHERE Key_name='PRIMARY', I identifie the primary key of the table(s), and append an ORDER BY to the query. The final solution looks like INSERT INTO a_copy SELECT * FROM a ORDER BY aID, aOtherColumn, where the primary key of a is (aID, aOtherColumn).

While I'm not perfectly happy with it, it works reasonably well and, foremost, way better than the original version.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .