DB Perf Is Like Cake: the Architecture Layer

22 April 2011

“Re-think it, and perf will come”

Layer 4 of The Layer Model is the architecture layer.

The architecture layer represents the high-level system design of your application, the technologies used, and how various systems interact with each other. If you change your architecture to be a better fit for your application, you will get far better performance. If you’re considering architecture work, that means you have tried all of the other, easier approaches to performance tuning. That means you have tuned your system’s hardware bottlenecks, tuned the indexes and queries being run, you have even tuned your table designs and data-access layer. What is left is an architectural solution.

How to Start

When people hear ‘architecture’, they often feel intimidated. Working with the entire system is daunting. Luckily, there’s a process that makes this easy. The key to architecture work is about choosing the right tools and design for the situation. Here’s a 4-step guide:

  1. What is the essence of the problem?
  2. What high-level designs do other people use for the same problem?
  3. Which design, after minor adjustments, is the best fit for your situation?
  4. What tools should you use for your modified system architecture?

The essence of the problem

Architectural challenges have a root problem. This is the essential issue, that, if fixed, makes all of the other problems much smaller. Since we are IT professionals, identifying root cause is something we’re good at.

What is the root problem with your system? Do you have single 80TB database powering your entire reporting system? A single OLTP system growing by 10X every month? Are there 80 critical system components that each need to talk to all of the others?

Knowing the problem is half the battle.

What other architectures do people use?

Brilliant innovators are very rare

Some of us can produce brilliant, never-before-seen system architectures that work. They’re rare. For the rest of us, we mimic the designs and best practices we hear about. Doing this is very wise; you can learn the strengths and weaknesses of many system designs without trying them out.

Luckily for us, the SQL Server community is very active. Chances are you have access to a nearby SQL Saturday, lots of helpful professionals on Twitter, and a gaggle of blogs with great resources. Most importantly, all of the people I’ve met in the community are easy to talk to.

I can learn more about architecture in an evening than in 6 months of reading. How? I offer to buy a 6 smart, experienced engineers a round of drinks after an event, and ask them lots of questions. Or I will find the one subject matter expert in this area, and expense a fancy dinner. Your manager should happily pay a $200 bill to avoid 6 months of project headaches.

Find out what others have done, and you benefit from decades of experience at a very low price.

CHOOSING THE RIGHT DESIGN

The scales of IT are not blind

Now you know the core problem to solve, and what others have done that works.

You should pick a system architecture based on what you know. I do this in a 5-step process:

  1. What are the 2-3 most critical requirements for my system, and my business? Scale? Latency? Interoperability? Ease of adding new features?
  2. Which architectures _do not do well _with these requirements? Discard the ones that don’t fit.
  3. Of the remaining ideas, list the strengths and weaknesses of each.
  4. Pick one.
  5. Tweak the system design a bit, but only to address your critical requirements.

Choose the right design, using good judgment and a critical eye.

Using the right tools

Some stacks are more fragile than others

You now have a proposed system architecture. Now you need tools. The last step is to pick a technology stack for the job. I’ve saved this step for last because the choice of system architecture should dictate which technologies you use, and not the other way around. I like to do the following:

  1. List the top 3 most common technologies for the problem. Is it SQL Server? WCF? Memcached? Windows Azure AppFabric?
  2. List the strengths and weaknesses of each. If you are familiar with a technology stack, that is a key strength.
  3. Pick one.

Pick the right tools for your design, and you’ll have an architecture that can last for years.

The Good:

**Re-architecting a system can give you a faster, more scalable, and easier to use system for years. It can reduce dozens of headaches for users and IT in one fell swoop. It can also foster a sense of camaraderie between IT, developers, program managers, and the business. After all, you’re all in this together.

The Bad:

**This is high-risk work. Doing this without testing is a recipe for horror. Bad communications and planning dooms a project this size. A lot depends on the judgment and teamwork of a few key IT and development personnel.

The Ugly:

Done wrong, you can completely screw up your system architecture, alienate various teams from each other, and lose massive amounts of money as your customers flee.

But wait, there’s more!

Several people have asked me for examples of architecture re-work, design tuning, and so forth. I’ll be putting together several posts over the next few weeks with examples, best practices, and anti patterns.