Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 really worth the price. The difference between SSD and a SATA 7200 hard drive is about $1500 and less desk 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.0.4

share|improve this question
    
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 Feb 27 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 Feb 27 at 23:06
5  
Serial ATA (SATA) is the interface. There are SATA SSDs and there are hard disk drives (HDDs) that do not use SATA. –  Dennis Feb 27 at 23:20
add comment

5 Answers

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.

share|improve this answer
    
How would you define a good SSD? –  Mike Feb 27 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. –  edvinas.me Feb 27 at 18:58
    
use percona server if you are using ssd –  Sam D Feb 27 at 20:28
    
@SamD why use Percona if you are using SSD? –  Ram Feb 27 at 20:57
1  
A link to why to use Percona that comes from Percona website... I hope next is not from Microsoft saying not to use Linux (; –  Francisco Presencia Mar 25 at 3:52
show 2 more comments

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

share|improve this answer
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 Feb 28 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 Feb 28 at 11:24
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
    
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 Mar 3 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 Mar 3 at 23:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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