MySQL, MyISAM, fallocate, and seekwatcher.
I’ve been meaning on posting about this for a while now but I finally have a good tool to help visualize this problem (seekwatcher).
MyISAM continues to append to the .MYD file as you write to it. Which seems pretty easy to manage from a performance standpoint because if you’re writing 1 file on one disk it will be 100% contiguous.
But what happens if you’re writing 100 files? or 1000? The file becomes fragmented on disk (in a more pure sense, a fresh disk) because each new write is stacked up on top of the previous file’s write.
What needs to happen is that MyISAM needs to fallocate 5-10MB at a time. This way for at least the next 5MB you have a large chunk of contiguous disk to use.
This isn’t just theoretical. Check out the following video. This is on a 11 disk RAID server with 3 tables being written, all MyISAM, two files per table (MYD and MYI) and also the binary log.
This just takes the MYD file and runs dd redirecting to /dev/zero.
I then read the first 4GB from the .MYD and wrote a NEW file and then ran cat redirecting THAT to /dev/zero
As you can see the second video is nearly 100% sequential (and beautiful to watch).