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

Toggle sidebar Toggle sidebar
G

Guest

Guest
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/storage/disk/ds4000/ds4100/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
 
P

peter

Distinguished
Mar 29, 2004
3,226
0
20,780
Archived from groups: comp.sys.ibm.pc.hardware.storage (More info?)

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

Sorry, I didn't mean to be too harsh, let me try again.

> 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).
>

You'd better start with estimated usage patterns.
How many concurrent users?
How many transactions per user per unit of time?
How many logical IO's per transaction?
What RAID you plan to use?
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).
Calculate for usage growth properly.

> 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."

Design and test properly. Your failover might choke with all active users.

>
> My job is to investigate various solutions for housing the data. I am
> considering the following storage device:
>
> http://www-1.ibm.com/servers/storage/disk/ds4000/ds4100/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?

No. It is designed for near online data storage.
(Unless database usage patterns are very light)
Or as a cell in a database storage grid.
http://www.oracle.com/technology/deploy/availability/pdf/lcs_OW.ppt.pdf

>
> 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?

That depends on the type of database. RAID 10 is fine.
You might get away with RAID 5 (more efficient) depending on RAID
hardware performance (test, test, test...)
Don't forget to include hot spares.
I think separating transaction logs to a separate set of spindles is an old
school.
(unless you know how to balance capacity and performance between different
storage groups).

>
> 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?

You have answered yourself.
For example, 256MB/sec = 64,000 IO/sec x 8KB/IO
You would need 500 active disks (128 IO/s per disk) to achieve that!
2Gb/sec is fine.

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

Have multiple HBA's for fault tolerance.
Think about backup and restore times, and network design too.
 
G

Guest

Guest
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
 
G

Guest

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

> I think separating transaction logs to a separate set of spindles is an
old
> school.

Could you elaborate on this a bit more? Are you saying that in most
cases it makes sense to have both the database and the logs on a single RAID
1+0 (for example) instead of placing the database, logs, and tempDB on
separate volumes?

> (unless you know how to balance capacity and performance between different
> storage groups).

What does this mean exactly? It sounds like you're saying that placing
logs on a different volume isn't just a no-brainer practice that everyone
should follow. It only makes sense in situations where one has a VERY good
understanding of the performance characteristics of the database. Is that
what you mean?

Thanks again,

David
 
P

peter

Distinguished
Mar 29, 2004
3,226
0
20,780
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/articles/200111/20011109Renaissance_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)
 
P

peter

Distinguished
Mar 29, 2004
3,226
0
20,780
Archived from groups: comp.sys.ibm.pc.hardware.storage (More info?)

> > I think separating transaction logs to a separate set of spindles is an
> old
> > school.
>
> Could you elaborate on this a bit more? Are you saying that in most
> cases it makes sense to have both the database and the logs on a single
RAID
> 1+0 (for example) instead of placing the database, logs, and tempDB on
> separate volumes?

That depends on database and logs IO activities. Old school observed that
log activites are usually sequential by nature and typical IO block size
different than database typical IO block size. As storage groups are
optimized using that criteria, it became logical that they should be
separate.
Otherwise sequential IO mixed with random IO becomes random IO.
Single disks can have performance of thousands IO/s if those IO's are
sequential.

All depend on detail. What is the ratio between random IO and
sequential IO? Does sequential log files IO occur at the same time as
database random IO?

What about if you want to have more than one database? Two sets of
log files will change IO access pattern (not sequential anymore) if placed
on the same disk group, etc...

>
> > (unless you know how to balance capacity and performance between
different
> > storage groups).
>
> What does this mean exactly? It sounds like you're saying that placing
> logs on a different volume isn't just a no-brainer practice that everyone
> should follow. It only makes sense in situations where one has a VERY good
> understanding of the performance characteristics of the database. Is that
> what you mean?

It is no-brainer if you do not have hard constraints and do not care about
resource waste. Then you design for two (or more) separate disk groups.

Knowledge of system characteristics enables you to maximize
efficiency and/or reduce cost.
 
G

Guest

Guest
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/articles/200111/20011109Renaissance_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)
 
You must log in or register to reply here.

TRENDING THREADS

Latest posts

Moderators online

Share this page

COMPANY
RESOURCES
FOLLOW
Top Bottom