Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance

The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from both Intel and AMD and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.

Attendance was quite good for a hardware-related session, and there were a lot of interesting questions after the presentation.

Just in case this session whetted your appetite for a much deeper level of knowledge about SQL Server Hardware, you should consider attending the upcoming two-day IEHW: Immersion Event on SQL Server Hardware course that I will be teaching in Bellevue, WA on September 18-19, 2013.

The RMOUG event was interesting, since I had never been to an Oracle conference before. It felt a little strange, since I did not know anyone (and no one knew me). 

I was also surprised that there was a noticeably higher percentage of women in attendance than you normally see at most technical conferences. Typically, you might see only roughly 1-2% of the attendees at most computer-related conferences being female (which is very unfortunate). Just from my casual observation yesterday, it seemed like the female percentage was at least double or triple that (which is still extremely low).

I am not sure what can really be done to change those numbers, besides continuing to encourage and mentor women who want to work in I.T.  I think there are a lot of societal factors that discourage many young women from pursuing I.T. careers.

The LiveMeeting link is here. If you want a chance to win a $50.00 Amazon gift certificate, you need to register here.

Here is the abstract for the presentation:

Hardware 301: Diving Deeper into Database Hardware

Making the right hardware selection decisions is extremely important for database scalability. Having properly sized and configured hardware can both increase application performance and reduce capital expenses dramatically. Unfortunately, there are so many different choices and options available when it comes to selecting hardware and storage subsystems, it is very easy to make bad choices based on outmoded conventional wisdom. This session will give you a framework for how to pick the right hardware and storage subsystem for your workload type. You will learn how to evaluate and compare key hardware components, such as processors, chipsets, and memory. You will also learn how to evaluate and compare different types of storage subsystems for different database workload types. This session will give you the knowledge you need to make sure you get the best performance and scalability possible from your hardware budget!

I really enjoy talking about SQL Server hardware, so this should be a lot of fun. I hope you are able to take the time to listen to the presentation!

I plan on developing additional hardware-related courses that are SQL Server specific, and even deeper in scope. In the meantime, you might appreciate my recent post on SQLskills.com, Two Database Servers Compared.

There are a lot of available good choices for high end mobile workstations and choosing one comes down to several main factors:

1. Your intended usage
2. Your budget
3. Your tolerance for size and weight
4. Your desired screen resolution
5. Your vendor preference

A common usage request in the SQL Server community is that someone wants to be able able to run multiple, concurrent virtual machines on a laptop so that they can start learning about SQL Server 2012 AlwaysOn Availability Groups.

From this usage description, you would want a relatively powerful multi-core machine with lots of RAM and multiple drive bays if possible. This means you will need a 15″ or 17″ laptop that will be pretty large and heavy (6-8lbs). These larger machines will usually have four memory slots instead of two, and they will have two or three internal drive bays instead of one. The HD4000 integrated graphics in the Intel Ivy Bridge processor will be more than sufficient for this scenario. Using integrated graphics will give you more battery life than discrete graphics.

Remember, you are going to be stuck with the screen that you pick and with the processor that you select for the life of the laptop. Unless you choose an Ultrabook, it should not be too difficult to upgrade your RAM and to install a fast SSD.

A single fast 6Gbps SATA III SSD would probably have more than enough performance (both random and sequential) to support this scenario, but you might be worried about disk space unless you get a larger and more expensive SSD, such as a 512GB model.

The latest models will have 22nm Intel Ivy Bridge (3rd Generation Core) processors, with 6Gbps SATA III and USB 3.0 support, and support for up to 32GB of RAM (with four memory slots). You will also want a 1080P screen (1920 x 1080), rather than a 720P screen (1366 x 768).

I think the sweet spot for mobile processors right now is the Intel Core i7-3820QM. Going for the top of the line, Intel Core i7-3920XM does not make economic sense, since it is double the cost of the i7-3820QM while only offering maybe 10% more performance.

Rather than ordering a machine fully populated with RAM from the system vendor, you should just get a single 2GB stick of RAM from the system vendor and then buy 8GB sticks of RAM from someplace like NewEgg. This will save you several hundred dollars. Here are some 16GB memory kits from NewEgg.

Depending on your comfort level with hardware and whether you want to install a fresh copy of an OS, you should consider buying your own SSD(s) from NewEgg instead of buying them from the system vendor. This would save you even more money, plus you can pick the SSD that you want. The 256GB OCZ Vertex 4 is one of the better choices right now.

Vendor selection comes down to personal preference, really. Most people (including myself) actually only have anecdotal experience and evidence regarding vendor and specific model selection. For example, perhaps you bought a Dell laptop a few years ago, and you had some problems with it, so you are not likely to be a Dell fan. I would argue that your personal  experience is only anecdotal (unless you were a desktop support technician, and your company bought 100 of that model laptop, and a high percentage of them had problems). Even then, your experience would be only be valid for that particular model.

Ideally, you would want to be able to actually play with the specific model before you ordered it, to check the build quality, the ergonomics, etc. That is usually not possible with Windows laptops, since most brick and mortar retailers only carry poor quality, entry level laptops. That is one huge advantage that Apple has, since you can go into an Apple store and play around with the latest model MBP before you buy it.

Here are a few candidate, workstation class laptops:
Dell Precision M4700
Dell Precision M6700
Lenovo W530

Most current Ultrabooks use the low power versions of Intel processors (such as the Intel Core i5-3317U) which generate less heat and provide better battery life than the standard voltage models such as the Intel Core i5-3210M. On the downside, those low voltage models also give you significantly less performance than the standard voltage models. Another issue with most Ultrabooks is that they only have 4GB (or in some cases 8GB) of RAM, which cannot be upgraded. Finally, many Ultrabooks use small capacity mSATA SSD drives that are difficult or nearly impossible to upgrade.

On the upside, most modern Ultrabooks have higher quality, high resolution screens (often 1600 x 900 or 1920 x 1080), and they are very thin and light (less than 3.3 lbs). They also tend to have higher build quality than lower cost Windows laptops, along with a higher cost.

A couple of years ago, I bought a Toshiba Portege R705-P25 with a 32nm 2.26GHz Intel Core i3-350M “Arrandale” processor and Intel integrated graphics. About a year ago, I bought an externally identical Toshiba Portege R835-P55X with a 32nm 2.3GHz Intel Core i5-2410M “Sandy Bridge” processor, with improved Intel integrated graphics.  Finally, a few weeks ago, I bought another externally identical Toshiba Portege R935-P330 with a 22nm 2.5GHz Intel Core i5-3210M “Ivy Bridge” processor, with even better Intel integrated graphics.

Overall, I have been pretty happy with these machines. They have all been special OEM versions that were not configured exactly how I would have preferred, but they have been hundreds of dollars less expensive than the same configuration would have been if I had custom ordered it directly from Toshiba. The biggest weakness in these machines is their pretty mediocre 1366 x 768 screen. I would have happily paid more money for a better quality 1600 x 900 or 1920 x 1080 screen.

In each case, I eventually modified these machines from their original stock configuration by adding more memory, and installing a fresh copy of Windows 7 on a solid state drive (SSD). The older R705-P25  does not have SATA III support, so its disk performance is somewhat reduced with the current 6Gbps SATA III SSDs. Here is the Windows 7 Windows Experience Index (WEI) scores for these three systems after my modifications:

Figure 1: Toshiba Portege R705-P25 with 8GB of RAM and 128GB OCZ Agility 4 SSD

Figure 2: Toshiba Portege R835-P55X with 16GB of RAM and 180GB Intel 520 SSD

Figure 3: Toshiba Portege R935-P330 with 16GB of RAM and 256GB OCZ Vertex 4 SSD

This chart summarizes the WEI scores for all three systems:

Table 1: Windows Experience Scores on Modified Systems

On Windows 7, the WEI score has a maximum value of 7.9. The disk score is limited to 5.9 unless you have an SSD installed.

For comparison’s sake, here are the CPU-Z screenshots for all three systems:

Figure 4: CPU-Z CPU screen for Toshiba Portege R705-P25

Figure 5: CPU-Z CPU screen for Toshiba Portege R835-P55X

Figure 6: CPU-Z CPU screen for Toshiba Portege R935-P330

The newer Sandy Bridge and Ivy Bridge processors handle power management differently (and better) than older Intel processors such as the Arrandale.  Even when the High Performance power plan is used, the newer Sandy Bridge and Ivy Bridge processors will not run at full speed when they are not under any load, but they will throttle up much more quickly to full speed when they do see a processor load. There is much less of a performance difference between the default Balanced power plan and the High Performance power plan.

Again for comparison’s sake, I ran the 32-bit version of Geekbench 2.3.4 on all three systems. Geekbench only measures processor and memory performance, but it is a useful, quick benchmark for a system, whether it is a laptop or a database server.

Figure 7: Geekbench 2.3.4 score for Toshiba Portege R705-P25

Figure 8: Geekbench 2.3.4 score for Toshiba Portege R835-P55X

Figure 9: Geekbench 2.3.4 score for Toshiba Portege R935-P330

What I find interesting about this is the very large jump from the Arrandale in the R705 to the Sandy Bridge in the R835 (at roughly the same clock speed), showing the effects of a full  Intel Tock release. Moving from the Sandy Bridge in the R835 to the Ivy Bridge in the R935 is much smaller, and a good portion of this small improvement is due to the increased 2.5GHz clock speed in the Ivy Bridge compared to the 2.3GHz clock speed in the Sandy Bridge. Intel calls the Ivy Bridge a “Tick plus” release, since they made more substantial improvements in the integrated graphics performance in Ivy Bridge.

Table 2: Geekbench score comparison

Finally, I ran CrystalDiskMark 3.0.1 on all all three systems, using the default file size of 1000MB. Figure 10 shows the results for the Toshiba Portege R705-P25. Note: I made a mistake when I typed in the model of the SSD in Figure 10. It is actually a 128GB OCZ Agility 4 SSD that is plugged into a slower SATA II port, which limits its sequential read and write performance.

Figure 10: CrystalDiskMark for 128GB OCZ Agility 4 SSD in SATA II port

Figure 11: CrystalDiskMark for 180GB Intel 520 SSD in SATA III port

Figure 11 shows the results for the Toshiba Portege R835-P55X. Note that the Intel 520 SSD uses the SandForce 2281 controller which compresses data before it is written. The default setting in CrystalDiskMark uses a data file which is filled with random data, which does not compress well, which affects the performance quite a bit in this test.

Figure 12: CrystalDiskMark for 256GB OCZ Vertex 4 SSD in SATA III port

Figure 12 shows the results for the Toshiba Portege R935-P330. The controller used in the OCZ Vertex 4 SSD does not use compression. This particular SSD was updated to use the latest 1.5 firmware. This SSD is showing much better performance in this bench mark that the other two SSDs.

In case you are wondering why I have three externally identical laptops, it turns out that I have recently given the older R705-P25 to one of my nieces, and I will probably give the R835-P55X to another niece. Sometimes my family jokes that my hand-me-down computers are better than many people’s new machines.

Several of these new server models were just announced this week, and are not yet available for sale, while several others have been available for a couple of months now. The new models include three entry level servers (R320, R420, and R520) that use the new 32nm Intel Xeon E5-2400 series processor, and the new quad-socket PowerEdge R820 that uses the new 32nm Intel Xeon E5-4600 series processor. In case you are not fluent in how to decode Dell server model numbers, the R means rack-mounted, the first numerical digit is an indication of where the model fits in the overall lineup (with entry level models having lower numbers), the second numerical digit is the generation ( 1 means 11th Gen, 2 means 12th Gen), and the final numerical digit tells you whether it is an Intel based model or an AMD based model (0 means Intel, 5 means AMD).

So, if you are not a complete hardware geek like me, here is a handy little cheat sheet that lays out the major differences between these seven models. For memory capacity, I am assuming the use of 16GB DIMMs, since 32GB DIMMs are still extremely expensive, and frankly don’t make economic sense. From a processor choice perspective, each of these model servers are available with Sandy Bridge-EP processors that have either 4, 6, or 8 physical cores. In some cases (E5-2600 and E5-4600 series), you can choose a processor model that has fewer physical cores, but a higher base clock speed. This could make sense if you had an OLTP workload and are worried about the core-based licensing in SQL Server 2012 Enterprise Edition.

Personally, I really like the R720xd model, with up to (26) 2.5” internal drive bays. I suspect that a very high percentage of SQL Server workloads would run extremely well on one of those. If twenty-six internal drives did not give you enough I/O performance and disk space, you could always add some Fusion-io cards and/or use some external DAS enclosures or a SAN. If you can partition your workload across multiple servers, two R720xd servers would be much better than one R820 server, since you would have faster, less expensive processors, over three times as many internal drive bays, and nearly twice as many PCI-E expansion slots for things like RAID controllers or HBAs.

As a DBA, I would be actively lobbying against using the R420 or R520 models, since they use the lower-end E5-2400 series processors, which have lower clock speeds and less memory bandwidth compared to the E5-2600 series used in the R620, R720, and R720xd. They also have have half the memory capacity and fewer PCI-E slots. They are less expensive, but the hardware cost delta is pretty small compared to the SQL Server license costs, especially for SQL Server 2012. Remember, you are paying based on physical core counts, so you want to get the best package you can as far as the rest of the server goes. I can see where the R320 could be a good choice for a smaller workload, where you can still get 96GB of RAM in a one socket server. Don’t forget that SQL Server 2008 R2 and 2012 Standard Edition are limited to using 64GB of RAM.

Dell PowerEdge R320

1U form factor, one CPU socket, uses Intel Xeon E5-2400 series, 6 memory slots (96GB RAM),  (8) 2.5” drive bays, (1) x8 and (1) x16 PCI-E 3.0 expansion slots

Total of 4, 6, or 8 physical cores for SQL Server 2012 Enterprise Edition licensing purposes.  Total of 8, 12, or 16 logical cores with HT enabled.

Dell PowerEdge R420

1U form factor, two CPU sockets, uses Intel Xeon E5-2400 series, 12 memory slots (192GB RAM), (8) 2.5” drive bays,  (2) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled.

Dell PowerEdge R520

2U form factor, two CPU sockets, uses Intel Xeon E5-2400 series, 12 memory slots (192GB RAM), (8) 3.5” drive bays, (3) x8 and (1) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled.

Dell PowerEdge R620

1U form factor, two CPU sockets, uses Intel Xeon E5-2600 series, 24 memory slots (384GB RAM), (10) 2.5” drive bays, (1) x8 and (2) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled

Dell PowerEdge R720

2U form factor, two CPU sockets, uses Intel Xeon E5-2600 series, 24 memory slots (384GB RAM), (16) 2.5” drive bays, (6) x8 and (1) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled

Dell PowerEdge R720xd

2U form factor, two CPU sockets, uses Intel Xeon E5-2600 series, 24 memory slots (384GB RAM), (26) 2.5” drive bays, (4) x8 and (2) x16 PCI-E 3.0 expansion slots

Total of 8, 12, or 16 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 16, 24, or 32 logical cores with HT enabled

Dell PowerEdge R820

2U form factor, four CPU sockets, uses Intel Xeon E5-4600 series, 48 memory slots (768GB RAM), (16) 2.5” drive bays, (5) x8 and (2) x16 PCI-E 3.0 expansion slots

Total of 16, 24, or 32 physical cores for SQL Server 2012 Enterprise Edition licensing purposes. Total of 32, 48, or 64 logical cores with HT enabled

Looking at the table below, you can see how the i7-2700K compares to the upcoming (next month?) Sandy Bridge-E processors. The sweet spot for the Sandy Bridge-E will be the Core i7-3930K, which will have six cores, plus hyper-threading. It has nearly the same clock speed as the i7-3960X, with a slightly smaller L3 cache, at a 40% lower cost.

Last week, AMD finally released their long anticipated Bulldozer desktop processor line, led by the FX-8150. Most of the reviews that I have seen for Bulldozer have shown pretty disappointing single-threaded performance for this processor. It has done better on multi-threaded tests, which could be a hopeful sign for database performance, especially for reporting or data warehouse workloads. I cannot wait until someone does some benchmarks against the server equivalent of this processor, which will be the Opteron 4200 and 6200 series.

Finally, there have been a lot of stories about the effects of the recent flooding in Thailand on the hard drive market, with a large percentage of the production capacity of the major hard drive manufacturer’s being under water. This has already had a significant effect on prices, and it will affect availability for quite some time. This might increase the feasibility of SSDs, which is at least a small silver lining.

Here is the abstract for the session:

Making the right hardware selection decisions is extremely important for database scalability. Having properly sized and configured hardware can both increase application performance and reduce capital expenses dramatically. Unfortunately, there are so many different choices and options available when it comes to selecting hardware and storage subsystems, it is very easy to make bad choices based on outmoded conventional wisdom. This session will give you a framework for how to pick the right hardware and storage subsystem for your workload type. You will learn how to evaluate and compare key hardware components, such as processors, chipsets, and memory. You will also learn how to evaluate and compare different types of storage subsystems for different database workload types. This session will give you the knowledge you need to make sure you get the best performance and scalability possible from your hardware budget!

You can get the PowerPoint for this session from Dropbox here. You can get an even deeper dive into the subject from my book, available here. I hope to see you there on Wednesday morning!

If you want even more information on this subject, you might consider buying a copy of my upcoming book from Amazon!