Andy Leonard : scalable

Twitter Woes

andyleonard — Fri, 30 May 2008 13:42:00 GMT

"My name is Andy Leonard. I tweet."

"Hi Andy, we love you."

It's true, I am a bona fide Twitter-holic. If you use the service you may have noticed disruptions lately. Frank La Vigne (recently married - congratulations Frank!) blogged about it. The Twitter developers are blogging about it. And they have raised the ire of Mr. Scoble, who posted his feelings on the matter at FriendFeed - a competing service (if "competing" applies here...).

From the Twitter Dev blog:

The events that hit our system the hardest are generally when "popular" users - that is, users with large numbers of followers and people they're following - perform a number of actions in rapid succession. This usually results in a number of big queries that pile up in our database(s). Not running scripts to follow thousands of users at a time would be a help, but that's behavior we have to limit on our side.

The Twitter Dev comments have been interpreted by some as complaining about user load. This is not good for Twitter and they should take immediate steps to manage the buzz before this goes any farther.

I jokingly tell clients sometimes the problem with their application or database performance is a combination of their data and their clients. I only do this in person and after reaching a comfortable comfort level with the client, and even then it's sarcasm that I carefully throw out. Why? It's the technological equivalent of saying the word "bomb" while waiting in the security line at the airport - within earshot of the good people of the TSA.

In other words, it's a really dumb thing to say.

Let's look at why: First, Twitter is demonstrating that they made a mistake in architecture. I don't know what that mistake is, but it's obvious to everyone using or attempting to use the platform that an error has occurred. It's not the HTTP 500 error I saw last night or the on-again-off-again link to older tweets. It goes beyond that back to the design.

Part of the problem can be stated thus: "Twitter did not know we were going to grow so fast." Another way to say that is "Twitter doesn't scale."

As a database professional in the data warehousing field, I feel Twitter's pain.

We are often pressured to "just make it go!" - deliver something now and fix it later. And oh the temptation is strong, the logic sounds sound, the song so sweet... "you can circle around later and fix it" they say. When you hear these words and are so tempted you can be certain of one and only one thing: the person saying this to you is lying. Malice may or may not be present - they may simply be repeating what they heard or they may be utterly ignorant, but they speak not the truth.

Second - although I could be wrong about this - I would wager good money that Twitter doesn't need a DBA. I've seen / heard / experienced this before. They have a talented team of application developers who have built large applications in the past and never once paid a database professional. Look at the money they've saved! </sarcasm>

Why does this happen? Experienced database professionals slow down project development. We get in the way. We muck about with stress tests and bulky architectures and referential integrity and schemas and the like. Who needs us? We're an unnecessary expense.

Or are we?

From the quote above, the bottleneck is occuring in the database. It's those pesky queries. And the users causing them to be executed. It's <insert-your-favorite-excuse-here> - everyone and everything, except the designers and architects who built a non-scaling solution.

My lovely bride Christy has this saying: "Good judgment comes from experience, and experience comes from bad judgment." My hope is Twitter will discontinue offering excuses and blaming users, and instead fix the (apparently database-scaling-related) problem.

If this comes across as harsh, that is not my intention. I have been there and done that myself. These are learning experiences and growth opportunities. Hopefully Twitter will enjoy the fruits of this learning and growth in the future.

For now, I've created a FriendFeed account. Let's see how they scale...

:{> Andy

Context and Grain

andyleonard — Sun, 04 Nov 2007 00:41:00 GMT

Introduction: The World Series (1979) and Photography

Willie "Pops" Stargell started my interest in photography (...a funny way to start a post on a SQL Server blog site, but bear with me). Pops Stargell led the 1979 Pittsburgh Pirates to win the World Series. He was the MVP that year as well.

My Mom, in the only time I ever remember her gambling when I was a kid, bought a $10 spot in a World Series baseball pool that year. The pool was based on the 1's digit of the total runs scored by the Pirates and Orioles in the entire series with the person who had the 1's digits in the correct order winning $600, the person getting them in reverse order winning $300, and the organizer kept $100. The series lasted seven games that year. Mom was set to win the $600 if the final score of Game 7 was 4-1 in the Pirate's favor. Willie Stargell hit a home run that helped achieve that score, along with some good fielding (especially for a 39-year old player).

I had a budding interest in photography because some friends at high school were photographers. There was just no way we could swing $200 for a mid-range 35mm SLR back them. But Mom promised me if she won the big money she'd buy me the camera. She won and she bought me the camera.

I got to attend an interesting Yearbook Seminar the next summer held at Longwood University in Farmville, VA - located about 7 miles from where I currently live. I learned some fascinating things about photography and did a decent job getting shots for my senior year yearbook.

Photography Context and Grain

One of the things I learned was this: If you take an out-of-focus shot, there's nothing you can do in chemical and light processing to bring that shot into focus. There may be (and probably are) digital things we can do nowadays, but this was the 70's. The very best you could do with an out-of-focus was produce a print no less in focus.

In the context of the photograph, the focus attribute was set when the picture was taken. The focus could not be improved after the picture was taken. It was the best it was ever going to be from that moment until forever.

Database Design Context and Grain

Now here's a lesson we can take into database development.

Several for-instances leap to mind: granular resolution in a business intelligence data acquisition system, for one.

Imagine you are tasked with reporting real-time data for a business intelligence scorecard application. The client expects up-to-the-second updates from the data acquisition systems on the manufacturing floor and this is a project requirement.

Sure, you can do that.

Unless data is collected every five seconds on the floor. Then you have an issue. There's nothing you can do from your side of the project - since you merely read, store, transform, and display the acquired data - to "fix" this. You can give updates every second, but these counters (and metrics derived from them) will change only every five seconds.

In this imaginary scenario you cannot "re-focus" the picture. The best you can do is present the information at it's current grain - and no finer.

Software Development Context and Grain

The same can be said for software development. I will state it in another way: Every decision to develop Feature A in a certain way is also a decision to not develop Feature A in any number of other ways.

There are consequences to choosing an approach, methodology, or context. These consequences are usually discovered well down the path towards a deliverable or release. Occassionally, talented developers can find a "silver bullet" - a fix that solves the current consequence to a past decision without breaking anything else. But this is unfortunately rare. Usually there are consequences to the consequence-initiated fix, and so on, ad infinitum.

There is a point early on in a development project where such consequences, if detected, can be addressed with relatively little effort. I use the analogy of deflecting an approaching asteroid: If you detect it early enough you can deflect it with a BB. Wait, and all the nukes on the planet won't stop it.

One rule of processes - I first saw this in The Goal - is "Losses accumulate, gains do not" (just call me Mr. Encouraging). Most good process measurement methodologies account for this. One way to think about it is to consider the example of a three-stage linear process where each stage is running at 90% efficiency.

<TrickQuestion> What is the overall efficiency of the system?
<TrickAnswer> 90%</TrickAnswer>
</TrickQuestion>

90% is incorrect. It's a linear process. The output of stage 1 is 90%. The output of stage 2 is 90% of 90%, or 81%. The output of stage 3 is 90% of 81%, or 73%.

Consider a process-improvement initiative that results in 100% efficiency at stage 1. The output improves, but only to 81%. Consider the alternative - a reduction at stage 1 to 80%. Stage 2's output becomes 72%; stage 3's: 65%. The losses accumulate, the gains don't. To quote Foghorn Leghorn, "Figures don't lie."

Why Are You Sharing This, Mr. Encouraging?

There are a few reasons: First, this is a trap into which many a young developer falls. It looks simple but it is not. Life is filled with things that appear deceptively simple. Software development is but one of them.

Second, underestimating the effort required to accomplish any software related task is a trait shared by every good developer I know, plus me. We all think things will take less time than they do. I don't know why, but that doesn't make the fact any less true. Joel Spolsky has an excellent article on Evidence Based Scheduling that presents an interesting solution to this.

Third, it doesn't matter how good you are, this can happen to you. The odds of getting caught in a process-resolution trap increase exponentially with complexity.

Conclusion

Be aware during design / architecture phases, the decisions made today will limit future options. There's a side order of art included with the science here. Balance is the key.

:{> Andy

Everything Scales

andyleonard — Sun, 19 Aug 2007 12:51:00 GMT

The tune to a Bush song is running through my head as I type this... the band, not the president - although imagining the President singing the song is an interesting brain-stretch.

It's a fact of IT life that everything scales. Some successfully, even. Problems start when things do not scale successfully (or well). It happens in business. It happens with software systems.

When it happens with businesses, you hear things like "They grew too fast." When it happens with software systems, you browse to a website and receive an HTTP 500 or 404 error.

Can this be avoided (in business or software)? I think that's an excellent question - one well worth examining.

The answer, I believe, lies with how predictable the scalability is.

Consider a database application: If you know which tables are going to grow, how, and how much, you can plan for said growth. How would you plan? You could partition the tables using one or a combination of partitioning techniques. You could appropriate filegroups, snapshots, and a host of other functionality. If you only you knew where to apply these techniques.

That's the key.

Achieving scalability starts with capturing metrics. If you know how your database is growing from the beginning - if you can chart the growth of individual tables, access patterns, and internal performance data - you can predict growth and manage scalability.

So the key is measurement.

Measurement is an engineering discipline in its own right. The field of applied measurement is called Instrumentation. Applying measurement to a process is referred to as "instrumenting the process."

How do you instrument a database process? Iteration 1 would include creating an internal table to house and maintain process metadata:

CREATE TABLE dbo.ProcessData
(ProcessDataID int IDENTITY(1,1) NOT NULL,
ProcessDataDateTime datetime NULL CONSTRAINT DF_ProcessDataDateTime DEFAULT (getdate()),
ProcessDataIndicatorName varchar(50) NULL,
ProcessDataIndicatorValue varchar(50) NULL,
ProcessDataIndicatorStatus char(1) NULL CONSTRAINT DF_ProcessDataIndicatorStatus DEFAULT ('C'),
CONSTRAINT PK_ProcessData PRIMARY KEY CLUSTERED (ProcessDataID)

If your instrumented process is stored-procedure-based, you could add INSERT statements to your existing stored procedures. Consider instrumenting a parent stored procedure that calls child stored procedures. The instrumented proc could look like the following (instrumentation emphasized):

CREATE PROCEDURE dbo.SomeProcess
AS

begin

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc1','Starting');

EXEC dbo.ChildProc1

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc1','Ending');

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
('ChildProc2','Starting');

EXEC dbo.ChildProc2

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc2','Ending');

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
('ChildProc3','Starting');

EXEC dbo.ChildProc3

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName,
ProcessDataIndicatorValue)
VALUES('ChildProc3','Ending');

end

Before moving forward, removing code duplication would be a worthwhile effort. In application development, this is one of many processes generally referred to as Refactoring.

The INSERT statements are a prime candidate for refactoring and we can address this with a stored procedure:

CREATE PROCEDURE dbo.AddProcessData
@ProcessDataIndicatorName varchar(50),
@ProcessDataIndicatorValue varchar(50)
AS

begin

INSERT INTO dbo.ProcessData
(ProcessDataIndicatorName, ProcessDataIndicatorValue)
VALUES(@ProcessDataIndicatorName, @ProcessDataIndicatorValue);

end

Now the parent stored procedure instrumentation above can be modified to look like this:

CREATE PROCEDURE dbo.SomeProcess
AS

begin

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc1', @ProcessDataIndicatorValue='Starting';

EXEC dbo.ChildProc1

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc1', @ProcessDataIndicatorValue='Ending';

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc2', @ProcessDataIndicatorValue='Starting';

EXEC dbo.ChildProc2

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc2', @ProcessDataIndicatorValue='Ending';

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc3', @ProcessDataIndicatorValue='Starting';

EXEC dbo.ChildProc3

EXEC dbo.AddProcessData @ProcessDataIndicatorName='ChildProc3', @ProcessDataIndicatorValue='Ending';

end

Much better.

Measuring the current process provides a baseline - the first step in a continuous improvement process that will provides dynamic design changes, performance monitoring, and - eventually - a dynamically-scalable system. It also supplies the current performance status against which we can benchmark future improvements and modification.

:{> Andy

Database Professionals: An Enterprise Requirement

andyleonard — Fri, 13 Jul 2007 01:53:00 GMT

A friend (who shall remain nameless) recently told me his company interviewed a competent database developer and DBA. All seemed in agreement an offer would be forthcoming until the very end of the recruiting process. At that time, someone made the comment "we don't need a DBA."

It would be notable if this sentiment wasn't so widespread - but I see it often. How often? Well, I would have to tell you how I see it to qualify that statement:

You see, people rarely say to me "We don't need a DBA." Mostly I see it in their applications - many of them prominent companies in which you may even own stock. I can tell when I examine their schema. I can see it when I execute Profiler against their SQL Server database.

Now, there are lots of reasons to design a denormalized schema. And there are lots of reasons to encapsulate the business rules in code. This is not what I'm talking about - though some of these systems would clearly perform better (or at all, in extreme cases) if they took advantage of better design patterns.

I'm talking about designs where this much is obvious:

1. At least two people designed the data layer; and
2. They did not communicate during the process.

Often, enterprise-level database design is shoveled onto developers as a secondary task. No, I'm not making this up - it's too tragic to joke about. There are developers who can handle this task. But there are more who believe they are database developers than actually are. (Before I became a SQL Server DBA I was a developer who thought I was a SQL Server DBA...)

There will doubtless be readers who can provide examples of how their enterprise application was built by junior developers who did the database and code work and whose systems are performing just fine. I'm happy for you and sincerely hope the system scales.

Designing a scalable solution - database, application, or enterprise architecture - is one of those things that consumes time, thinking, resources, and money during the early phases of an enterprise development cycle. But it is - hands down - one of the best investments (if not the best) in the solution.

In today's market, scalability is as optional as security. And like security, a scalable design is not something you "add later." It's not part of the foundation - it is the foundation.

My experiences with designing scalable solutions has proven there is no free lunch nor any shortcuts that work. If anyone - me included - skips the work of designing for scalability, there comes a day when they (or I) must pay the fiddler. From what I hear and have experienced, designing in this fashion is most often sacrificed on the altar of the deadline. Trust me, if it falls apart in six weeks or six months, you haven't saved any time - and you may have lost a job or a customer.

Someone told me this and I remember because it has proven true several times over: "Deliver quality late, no one remembers. Deliver junk on time, no one forgets."

If you're building (or upgrading to) cutting edge technology, you need a DBA.

:{> Andy

Technorati Tags: Software Business scalable database design quality