Database Tuning

Database Tuning

by Dennis Shasha, Philippe Bonnet
Released June 2002
Publisher(s): Morgan Kaufmann
ISBN: 9780080503783

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

Table of contents

  1. Front Cover
  2. Database Tuning: Principles, Experiments, and Troubleshooting Techniques
  3. Copyright Page
  4. Foreword
  5. Contents (1/2)
  6. Contents (2/2)
  7. Preface (1/2)
  8. Preface (2/2)
  9. CHAPTER 1. BASIC PRINCIPLES
    1. 1.1 The Power of Principles
    2. 1.2 Five Basic Principles
    3. 1.3 Basic Principles and Knowledge
  10. CHAPTER 2. TUNING THE GUTS
    1. 2.1 Goal of Chapter
    2. 2.2 Locking and Concurrency Control
    3. 2.3 Logging and the Recovery Subsystem (1/3)
    4. 2.3 Logging and the Recovery Subsystem (2/3)
    5. 2.3 Logging and the Recovery Subsystem (3/3)
    6. 2.4 Operating System Considerations (1/2)
    7. 2.4 Operating System Considerations (2/2)
    8. 2.5 Hardware Tuning (1/3)
    9. 2.5 Hardware Tuning (2/3)
    10. 2.5 Hardware Tuning (3/3)
    11. Bibliography
    12. Exercises
  11. CHAPTER 3. INDEX TUNING
    1. 3.1 Goal of Chapter
    2. 3.2 Types of Queries
    3. 3.3 Key Types
    4. 3.4 Data Structures
    5. 3.5 Sparse Versus Dense Indexes
    6. 3.6 To Cluster or Not to Cluster (1/3)
    7. 3.6 To Cluster or Not to Cluster (2/3)
    8. 3.6 To Cluster or Not to Cluster (3/3)
    9. 3.7 Joins, Foreign Key Constraints, and Indexes
    10. 3.8 Avoid Indexes on Small Tables
    11. 3.9 Summary: Table Organization and Index Selection
    12. 3.10 Distributing the Indexes of a Hot Table
    13. 3.11 General Care and Feeding of Indexes
    14. Bibliography
    15. Exercises (1/2)
    16. Exercises (2/2)
  12. CHAPTER 4. TUNING RELATIONAL SYSTEMS
    1. 4.1 Goal of Chapter
    2. 4.2 Table Schema and Normalization (1/3)
    3. 4.2 Table Schema and Normalization (2/3)
    4. 4.2 Table Schema and Normalization (3/3)
    5. 4.3 Clustering Two Tables
    6. 4.4 Aggregate Maintenance
    7. 4.5 Record Layout
    8. 4.6 Query Tuning (1/3)
    9. 4.6 Query Tuning (2/3)
    10. 4.6 Query Tuning (3/3)
    11. 4.7 Triggers
    12. Bibliography
    13. Exercises
  13. CHAPTER 5. COMMUNICATING WITH THE OUTSIDE
    1. 5.1 Talking to the World
    2. 5.2 Client-Server Mechanisms
    3. 5.3 Objects, Application Tools, and Performance
    4. 5.4 Tuning the Application Interface (1/2)
    5. 5.4 Tuning the Application Interface (2/2)
    6. 5.5 Bulk Loading Data
    7. 5.6 Accessing Multiple Databases
    8. Bibliography
  14. CHAPTER 6. CASE STUDIES FROM WALL STREET
    1. 6.1 Techniques for Circumventing Superlinearity
    2. 6.2 Perform Data Integrity Checks at Input Time
    3. 6.3 Distribution and Heterogeneity
    4. 6.4 Trading Space for Time in History-Dependent Queries
    5. 6.5 Chopping to Facilitate Global Trades
    6. 6.6 Clustering Index Woes
    7. 6.7 Beware the Optimization
    8. 6.8 Disaster Planning and Performance
    9. 6.9 Keeping Nearly Fixed Data Up to Date
    10. 6.10 Deletions and Foreign Keys
    11. 6.11 Partitioning Woes: The Hazards of Meaningful Keys
    12. 6.12 The Problem of Time
    13. Bibliography
    14. Exercises
  15. CHAPTER 7. TROUBLESHOOTING
    1. 7.1 Introduction
    2. 7.2 How to Gather Information: The Tools (1/2)
    3. 7.2 How to Gather Information: The Tools (2/2)
    4. 7.3 Queries from Hell
    5. 7.4 Are DBMS Subsystems Working Satisfactorily?
    6. 7.5 Is the DBMS Getting All It Needs?
    7. 7.6 Conclusion
    8. Bibliography
  16. CHAPTER 8. TUNING E-COMMERCE APPLICATIONS
    1. 8.1 Goal
    2. 8.2 E-commerce Architecture
    3. 8.3 Tuning the E-commerce Architecture
    4. 8.4 Case Study: Shop Comparison Portal
    5. 8.5 Capacity Planning in a Nutshell
    6. Bibliography
    7. Exercises
  17. CHAPTER 9. CELKO ON DATA WAREHOUSES: TECHNIQUES, SUCCESSES, AND MISTAKES
    1. 9.1 Early History
    2. 9.2 Forget What the Elders Taught You
    3. 9.3 Building a Warehouse Is Hard
    4. 9.4 The Effect on the Bottom Line (1/2)
    5. 9.4 The Effect on the Bottom Line (2/2)
  18. CHAPTER 10. DATA WAREHOUSE TUNING
    1. 10.1 What's Different About Data Warehouses
    2. 10.2 Tuning for Customer Relationship Management Systems
    3. 10.3 Federated Data Warehouse Tuning
    4. 10.4 Product Selection
    5. Bibliography
    6. Exercises
  19. APPENDIX A. REAL-TIME DATABASES
    1. A.1 Overview
    2. A.2 Replicated State Machine Approach
  20. APPENDIX B. TRANSACTION CHOPPING
    1. B.1 Assumptions
    2. B.2 Correct Choppings
    3. B.3 Finding the Finest Chopping
    4. B.4 Optimal Chopping Algorithm
    5. B.5 Application to Typical Database Systems
    6. B.6 Related Work
    7. Bibliography
  21. APPENDIX C. TIME SERIES, ESPECIALLY FOR FINANCE
    1. C.1 Setting Up a Time Series Database
    2. C.2 FAME
    3. C.3 S-Plus
    4. C.4 SAS
    5. C.5 KDB
    6. C.6 Oracle 8i Time Series
    7. C.7 Features You Want for Time Series
    8. C.8 Time Series Data Mining
  22. APPENDIX D. UNDERSTANDING ACCESS PLANS
    1. D.1 Data Access Operators
    2. D.2 Query Structure Operators
    3. D.3 Auxiliary Operators
    4. Bibliography
  23. APPENDIX E. CONFIGURATION PARAMETERS
    1. E.1 Oracle
    2. E.2 SQL Server
    3. E.3 DB2 UDB (1/2)
    4. E.3 DB2 UDB (2/2)
  24. Glossary (1/6)
  25. Glossary (2/6)
  26. Glossary (3/6)
  27. Glossary (4/6)
  28. Glossary (5/6)
  29. Glossary (6/6)
  30. Index (1/6)
  31. Index (2/6)
  32. Index (3/6)
  33. Index (4/6)
  34. Index (5/6)
  35. Index (6/6)
  36. Author Biographies

Product information

  • Title: Database Tuning
  • Author(s): Dennis Shasha, Philippe Bonnet
  • Release date: June 2002
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780080503783