Database Benchmarking: Practical Methods for Oracle & SQL Server

Description

Every information system must know their limits.  As systems grow, resources are stressed and one of the major causes of unplanned database outages is a failure to anticipate the effects of growth.

Benchmarking is a critical job function and every database professional must understand the principles of capacity planning and database bottleneck analysis.  By using a benchmark method you can spot areas of pending problems before they cripple your database.

This landmark book explores all aspects of database benchmarking and shows you a real-world approach that ensures that you are prepared for whatever the future brings to your mission-critical database.

Table of Contents

Using the Online Code Depot
Supplemental Materials
Conventions Used in this Book
Acknowledgements
Preface
Database Predictive Analysis
Database Benchmark Validity
The Problems of Adaptive Threshold Forecasting
Predictive Modeling in the Real-world
Databases that Benefit from Predictive Analysis
The Transaction Processing Performance Council
Benchmarking Bi-modal Databases
Capacity Planning and Performance Testing
Forecasting the Future
Database Benchmarking Myths
Learning from Database Benchmarks
Conclusion
Database Benchmarking
Industry Standard Benchmarks
Basic Benchmark Components
Specifications
Details
Design Goals
Data Points
Test Plan
Control Logic
Repeatability
Accurate Statistics
Database Benchmarks with Benchmark Factory
TPC-C
TPC-H
TPC-E
ASAP and Scalable Hardware
Benchmarking with a Custom Workload
Conclusion
Introduction
Trend Identification with Benchmark Tools
Testing a Suggested Architecture
System Information
Database Objects
Details of Materialized View
Transaction Details
Insert Transaction
Select Transaction
Phase : Both Insert and Select Varying
Phase : SELECT Transaction Level Constant
Concurrent SELECTS
Concurrent SELECTS
Concurrent SELECTS
Phase : Materialized View with No Partitions

  Combined Results
Combined Results Summary
Recommendations
Planning Future Hardware & Software Needs
Architecture
Executive Summary
Limitations and Caveats
Phase : Issues Query Testing
Randomization of the Issues Queries
Transaction Times for Issues Tests
Average Transaction Times
Database Activity
Operating System Activity
Phase Conclusion
Maintaining Service Level Agreements (SLA’s)
Determining SLA Test Queries
Ok here is the SLA and Queries What Now?
Issues with Generating Your Own Scripts
The Easy Way
Supporting Server & Storage Consolidations
Database Implosion Therapy
Hardware Issues in Stress Testing
Configuring the Server Environment
Stress Testing Specific Workloads
Stressing Read-Only Databases (Read-only Implosion)
Stressing OLTP Databases (DML Implosion)
Stressing Mixed Databases (General Implosion)
Stressing Specialized Environments
Stressing Distributed Systems
Stressing Consolidated Systems
Stressing Virtualized Systems
Stressing Specific Database Platforms
Not all SQL Implementations up to Spec
Not all Query Optimizers Created Equal
Conclusion
Isolate the Benchmark Environment
Prepare the Server Operating System
Windows
Linux
Prepare the Database Configuration
SQL Server
Oracle
Concentrate on What and not How
Installing Benchmark Factory
Conclusion
Selecting among Benchmarks
Creating Benchmark Jobs
Running Benchmark Jobs
Monitoring Benchmark Runs
Special Oracle RAC Considerations
Conclusion
Don’t Rush to Conclusions
An Example on Oracle
An Example on Microsoft SQL Server
Finding the “Sweet Spot”
When Conclusions are not Self-Evident
Correlating Results
Real-time versus “Run” Reports
Extrapolation & Interpolation
Documenting the Final Analysis