Very slow copy of MyISAM .MYD file

Question

We noticed that a few of our MyISAM .MYD files (MySQL databasebase tables) copy extremely slow. Both the C: drive and the D: drive are SSDs; theoretical limit is 500MB / sec data rate. For timings, we turn off the MySQL service. Here are some sample timing for the file test.myd with 6GB:

NET STOP MYSQL56
Step1: COPY D:\MySQL_Data\test.myd C:\Temp      --> 61MB / sec copy speed
Step2: COPY C:\Temp\test.myd D:\temp            --> 463 MB / sec
Step3: COPY D:\Temp\test.myd c:\temp\test1.myd  --> 92 MB / sec

Strange results; why would the speed in one direction be so different from the other direction? Let's try this:

NET START MYSQL56
in MySQL: REPAIR TABLE test; (took about 6 minutes)
NET STOP MYSQL56
Step4: COPY D:\MySQL_Data\test.myd C:\Temp      --> 463 MB / sec
Step5: COPY C:\Temp\test.myd D:\temp            --> 463 MB / sec
Step6: COPY D:\Temp\test.myd c:\temp\test1.myd  --> 451 MB / sec

• Can anybody explain the difference in copy speed?
• What might have caused the slow copy speed in the first place?
• What would REPAIR make a difference, but OPTIMIZE which we tried first, did not make a difference.
• Would there be any kind of performance hit on the SQL level with the initial version (ie before the REPAIR)? Sorry, I did not test this out before running these tests.

Answer 1

• REPAIR would scan through the table and fix issues that it finds. This means that the table is completely read.

• OPTIMIZE copies the entire table over, then RENAMEs it back to the old name. The result is as if the entire table were read.

• COPY reads one file and writes to the other file. If the target file does not exist, it must create it; this is a slow process on Windows.

• When reading a file, the data is fetched from disk (SSD, in your case) and cached in RAM. A second reading will use the cached copy, thereby being faster.

This last bullet item may explain the discrepancies you found.

Another possibility is "wear leveling" and/or "erase-before-write" -- two properties of SSDs.

Wear leveling is when the SSD moves things around to avoid too much "wear". Note that a SSD block "wears out" after N writes to it. By moving blocks around, this physical deficiency is avoided. (It is a feature of Enterprise-grade SSDs, but may be missing on cheap drives.)

Before a write can occur on an SSD, the spot must first be "erased". This extra step is simply a physical requirement of how SSDs work. I doubt if it factors into your question, but it might.

I am removing [mysql] and [myisam] tags since the question really only applies to file COPY with Windows and SSD.