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

  • Thread starter Thread starter Larry David
  • Start date Start date
L

Larry David

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
 
Your choice of an appropriate storage solution for a large database,
proves

OMG, that was so insightful!!! Thanks.
Click to expand...

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).
Click to expand...

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."
Click to expand...

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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

Have multiple HBA's for fault tolerance.
Think about backup and restore times, and network design too.
 
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).
Click to expand...

...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
 
I think separating transaction logs to a separate set of spindles is an
old
school.
Click to expand...

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).
Click to expand...

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


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

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.
Click to expand...

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. :(
Click to expand...

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)
 
I think separating transaction logs to a separate set of spindles is an
old

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?
Click to expand...

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...
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?
Click to expand...

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.
 
Peter said:
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
Click to expand...

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.


Because cheaper interface is matched with cheaper drive assembly.
Click to expand...

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.


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.
Click to expand...

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