Database Tuning: Principles, Experiments, and Troubleshooting Techniques (The Morgan Kaufmann Series in Data Management Systems) [Paperback]

Dennis Shasha (Author), Philippe Bonnet (Author)

Book Description

ISBN-10: 1558607536 | ISBN-13: 978-1558607538 | Publication Date: June 12, 2002 | Edition: 1

Tuning your database for optimal performance means more than following a few short steps in a vendor-specific guide. For maximum improvement, you need a broad and deep knowledge of basic tuning principles, the ability to gather data in a systematic way, and the skill to make your system run faster. This is an art as well as a science, and Database Tuning: Principles, Experiments, and Troubleshooting Techniques will help you develop portable skills that will allow you to tune a wide variety of database systems on a multitude of hardware and operating systems. Further, these skills, combined with the scripts provided for validating results, are exactly what you need to evaluate competing database products and to choose the right one.

* Forward by Jim Gray, with invited chapters by Joe Celko and Alberto Lerner
* Includes industrial contributions by Bill McKenna (RedBrick/Informix), Hany Saleeb (Oracle), Tim Shetler (TimesTen), Judy Smith (Deutsche Bank), and Ron Yorita (IBM)
* Covers the entire system environment: hardware, operating system, transactions, indexes, queries, table design, and application analysis
* Contains experiments (scripts available on the author's site) to help you verify a system's effectiveness in your own environment
* Presents special topics, including data warehousing, Web support, main memory databases, specialized databases, and financial time series
* Describes performance-monitoring techniques that will help you recognize and troubleshoot problems

Editorial Reviews

Review

"For the novice, this book gives sage advice on the performance issues of SQL-level logical database design that cuts across all systems. For me at least, the physical database design was particularly interesting, because the book presents the implications of design choices on IBM, Oracle, and Microsoft systems. These systems are quite different internally, and the book's example will surprise even the systems' implementers."
—Jim Gray, Microsoft

"Shasha and Bonnet prove the value of applying timeless principles to everchanging technology. The unique wealth of practical ideas, facts, and examples equip database practitioners "in the trenches" like no other resource. Chapter 2 alone is worth the price of the book."
—Bob Badour, Online Curmudgeon

From the Back Cover

"For the novice, this book gives sage advice on the performance issues of SQL-level logical database design that cuts across all systems. For me at least, the physical database design was particularly interesting, because the book presents the implications of design choices on IBM, Oracle, and Microsoft systems. These systems are quite different internally, and the book's example will surprise even the systems' implementers."

--Jim Gray, Microsoft

"Shasha and Bonnet prove the value of applying timeless principles to everchanging technology. The unique wealth of practical ideas, facts, and examples equip database practitioners "in the trenches" like no other resource. Chapter 2 alone is worth the price of the book."

--Bob Badour, Online Curmudgeon

---

Tuning your database for optimal performance means more than following a few short steps in a vendor-specific guide. For maximum improvement, you need a broad and deep knowledge of basic tuning principles, the ability to gather data in a systematic way, and the skill to make your system run faster. This is an art as well as a science, and Database Tuning: Principles, Experiments, and Troubleshooting Techniques will help you develop portable skills that will allow you to tune a wide variety of database systems on a multitude of hardware and operating systems. Further, these skills, combined with the scripts provided for validating results, are exactly what you need to evaluate competing database products and to choose the right one.

Features

• Forward by Jim Gray, with invited chapters by Joe Celko and Alberto Lerner
  
• Includes industrial contributions by Bill McKenna (RedBrick/Informix), Hany Saleeb (Oracle), Tim Shetler (TimesTen), Judy Smith (Deutsche Bank), and Ron Yorita (IBM)
  
• Covers the entire system environment: hardware, operating system, transactions, indexes, queries, table design, and application analysis
  
• Contains experiments (scripts available on the author's site) to help you verify a system's effectiveness in your own environment
  
• Presents special topics, including data warehousing, Web support, main memory databases, specialized databases, and financial time series
  
• Describes performance-monitoring techniques that will help you recognize and troubleshoot problems

See all Editorial Reviews

Product Details

• Paperback: 440 pages
• Publisher: Morgan Kaufmann; 1 edition (June 12, 2002)
• Language: English
• ISBN-10: 1558607536
• ISBN-13: 978-1558607538
• Product Dimensions: 9 x 7.4 x 1.2 inches
• Shipping Weight: 1.6 pounds (View shipping rates and policies)
• Average Customer Review: 4.5 out of 5 stars  See all reviews (4 customer reviews)
• Amazon Best Sellers Rank: #172,067 in Books (See Top 100 in Books)

More About the Author

Discover books, learn about writers, read author blogs, and more.

Customer Reviews

Most Helpful Customer Reviews

3 of 3 people found the following review helpful:

4.0 out of 5 stars Extensive if assorted coverage on the topic, December 10, 2006

By 

Dmitry Dvoinikov (Ekaterinburg, Russia) - See all my reviews

This review is from: Database Tuning: Principles, Experiments, and Troubleshooting Techniques (The Morgan Kaufmann Series in Data Management Systems) (Paperback)

When starting this review, I was not sure whether to rate this book with 4 stars or with 5 stars. I finally went with 4, but I will have hard times trying to explain why.

The book is good, no doubt about that, even very good, but it lacks a plot. It would have made an excellent brochure for a university course in database tuning - it's extensive, has nice and clear diagrams, some math and stories from the field. And it's not big - 350 pages covering many different aspects - from hardware to data warehouses.

Specifically, it covers hardware tuning, index tuning, designing the data model and optimizing external interfaces. This makes the first half of the book or less than 200 pages. The rest is pure fiction - case studies from the Wall Street, fictional troubleshooting chapter which tells you that if you have a problem you should be looking to its source, and from that point to the end of the book it's all stories about e-commerce and data warehouses. Look, it is interesting for sure and it makes a nice introduction to the specifics of large analytical databases, but there is absolutely no technical details.

From the practical standpoint, the whole book (and database tuning itself) is summarized with the very first sentences in the book:

[quote]
Tuning rests on a foundation of informed common sense. This makes it both easy and hard. [...] Tuning is easy because the tuner needs not struggle through complicated formulas or theorems. [...] Tuning is difficult because the principles and knowledge underlying the common sense require a broad and deep understanding [...]
[/quote]

I could give 5 stars for this alone. But let's see what it implies.

There is almost no formulas and theorems in this book (except for a rather interesting appendix explaining method for splitting large transactions into smaller ones while remaining the whole set conflict-free). But then I wouldn't mind more math if each piece was accompanying something similarly interesting.

On the other hand, common sense is common, there is nothing really so new, if you have done database tuning before and/or read about it - it's the same thing. "broad and deep understanding" - this books gives you some of both but don't forget to practice.

The word "experiments" in the title - did it look promising ! I sometimes practice setting up a toy database installation to run specific workload through and test some ideas and I know exactly how much time it takes and how difficult it is to start over and over again until the graphs start telling something. Here, a book whose authors did it for you ! Or, is it ?

Yes and no. Yes - there are bar graphs and plots telling how much the performance gains or suffers in this or that case, but what those cases are ? Simplistic. What happens if you add more secondary indexes ? Insert/update performance suffers. How much ? Say, -40%. Is it good to know ? Yes. Will it alone suffice for a practical project ? No. Likewise, there are many other graphs depicting what you should have already known.

Oh, the authors use SQL Server, Oracle and DB2 for its experiments ? That's nice, but then, given that not all the tests are repeated on all the platforms, and differences between the engines - how good is it ?

Don't get me wrong - those graphs a very nice rule of thumb kind of source. Very useful for a quick refreshment. But there are no real-life complex workload experiments.

See, I'm still trying to find grounds for 4 stars, and still with no luck. Be it 4 1/2.

Who should read the book ? Everyone from a student to an experienced database developer or an administrator, although the beginners will benefit more.

2 of 2 people found the following review helpful:

5.0 out of 5 stars Database Tuning, November 27, 2002

By 

"bradb438" - See all my reviews

This review is from: Database Tuning: Principles, Experiments, and Troubleshooting Techniques (The Morgan Kaufmann Series in Data Management Systems) (Paperback)

Shasha and Bonnet's Database Tuning is a worthy successor to Shasha's Database Tuning: A principled approach, 1992. It adds experimental tests for tuning options, warnings about object-oriented approaches, and recommendations for application interfaces. There are new sections on financial applications, data mining, troubleshooting, e-commerce, and time series.

Databases are critical resources for business, personalized web sites, and web services. If a database is not tuned, simple optimizations can dramatically improve response time and throughput. With the methods learned from this book, database developers and administrators can improve performance and fix slow queries.

The book is well written and carefully detailed. It moves step-by-step from principles to practice. There are examples and exercises throughout.

2 of 2 people found the following review helpful:

4.0 out of 5 stars Relational Database Tuning Principles/Experiments, July 21, 2002

By A Customer

This review is from: Database Tuning: Principles, Experiments, and Troubleshooting Techniques (The Morgan Kaufmann Series in Data Management Systems) (Paperback)

A fine introduction to the topic, highly recommended for new DBAs. For experienced DBA also well worth reading, the information contained in the implementation and performance comparisons of current versions of Oracle, IBM DB2 and MS SQL Server (W2K/Linux platform) is not easily obtained elsewhere. Presents a framework in which to understand what relational DB Tuning is concerned with, starting with the principles, and offers concrete examples and measurements as well as a few exercises.
The first five chapters and a later one on database tuning are especially valuable... The book includes a very useful bibliography pro chapter.