CODEWITHSUNDEEP
mysqlperformanceoptimizationsolid-state-drive
SSDdump
SSDdump

Reputation: 31

Tuning SSD MySql Performance

I'm testing SSDs for use with MySql and am unable to see any performance benefits. This makes me feel like I must be doing something wrong.

Here is the setup:

For the test I moved the mysql directory on the SSD.

I imported a table with 3 million rows. Then imported the same table with the data and index directories symlinked to the 15k drive.

Loading the data into the tables via a dump from mysqldump the 15k drives showed a faster insert rate over the SSDs:

Then I tested SELECT speed by doing 'SELECT * FROM table INTO OUTFILE '/tmp/table.txt':

The SELECTS were almost identical and the writes were actually slower on the SSD which does not seem right at all. Any thoughts on what I should look into next?

Extra note: I tuned the SSD with the standard changes: noatime and noob-scheduler

Upvotes: 3

Views: 7577

Answers (3)

Juha Vehnia
Juha Vehnia

Reputation: 39

I would use some better tool to run the tests. One such tool is FIO for Linux which can simulate all kind of read/write IOPS scenarios for you. I recommend that you check on RAID controller and make sure it has optimizations for SSD drives. The older RAID controller were never designed for SSD and they generally speaking perform poorly. For example with Dell servers you need to have a never generation PERC 710H to get the full speed out of your SSDs. Intelligent RAID controller can also spare SSD for caching in front of regular mechanical drives improving overall read/write performance.

I recently wrote up a blog post about this subject that you may find helpful. I've also included some benchmark results SSD vs. mechanical disks.

http://www.juhavehnia.com/2015/05/using-ssds-to-improve-mysql-performance.html

Good luck in your tests, Juha Vehnia

Upvotes: 1

Guy Gordon
Guy Gordon

Reputation: 740

Two points:

  1. I see nothing surprising about your 15k drives writing faster than the SSD. Flash memory is slow writing. Internally, NAND flash memory it is page oriented (pages>sectors). So to write a sector the SSD reads the page, replaces 512 bytes, erases the page, and finally writes the page.

  2. Your tests are totally sequential. Your write test is a load via a dump, and your read test is 'SELECT *...'. The real strength of SSD drives is their random read speed. Your test is not forcing the 15k HD to seek often, so what you are really testing is just the interface speed.

Upvotes: 13

Preet Sangha
Preet Sangha

Reputation: 65466

All you testing should be under simmillar conditions. Ie. Initialse disks, reboot, load data, run selects. Shutdown. And then repeat. If this gives a better indication, as to if one is better or worse than the other.

Once you've done this, you can then investigate whether the tests are actually taxing the 15 disk at all. If both tests are not stressing you system, then I'd say that you've not discovered the bottlenecks that require a particular upgrade such as this one.

Once you have, then please post the results. That would make analysis and tuning easier.

Upvotes: 1

Related Questions