I always read about blazing performance of MySQL, how many millions of transactions it can do per second when using a cache, SSD, Fusion-IO or whatever. Check out some of these reports:
FusionIO – time for benchmarks
Fusion-io Tweaks Flash To Speed Up MySQL Databases
I like big numbers and faster this and faster that, who doesn’t. I’ve tried Fusion-IO cards, I’ve tried SSD, I’ve tried normal hard drives with PCI SSD as a cache using Intel CAS. Bottom line, nothing works to improve the performance in any significant way.
If I test with a lab testing tool, or benchmark test it will report an Fusion-IO, SSD or Intel CAS have the potential to provide speed improvements. What about a real world test? That is where I want to see a difference. When I am doing a database restore on of a MySQL server, I would like to see something actually impact the restore times. In reality on a 8 core machine only one core is used since the default restore can only run as a single process.
I read recently about disabling hyper threading on the CPU may actually give MySQL a boost. I also read about innodb_doublewrite in MySQL (dangerous, not recommended to use).
Lets run a few tests under different situations. I am going to restore a 3gb database with about 11.9 million records. This is not benchmark software, I want to see changes in real life.
Each restore is run twice and the average if the two runs used.
Testing with 4 cores (hyper-threading disabled)
Intel CAS disabled – 27.25 minutes
Intel CAS enabled – write through cache 29.10 minutes.
Intel CAS enabled – write back cache 27.18 minutes
So none of this seems to make any major difference. Perhaps the disk is not the bottleneck. Lets try another approach to the testing.
Conduct the same tests, but rather than performing a single restore we will restore four instances of the database simultaneously… Lets see what happens…
Intel CAS disabled – 1 hour 33 minutes
Intel CAS enabled – write through cache 1 hour 34 minutes
Intel CAS enabled – write back cache 43 minutes
Finally some improvement. That represents a 100% speed increase when using Intel CAS software, increasing the stress on the disks does show some improvement as a of the caching.
So the best performance comes from putting some pressure on the disk system, only then does the cache start to show some benefit.
Next, a quick test with SQL’s skip-innodb_doublewrite feature enabled (or I should say disabled).
So disable the innodb_doublewrite feature, leaving the Intel CAS inabled with write back caching and restoring 4 copies of the same database simultaneously… Lets see what happens…
Intel CAS enabled – write back cache 26 minutes
Nice! While turning off innodb_dobulewrite is not safe (at all!) for production, if you are trying to recover from a disaster situation and need to restore a large database (or several large databases) turning it off can certainly reduce your recovery time.
I’m curious, lets see our restore time with innodb_doublewrite turned off, but only restoring a single copy of the database.
Intel CAS enabled – write back cache 19 minutes