Throughput versus Response Time

I like Doug Burns's recent blog post called Time Matters: Throughput vs. Response Time. If you haven't read it, please do. The post and its comment thread are excellent.

The principle Doug has recognized is why the knee in the performance curve is defined as the traffic intensity (think utilization, or load) value at which, essentially, the ratio of response time divided by throughput is minimized. It's not just the place where response time is minimized (which, as Doug observed, is when there's no load at all except for you, ...which is awesome for you, but not so good for business).

I'd like to emphasize a couple of points. First, batch and interactive workloads have wholly different performance requirements, which several people have already noted in their comments to Doug's post. With batch work, people are normally concerned with maximizing throughput. With online work, individual people care more about their own response times than group throughput, although those people's managers probably care more about group throughput. The individual people probably care about group throughput too, but not so much that they're happy about staying late after work to provide it when their individual tasks run so slowly they can't finish them during the normal working day.

In addition to having different performance requirements, batch workload can often be scheduled differently, too. If you're lucky, you can schedule your batch workload deterministically. For example, maybe you can employ a batch workload manager that feeds workload to your system like a carefully timed IV drip, to keep your system's CPU utilization pegged at 100% without causing your CPU run-queue depth to exceed 1.0. But online workload is almost always nondeterministic, which is to say that it can't be scheduled at all. That's why you have to keep some spare un-utilized system capacity handy; otherwise, your system load goes out past the nasty knee in your performance curve, and your users' response times behave exponentially in response to microscopic changes in load, which results in much Pain and Suffering.

My second point is one that I find that a lot of people don't understand very well: Focusing on individual response time—as in profiling—for an individual business task is an essential element in a process to maximize throughput, too. There are good ways to make a task faster, and there are bad ways. Good ways eliminate unnecessary work from the task without causing negative side-effects for tasks you're not analyzing today. Bad ways accidentally degrade the performance of tasks other than the one(s) you're analyzing.

If you stick to the good ways, you don't end up with the see-saw effect that most people seem to think of when they hear "optimize one business task at a time." You know, the idea that tuning A breaks B; then tuning B breaks A again. If this is happening to you, then you're doing it wrong. Trying to respond to performance problems by making global parameter changes commonly causes the see-saw problem. But eliminating wasteful work creates collateral benefits that allow competing tasks on your system to run faster because the task you've optimized now uses fewer resources, giving everything else freer and clearer access to the resources they need, without having to queue so much for them.

Figuring out how to eliminate wasteful work is where the real fun begins. A lot of the tasks we see are fixable by just changing just a little bit of source code. I mean the 2,142,103-latch query that consumes only 9,098 latches after fixing; things like that. A lot more are fixable by simply collecting statistics correctly. Others require adjustments to an application's indexing strategy, which can seem tricky when you need to optimize across a collection of SQL statements (here comes the see-saw), but even that is pretty much a solved problem if you understand Tapio Lahdenmäki's work (except for the inevitable politics of change control).

Back to the idea of Doug's original post, I wholeheartedly agree that you want to optimize both throughput and response time. The business has to decide what mixture is right. And I believe it's crucial to focus on eliminating waste from each individual competing task if you're going to have any hope of optimizing anything, whether you care more about response time, or throughput.

Think about it this way... A task cannot run at its optimal speed unless it is efficient. You cannot know whether a task is efficient without measuring it. And I mean specifically and exactly it, not just part of "it" or "it" plus a bunch of other stuff surrounding it. That's what profiling is: the measurement of exactly one interesting task that allows you to determine exactly where that task spends its time, and thus whether that task is spending your system's time and resources efficiently.

You can improve a system without profiling, and maybe you can even optimize one without profiling. But you can't know whether a system is optimal without knowing whether its tasks are efficient, and you can't know whether a given task is efficient without profiling it.

When you don't know, you waste time and money. This is why I contend that the ability to profile a single task is absolutely vital to anyone wanting to optimize performance.

Thank you, Tim Sanders

I grew up with the idea that you never, ever write in a book. Unless you're correcting a mistake. (The book never "gets it" except when the book deserves it.)

But I was wrong. Tim Sanders fixed me. His book, Love Is the Killer App: How to Win Business and Influence Friends, in the section about Knowledge (page 82) just saved me a few hours. Tim recommended to me that as I read, I should tag and cliff within the book itself. His advice made me uncomfortable, because it made good sense, but I do not write in books.

So, I was just wrong. I didn't summon the courage to tag or cliff Tim's book, but I resolved to tag and cliff the next few. And wow, it's a Good Thing. It's basically the act of creating an index for stuff you want to find later. I just saved myself a couple of hours looking for a passage in a book that I found in about 30 seconds because I had tagged it inside the front cover. My tag told me the page number and the concept I was looking for, and—poof!—I snapped right to it, there it was. That's index access by rowid for the brain.

Now I write in all the books I read. It works, and I highly recommend it.

Thank you, Karen Morton, for giving me the copy of Tim's book.

Thank you, RMOUG

I want to thank everyone who came to watch me present my session called, "For Developers: Making Friends with the Oracle Database" session yesterday at RMOUG in Denver. I don't know how many of you were there, but it felt like at least a couple hundred, and I'm grateful to you for sharing your time with me. I hope you had as much fun as I did.

RMOUG was another excellent show. Lots of happy people learning and sharing what they know: you can't beat that. I think Dan Norris captured the spirit of the event very well in his blog (which is now on my blogroll).

Oracle Optimizer Statistics

Accidentally messing up table statistics is probably the single most common cause for poor SQL query performance that I've ever seen. At Karen Morton's blog, you'll find a link to the best paper on the subject that I've ever seen.

Karen is going to present this topic at the Hotsos Symposium here in Dallas in just a few weeks. I'll hope to see you there. This one's big: Chris Date.

On the Usefulness of Software Instrumentation

I appreciate that in today's reading, Chen Shapira has taught me about the psychological principle of the endowment effect and its influence over people's decision-making about whether to instrument their code. I'm beginning to collect a nice little list of inspiring observations about performance instrumentation. I've posted them in a public wiki. I hope you'll join in.

Here are some samples:

Chen Shapira: The pervasive lack of instrumentation in software products is more a result of psychological bias than real technical concerns. Software vendors can work around these psychological issues by building instrumentation as a default into tools involved in the development and deployment process. Just as Knuth said 35 years ago.

Don Knuth: I've become convinced that all compilers written from now on should be designed to provide all programmers with feedback indicating what parts of their programs are costing the most; indeed, this feedback should be supplied automatically unless it has been specifically turned off.

Tom Kyte: I have yet in 18 years to hear a valid reason why instrumentation should not be done. I have only heard extremely compelling reasons why it must be done.

Tom Kyte: To the developers that say “this is extra code that will just make my code run slower” I respond “well fine, we will take away V$ views, there will be no SQL_TRACE, no 10046 level 12 traces, in fact–that entire events subsystem in Oracle, it is gone”. Would Oracle run faster without this stuff? Undoubtedly—not. It would run many times slower, perhaps hundreds of times slower. Why? Because you would have no clue where to look to find performance related issues. You would have nothing to go on. Without this “overhead” (air quotes intentionally used to denote sarcasm there), Oracle would not have a chance of performing as well as it does. Because you would not have a change to make it perform well. Because you would not know where even to begin.

Tom Kyte (I don't know of a link to this one; I'm paraphrasing from public statements I've seen him make): Showing where your code spends its time is a necessary function of any production application. The fact that the extra code required to do this consumes extra system capacity is no different from any other feature in your application. As with any required feature, you simply have to size your hardware with the feature in mind.

A report about our course in Utrecht

Toine van Beckhoven has blogged about his experience in our recent course in Utrecht, hosted by Miracle Benelux. I also like his post about the new function-based index he created after an inspiration from the Tom Kyte course he attended.

Reading Knuth

I've been reading a little Knuth for the past couple of days. I've read about the famous statement, "premature optimization is the root of all evil," many times, and yesterday I decided to read his statement in its whole context. You can find Knuth's whole article here (thank you, Wikipedia):

Knuth, Donald: Structured Programming with Goto Statements. Computing Surveys 6:4 (1974), 261–301.

I want to quote the passage for you that contains the famous line about premature optimization. It's on page 268 (the eighth page of the article):

There is no doubt that the grail of efficiency leads to abuse. Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.

Most people stop here when they quote Knuth. But actually, it just keeps getting better:

Yet we should not pass up our opportunities in that critical 3%. A good programmer will not be lulled into complacency by such reasoning, he will be wise to look carefully at the critical code; but only after that code has been identified. It is often a mistake to make a priori judgments about what parts of a program are really critical, since the universal experience of programmers who have been using measurement tools has been that their intuitive guesses fail. After working with such tools for seven years, I've become convinced that all compilers written from now on should be designed to provide all programmers with feedback indicating what parts of their programs are costing the most; indeed, this feedback should be supplied automatically unless it has been specifically turned off.

What a beautiful expression of the idea that's at the core of what I do for a living.

If you haven't seen "For Developers: Making Friends with the Oracle Database" yet, I hope you'll take a look. My aim is to help Oracle application developers writing Java, PHP, C#—or anything else—understand how to find that critical code that Knuth wrote about in 1974. ...And, just as importantly, how to ignore the other 97% of your code that you shouldn't be worrying about.