MySQL on SSD or classic HDD?

[ICPH]

Do You think SSD or classic HDD is better to be used for MySQL data?

when i do "iotop -a" i see almost exclusivelly io usage in like 15 processes like this:

mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64~mit=43762 --pid-file=/var/lib/mysql/host.domain.com.pid

but i read SSDs are limitted on writing cycles (if i say correct), reading is unlimitted on SSD.

So while i see write rate around 5 megabytes per second (12Tb/mo.,156Tb/year if count correct), mainly at mysql processes like one above, i have doubts if SSD is correct choice. (here is test where SSDs was able to write 600tb without issues)

Note that i cant modiffy mysqls structure, its not my mysqls i host them. mysql config is optimised according to 2 mysql optimising scripts.

 

[BlackoutIsHere]

When it comes to MySQL having lots of RAM will help but in general I would go with SSDs.  Limited write cycles shouldn't be a concern for you if you use good (Read: Non-Consumer) SSDs.

 

[raindog308]

I think you're confused by "write cycles".

Write cycles in this context is not "how fast it can write" or "how much can it write concurrently" but rather "how soon will it wear out".
 
And you're not going to hit it: http://www.anandtech.com/show/2829/6
 

 

[clouds4india]

if your database is frequently read / wrote by game servers or database intensive applications i'd suggest you go with SSD powered servers because as the name states Solid State Drive ( doesnt rotate ) laser based nand flash memory faster I/O providing good performance even the reboots / starting new processes are much faster although it depends on the processor too E3's E5's / L series processors like L5410 , 5520 5639 should work better with such config

 

[datarealm]

We had a client a year or so ago switch from 15k rpm drives to ssd (in both cases, hardware raid 1) for a pretty heavily used mysql database.  The next morning he emailed to let us know he was still picking pieces of jaw up off the floor...  According to him he expected a performance boost but was unprepared for just how big a boost he actually received....

 

[Neo]

We had this: http://lowendtalk.com/discussion/23028/mysql-on-ssd-vps-bad-idea#latest maybe it helps.

It is faster but think about when the SSD's are failing.

 

[Onra Host]

In any "real word" MySQL use, a SSD will outperform a classic HDD a million times better (exaggerated a bit). The newer SSD's on the market like the Intel S3500, Samsung 840 pros, etc. etc., have the ability to last for years and years without failing, so any of those stories you have heard are fading everyday now. 

Just from the vast performance increases we have seen in our shared servers after being upgraded to SSD, I would recommend SSD for MySQL 10 out of 10 times. 

 

[Schultz]

SSD for sure - given you have a good node (good ram & good cpu).

 

[libro22]

Should the SSD drives be at least in RAID1? I'm not familiar with redundancy in SSDs but is SW RAID fine enough? 

 

[drmike]

See it all depends on what you are using the database for.  If warehousing data for infrequent use (archive) then SSD is meh, wasted.

If you are piled up with reads and writes on something truly busy, then SSDs are part of the solution.  SSDs still have high cost per gigabyte with any chunk of disk I tend to touch with a database.

Performance *should* be better with SSD, but that is really dependent upon proper configs and actually utilizing the benefits of SSD.

Basically, SSD in this instance is a down the road optimization (well should be).  Where you have done everything else and mostly right to use ample RAM, caching, great indexes, effective schema, etc.  Taking this as the up front approach like many do with unoptimized MySQL rubbish they run, yeah will only make the horror, failing, etc. far worse down the road when some ceiling is hit and SSDs alone can't lifeboat you out of that problem.

I feel the OP is speaking of shared environment, so optimization of such to more properly benefit from SSDs, meh, not in control of the use.... I'd consider that a risky investment for limited storage.  Yeah, quick uptick throw into the recipe, but won't fix abuse and lousy code people are running on your servers.

 

[splitice]

If you are concerned about write cycles then monitor it, SSDs will VERY rarely expire before their write count (some times get 3x their write cycle count). This is in contrast to Hard Drives which will fail randomly, with the probability significantly increasing with age. That alone should be a major factor for consideration for any large and important database.

MySQL performs very well on SSDs, rarely do I run MySQL on spinning disks any more - and never if there is any volume to the queries. The performance increase is truly jaw dropping for IO bound workloads.

 

[BlaZe]

SSD has IO cycles. It is way better than the classic SATA HDDs.

Don't worry about the limited Write Cycles, it just means that the concurrency in writing data. It shouldn't be a problem.

 

[Roger]

SSDs will outperform any HDD comparable setup for intensive I/O operations, such as DB access. Give it a try and see it for yourself!

Write cycles should not be much of a concern if compared to the shorter lifespan of conventional rotational disks. Needless to say in a enterprise hardware environment.

If you get RAID1 your disk failure will be protected by mirroring, but if you get RAID10, you will get RAID1 protection plus the increased performance of the disk striping (RAID0).

 

[ZweiTiger]

I think its not matter HDD or SSD if the mysql database read it 5mb/s. HDD could do it too. But if something bad table abuse the system.. yes then HDD could be slow.

But course.. SSD is better. I got Samsung Evo 840 at my home PC. Bit different rather then HDD.. Very different... !

What do you think?

 

[sshgroup]

definatly SSD i have a 500GB mysql   but i can't effort enterprise ssd so i run a virtual SAN that handdle it veryyyyyyyy good beyond of imagination

 

[TierNet]

SSD read/writes data up to 800x faster than classic HDD, so I would recommend SSD if you are looking for faster performance.

 

[EnveraHost]

Definitely go with SSD's. A lot of people seem to have doubt about drive failure, if anything these server grade SSD's prove to be a lot more reliable than HDD's.

 

[HostAg]

I would recommend SSD in raid1 or raid10 configuration. It increases the performance while the wear is acceptable on the ssd's because on databases you mostly have 90% read .