RE: Is SSD suitable for mysql server?
--- On Mon, 25/10/10, Daevid Vincent dae...@daevid.com wrote: From: Daevid Vincent dae...@daevid.com Subject: RE: Is SSD suitable for mysql server? To: 'mysql' mysql@lists.mysql.com Date: Monday, 25 October, 2010, 21:52 I guess it depends on how important your data is too. Quite a few of the SSDs on the market have been proven to not honour flush requests, so if the power goes out you've got corrupted data. Uh. If you're not using a UPS battery backup then you deserve to loose your data. And if you don't have it configured to auto-power off when it's low on battery (extended outtage) then you also deserve to feel the resulting pain. (http://www.apcupsd.com/) A UPS doesn't save you from the possibility of a server crash or any other outage (including the UPS failing), in that instance your data is hosed anyway. You have to make sure the data that the database thinks is comitted actually is, and for that you need a storage medium that honours flush requests properly. Disregarding your storage system reliability because you have a UPS is madness. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is SSD suitable for mysql server?
Hi Warren, all! Your statement is true ... Warren Young wrote: [[...]] A lone 2 TB rotating disk will beat a top-of-the-line SSD for linear writes, and you can beat an SSD for linear reads with a pair of disks in RAID-0 or -1, or four disks in RAID-10. [[...]] ... but irrelevant: Linear writes and linear reads are not what governs DBMS performance. More relevant is this: [[...]] SSDs have a clearer advantage for random I/O, a useful property for databases, but still, you shouldn't ignore the fact that SSD writes are expensive. Especially important is the latency (not throughput!) of random writes to the log, which may govern your transaction turnaround time. I don't doubt SSD writes are expensive, but that holds for any disk subsystem write (regardless of the technology). Therefore, you get the SSD speed benefit only if writes are rare enough that more data is coming off the drive at any given time than is being written, or if your current disk subsystem is bottlenecked by rotating disk head seek time, or some combination. Exactly: Seek time before writing a commit to the log. So if your architecture uses a disk subsystem for stable storage (as opposed to MySQL Cluster based on RAM and duplication), its write speed is a limiting factor for the performance of write transactions. [[...]] Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is SSD suitable for mysql server?
At 12:56 AM 10/25/2010, you wrote: Hello, We are a company for gaming. Our main db is mysql 5.1 installed on Linux. Currently the hardware for mysql is 2*4 CPU, 16G memory, Raid 10 (four disks). Now we have the plan to replace the disks with SSD for better performance. Do you think is it right for the SSD solution for mysql? Thanks. Regards, Kenn. Kenn, If you have the money, you can also try Hyperdrive 5 which is a RAM drive device. It comes in an enclosure similar to a hard drive and looks like a SATA drive. Each device supports up to 32GB (64GB with 8GBM DIMMS) of ram and can be raided together to form a larger drive. It has battery backup and impressive specs: 175MB/s read, 145MB/s write. It has no moving parts so there is nothing to wear out. It can also be backed up to a hard drive. http://www.hyperossystems.co.uk/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is SSD suitable for mysql server?
On Mon, Oct 25, 2010 at 7:56 AM, wroxdb wro...@gmail.com wrote: Hello, We are a company for gaming. Our main db is mysql 5.1 installed on Linux. Currently the hardware for mysql is 2*4 CPU, 16G memory, Raid 10 (four disks). Now we have the plan to replace the disks with SSD for better performance. Do you think is it right for the SSD solution for mysql? It may or may not be, depending on which problem you're trying to solve :-) For starters, how big is your DB ? If it fits in memory anyways, you'll not see a lot of benefit for selects. SSD may still be useful if you have a lot of writes, though. If the database doesn't fit in available memory, a lot more factors are going to apply, depending on the usage patterns. Incidentally, i'm not aware of how SSD plays with hard/software RAID setups - anyone know more about this ? Thanks. Regards, Kenn. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Is SSD suitable for mysql server?
--- On Mon, 25/10/10, Johan De Meersman vegiv...@tuxera.be wrote: From: Johan De Meersman vegiv...@tuxera.be Subject: Re: Is SSD suitable for mysql server? To: wroxdb wro...@gmail.com Cc: mysql mysql@lists.mysql.com Date: Monday, 25 October, 2010, 10:03 On Mon, Oct 25, 2010 at 7:56 AM, wroxdb wro...@gmail.com wrote: Hello, We are a company for gaming. Our main db is mysql 5.1 installed on Linux. Currently the hardware for mysql is 2*4 CPU, 16G memory, Raid 10 (four disks). Now we have the plan to replace the disks with SSD for better performance. Do you think is it right for the SSD solution for mysql? It may or may not be, depending on which problem you're trying to solve :-) For starters, how big is your DB ? If it fits in memory anyways, you'll not see a lot of benefit for selects. SSD may still be useful if you have a lot of writes, though. If the database doesn't fit in available memory, a lot more factors are going to apply, depending on the usage patterns. Incidentally, i'm not aware of how SSD plays with hard/software RAID setups - anyone know more about this ? There have been some reports of raid cards not behaving themselvs with SSDs attached. I guess it depends on how important your data is too. Quite a few of the SSDs on the market have been proven to not honour flush requests, so if the power goes out you've got corrupted data. That's not to say that SSDs don't look promising, the more expensive ones with a supercapacitors on board have potential. But I just think you'd have to do a good bit of testing yourself before trusting them no matter what the manufacturers say. A good raid controler with BBU and a few more spindles will greatly improve your write performance too, maybe that's all you need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is SSD suitable for mysql server?
On 10/25/2010 3:03 AM, Johan De Meersman wrote: SSD may still be useful if you have a lot of writes, though. Only if by a lot you mean a minority. A lone 2 TB rotating disk will beat a top-of-the-line SSD for linear writes, and you can beat an SSD for linear reads with a pair of disks in RAID-0 or -1, or four disks in RAID-10. (Or, I suppose, some huge number of spindles in RAID-5 or -6, but I've never seen such an array big enough to be called fast at writes.) SSDs have a clearer advantage for random I/O, a useful property for databases, but still, you shouldn't ignore the fact that SSD writes are expensive. Therefore, you get the SSD speed benefit only if writes are rare enough that more data is coming off the drive at any given time than is being written, or if your current disk subsystem is bottlenecked by rotating disk head seek time, or some combination. Since the original poster is using RAID-10, it's definitely not a sure deal that replacing that array with a single SSD will help. However, it might be entertaining to benchmark it against 4 SSDs in RAID-10. Or 8. :) Incidentally, i'm not aware of how SSD plays with hard/software RAID setups - anyone know more about this ? Some software RAID and RAID-like systems are gaining SSD awareness so they can intentionally place frequently-accessed data on the SSD. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is SSD suitable for mysql server?
On 10/25/2010 4:32 AM, Glyn Astill wrote: There have been some reports of raid cards not behaving themselvs with SSDs attached. I'd be surprised if these bugs haven't all been worked out by now. SSDs started to hit the mass market in force about two years ago. Any vendor still shipping a disk controller that eats SSDs likely is trying to EOL that controller anyway. I guess it depends on how important your data is too. Quite a few of the SSDs on the market have been proven to not honour flush requests, so if the power goes out you've got corrupted data. I doubt that's true of enterprise SSDs. Sure, if you go and fill your server with SSDs made for laptops you may find yourself sliding down the bleeding edge, but one wouldn't do that, would one? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Is SSD suitable for mysql server?
I guess it depends on how important your data is too. Quite a few of the SSDs on the market have been proven to not honour flush requests, so if the power goes out you've got corrupted data. Uh. If you're not using a UPS battery backup then you deserve to loose your data. And if you don't have it configured to auto-power off when it's low on battery (extended outtage) then you also deserve to feel the resulting pain. (http://www.apcupsd.com/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org