Had an interesting thought today. First let me talk a bit about our infrastructure.
We have a largeish table with about 60GB of data. THis data changes often and is reloaded from csv files using loaddata. We currently load the new dataset into a second table, then rename the two and delete the old one. This is a pain i nthe ass for a few reasons, but mostly because it means that suddenly none of our data is in memory, so mysql has to rebuild the entire innodb bufferpool when we make this switch. This of course makes our app VERY slow to respond for a half hour or so.
Our entire dataset fits nicely in memory, and after it is loaded, it is entirely read only. We only run selects against it.
So, my question is, instead of doing what we do now, if we are using SSD’s AND the entire dataset fits in memory, would we possibly be able to load the new data over the current data so the records are updated, then delete the records that are no longer used? The ONLY reason we don’t do that now is because I didn’t want the table to become fragmented, but using SSD’s and keeping the dataset in memory, fragmentation shouldn’t matter. You don’t defrag an SSD on a desktop system for example because it wears the drive out faster, and access time is the same no matter where you are reading from the disk.
Am I onto something here, or completely off base?