Database Tuning: Principles, Experiments, and Troubleshooting Techniques

✍ Scribed by Dennis Shasha, Philippe Bonnet

• Publisher: Morgan Kaufmann
• Year: 2002
• Tongue: English
• Leaves: 441
• Series: The Morgan Kaufmann Series in Data Management Systems
• Edition: 1st
• Category: Library

✦ Synopsis

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

Front Cover......Page 1
Database Tuning: Principles, Experiments, and Troubleshooting Techniques......Page 6
Copyright Page......Page 7
Contents......Page 12
Foreword......Page 10
Preface......Page 20
1.1 The Power of Principles......Page 26
1.2 Five Basic Principles......Page 27
1.3 Basic Principles and Knowledge......Page 32
2.2 Locking and Concurrency Control......Page 34
2.3 Logging and the Recovery Subsystem......Page 61
2.4 Operating System Considerations......Page 74
2.5 Hardware Tuning......Page 84
Bibliography......Page 96
Exercises......Page 99
3.2 Types of Queries......Page 102
3.4 Data Structures......Page 106
3.5 Sparse Versus Dense Indexes......Page 114
3.6 To Cluster or Not to Cluster......Page 115
3.7 Joins, Foreign Key Constraints, and Indexes......Page 127
3.9 Summary: Table Organization and Index Selection......Page 130
3.10 Distributing the Indexes of a Hot Table......Page 135
3.11 General Care and Feeding of Indexes......Page 136
Bibliography......Page 140
Exercises......Page 141
4.1 Goal of Chapter......Page 148
4.2 Table Schema and Normalization......Page 149
4.3 Clustering Two Tables......Page 162
4.4 Aggregate Maintenance......Page 163
4.5 Record Layout......Page 165
4.6 Query Tuning......Page 168
4.7 Triggers......Page 183
Bibliography......Page 186
Exercises......Page 187
5.1 Talking to the World......Page 190
5.2 Client-Server Mechanisms......Page 192
5.3 Objects, Application Tools, and Performance......Page 193
5.4 Tuning the Application Interface......Page 196
5.5 Bulk Loading Data......Page 204
5.6 Accessing Multiple Databases......Page 205
Bibliography......Page 208
6.1 Techniques for Circumventing Superlinearity......Page 210
6.3 Distribution and Heterogeneity......Page 213
6.4 Trading Space for Time in History-Dependent Queries......Page 218
6.5 Chopping to Facilitate Global Trades......Page 219
6.7 Beware the Optimization......Page 220
6.8 Disaster Planning and Performance......Page 221
6.9 Keeping Nearly Fixed Data Up to Date......Page 226
6.10 Deletions and Foreign Keys......Page 227
6.12 The Problem of Time......Page 228
Bibliography......Page 234
Exercises......Page 235
7.1 Introduction......Page 238
7.2 How to Gather Information: The Tools......Page 242
7.3 Queries from Hell......Page 251
7.4 Are DBMS Subsystems Working Satisfactorily?......Page 256
7.5 Is the DBMS Getting All It Needs?......Page 261
Bibliography......Page 265
8.2 E-commerce Architecture......Page 268
8.3 Tuning the E-commerce Architecture......Page 271
8.4 Case Study: Shop Comparison Portal......Page 275
8.5 Capacity Planning in a Nutshell......Page 278
Bibliography......Page 282
Exercises......Page 283
9.1 Early History......Page 286
9.2 Forget What the Elders Taught You......Page 287
9.3 Building a Warehouse Is Hard......Page 292
9.4 The Effect on the Bottom Line......Page 293
10.1 What's Different About Data Warehouses......Page 300
10.2 Tuning for Customer Relationship Management Systems......Page 314
10.3 Federated Data Warehouse Tuning......Page 319
10.4 Product Selection......Page 320
Bibliography......Page 322
Exercises......Page 324
A.1 Overview......Page 326
A.2 Replicated State Machine Approach......Page 328
B.1 Assumptions......Page 330
B.2 Correct Choppings......Page 332
B.3 Finding the Finest Chopping......Page 337
B.4 Optimal Chopping Algorithm......Page 340
B.5 Application to Typical Database Systems......Page 342
B.6 Related Work......Page 344
Bibliography......Page 348
APPENDIX C. TIME SERIES, ESPECIALLY FOR FINANCE......Page 350
C.1 Setting Up a Time Series Database......Page 351
C.2 FAME......Page 352
C.4 SAS......Page 354
C.5 KDB......Page 355
C.7 Features You Want for Time Series......Page 357
C.8 Time Series Data Mining......Page 358
APPENDIX D. UNDERSTANDING ACCESS PLANS......Page 362
D.1 Data Access Operators......Page 364
D.2 Query Structure Operators......Page 367
D.3 Auxiliary Operators......Page 371
Bibliography......Page 373
APPENDIX E. CONFIGURATION PARAMETERS......Page 374
E.1 Oracle......Page 375
E.2 SQL Server......Page 379
E.3 DB2 UDB......Page 380
Glossary......Page 386
Index......Page 412
Author Biographies......Page 441