It is important to remember that when analyzing the performance of the MySQL storage in Linux, the cache is used. I was very interested to know about the same case. This is always funny when the user complains about a slow request. They call you and run again to find their 50-minute request, which ends in 30 seconds due to the request cache. Always run
mysql> reset query cache;
in MySQL when trying to optimize queries. However, there is one more step when comparing SSDs with traditional spindles: disk cache. It is difficult to compare access times or IOps when the OS caches a disk in memory on its own. To clear the disk cache, run the following from the shell:
$ sync && sysctl -w vm.drop_caches=3
These commands are run before each of your test requests helps you realize the potential of your SSD compared to having 7k2 SATA slowpoke. Verify this by running the same query twice without clearing the cache and observing the request time. At the moment, it’s nice to try some queries with and without indexes, and also, if possible, some associations. Use PLUS EXPLAIN for each query to verify that the index is being used. Random read access between indexes and data files will reveal bottlenecks on slower disks. Make sure your my.cnf is consistent between your SSD performance and your drive. I tested some things on a simple desktop OCZ SSD and noticed that query performance is 10 times faster than my 7200rpm SATA drive. In an SSD-based transactional database, I would be careful when using OPTIMIZE TABLE, since frequent database densification in combination with TRIM SSDs can affect disk life. This is theoretical, though, and I have not yet seen evidence to support this.
Hope this helps! I can’t wait for the days when magnetic HD disks replace tape as backup media and are completely replaced by SSD on most hardware.