A review of Optimizing Oracle Performance by Cary Millsap
Optimizing Oracle Performance
Optimizing Oracle Performance. By Cary Millsap, O’Reilly 2003. Page count: about 375 pages with appendices. (Here’s a link to the publisher’s site.)
This is easily one of the best books I’ve ever read on performance optimization. I’ve just finished reading it for the second-and-a-half time in two weeks, and I very rarely read a book more than once. I’ve been telling a lot of people about it.
Despite the title, it is actually not about Oracle performance. It is a book on how to optimize a) any system, including a MySQL-based application b) the process of optimization itself. It is very, very good, and I highly recommend it to all database users. My bet is that most people will learn more by reading this book than by spending thousands of dollars on conferences and training, especially since a lot of what you’ll learn from those sources is wrong and harmful. So not only will you save money and time on learning, you’ll reap great rewards thereafter.
The book is organized into three sections. The first section explains a performance optimization methodology called Method R (for response time) that is designed to deterministically advance from identifying what needs to be optimized, to collecting diagnostic data, and then choosing the correct activities to optimize. Simply put, it is probably the clearest and most logical process I’ve ever seen for focusing on what matters. It is quite similar to our process at Percona, which we call Goal-Driven Performance Optimization. But Cary does a really good job explaining it. Cary told me that he wrote the book while simultaneously developing Method R training classes, and did dozens of classes before the book went to press. You can tell!
The second and third parts of the book are about putting the method into practice.
Cary shows many typical mistakes, such as focusing on ratios, working on things that “look bad,” ignorance of Amdahl’s Law, trying to draw conclusions about specific activities by examining system-wide information, and so on. He brings the focus back to response time again and again.
Another typical “tuning mistake” is not knowing when to quit. Cary shows how to know when further performance improvements, even if they’re possible, will not be economically justified. At the point where the gains don’t exceed the cost, you’re done. The system is optimized. Maybe it’s not performing as well as it could, but if it costs too much to get any more performance, it’s still optimal.
This is a book that backs up every assertion with references to source material, or even with a proof or derivation from first principles. It is significantly more rigorous than most books of this type. There’s a very good chapter on kernel timings that explains a ton of stuff about how processes work, measurement intrusion effect, quantization error, and so on.
There is a very good section on queueing theory, with excellent examples of using it to prove that a desired improvement is mathematically impossible and/or impractically expensive. There are also examples of how to use queueing theory to predict when a hardware upgrade will worsen your performance problems. (And lots of other examples of when “optimizing” something will actually make it worse, as for example improving performance of some task other than the one you’re interested in, thereby making it use and contend for more resources — and hurting performance of the task you wanted to improve.)
What is it NOT about? It’s not about how to write more efficient SQL — there is practically nothing in the book about SQL. It’s not about how everything inside Oracle works, though it does have one or two chapters that are mostly about how to apply the principles to Oracle through extended SQL trace files, a discussion that will transfer well to any other system that emits trace data. (Read these, or you’ll miss learning about quantization error and such!)
Highly, highly recommended.
Further Reading:
Hi,
Nice review. In fact, I had been thinking on buying this book (I’m mostly an “Oracle guy” nowadays) but since it was written back in 2003 I thought it would be a bit dated.
According to your comments it seems it’s more about general theory on how to tune systems than a “RDBMS X v.Y tuning guide” (which I think is much more interesting and useful in the long term).
I will definitely take a look at it.
Regards,
Fran
Fran
8 Nov 09 at 5:31 pm
Give it a read and see if it doesn’t change your mind about “tuning” :-)
Xaprb
8 Nov 09 at 9:49 pm
I agree, this is the best performance book I’ve read.
Using this methodology I was able turn a traditional enterprise software solution that was struggling to support one large customer into a SAS solution handling 200 customers in one database. The solution wasn’t pretty at times, but it worked. And when I worked there this solution was running on SQL Server, so these tuning concepts apply to all databases and performance tuning in general.
I also recommend reading Tom Kyte’s books. They are more Oracle based, but I find many of the concepts carry over to other databases.
Broosk Johnson
9 Nov 09 at 1:31 pm
[...] Millsap’s Method R and Peter Zaitsev’s Goal-Driven Performance Optimization. Cary wrote an excellent book about his method, and I recommend buying that book and reading it at least twice. These methods are guaranteed to [...]
Response-time optimization in systems that are queued at Xaprb
9 Dec 09 at 11:28 pm
[...] a lot more from mk-query-digest if you read this paper — and you should also read his book, reviewed here. It’s one of the top books on my Essential Books [...]
Cary Millsap: Thinking Clearly about Performance at Xaprb
23 Feb 10 at 11:52 am
I’m a long time Oracle user, and to provide a counterpoint to all the good reviews, this is one of the most disappointing books I’ve read. The basis is good in suggesting a solid methodology is the best way to diagnose performance issues. I found the method description a bit repetitive, well over half the book, and also too much of a sales pitch to the author’s own tools at times. I then started skimming the queueing theory chapter until I couldn’t believe the verdict of the worked example(pg 266 – In summary how many servers do you need for a 100 users executing a 0.49s sql statement 4 times a minute with 95% sub 1s response. The answer being it wasn’t possible!!). I had to then flick back to pg245 to find out why. The author does correctly say test this on your own system but the assumption used in example used an exponential distribution of response time. 0.49s is the average but the most common response time nearly 3 (e) times more likely is 0s. It quickly explains that just with the exponential tail over 10% of the 0.49s take over 1s, hence 95% rule can’t ever be met. As I said the author does urge you to test this assumption, but this test is assuming zero contention, I’d expect 0.48/0.51s maybe due to internal fluctuations but a very tight normal distribution. I definitely don’t think that a 1 minute reporting query is 3 times more likely to finish instantly. I also found the case studies weak in that typical data dictionary and especially Oracle wait interface queries would pick up the issues, as long as the investigator was using at least a reasonable methodology. I don’t want to be too harsh in this as there are good insights in it but I’d never recommend it over any Tom Kyte book (He literally transformed the Oracle technical book scene ,with his proof by example), or the excellent, pragmatic, real life experienced High Performance MySQL book. I’m now just waiting for a definitive hands on real world example Postgres book.
RobB
2 Mar 10 at 8:57 am
Just to clarify as it comes up in the related performance post. I think the middle M in M/M/m for exponential service_time isn’t a valid assumption for a typical service time and checking the link to wikipedia Kendall notation an M/D/m model is the valid assumption, especially for the book example.
RobB
2 Mar 10 at 10:40 am