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:
- Xeon 5520 2.26 Ghz Quad Core
- 12 GB Ram
- 300GB 15k in RAID 1
- 64GB SSD in RAID 1
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:
- 15k ~= 35,800 inserts/sec
- SSD != 27,000 inserts/sec
Then I tested SELECT speed by doing 'SELECT * FROM table INTO OUTFILE '/tmp/table.txt':
- 15kk ~= 3,000,000 rows in 4.19 seconds
- SSD ~= 3,000,000 rows in 4.21 seconds
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
Answer
Two points:
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.
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.