After discovering that we had a throughput cap, we had to assess what our options were to alleviate it. Reflecting on Brent’s performance tuning strategy, because we were working with a vendor-provided architecture, there would be limitations to the type of query modifications we can make and the indexes we could add to the schema. The emphasis would be  on the memory and the storage array.

Remembering Your Memory, Your Age, and that You’re Not Alone in This

The first thing we had to ask was can we add more RAM to the host. Working with only 96 GB on board for a hybrid data warehouse that will be easily beyond 10 TB is going to be very difficult.

But we’re working with a Dell M910, a full-height blade vs. a rack mount server. What options do we have?

  • How much RAM is on-board already?

  • What size DIMMs are currently on-board?

  • How many DIMM slots are still free?

  • If I get more of the same size DIMMs, how much more RAM can I add, and at what price?

  • If I get larger size DIMMs, what would be my maximum RAM, and at what price point?

Well, the Dell M910 has 32 DIMM slots with the largest chip size being 32 GB so you can have up to 1 TB of RAM on-board. When the box was procured however, we used 12 slots with 8 GB DIMMs. If we keep to that denomination, we’d only add 160 GB for a total of 256 GB. Furthermore, the 8 GB DIMMs can go for $40 each, while the 32 GB units go for $320 apiece depending on when and where you’re buying. Do we spend $800 for the cheaper upgrade, or $10,240 for the maximum upgrade, or something in the middle?

You also have to keep in mind the age of the server and whether it’s a capital or operating expense. Is the host young enough and still under warranty to make it worth the additional investment? Second, do you need capital dollars to upgrade, meaning you have to budget for it in advance, or can you use operating expense dollars and shift funding around to cover for the upgrade?

Lastly, what do the Wintel engineers think? If the box is near the end of the depreciation cycle, will they suggest replacing the host or extending the warranty? Are they planning to go to a new hardware vendor anyway and won’t upgrade existing gear anymore so they can focus on the new equipment?

Something in Store for You

The storage array and associated hookup are the last options for upgrade as they’re the most expensive and cumbersome to adjust.

The low-hanging fruit starts off with the host bus adapters (HBAs) that connect the host server to the SAN. The Dell M910 we have used Qlogic QME2572 cards. An 8Gb HBA should be capable of getting up to 1 GB/sec of throughput. However, we’re capping out at around 430 MB/s, less than half that.

  • Are we running on a connection to a Brocade switch meant for 4 Gb/s?

  • Are we sharing our channel with other applications in the blade chassis?

  • Was a fan-out performed that capped the number of outstanding requests that would be queued by the HBA?

Maybe we can add HBAs to the host and increase the number of pathways to the Brocade switch and to the SAN. But we’re working with a full-height blade with an I/O mezzanine card vs. a rack-mounted server with several PCIe slots.

  • Can we add more HBAs? Or better ones?

  • Does the Brocade switch have extra ports to allow additional hookups from the same host?

  • Do we need to move the server to another chassis or rack?

Last but not least, we have to think about the SAN enclosure itself. Even if you have the right host with the right # and speed of HBA cards, can the SAN enclosure give you enough throughput? If you’re one of many hosts, how much sustained throughput can you anticipate at peak times?

We Need Something Fast to Get Back on Track

At this stage, all the upgrade-in-place options may be gone. The alternative, building a system end-to-end, may be in order, but it’s one expensive shopping list…

  • New server with O/S: $20,000

  • New SQL Server Enterprise license for 24 cores: $168,000

  • New SAN enclosure: $250,000

And that’s just for one server. Do you want high availability with a cluster? Do you want identical UAT gear?

What’s more, will the new even gear work? Going from commodity gear that your WinTel and Storage teams prescribed for all the application teams to your own custom rig has financial and political implications. Are you willing to put your neck out to buy new gear? Whose support can you garner to procure it? What should you get to ensure a high degree of success? What can you get fast that will get your data warehouse project back on track?

Well, how about a Fast Track system?