49

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

Improve this question
4
  • How large is your database in terms of gigabytes? Which of the answers below is most correct really depends on understanding how much data there is.
    – Nathan Jolly
    Commented Feb 27, 2014 at 22:39
  • 1
    If you do end up using SSD's you may want to wait until April for Ubuntu 14.04 or enable TRIM manually on 12.04. See this article for more info.
    – OSE
    Commented Feb 27, 2014 at 23:06
  • 6
    Serial ATA (SATA) is the interface. There are SATA SSDs and there are hard disk drives (HDDs) that do not use SATA.
    – Dennis
    Commented Feb 27, 2014 at 23:20
  • Buying something which does not make money for you is hardly an investment...
    – inf3rno
    Commented Aug 14, 2015 at 2:12

6 Answers 6

Reset to default
26

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.

Improve this answer
2
  • 4
    How would you define a good SSD?
    – Mike
    Commented Feb 27, 2014 at 18:53
  • It is all about performance and benchmarks. What I would do is google for benchmarks or reviews of the model of SSD you are buying. Other than that en.wikipedia.org/wiki/IOPS#Examples gives a very generic overview.
    – ek9
    Commented Feb 27, 2014 at 18:58
24

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.

Improve this answer
4
  • How can I put the database in the memory? the server is a slave so it will have writes on it all the time but it will also have a heavy read load because of the reports
    – Mike
    Commented Mar 3, 2014 at 19:58
  • If your tables are InnoDB, and if your InnoDB buffer pool is large enough, MySQL will automatically manage the caching of your database in memory, and will try to keep the most frequently used data in memory all of the time. MySQL's documentation provides a pretty good overview of how this works.
    – Nathan Jolly
    Commented Mar 3, 2014 at 23:08
  • @NathanJolly even if the whole database is in memory, there will still be read and writes to hard disk when data is saved + there is limitation of various servers. For example SQL Server Express version is capped to used only 1GB memory so it can't accomodate large database in memory in the fist place.
    – TheTechGuy
    Commented Jul 30, 2015 at 9:48
  • 1
    @Jackofall while that's absolutely true, the question here specified a read-heavy MySQL database. Every read-only query that can be served from memory rather than reaching to disk - even fast disk - is good news.
    – Nathan Jolly
    Commented Aug 14, 2015 at 4:16
8

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

Improve this answer
2
  • 1
    The article you are linking to is almost 4 years old, prices of SSD compared to that moment in time are more than half less and performance has increased a lot too. Memory the size of the database? What about about 500Gb database? Is not only the quantity of RAM but also the server you need to buy that has so many memory banks available.
    – Yaroslav
    Commented Feb 28, 2014 at 8:30
  • Regarding DB size ! Yes, no way you can do that- But as i said This would be a perfect scenario!
    – Up_One
    Commented Feb 28, 2014 at 11:24
8

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
Improve this answer
1
  • MySQL uses what's known as "Clustered indexing" which means that the primary key index actually stores the record data! This means that a second read to the drive is typically not necessary after the right position in the index has been read - because the associated data for that record is right there, at the same place, typically already loaded in the same block that was read from the disk when accessing that part of the index. I'm not sure how the HDD/SDD split would work in this case except for the non primary index files.
    – Volksman
    Commented Oct 7 at 9:38
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.

Improve this answer
4

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.