Thursday, February 26, 2009

Upcoming Speaking Engagements

I've got three speaking engagements lined up in next two months:

  • Wed, March 11 @ oPASS: The Truth About Disk Performance & Configuration. This presentation is based on the results from my recent Disk Performance Hands On series
  • Sat, March 28 @ Orlando .NET Code Camp: Useful T-SQL Statements You May Not Be Aware Of. This session will highlight several useful T-SQL statements that most people may not be aware of. Examples include: PIVOT, MERGE, ROW_NUMBER, RANK, OUTPUT, SCOPE_IDENTITY(), and more
  • Sat, April 25 @ SQLSaturday #13 (ATL): The Truth About Disk Performance & Configuration 

If you make it out to any of these be sure to drop by and say hello!

Monday, February 23, 2009

My First Computer

Image Courtesy of www.computercloset.org Denis Gobo started another quiz, this time mostly unrelated to SQL. The question: What was your first computer and what were some of your favorite games? Brent Ozar tagged me last week but I was so wrapped up in finishing my disk performance series that I didn't get around to answering this until now…

My first computer was a Tomy Tutor. "A what?" you're probably saying to yourself. Introduced to the US in 1983, the Tomy Tutor had a CPU than ran at a blazing 2.7 MHz. It sported 16K RAM (expandable to 64K!), 48K ROM, and with a 256 x 192 resolution at a stunning 16 colors. It ran the Tomy BASIC OS and had built in paint and GBASIC programs. It had a useless 55 key rubber keyboard (think rubber = waterproof? Uh, no, think again). Peripherals included a cassette tape drive for saving drawings and programs, a joystick, and paddles for playing games.

The games were cartridges that plugged into a slot above the keyboard. My favorites – which happened to be the only ones I owned - were Traffic Jam, Deep Six, and Pooyan.

I managed to find this Google video which shows off how high-tech this was (For some reason embedding it blew up IE so I'm linking to it instead).

My Second Computer
OK, the Tomy Tutor was lame, but it was my first computer. It didn't take me long before I graduated to the C64, and oh what a difference that was! It had this nifty thing called a modem that you could use to call BBSs and share stuff. And don't forget about a floppy disk drive that could load and save data so much faster than those cassette tapes! How cool was that? OK, still lame by today's standards – but that was 1984 and it was all the rage.

Favorite games include: BC's Quest For Tires, Choplifter, Impossible Mission, Moon Buggy, and the classic M.U.L.E. Wow, I think I just got a tear in my eye remembering all the good times I had playing those games!

After That Came…
There were a couple of others I used afterwards worth mentioning for the fun of it. Yes, I am a geek.

  • Franklin (Apple clone, loved programming BASIC on it!)
  • KayPro 2X (I still remember typing papers on that green monochrome screen)
  • 286 (many gaming memories here – King's Quest, Loom, and Links 286 - "Looks like he…hit the tree, Jim")
  • 386 (with a math coprocessor!)
  • 486

Once the Pentiums came out everything was a blur. But I will always look back fondly at those first couple of computers. Hindsight is 20\20, and it's my early days tinkering around with those machines that led me to a career in computer science.

I'm pretty late on my response so I'm not going to tag anyone else since everyone else I know has already been tagged.

Friday, February 20, 2009

Disk Performance Hands On: Series Recap

This is Part 8 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

Now that we’ve come to the end of the series let’s look back at what we’ve learned:

OLTP Operations

  • For data files SQL Server reads and writes are 8KB and random in nature
  • For transaction logs SQL Server writes are between 512 bytes & 64KB and sequential in nature

RAID Stripe, Partition Offset, & Allocation Unit Size

  • Optimal settings for OLTP data operations on local disks and DASD
    • 64 KB RAID stripe
    • 64 KB partition offset
    • 64 KB allocation unit size
  • For OLTP transaction log operations on RAID 1 choice of partition offset and allocation unit size make little to no difference in performance
  • 64 KB partition offset vs. 32 KB partition offset
    • For RAID 10 with a 64 KB RAID stripe and 64 KB allocation unit, using a 64 KB offset resulted in an 11% improvement in IOs/sec and MBs/sec, and a 10% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 10.6% improvement in IOs/sec, MBs/sec, and average latency
    • For RAID 5 with a 64 KB RAID stripe and a 64 KB allocation unit, using a 64 KB offset resulted in a 5.7% improvement in IOs/sec and MBs/sec and an 8% improvement in average latency for 8 KB random reads. For 8 KB random writes using a 64 KB offset resulted in a 5.5% improvement in IOs/sec, MBs/sec, and average latency

RAID 10 vs. RAID 5

  • For a RAID 5 array using the same number of physical disks as a RAID 10 array, RAID 5 offers 10-15% better performance for 8 KB random reads but a whopping 60-65% worse performance for 8 KB random writes
  • To get RAID 10 write performance out of RAID 5 will require a RAID 5 array to use roughly 2x the number of physical disks than are present in the RAID 10 array

RAID 10 vs. RAID 1

  • If you take a RAID 10 drive which holds a single OLTP data file and reconfigure the physical drives as multiple RAID 1 drives which each holding a data file of equal size and belonging to the same filegroup, expect to see roughly equivalent performance as the RAID 10 configuration

PowerVault 220S (SCSI) vs. PowerVault MD1000 (SAS)

  • For both RAID 10 and RAID 5 on a PowerVault MD1000 expect to see 5-10% better performance for 8 KB random reads and 10-12% better performance for 8 KB random writes than the equivalent configuration on a PowerVault 220S

Misc Takeaways

  • Make sure you test against your target workloads. As I was writing this series I initially looked at 64 KB random reads. After I re-evaluated my results using 8 KB random reads I changed my recommendation for the optimal RAID stripe size to use.
  • My findings apply to local disks and DASD. If you’re on a SAN, things like partition offset are still applicable but ultimately you should consult your vendor or enterprise storage architect about optimizing disk performance.

I’ve enjoyed writing this series, and I hope that you have likewise enjoyed reading it! If you have any questions, comments, etc. please leave a comment below or feel free to contact me at kendal.vandyke at gmail dot com.

Thursday, February 19, 2009

Disk Performance Hands On, Part 7: PowerVault 220S vs. PowerVault MD1000

This is Part 7 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

For the last 7 or so years the PowerVault 220S was Dell's offering in DASD enclosures. It supports 14 SCSI drives and can run in split bus mode with 7 drives per SCSI bus. It's since been replaced by the PowerVault MD1000 which supports 15 SAS\SATA drives and can also run in split bus mode with 7 drives on one bus and 8 drives on the other. I recently had the opportunity to run tests against both models to see how they compare against one another. I didn't have too long with them so I was only able to test RAID 10 and RAID 5 before I had to put them back into action in production.

The PowerVault 220S tests were run using 12 146 GB 15K RPM Ultra320 SCSI drives and the enclosure was connected to a PowerEdge 2850 via a PERC 4/DC controller card. The PowerVault MD1000 tests were run using 12 146 GB 15K RPM SAS drives and the enclosure was connected to a PowerEdge 2950 via a PERC 5/E controller card. Tests were done against each enclosure in split bus mode using a 64 KB RAID stripe, 64 KB allocation unit size, and partition offsets of 32 KB, 64 KB, and 1024 KB. To see how each system scaled under load I ran separate tests with 4, 8, and 16 threads with an IO queue depth of 8 against a 512 GB data file. You can view the specifics of each setup here.

PowerVault 220S – RAID 10
Here's what 8 KB random reads look like:

IOs/sec, 8 KB random reads, PowerVault 220S, RAID 10 MBs/sec, 8 KB random reads, PowerVault 220S, RAID 10 Avg latency, 8 KB random reads, PowerVault 220S, RAID 10

And here's what 8 KB random writes look like:

IOs/sec, 8 KB random writes, PowerVault 220S, RAID 10 MBs/sec, 8 KB random writes, PowerVault 220S, RAID 10 Avg latency, 8 KB random writes, PowerVault 220S, RAID 10

PowerVault 220S – RAID 5
Here's what 8 KB random reads look like:

IOs/sec, 8 KB random reads, PowerVault 220S, RAID 5 MBs/sec, 8 KB random reads, PowerVault 220S, RAID 5 Avg latency, 8 KB random reads, PowerVault 220S, RAID 5

And here's what 8 KB random writes look like:

IOs/sec, 8 KB random writes, PowerVault 220S, RAID 5 MBs/sec, 8 KB random writes, PowerVault 220S, RAID 5 Avg latency, 8 KB random writes, PowerVault 220S, RAID 5

PowerVault MD1000 – RAID 10
Here's what 8 KB random reads look like:

IOs/sec, 8 KB random reads, PowerVault MD1000, RAID 10 MBs/sec, 8 KB random reads, PowerVault MD1000, RAID 10 Avg latency, 8 KB random reads, PowerVault MD1000, RAID 10

And here's what 8 KB random writes look like:

IOs/sec, 8 KB random writes, PowerVault MD1000, RAID 10 MBs/sec, 8 KB random writes, PowerVault MD1000, RAID 10 Avg latency, 8 KB random writes, PowerVault MD1000, RAID 10

PowerVault MD1000 – RAID 5
Here's what 8 KB random reads look like:

IOs/sec, 8 KB random reads, PowerVault MD1000, RAID 5 MBs/sec, 8 KB random reads, PowerVault MD1000, RAID 5 Avg latency, 8 KB random reads, PowerVault MD1000, RAID 5

And here’s what 8 KB random writes look like:

IOs/sec, 8 KB random writes, PowerVault MD1000, RAID 5 MBs/sec, 8 KB random writes, PowerVault MD1000, RAID 5 Avg latency, 8 KB random writes, PowerVault MD1000, RAID 5

Head To Head - RAID 10
Now for fun I’ll look at a head to head RAID 10 matchup of the 220S against the MD1000. These comparisons use the same testing parameters I outlined at the top of this post except that I am focusing only on a 64 KB partition offset this time.

Here’s what 8 KB random reads look like:

IOs/sec, 8 KB random reads, PowerVault 220S vs MD1000, RAID 10 MBs/sec, 8 KB random reads, PowerVault 220S vs MD1000, RAID 10 Avg latency, 8 KB random reads, PowerVault 220S vs MD1000, RAID 10

And here’s what 8 KB random writes look like:

IOs/sec, 8 KB random writes, PowerVault 220S vs MD1000, RAID 10 MBs/sec, 8 KB random writes, PowerVault 220S vs MD1000, RAID 10 Avg latency, 8 KB random writes, PowerVault 220S vs MD1000, RAID 10

 

Head To Head - RAID 5
Here’s what 8 KB random reads look like:

IOs/sec, 8 KB random reads, PowerVault 220S vs MD1000, RAID 5 MBs/sec, 8 KB random reads, PowerVault 220S vs MD1000, RAID 5 Avg latency, 8 KB random reads, PowerVault 220S vs MD1000, RAID 5

And here’s what 8 KB random writes look like:

IOs/sec, 8 KB random writes, PowerVault 220S vs MD1000, RAID 5 MBs/sec, 8 KB random writes, PowerVault 220S vs MD1000, RAID 5 Avg latency, 8 KB random writes, PowerVault vs MD1000, RAID 5

Conclusion
No surprises here, the newer controller card and faster drives in the PowerVault MD1000 outperformed the PowerVault 220S. The chart below shows the MD1000’s performance metrics as a percentage relative to the 220S. Recall that for average latency lower values mean better performance so a negative number indicates a decrease in latency (which is good)

PowerVault MD1000 performance relative to PowerVault 220S

In Part 8 I will wrap things up and recap the conclusions reached in Parts 1-7.

Wednesday, February 18, 2009

Disk Performance Hands On, Part 6: RAID 10 vs. RAID 1

This is Part 6 in an ongoing series on disk performance. You can read the entire series by starting at the Introduction.

Pop quiz: If you had to configure 4 physical disks to hold data, which of these two options will result in the best performance:

  1. One RAID 10 array which will hold a single data file.
  2. Two RAID 1 arrays, each holding a data file that belong to the same filegroup.

I asked this same question recently on SQLServerCentral.com (read the thread here – free registration required). The resulting debate showed me that the answer is not as obvious as it might seem. Recall that in RAID 10 a hardware based controller card handles writing data evenly to all drives (unless you’re using software based RAID, which is beyond the scope of this series). SQL MVP Tony Rogerson did some tests to show that SQL Server will write data evenly to multiple files if they are in the same filegroup. That makes the two RAID 1 drive scenario, in principle, operate in a similar way as RAID 10.

Walks Like RAID 10, Talks Like RAID 10…
To find out which is the better choice I’ll use the results from Test Harness #1 – a Dell PowerEdge 2950 (view the test harness specifics here). I’ll look at the optimal settings first: 64 KB raid stripe (for RAID 10), 64 KB partition offset, and 64 KB allocation unit. On the RAID 10 setup I tested a 128 GB data file using 8 threads with an IO queue depth of 8. On the RAID 1 setup I tested a 64 GB data file using 4 threads with an IO queue depth of 8. That’s half the file size and load I put on RAID 10; my voodoo math reasoning is that since I am using two data files on RAID 1 I can double the IOs/sec and MBs/sec results for the single data file and arrive at something that’s comparable to the RAID 10 load. Note I’m doing it this way because SQLIO can’t measure the load on multiple data files simultaneously. Also, since latency is an average value I’m not doubling it for RAID 1 – instead I’m just doing a direct comparison to RAID 10.

Here’s what 8 KB random reads look like:

IOs/sec, 8 KB random reads, RAID 10 vs. RAID 1 MBs/sec, 8 KB random reads, RAID 10 vs. RAID 1 Avg Latency, 8 KB random reads, RAID 10 vs. RAID 1

And here’s what 8 KB random writes look like:

IOs/sec, 8 KB random writes, RAID 10 vs. RAID 1 MBs/sec, 8 KB random writes, RAID 10 vs. RAID 1 Avg Latency, 8 KB random writes, RAID 10 vs. RAID 1

By the numbers it looks like the two RAID 1 drive scenario not only walks and talks like RAID 10, but behaves just like RAID 10 too.

Can Multiple RAID 1 Drives Really Outperform RAID 10?
I’ll admit, before I knew about using the right partition offset and allocation unit size I set up my servers using multiple RAID 1 drives. My gut feel was that it just seemed to perform better than if the same drives were set up in RAID 10. Let’s say for the sake of argument that you’re in the same situation - using multiple RAID 1 drives built using the defaults and after reading this series you’re thinking you need to rebuild everything as RAID 10. Is it really worth it? To find out, I’ll compare an optimal RAID 10 setup (64 KB stripe, 64 KB offset, and 64 KB allocation unit) against two RAID 1 drives built on the Windows defaults (32 KB offset and 4 KB allocation unit). On the RAID 10 I’ll use a 128 GB data file with 8 threads and an IO queue depth of 8. For RAID 1 I’ll use a 64 GB data file with 4 threads and an IO queue depth of 8, doubling the IOs/sec and MBs/sec to simulate the same load as the RAID 10 tests.

Here’s how 8 KB random reads look:

IOs/sec, 8 KB random reads, RAID 10 vs. RAID 1 MBs/sec, 8 KB random reads, RAID 10 vs. RAID 1 Avg Latency, 8 KB random reads, RAID 10 vs. RAID 1

And here’s how 8 KB random writes look:

IOs/sec, 8 KB random writes, RAID 10 vs. RAID 1 MBs/sec, 8 KB random writes, RAID 10 vs. RAID 1 Avg Latency, 8 KB random writes, RAID 10 vs. RAID 1

Once again the two RAID 1 drive scenario doesn’t look so bad. Obviously that’s not a very compelling argument to reconfigure your existing RAID 1 drives as RAID 10!

Conclusion
The point of these comparisons is to show that, in the most simplistic way, if you are already using multiple RAID 1 drives which hold data files in the same filegroup you’re not as bad off performance-wise as you may think. That said, if you’re setting up a server from scratch I’d recommend RAID 10 over multiple RAID 1’s for two reasons:

  1. Today’s hardware based RAID controllers are pretty sophisticated and designed for reading from and writing data to disks in the most efficient way possible. By comparison, SQL Server’s proportional fill algorithm but doesn’t understand much about the physical disks on which files reside, nor does SQL Server employ the hardware based caching techniques that are built into controllers (and which were not factored into any of these tests).
  2. Towards the end of Tony Rogerson’s post he mentions that SQL will only round robin writes if there is equal free space in each file in the filegroup. As soon as the free space is different you’ll have one set of disks taking more IO than the other. That won’t happen with a single data file on a RAID 10 disk.

Next, In Part 7 I will compare a PowerVault 220S with a PowerVault MD1000.