| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2008-01-21 : 10:19:16
|
Or should I place my database files on SAN or directly-attached storage? This is a frequently asked question. It comes uprepeatedly in public newsgroups, email discussion lists, and private meetings with customers who are concerned with database performance. Read Which is Faster: SAN or Directly-Attached Storage? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2008-01-21 : 10:34:25
|
| DAS is faster-ec |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-21 : 16:25:52
|
| What kind of link used for each of them? |
|
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2008-01-21 : 17:48:30
|
| Standard answer: It depends."Disk may be cheap, but well-performing disk certainly is not cheap" - has been my motto for many years now now that storage has gotten so 'cheap'. ;) |
|
|
|
rod merritt
Starting Member
1 Post |
Posted - 2008-02-05 : 09:44:16
|
| Holy cow! I tried the benchmarking program on our local C: drive and the SAN, and both came out between 130 and 160 milliseconds! If they should be in the microseconds range then I apparently have big problems to look into. Otherwise I'm wondering if the time is really thrown off by having to cast integer as character and do the datetime arithmatic. Isn't this measuring a lot more than disk I/O?Has anybody else run this benchmark with the same results as me? I get .0013 to .0016 which is in millisecs, not microsecs. Rod Merritt |
|
|
|
cfederl
Starting Member
26 Posts |
Posted - 2008-02-05 : 21:01:37
|
| The benchmarking process does not indicate if the SAN is dedicated to the single server or is shared amoung multiple servers. You mention the "highly shared .. nature of a large-scale SAN" but have given no indication of throughput when the SAN is shared. For the customer I am at, a single SAN is associated with 48 servers having 272 CPUs, all dedicated to SQL Server. How do you think the SAN would fare if the Disk I/O latency benchmark was run under these scenarios:With 272 databases and one benchmark process per database?With 48 databases (one per server) and 272 benchmark processes(one per CPUs)?With 48 databases (one per server) with 1088 benchmark processes (four per CPUs)?"servers in a large data center can take advantage of the SAN-level replication technologies to effectively meet the application disaster recovery requirements en mass."This is a significant advantage of SANs. We do DR tests every month (each application is on a semi-annual cycle) and when using SAN replication, a DR environment is available in about 30 minutes with most of the time waiting for the DNS changes to "replicate". If the servers are DAS with third party file replication software, we have yet to have a DR test succeed !Carl Federl |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-05 : 22:59:14
|
| Tried XOsoft's WANSync HA for SQL? |
|
|
|
lshea
Starting Member
5 Posts |
Posted - 2008-02-19 : 13:32:13
|
| Carl;The SAN used in the benchmark tests was shared by many server hosts. I didn't check exactly how many though. For the described tests, the question of exactly how many hosts were sharing the disk array and what exactly was load from these hosts at the time of testing was interesting, but not exactly relevant, as long as I made sure that the test results were reproducible through repeated test runs. The load test scenario you described would give some useful insight, but would be very difficult, if not highly impractical, to administer. Also, as mentioned in the article, SAN is not a monolithic entity. Rather, it's made up of many components, and each of these components can become the bottleneck for a given workload. In your case, one way to get a better understanding of how these hosts may come down on the SAN infrastructure is to review the SAN infrastructure architecture. For instance, you may want to find out how many hosts are sharing a FA port (or a set of FA ports). By looking at the I/O traffic from these hosts, you often can determine whether these shared FA ports will become a bottleneck without actually running any tests that involve all the related hosts. Now, often FA ports are over subscribed because I/Os from hosts very rarely come in evenly. Rather, they almost always come in bursts. Not oversubscribing the FA ports would result in waste. Oversubscribing the FA ports may result in I/O traffic jam at these FA ports. But the hope is that because I/Os come in bursts, they'll even the load level out for teh FA ports. |
|
|
|
lshea
Starting Member
5 Posts |
Posted - 2008-02-19 : 13:36:41
|
| Rod;If the I/O latency under very low load level comes in around 120 milliseconds, you've got something terribly wrong with your I/O subsystem. For instance, I/O latency of 120 milliseconds mean that you can only do about 8 transaction commits per second in a given database, a ver low transaction rate for any application. |
|
|
|
mtomlins
Starting Member
1 Post |
Posted - 2008-03-15 : 13:10:00
|
| Please consider the following:A single SAS controller (e.g. HP P800) can deliver roughly about 1 Gigabyte/sec to the hostA single FC HBA link can delivery a maximum of about 400 Megabyte/sec - commonly now HBA's are dual-port ~800 Megabyte/secSo - if you want to deliver 2 Gigabytes/sec to SQL, you'll need: ~2 SAS controllers ~3 Dual-port FC HBA (total of 6 links)In that respect, a DAS is slightly more efficient. However, if you look at large relational datawarehouse which may require sequential scans of 100TB+, then the difference is massive. With SAN - there are large, redundant drive arrays managed by the arrayWith DAS - there are small, independent disk shelves with limited "intelligence"With SAN - there are internal processors and logic for managing 100+ TB of data (replication, migration, snapshots, etc.)With DAS - because the shelves are "dumb" the host OS must do *all* the work - mirroring, or snapshots, backups, etc.With SAN - there are exponentially more capacity for host connectivity, up to 100's of FC portsWith DAS - the disk shelves are typically connected to 1 host, and very limited sharing of data with other hostsSo, there are pros and cons beyond just MB/IOps that should be considered here. |
|
|
|
RevMike
Starting Member
9 Posts |
Posted - 2008-04-04 : 09:42:27
|
| DAS vs SAN is largely a tradeoff of hardware and administration cost vs. database performance. DAS can always be made faster than SAN, but a well run SAN environment can usually provide acceptable performance at a lower cost.Consider one performance aspect. With a DAS system, a DBA will have an array of "spindles" to deal with, connected to various controllers. The DBA can plan such that a particular table is on one spindle, and the indexes associated with that table are on other spindles, and tables that commonly join with that table are still on other spindles. This kind of careful planning allows the DBA to prevent thrashing and contention. On the other hand, the DBAs who can do this type of work demand top dollar.Alternately with a SAN (or even with the wrong kind of RAID), it is difficult or impossible to achieve this level of control. It is possible in the worst case that the SAN will place the table and it's index on the same spindle, and so any sort of read or write operation there means that the drive head is constantly moving back and forth, and performance is much slower. On the other hand, a DBA that doesn't understand how to manage this is probably going to be ok overall. The likelihood of this worst case scenario is lower with a less skilled DBA than with DAS. A less skilled DBA is almost certainly going to use DAS incorrectly. |
|
|
|
rowlandg
Starting Member
1 Post |
Posted - 2009-01-30 : 15:36:49
|
A whole article to tell us 'it depends' -- there's a surprise  While I agree you shouldn't make either blanket statement my experience has been that SAN vendors stretch the truth beyond recognition. And once they've gotten a 'C' level exec on the golf course (or worse) it is often a done deal. So we're left to clean up the mess--broken promises, lousy performance and the rest. The argument isn't SAN vs. DAS. It's really more about who should be making this decision. tx |
|
|
|
|
Article: Which is Faster: SAN or Directly-Attached Storage?