SSD vs HDD for databases

Question

I am trying to purchase a new Server to run MySQL Server on. This new server will be a slave of my main machine. However, this server will be dedicated for reporting only "Lots of reads and complex queries."

Now I am looking into investing into solid state hard drives but was wondering if it's really worth the price. The difference between an SSD and a SATA 7200 hard drive is about $1500 and the SSD has less disk space. If I do invest into SSD will the speed be noticeable?

I can purchase 4 (500GB SATA 7200) for $1500 less than purchasing 2 (500GB SSD)

Can you please help me making the decision to see if it is worth the upgrade or not?

Once more thing that I would like to mention is that I am not using query_cache so there will be lots of disk reads.

This server will have 32GB of RAM and will run Ubuntu 12.04

Answer 1

Yes with a lot of reads and reporting an SSD will make a huge difference. From 7200 RPM drive you could expect no more than ~100 IOPS while cheapest SSD can be as minimum 5x as fast as that. With good SSD you can get to 20000 IOPS or even more.

Also random writes in SSD are a lot faster as disk does not have to move every time.

Answer 2

There are three factors you need to consider here:

1. The size of your database
2. The amount of memory you have in the server
3. Your my.cnf configuration, specifically innodb_buffer_pool_size

If available memory > database size, your server will probably be able to keep all of your data in memory, and therefore an SSD might be a waste of money. The InnoDB buffer has nothing to do with the query_cache options.

If available memory < database size, it's possible that queries will need to retrieve data from disk. For extremely complex queries, or if many users are running queries at once, this can start to put stress onto the disk.

In general, databases keep the most used data into memory - if 80% of your data is rarely/never used, you will only need to keep 20% of your database in memory to maintain performance.

The exact amount of memory you need won't be immediately obvious, but unless your database is 200GB+, I would thoroughly recommend taking Up_One's advice and spending extra money on memory instead of SSDs.

NB: If your database is using MyISAM (you can check this with show table status;), consider changing to InnoDB. The MyISAM key_buffer_cache only stores index blocks, where as the InnoDB Buffer Pool stores entire data blocks. In most cases, InnoDB will prove to be a better engine to work with.

Answer 3

I don't think is such a good idea!
My Advice
increase the size of your InnoDB buffer pool is the best way to speed up MySQL. If you can add more RAM, do it. This will put most of your hot data in memory so imagine ! Disk vs Memory!
Perfect scenario is to have your memeory the size of your database
SSD - is great but it will come expensive ! and it's only good for read intensive jobs .

Check this link for nice article on this from Vadim Tkachenko

Answer 4

To give an alternative: you could use both, a large harddisk (ideally, RAID1 with three disks) to keep data, and a smaller SSD to keep indexes.

Rationale:

• the indexes are fairly small, so you can use a smaller SSD
• common queries should mainly hit the indexes anyway
• the RAID1 gives you fault tolerance
• the RAID1 gives you load balancing for random reads
• three disks leaves fault tolerance if a disk fails
• the indexes can be rebuilt if the SSD fails

Answer 5

Do it.

You mentioned that you have a read-heavy workload, so you've already avoided the big problem with using SSDs on databases: wearout. No writes means no wear, so you're golden.

As edvinas.me mentioned, your IOPS is orders of magnitude faster with the SSD than with spinning disks. For a database, IOPS pretty much translates to requests per second. Ignoring RAM cache, you'll serve about 100x as many requests from an SSD than from a 7200RPM disk.

TRIM won't make much difference as it's a read-heavy workload and it sounds like you plan on filling the disk anyway. Don't stress about it.

I'm not sure where the $1500 thing came from. Checking my local (Australian) supplier, I can get a 960GB SSD of reputable make for $750 (http://www.auspcmarket.com.au/960gb-crucial-m500-sata-6gbps-2-5-7mm-with-9-5mm-adapter-ssd-read-500mb-s-write-400mb-s/). Spinning disks are more-or-less free, but $750 is still a lot more palatable than $1500.

(Oh, wait - you're probably ordering from a big-name supplier, so they're charging you through the nose for the SSD? I always buy the SSD separately and swap it in myself, but I don't know if that's permissible in your environment.)

You'll likely get away with less RAM, too, but without knowing your exact workload, it's difficult to judge whether you can safely reduce RAM without hurting performance.

If you're still not sure, you can get large 10k RPM drives, but they'll end up costing almost as much as the SSD anyway while being much slower.

If you need to scale much beyond 1TB then SSDs start to become too expensive, but at 1TB, I'd say the SSD is a clear win.

Answer 6

I definitely agree the biggest bang for the buck comes from increasing your innodb_db_bufferpool size but unfortunately it completely depends on how large is your data set and how often different disk blocks are accessed. I maintain several database that are fairly large 200 GB+ so fitting everything into the RAM is not really an option and for that reason we recently switched to SSD based storage. I've done quite a big research in terms of the IOPS for MySQL use on different RAID arrays I have access to. Here are the results:

1,253 IOPS - 4 x SCSI 15k (3.5") disk

test: (g=0): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=64 read : io=3071.7MB, bw=5012.8KB/s, iops=1253, runt=627475msec write: io=1024.4MB, bw=1671.7KB/s, iops=417, runt=627475msec cpu : usr=0.63%, sys=3.11%, ctx=985926, majf=0, minf=22

2,558 IOPS - 8 x 10K RPM 900GB SAS (2.5") disk

test: (g=0): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=64 read : io=3071.7MB, bw=10236KB/s, iops=2558, runt=307293msec write: io=1024.4MB, bw=3413.5KB/s, iops=853, runt=307293msec cpu : usr=2.73%, sys=8.72%, ctx=904875, majf=0, minf=25

23,456 IOPS - Rackspace Performance 2 SSD server

test: (g=0): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=64 read : io=3071.7MB, bw=93708KB/s, iops=23426, runt= 33566msec write: io=1024.4MB, bw=31249KB/s, iops=7812, runt= 33566msec cpu : usr=5.73%, sys=35.83%, ctx=181568, majf=0, minf=23

35,484 IOPS - 2 x Mirrored EDGE Boost 480GB 2.5" MLC (http://www.edgememory.com)

test: (g=0): rw=randrw, bs=4K-4K/4K-4K/4K-4K, ioengine=libaio, iodepth=64 read : io=3068.4MB, bw=141934KB/s, iops=35483 , runt= 22137msec write: io=1027.7MB, bw=47537KB/s, iops=11884 , runt= 22137msec cpu : usr=11.68%, sys=69.89%, ctx=24379, majf=0, minf=20

So its clear that high quality SSD of today are amazing performers. Two Mirrored SSD can easily outperform 16 disk SAN storage enclosure and that is a compelling statement alone.

If you are interested in full details the rest of write up is found on my blog:

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