Database server (MySQL) on SSD - trim command

Question

I can't seem to find much expert information about using a database servers on SSD with regards to trim. I know I can enable RAID and LVM discard, and use an fstrim / cron job or the discard mount option for ext4 (which one depending on write behaviour), but for example MySQL's data files only shrink with an optimize table and then you have to have inno_db_file_per_table enabled. On tables of several 100 GBs, optimize table is going to take a while, and is not something one easily does.

Do databases, MySQL in particular, handle discards properly? Can they even send discard about files of which the file system thinks still contain data?

I know that without trim, your performance can really drop after a few months and give you write amplification, so hence my concerns.

Answer 1

Unless you're using the cheapest SSDs on the consumer market, and with databases at 100GB+ you probably won't, this is not likely to be a problem for you.

Write Amplification
You'd have to be doing a really large amount of writing to even come close to limiting the effective life of a modern (as in, manufactured in the last 12 months) SSD. The industry as a whole has managed to push cell endurance out enough, figured out how much spare area is needed to compensate for failed cells, and outright firmware optimizations that you should be able to get at least 3 years life with punishing performance even on consumer-grade hardware. For older SSDs this was true, but that is no longer the case; in most cases it's leftover mythology from an older era.

To be fair, MLC endurance is still a factor at high write loads. And by high we're talking the equivalent of multiple full-disk writes per day over the course of years. If you're in this performance class you're probably no longer in the 'consumer' market anyway.

Cell reprogramming performance hits
Or, what you said you need TRIM/Discard for. This is the well known 'defect' of MLC style SSDs. However, firmware tweaks over the years have mitigated most of the performance hit for writes to this style of SSD. SLC drives are still available and thanks to the tweaks to make MLC drives last longer the big reason to use SLC is no longer there, so they're pretty uncommon now. But if you are pushing enough tiny writes to disk that you'll be pushing the lifespan of an MLC, an SLC device begins to make sense.

If you allocate enough unused space on your DB drives most SSD firmware is smart enough to notice that certain blocks are unallocated and treat it like additional spare area. When background defrag processes kick off the spare area is reprovisioned. This will keep performance high. So the lack of block-level discard support in MySQL doesn't matter much.

Answer 2

For SSD performance a good summary and some excellent references can be found on this series of blog posts.

The generic recommendations from chapter 6 might be relevant for you as I doubt that TRIM will help you very much with databases, I think a database typically will overwrite blocks and not delete that many files. A good generic performance tuning tip is:

26. Over-provisioning is useful for wear leveling and performance

A drive can be over-provisioned simply by formatting it to a logical partition capacity smaller than the maximum physical capacity. The remaining space, invisible to the user, will still be visible and used by the SSD controller. Over-provisioning helps the wear leveling mechanisms to cope with the inherent limited lifespan of NAND-flash cells. For workloads in which writes are not so heavy, 10% to 15% of over-provisioning is enough. For workloads of sustained random writes, keeping up to 25% of over-provisioning will improve performance. The over-provisioning will act as a buffer of NAND-flash blocks, helping the garbage collection process to absorb peaks of writes.