[PE1950] SW- or HW-RAID for MySQL?

[Tilmann.Boess at bifab.de]

On Thu, 24 Aug 2006 <eclark at alabanza.com> wrote:

> This question is incomplete, as you havent stated what your priority is, 
or 
> how the data is to be accessed. You mentioned that it is read only, and 
the 
> data in question is fairly large, but no indication of how often it is 
> accessed, or how fast. Is the db run using replication? Are you 
expecting 
> raid to maintain your datas integrity? How do you forsee this data to 
grow. 

The database is accessed very often, the priority is to accelerate 
relative complicated queries (table joins).  Small and simple queries 
are no problem at all, the PE2500 is more than fast enough for all of 
them.  We run no replication.  RAID we need mostly for protection 
against a disk failure.  The MySQL database is 10 GB in size now.
It will grow steadily but slowly.

> Will traffic to/from the db increase, or stay steady? What roughly is 
your 
> max transaction size? Is it largely done as one large query, or as a 
whole 
> bunch of small queries across multiple tables which would be accessing 
> nonsequential data from anywhere in the 100gigs of data you have? 

The 100 GB data will be stored in our SAN.  Since the CPU load is very 
high
and I/O waits are very low when accessing and processing the data, I 
suppose 
we don't have an I/O-bottleneck here.  The data is stored on the internal
disks of the PE2500 (HW-RAID-5, 6 73GB-10krpm-disks).

Our most pressing problem are table joins, typical is 
something like (mysql-slow.log):
  # Query_time: 15  Lock_time: 0  Rows_sent: 94  Rows_examined: 1672538
For one result several of these queries are needed.

> As you are 
> coming from a relatively reasonable piece of hardware, I can not think 
your 
> needs would be that drastic. I would  probably suggest running a fairly 
beefy 
> diskserve with 73gig 15ks in a raid, handed out to your mysql server as 
an 
> iscsi mount as you mentioned this data is read only. 

The slow joins are new.  .  We tried to optimize the queries and the 
application, but we suppose we could gain much by using faster 
hardware.  I thought to use the internal disks for the MySQL database. 
Do you think the 15krpm-3.5in-disks are faster for a database than the
10krpm-2.5in-disks?  But I could also put the database into the
SAN (FC, HW-RAID, 10krpm-300GB-disks).

The network performance is not important, the volume transferred to the
clients is relatively small.  The MySQL database is only used by the
server (access is restricted to localhost only).

> If all of the 
> above is wrong and this is just a generic db, lvm may be a better choice 
for 
> you than software raid, in  whatever box you want to go. My preference 
for 
> lvm in this situation is only due to the ease of which you could 
> expand/contract your available space, as your needs may change in the 
future. 

LVM is nice, but as I said before, the data will grow slowly.
Therefore it is no problem to make the filesystems big enough for the
next years.

> I would not suggest software raid unless you are depending on raid to 
keep 
> your data intact, as you would be limited to higher disk sizes with 
faster 
> spin rates and larger costs, and potentially larger access times as disk 
size 
> increases.

It is highly improbable that the database will exceed 40 GB in the next 
few years.  So the question is:  Is SW-RAID-1 in a PE1950 as fast as 
HW-RAID-1 when you build the RAID out of two 73GB-disks?

--
   Dr. Tilmann Boess   (TeX: Tilmann B\"o\ss)
   e-mail: boess at bifab.de