Running SQL database off of SAN -- Is it feasible?

Archived from groups: comp.sys.ibm.pc.hardware.storage (More info?)

Sorry for the multi-post. I posted my last message to the wrong storage
newsgroup.

=========
Hi,

I'm trying to create a home for a database. The database will ultimately
reach approximately 200Gb in size. Usage patterns will consist of
approximately 80% read, 20% write. The vast majority of the reads will
require random access to small chunks of data (less than 64k).

Two SQL Server machines need to be connected to the same database. The
primary SQL Server will be a very powerful machine (4 CPUs). The second SQL
Server will be a machine of lesser capabilities and will only come online if
the primary data server fails. This concept, as it has been explained to me,
is known as "failover."

My job is to investigate various solutions for housing the data. I am
considering the following storage device:

http://www-1.ibm.com/servers/stora [...] index.html

This device holds 14 drives and transmits data via a mechanism called
Fibre Channel, which as far as I can tell, supports a throughput of 2Gbps.
Here are my questions and concerns:

1) Is this device suitable for hosting a database meeting the
characteristics and requirements that I've described?

2) Assuming that I placed 14 drives in this storage device, how would I
partition it? I've read that the transaction log should be on a separate
RAID 1 volume, so perhaps I would allocate drives 1 & 2 as a RAID 1 volume
and 3-14 as RAID 1+0 ? What do you recommend?

3) I'm concerned because the Fibre Channel maxes out at 2Gbps. This
equates to only 256MBps, as opposed to SCSI which is 320MBps. Is this even a
relevant concern given that most I/O will be random, not sequential, and
therefore I may not even be hitting the 256MBps cap anyway?

I won't actually be the one installing the storage system. I'll
obviously need a professional for that. I just want to do enough research to
determine whether or not the slick salesman who sells us the solution is
giving me accurate information regarding the suitability of various storage
solutions.

Thanks for the help,

David

Archived from groups: comp.sys.ibm.pc.hardware.storage (More info?)

> > http://www-1.ibm.com/servers/stora [...] index.html
>
> Your choice of an appropriate storage solution for a large database,
proves
> that you need to hire a database consultant first.
>

OMG, that was so insightful!!! Thanks.

Archived from groups: comp.sys.ibm.pc.hardware.storage (More info?)

Thanks for the great information. I understand most of your suggestions
and will try to answer the questions you've asked prior to meeting with the
SAN pro. I want to ask a follow-up question regarding one of your
recommendations.

> Estimate required hard IO/sec. Don't use SATA array for a database
> (unless very light usage <20 IO/s per disk).
> Don't put more than 75 IO/s per physical active SCSI disk
> (hot spares are not active).

...and how many IOPS would you estimate would be appropriate for a fibre
channel disk?

Why does the type of connector used for the drive (SATA, SCSI, etc.)
affect the number of IOs/second for the drive? I would have thought that the
max IOPS would be determined by the drive iteself (RPM, seek time, etc.),
not the connector.

Thanks for the tip on SATA. I was considering a SATA array product, but
given what you've presented here, SATA now appears unsuitable.

How does one even determine the max IOPS for a given drive? I've looked
at various hard drives and the crucial specs (IOPS, sustained read rate,
etc.) don't even appear on the box. They only like quoting the external bus
speed, which for SCSI is 320MB/s. This lack of information makes planning
very difficult.

David

Archived from groups: comp.sys.ibm.pc.hardware.storage (More info?)

> Thanks for the great information. I understand most of your
suggestions
> and will try to answer the questions you've asked prior to meeting with
the
> SAN pro. I want to ask a follow-up question regarding one of your
> recommendations.
>
> > Estimate required hard IO/sec. Don't use SATA array for a database
> > (unless very light usage <20 IO/s per disk).
> > Don't put more than 75 IO/s per physical active SCSI disk
> > (hot spares are not active).
>
> ...and how many IOPS would you estimate would be appropriate for a
fibre
> channel disk?

For a single hard disk? As far as I understand, maximum IO/s depends
mostly on average seek times and rotational speed. But SCSI allows to
queue up disk operations better than SATA. BTW, FC uses SCSI-3
internally so has the same characterustic as SCSI.
http://www.windowsitpro.com/Files/179/Table_01.html

Said that, FC/SCSI based hard drives can achieve:
http://www.storagereview.com/artic [...] nce_8.html
15,000 rpm SCSI
Maxtor Atlas 15K II (147 GB Ultra320 SCSI) 8E147L0 372 IO/s
Seagate Cheetah 15K.3 (73 GB Ultra320 SCSI) ST373453LW 345 IO/s
10,000 rpm SATA
Western Digital Raptor WD740GD (74 GB SATA w/ TCQ) WD740GD 226 IO/s
7,200 rpm SATA
Seagate Barracuda 7200.7 (160 GB SATA w/ NCQ) ST3160827AS 140 IO/s

I would take those benchmark numbers not so literally and estimate:
200 IO/s for a good 15K rpm SCSI disk
130 IO/s for a (Raptor) 10K rpm SATA disk
85 IO/s for a good 7.2K rpm SATA disk
But remember, those are physical disk IO's.
RAID implementation might add additional physical reads or writes
for every logical read or write request. That depends on RAID mode
and sometimes on RAID controller design (test, test, test ....)

That is for your "end of service period" performance, if you assume growth
in database activity throughout its life cycle.

> Why does the type of connector used for the drive (SATA, SCSI, etc.)
> affect the number of IOs/second for the drive? I would have thought that
the
> max IOPS would be determined by the drive iteself (RPM, seek time, etc.),
> not the connector.

Because cheaper interface is matched with cheaper drive assembly.
I'm yet to see 15K rpm SATA drives.

If you compare 7.2K SATA with 7.2K SCSI, difference is mostly due
to seek times and queuing. Old Barracuda 7.2K SCSI drive was rated
176 IO/s vs new Seagate SATA at 140 IO/s.
That would translate to my more conservative 105 vs 85 IO/s.

> Thanks for the tip on SATA. I was considering a SATA array product,
but
> given what you've presented here, SATA now appears unsuitable.
>
> How does one even determine the max IOPS for a given drive? I've
looked
> at various hard drives and the crucial specs (IOPS, sustained read rate,
> etc.) don't even appear on the box. They only like quoting the external
bus
> speed, which for SCSI is 320MB/s. This lack of information makes planning
> very difficult.

Sure, 320MB/s looks better than (obtained from Seagate site for
15K rpm ST3146854LC):
Formatted Int Transfer Rate (min) 85 MBytes/sec
Formatted Int Transfer Rate (max) 142 MBytes/sec
But (agree) there is no word on typical IO/s performance.

IO/s info is scattered in more or less formal benchmark results pblished on
web.
It depends who you believe...
But, did you ever try to evaluate a product(s) before you buy?

Don't forget reliability. SCSI drives can have easily 1.4M MTBF hours,
SATA do not have MTBF rating at all, but 0.34% AFR instead.
That change in reliability descriptor is probably driven by very different
test conditions for MTBF and AFR groups of products (again, conditions
often not disclosed by manufacturers)

Archived from groups: comp.sys.ibm.pc.hardware.storage (More info?)

Peter wrote:

>> Thanks for the great information. I understand most of your
> suggestions
>> and will try to answer the questions you've asked prior to meeting with
> the
>> SAN pro. I want to ask a follow-up question regarding one of your
>> recommendations.
>>
>> > Estimate required hard IO/sec. Don't use SATA array for a database
>> > (unless very light usage <20 IO/s per disk).
>> > Don't put more than 75 IO/s per physical active SCSI disk
>> > (hot spares are not active).
>>
>> ...and how many IOPS would you estimate would be appropriate for a
> fibre
>> channel disk?
>
> For a single hard disk? As far as I understand, maximum IO/s depends
> mostly on average seek times and rotational speed. But SCSI allows to
> queue up disk operations better than SATA. BTW, FC uses SCSI-3
> internally so has the same characterustic as SCSI.
> http://www.windowsitpro.com/Files/179/Table_01.html
>
> Said that, FC/SCSI based hard drives can achieve:
> http://www.storagereview.com/artic [...] nce_8.html
> 15,000 rpm SCSI
> Maxtor Atlas 15K II (147 GB Ultra320 SCSI) 8E147L0 372 IO/s
> Seagate Cheetah 15K.3 (73 GB Ultra320 SCSI) ST373453LW 345 IO/s
> 10,000 rpm SATA
> Western Digital Raptor WD740GD (74 GB SATA w/ TCQ) WD740GD 226 IO/s
> 7,200 rpm SATA
> Seagate Barracuda 7200.7 (160 GB SATA w/ NCQ) ST3160827AS 140 IO/s
>
> I would take those benchmark numbers not so literally and estimate:
> 200 IO/s for a good 15K rpm SCSI disk
> 130 IO/s for a (Raptor) 10K rpm SATA disk

One comment--Storage Review states that they were not using a host adapter
that supported command queuing on their tests of the Raptor--it would be
interesting to see how a Raptor does on those same tests with command
queuing supported, and whether the IBM array supports it.

> 85 IO/s for a good 7.2K rpm SATA disk
> But remember, those are physical disk IO's.
> RAID implementation might add additional physical reads or writes
> for every logical read or write request. That depends on RAID mode
> and sometimes on RAID controller design (test, test, test ....)
>
> That is for your "end of service period" performance, if you assume growth
> in database activity throughout its life cycle.
>
>> Why does the type of connector used for the drive (SATA, SCSI, etc.)
>> affect the number of IOs/second for the drive? I would have thought that
> the
>> max IOPS would be determined by the drive iteself (RPM, seek time, etc.),
>> not the connector.
>
> Because cheaper interface is matched with cheaper drive assembly.

Not necessarily. I've never seen any credible claim that the mechanicals on
a Raptor were less durable than the mechanicals on a SCSI drive of the same
capacity.

> I'm yet to see 15K rpm SATA drives.
>
> If you compare 7.2K SATA with 7.2K SCSI, difference is mostly due
> to seek times and queuing. Old Barracuda 7.2K SCSI drive was rated
> 176 IO/s vs new Seagate SATA at 140 IO/s.
> That would translate to my more conservative 105 vs 85 IO/s.
>
>> Thanks for the tip on SATA. I was considering a SATA array product,
> but
>> given what you've presented here, SATA now appears unsuitable.
>>
>> How does one even determine the max IOPS for a given drive? I've
> looked
>> at various hard drives and the crucial specs (IOPS, sustained read rate,
>> etc.) don't even appear on the box. They only like quoting the external
> bus
>> speed, which for SCSI is 320MB/s. This lack of information makes planning
>> very difficult.
>
> Sure, 320MB/s looks better than (obtained from Seagate site for
> 15K rpm ST3146854LC):
> Formatted Int Transfer Rate (min) 85 MBytes/sec
> Formatted Int Transfer Rate (max) 142 MBytes/sec
> But (agree) there is no word on typical IO/s performance.
>
> IO/s info is scattered in more or less formal benchmark results pblished
> on web.
> It depends who you believe...
> But, did you ever try to evaluate a product(s) before you buy?
>
> Don't forget reliability. SCSI drives can have easily 1.4M MTBF hours,
> SATA do not have MTBF rating at all, but 0.34% AFR instead.

????? Western Digital states that the MTBF on Raptors is 1.2 million hours
for 100% duty cycle.

> That change in reliability descriptor is probably driven by very different
> test conditions for MTBF and AFR groups of products (again, conditions
> often not disclosed by manufacturers)

--
--John
to email, dial "usenet" and validate
(was jclarke at eye bee em dot net)