Database Tuning

Principles, Experiments, and Troubleshooting Techniques

By: Dennis Shasha, Philippe Bonnet

Write A Review

Paperback | 1 May 2002

At a Glance

Paperback
440 Pages

Dimensions(cm)
24.13 x 19.05 x 2.54

Paperback

$238.75

or 4 interest-free payments of $59.69 with

Ships in 15 to 25 business days

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

Industry Reviews

"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

Shipping

	Standard Shipping	Express Shipping
Metro postcodes:	$9.99	$14.95
Regional postcodes:	$9.99	$14.95
Rural postcodes:	$9.99	$14.95

Orders over $79.00 qualify for free shipping.

How to return your order

At Booktopia, we offer hassle-free returns in accordance with our returns policy. If you wish to return an item, please get in touch with Booktopia Customer Care.

Additional postage charges may be applicable.

Defective items

If there is a problem with any of the items received for your order then the Booktopia Customer Care team is ready to assist you.

For more info please visit our Help Centre.

You Can Find This Book In

Non-Fiction Computing & I.T.Databases

This product is categorised by

Non-Fiction Computing & I.T.Databases

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

Database Tuning

Principles, Experiments, and Troubleshooting Techniques

At a Glance

Paperback

Industry Reviews

Shipping

How to return your order

Defective items

You Can Find This Book In

More in Databases

SPSS Survival Manual

7th Edition - A step by step guide to data analysis using IBM SPSS

Python All-in-One For Dummies

3rd Edition

Storytelling with Data

A Data Visualization Guide for Business Professionals, 10th Anniversary Edition

Database Systems

A Practical Approach

Tools and Applications of Data Mining

Big Data Analytics

A Practical Guide

Microsoft Power BI For Dummies

For Dummies (Computer/Tech)

The Data Science and Psychology

Data Mining

Concepts, Techniques and Applications

The Crypto Trader

How anyone can make money trading Bitcoin and other cryptocurrencies

The Data Warehouse Toolkit 3ed

The Definitive Guide to Dimensional Modeling

Microsoft Excel 365 Bible

Bible

Designing Data-Intensive Applications

Big Ideas Behind Reliable, Scalable, and Maintainable Systems

Hands-On Programming with R

Write Your Own Functions and Simulations

Database Systems 6ed

A Practical Approach to Design, Implementation, and Management, Global Edition

SQL Cookbook

Query Solutions and Techniques for All SQL Users 2nd Edition

Data Visualization with Microsoft Power Bi

How to Design Savvy Dashboards

Mindf*ck

Inside Cambridge Analytica's Plot to Break the World

Deciphering Data Architectures

Choosing Between a Modern Data Warehouse, Data Fabric, Data Lakehouse, and Data Mesh

Don't Make Me Think, Revisited

A Common Sense Approach to Web Usability

Modern Data Analytics in Excel

Using Power Query, Power Pivot and More for Enhanced Data Analytics

Oracle in a Nutshell

In a Nutshell (O'Reilly)

Interactively exploring high-dimensional data and models in R

1st Edition

Interactively exploring high-dimensional data and models in R

1st Edition

This product is categorised by