Skip to content

Database Tuning Principles, Experiments, and Troubleshooting Techniques

Spend $50 to get a free DVD!

ISBN-10: 1558607536

ISBN-13: 9781558607538

Edition: 2003

Authors: Dennis Shasha, Philippe Bonnet

List price: $91.95
Blue ribbon 30 day, 100% satisfaction guarantee!
Out of stock
what's this?
Rush Rewards U
Members Receive:
Carrot Coin icon
XP icon
You have reached 400 XP and carrot coins. That is the daily max!

Customers also bought

Book details

List price: $91.95
Copyright year: 2003
Publisher: Elsevier Science & Technology
Publication date: 6/7/2002
Binding: Paperback
Pages: 440
Size: 7.50" wide x 9.50" long x 1.00" tall
Weight: 1.936
Language: English

Philippe Bonnet is an experiment database researcher. He directs code development of the open source object-relational database system Predator developed at Cornell.

Foreword
Preface
Basic Principles
The Power of Principles
Five Basic Principles
Think Globally; Fix Locally
Partitioning Breaks Bottlenecks
Start-Up Costs Are High; Running Costs Are Low
Render unto Server What Is Due unto Server
Be Prepared for Trade-Offs
Basic Principles and Knowledge
Tuning The Guts
Goal of Chapter
Locking and Concurrency Control
Correctness Considerations
Lock Tuning
Logging and the Recovery Subsystem
Principles of Recovery
Tuning the Recovery Subsystem
Operating System Considerations
Scheduling
Database Buffer
How Much Memory Is Economical
Multiprogramming Level
Files: Disk Layout and Access
Hardware Tuning
Tuning the Storage Subsystem
Enhancing the Hardware Configuration
Bibliography
Exercises
Index Tuning
Goal of Chapter
Types of Queries
Key Types
Data Structures
Structures Provided by Database Systems
Data Structures for In-Memory Data
Sparse Versus Dense Indexes
To Cluster or Not to Cluster
Evaluation of Clustering Indexes
Nonclustering Indexes
Composite Indexes
Joins, Foreign Key Constraints, and Indexes
Avoid Indexes on Small Tables
Summary: Table Organization and Index Selection
Distributing the Indexes of a Hot Table
General Care and Feeding of Indexes
Bibliography
Exercises
Tuning Relational Systems
Goal of Chapter
Table Schema and Normalization
Preliminary Definitions
Some Schemes Are Better Than Others
Normalization by Example
A Practical Way to Design Relations
Functional Dependency Test
Tuning Normalization
Tuning Denormalization
Clustering Two Tables
Aggregate Maintenance
Record Layout
Query Tuning
Minimizing DISTINCTs
Rewriting of Nested Queries
Triggers
Uses of Triggers
Trigger Performance
Bibliography
Exercises
Communicating With the Outside
Talking to the World
Client-Server Mechanisms
Objects, Application Tools, and Performance
Beware of Small Objects
Beware of Application Development Tools
Tuning the Application Interface
Avoid User Interaction Within a Transaction
Minimize the Number of Round-Trips Between the Application and the Database Server
Retrieve Needed Columns Only
Retrieve Needed Rows Only
Minimize the Number of Query Compilations
Bulk Loading Data
Accessing Multiple Databases
Bibliography
Case Studies From Wall Street
Techniques for Circumventing Superlinearity
Perform Data Integrity Checks at Input Time
Distribution and Heterogeneity
Interoperability with Other Databases
Global Systems
Managing Connections Socialistically in a Distributed Setting
Trading Space for Time in History-Dependent Queries
Chopping to Facilitate Global Trades
Clustering Index Woes
Beware the Optimization
Disaster Planning and Performance
Keeping Nearly Fixed Data Up to Date
Deletions and Foreign Keys
Partitioning Woes: The Hazards of Meaningful Keys
The Problem of Time
Present Value
Regular Time Series and Statistics
Irregular Time Series and Frequency Counting
Bitemporality
What to Do with Time
Bibliography
Exercises
Troubleshooting
Introduction
A Consumption Chain Approach
The Three Questions
How to Gather Information: The Tools
Query Plan Explainers
Performance Monitors
Event Monitors
Now What?
Queries from Hell
Finding "Suspicious" Queries
Analyzing a Query's Access Plan
Profiling a Query Execution
Are DBMS Subsystems Working Satisfactorily?
Disk Subsystem
Buffer (Cache) Manager
Logging Subsystem
Locking Subsystem
Is the DBMS Getting All It Needs?
Checking on CPU
Checking on Disks and Controllers
Checking on Memory
Checking the Network
Conclusion
Bibliography
Tuning E-Commerce Applications
Goal
E-commerce Architecture
Tuning the E-commerce Architecture
Caching
Connection Pooling
Indexing
Case Study: Shop Comparison Portal
Capacity Planning in a Nutshell
Capacity Planning Essentials
What to Purchase
Bibliography
Exercises
Celko on Data Warehouses: Techniques, Successes, and Mistakes
Early History
Forget What the Elders Taught You
Building a Warehouse Is Hard
The Effect on the Bottom Line
Wal-Mart
Supervalu
Harrah's
Data Warehouse Tuning
What's Different About Data Warehouses
Uses of Data Warehouses
Technology for Data Warehousing
Tuning for Customer Relationship Management Systems
Federated Data Warehouse Tuning
Product Selection
Bibliography
Exercises
Real-Time Databases
Overview
Replicated State Machine Approach
Transaction Chopping
Assumptions
Correct Choppings
Finding the Finest Chopping
Optimal Chopping Algorithm
Application to Typical Database Systems
Chopping and Snapshot Isolation
Related Work
Bibliography
Time Series, Especially for Finance
Setting Up a Time Series Database
FAME
S-Plus
SAS
KDB
Oracle 8i Time Series
Features You Want for Time Series
Time Series Data Mining
Understanding Access Plans
Data Access Operators
Query Structure Operators
Auxiliary Operators
Bibliography
Configuration Parameters
Oracle
SQL Server
DB2 UDB
Glossary
Index