Is SSD suitable for mysql server?

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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Is SSD suitable for mysql server?

--20cf30549ea7ca2a2504936d45eb
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Oct 25, 2010 at 7:56 AM, wroxdb 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=vegivamp@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

--20cf30549ea7ca2a2504936d45eb--

Re: Is SSD suitable for mysql server?

--- On Mon, 25/10/10, Johan De Meersman wrote: > =
From: Johan De Meersman =0A> Subject: Re: Is SSD suitab=
le for mysql server?=0A> To: "wroxdb" =0A> Cc: "mysql" ysql@lists.mysql.com>=0A> Date: Monday, 25 October, 2010, 10:03=0A> On Mon,=
Oct 25, 2010 at 7:56 AM,=0A> wroxdb =0A> wrote:=0A> =0A>=
> Hello,=0A> >=0A> > We are a company for gaming.=0A> > Our main db is mys=
ql 5.1 installed on Linux.=0A> > Currently the hardware for mysql is 2*4 CP=
U, 16G=0A> memory, Raid 10 (four=0A> > disks).=0A> > Now we have the plan t=
o replace the disks with SSD for=0A> better performance.=0A> > Do you think=
is it right for the SSD solution for=0A> mysql?=0A> >=0A> =0A> It may or m=
ay not be, depending on which problem you're=0A> trying to solve :-)=0A> =
=0A> For starters, how big is your DB ? If it fits in memory=0A> anyways, y=
ou'll not=0A> see a lot of benefit for selects. SSD may still be useful=0A>=
if you have a lot=0A> of writes, though.=0A> =0A> If the database doesn't =
fit in available memory, a lot more=0A> factors are=0A> going to apply, dep=
ending on the usage patterns.=0A> =0A> Incidentally, i'm not aware of how S=
SD plays with=0A> hard/software RAID setups=0A> - anyone know more about th=
is ?=0A> =0A> There have been some reports of raid cards not behaving=
themselvs with SSDs attached. I guess it depends on how important you=
r data is too. Quite a few of the SSDs on the market have been proven to n=
ot honour flush requests, so if the power goes out you've got corrupted dat=
a. That's not to say that SSDs don't look promising, the more expensiv=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org

RE: Is SSD suitable for mysql server?

--- On Mon, 25/10/10, Daevid Vincent wrote: > From=
: Daevid Vincent =0A> Subject: RE: Is SSD suitable for m=
ysql server?=0A> To: "'mysql'" =0A> Date: Monday, 25=
October, 2010, 21:52=0A> > I guess it depends on how=0A> important your da=
ta is too.=A0 Quite =0A> > a few of the SSDs on the market have been proven=
to=0A> not =0A> > honour flush requests, so if the power goes out you've=
=0A> got =0A> > corrupted data.=0A> =0A> Uh. If you're not using a UPS batt=
ery backup then you=0A> deserve to loose your=0A> data. And if you don't ha=
ve it configured to auto-power off=0A> when it's low=0A> on battery (extend=
ed outtage) then you also deserve to feel=0A> the resulting=0A> pain. (http=
://www.apcupsd.com/)=0A> 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 th=
e 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. =0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Is SSD suitable for mysql server?

Hi Warren, all!


Your statement is true ...

Warren Young wrote:
> [[...]]
>=20
> 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 i=
n
> 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).

>=20
> 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.

>=20
> [[...]]
>=20

Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@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=3Dgcdmg-mysql-2@m.gmane.o rg

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=gcdmg-mysql-2@m.gmane.org