2

Assuming that the workload for the SQL Server is just a normal OLTP database, and that there are a total of 20 disks available, which configuration would make more sense?

A single RAID 1+0, containing all 20 disks. This physical volume would contain both the data files and the transaction log files, but two logical drives would be created from this RAID: one for the data files and one for the log files.

Or...

Two RAID 1+0s, each containing 10 disks. One physical volume would contain the data files, and the other would contain the log files.

The reason for this question is due to a disagreement between me (SQL Developer) and a co-worker (DBA).

For every configuration that I've done, or seen others do, the data files and transaction log files were separated at the physical level, and were placed on separate RAIDs.

However, my co-workers argument is that by placing all the disks into a single RAID 1+0, then any IO that is done by the server is potentially shared between all 20 disks, instead of just 10 disks in my suggested configuration.

Conceptually, his argument makes sense to me. Also, I've found some information from Microsoft that seems to supports his position.

http://technet.microsoft.com/en-us/library/cc966414.aspx

In the section titled "3. RAID10 Configuration", showing a configuration in which all 20 disks are allocated to a single RAID 1+0, it states:

In this scenario, the I/O parallelism can be used to its fullest by all partitions. Therefore, distribution of I/O workload is among 20 physical spindles instead of four at the partition level.

But... every other configuration I've seen suggests physically separating the data and log files onto separate RAIDs. Everything I've found here on Server Fault suggests the same.

I understand that a log files will be write heavy, and that data files will be a combination of reads and writes, but does this require that the files be placed onto separate RAIDs instead of a single RAID?

Michael Fredrickson
  • 208
  • 1
  • 6
  • Thank you all for the information. My confusion was as to why it is suggested to physically split the data and log files, and I now know that it is because data files are typically accessed randomly, while the log files are accessed sequentially. So for a disk array writing to a log file, the read/write head on the drive only has to typically move to the next sector... where as a data file will have the head moving all over the place. Does the sequential performance boost still hold true if there are log files from different databases on the same physical disk? – Michael Fredrickson Feb 28 '11 at 21:42

5 Answers5

3

The DBA's argument does make sense, but here's the rub: If I'm reading the article correctly MS is speaking to the performance at the DB partition and table level, not the disk/file system partition level. I believe the DBA would win the debate IF you were talking about what type of array to create for the data files ONLY. Since you're talking about what type of array to create for the data AND log files I believe your argumant makes the most sense, from a disk/file system performance perspective. Database files are always accessed randomly, log files are always accessed sequentially. You should never mix those two types of I/O on the same physical or logical drive. In addition, creating multiple logical partitions on the same physical disk (or disk array) buys you nothing as the databases and logs will be contending for the same physical resource.

My recoomendation is this:

Create one physical array (RAID10) for the database files and create a separate physical array (RAID10) for the log files.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
2

It's going to depend on the workload that you are putting on the system. If you have a high workload you want to break them apart because the data files are going to be a very random workload, while the transaction log is a very sequential workload. You will usually get better performance when you break them apart as you won't want the IO for the log to be slowed down, or to be impacted by the load from the data files.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
1

Hate to be snarky, but the DBAs are.. DBAs.

They're infinitely smarter than the server admin when it comes to constructing a complex query that doesnt do a bazillion table-scans, but the hardware/server admin is infinitely smarter just the same in allocating proper resources, (assuming he has a gray hair or four). :)

My short answer: It's all about the spindles, and it's all about spindles, and as of the past few years, your choice of filesystem and how much ram that filesystem can suck up.

I've had awesome success segregating dbdata / logs / transactionals amongst (a) different physical controllers, (b) using tweaked filesystem params (specifically, and this is a big one, matching the params of your db writes/reads/commits to the same size sector/block that the fs was constructed under) and (c) "Choosing my poison".

Noncritical data, logs, rollback datars and such can more or less go on "vulnerable" filesystems (memfs, fast-io-fs w/o journaling/preflight/precommit caching) while actual data files (depending on the flavor of db) are nicely spread on things as cheap as a well-constructed zpool.

The previous poster is absolutely right in that trans logs are sequential, and can/should go on volumes made for fast write, not read (and arguably not necessarily "stability",) such as a big stripe. The responder ("here's the rub") is also in the right : Disk contention is nasty if the data isn't in ram or in disk-cache, and without serious (pronounced "long, monotonous, tedious and prone to guessing error") analyses, you absolutely should try to avoid mixing disks with databases that function differently. (e.g. High trans read, and low trans bigdata write -- totally fubar's any cache strategy).

My "ISO Layer 8" advice is this : Approach the DBA and say, hey, I'm not gonna tell you you're wrong, and in return, you're not gonna tell me how to architect my systems :) They very often go on boilerplate recommendations that in the long run are rarely optimal. Not because they don't know what they're doing -- but because they're putting trust in a middle-of-the-road doc pushed out by $vendor as "designed to annoy the fewest # of customers / resulting in less help calls".

If you want to direct-msg me, feel free; But keep in mind -- There's no global ideal config. The number of rows, the anticipated scans, the cardinality and efficiency of your keys/indexes, queries/sec, full tablescans per interval, etc, all play into it. It's a tough game to play.

Reminds me of WARGAMES..

Would you like to play a game?

Yes. Let's play Global Thermonuclear War.

..

How about a nice game of Database Architecture?

[ Global Thermonuclear War strategy would have been easier.. ]

:)

jamie rishaw
  • 106
  • 1
0

The grossly simplified answer is that it will depend on your read/write ratio. Given OLTP, you've probably got a good mix, and keeping the trans logs separate from the DBs will allow the transaction to the DB array to "chase" the log array instead of one array bouncing between the two.

Chris S
  • 77,945
  • 11
  • 124
  • 216
0

The transaction log is normally only written to and it is written sequentially. If you keep the logs on an own set of disks, those disks will hardly ever have any seeks at all (except when updating file system metadata), just stepping forward one sector at a time.

If you have the logs and data mixed up on the same RAID set (even though they are on different logical units) you'll loose the performance boost from the sequential writing of the transaction logs.

I'd guess that if you will be writing small amounts of data in each transaction you'll win performance by keeping the logs on an own array. If you have so much data that the write time of the data to the disks is substantial compared to the seek time, it could be better to have one large array.

Anders Abel
  • 569
  • 3
  • 7