Database Tuning

by Shasha; Bonnet

• ISBN13: 9781558607538
• ISBN10: 1558607536
• Edition: Revised
• Format: Paperback
• Copyright: 2002-05-29
• Publisher: Elsevier Science

Summary

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

Foreword	ix
Preface	xix
Basic Principles	1	(8)
The Power of Principles	1	(1)
Five Basic Principles	2	(5)
Think Globally; Fix Locally	2	(1)
Partitioning Breaks Bottlenecks	3	(1)
Start-Up Costs Are High; Running Costs Are Low	4	(1)
Render unto Server What Is Due unto Server	5	(1)
Be Prepared for Trade-Offs	6	(1)
Basic Principles and Knowledge	7	(2)
Tuning the Guts	9	(68)
Goal of Chapter	9	(1)
Locking and Concurrency Control	9	(27)
Correctness Considerations	12	(4)
Lock Tuning	16	(20)
Logging and the Recovery Subsystem	36	(13)
Principles of Recovery	37	(5)
Tuning the Recovery Subsystem	42	(7)
Operating System Considerations	49	(10)
Scheduling	50	(2)
Database Buffer	52	(2)
How Much Memory Is Economical	54	(2)
Multiprogramming Level	56	(1)
Files: Disk Layout and Access	57	(2)
Hardware Tuning	59	(18)
Tuning the Storage Subsystem	59	(7)
Enhancing the Hardware Configuration	66	(5)
Bibliography	71	(3)
Exercises	74	(3)
Index Tuning	77	(46)
Goal of Chapter	77	(1)
Types of Queries	77	(4)
Key Types	81	(1)
Data Structures	81	(8)
Structures Provided by Database Systems	82	(5)
Data Structures for In-Memory Data	87	(2)
Sparse Versus Dense Indexes	89	(1)
To Cluster or Not to Cluster	90	(12)
Evaluation of Clustering Indexes	92	(4)
Nonclustering Indexes	96	(4)
Composite Indexes	100	(2)
Joins, Foreign Key Constraints, and Indexes	102	(3)
Avoid Indexes on Small Tables	105	(1)
Summary: Table Organization and Index Selection	105	(5)
Distributing the Indexes of a Hot Table	110	(1)
General Care and Feeding of Indexes	111	(12)
Bibliography	115	(1)
Exercises	116	(7)
Tuning Relational Systems	123	(42)
Goal of Chapter	123	(1)
Table Schema and Normalization	124	(13)
Preliminary Definitions	124	(1)
Some Schemes Are Better Than Others	125	(2)
Normalization by Example	127	(2)
A Practical Way to Design Relations	129	(2)
Functional Dependency Test	131	(1)
Tuning Normalization	131	(5)
Tuning Denormalization	136	(1)
Clustering Two Tables	137	(1)
Aggregate Maintenance	138	(2)
Record Layout	140	(3)
Query Tuning	143	(15)
Minimizing DISTINCTs	151	(2)
Rewriting of Nested Queries	153	(5)
Triggers	158	(7)
Uses of Triggers	158	(2)
Trigger Performance	160	(1)
Bibliography	161	(1)
Exercises	162	(3)
Communicating with the Outside	165	(20)
Talking to the World	165	(2)
Client-Server Mechanisms	167	(1)
Objects, Application Tools, and Performance	168	(3)
Beware of Small Objects	169	(2)
Beware of Application Development Tools	171	(1)
Tuning the Application Interface	171	(8)
Avoid User Interaction Within a Transaction	172	(1)
Minimize the Number of Round-Trips Between the Application and the Database Server	172	(2)
Retrieve Needed Columns Only	174	(1)
Retrieve Needed Rows Only	175	(1)
Minimize the Number of Query Compilations	176	(3)
Bulk Loading Data	179	(1)
Accessing Multiple Databases	180	(5)
Bibliography	183	(2)
Case Studies from Wall Street	185	(28)
Techniques for Circumventing Superlinearity	185	(3)
Perform Data Integrity Checks at Input Time	188	(1)
Distribution and Heterogeneity	188	(5)
Interoperability with Other Databases	188	(2)
Global Systems	190	(2)
Managing Connections Socialistically in a Distributed Setting	192	(1)
Trading Space for Time in History-Dependent Queries	193	(1)
Chopping to Facilitate Global Trades	194	(1)
Clustering Index Woes	195	(1)
Beware the Optimization	195	(1)
Disaster Planning and Performance	196	(5)
Keeping Nearly Fixed Data Up to Date	201	(1)
Deletions and Foreign Keys	202	(1)
Partitioning Woes: The Hazards of Meaningful Keys	203	(1)
The Problem of Time	203	(10)
Present Value	203	(1)
Regular Time Series and Statistics	204	(1)
Irregular Time Series and Frequency Counting	205	(1)
Bitemporality	206	(1)
What to Do with Time	207	(2)
Bibliography	209	(1)
Exercises	210	(3)
Troubleshooting	213	(30)
Alberto Lerner
Introduction	213	(4)
A Consumption Chain Approach	214	(3)
The Three Questions	217	(1)
How to Gather Information: The Tools	217	(9)
Query Plan Explainers	217	(4)
Performance Monitors	221	(2)
Event Monitors	223	(1)
Now What?	224	(2)
Queries from Hell	226	(5)
Finding ``Suspicious'' Queries	226	(1)
Analyzing a Query's Access Plan	227	(2)
Profiling a Query Execution	229	(2)
Are DBMS Subsystems Working Satisfactorily?	231	(5)
Disk Subsystem	231	(2)
Buffer (Cache) Manager	233	(1)
Logging Subsystem	234	(1)
Locking Subsystem	235	(1)
Is the DBMS Getting All It Needs?	236	(4)
Checking on CPU	237	(1)
Checking on Disks and Controllers	237	(2)
Checking on Memory	239	(1)
Checking the Network	239	(1)
Conclusion	240	(3)
Bibliography	240	(3)
Tuning E-Commerce Applications	243	(18)
Goal	243	(1)
E-commerce Architecture	243	(3)
Tuning the E-commerce Architecture	246	(4)
Caching	246	(3)
Connection Pooling	249	(1)
Indexing	249	(1)
Case Study: Shop Comparison Portal	250	(3)
Capacity Planning in a Nutshell	253	(8)
Capacity Planning Essentials	254	(2)
What to Purchase	256	(1)
Bibliography	257	(1)
Exercises	258	(3)
Celko on Data Warehouses: Techniques, Successes, and Mistakes	261	(14)
Joe Celko
Early History	261	(1)
Forget What the Elders Taught You	262	(5)
Building a Warehouse Is Hard	267	(1)
The Effect on the Bottom Line	268	(7)
Wal-Mart	269	(2)
Supervalu	271	(2)
Harrah's	273	(2)
Data Warehouse Tuning	275	(26)
What's Different About Data Warehouses	275	(14)
Uses of Data Warehouses	276	(2)
Technology for Data Warehousing	278	(11)
Tuning for Customer Relationship Management Systems	289	(5)
Federated Data Warehouse Tuning	294	(1)
Product Selection	295	(6)
Bibliography	297	(2)
Exercises	299	(2)
Appendix A: REAL-TIME DATABASES	301	(4)
A.1 Overview	301	(2)
A.2 Replicated State Machine Approach	303	(2)
Appendix B: TRANSACTION CHOPPING	305	(20)
B.1 Assumptions	305	(2)
B.2 Correct Choppings	307	(5)
B.3 Finding the Finest Chopping	312	(3)
B.4 Optimal Chopping Algorithm	315	(2)
B.5 Application to Typical Database Systems	317	(2)
B.5.1 Chopping and Snapshot Isolation	318	(1)
B.6 Related Work	319	(6)
Bibliography	323	(2)
Appendix C: TIME SERIES, ESPECIALLY FOR FINANCE	325	(12)
C.1 Setting Up a Time Series Database	326	(1)
C.2 Fame	327	(2)
C.3 S-Plus	329	(1)
C.4 SAS	329	(1)
C.5 KDB	330	(2)
C.6 Oracle 8i Time Series	332	(1)
C.7 Features You Want for Time Series	332	(1)
C.8 Time Series Data Mining	333	(4)
Appendix D: UNDERSTANDING ACCESS PLANS	337	(12)
D.1 Data Access Operators	339	(3)
D.2 Query Structure Operators	342	(4)
D.3 Auxiliary Operators	346	(3)
Bibliography	348	(1)
Appendix E: CONFIGURATION PARAMETERS	349	(12)
E.1 Oracle	350	(4)
E.2 SQL Server	354	(1)
E.3 DB2 UDB	355	(6)
Glossary	361	(26)
Index	387