Database Management Technology

Tuning & Configuration Course Outline

1. Ingres Multi-Server Architecture (3-4 hours)
   1. Overall System Architecture
   2. OS Implementation Specifics
   3. Ingres II Enhancements
   4. Subsystem Interaction
   5. Ingres/Net
      
      
2. Query Performance (4-6 hours)
   1. Query Processing System
   2. Storage Structures
      1. Available Structures
      2. Storage Structure Characteristics
      3. Data Compression
      4. Secondary Indexes
   3. SYSMOD
   4. OPTIMIZEDB
   5. STATDUMP
   6. Server Performance Constants
   7. Query Execution Plans
   8. Multi-Location Tables
   9. Trace Flags
      
      
3. Ingres Configuration System (2-3 hours)
   1. Release 6 Configuration System
   2. Configuration By Forms (CBF)
      1. System Architecture
      2. Configuration Repository
      3. Rules System
      4. Named Configurations
      5. Parameter Types
      6. Custom Configuration Rules
         
4. Concurrency and Locking (3-4 hours)
   1. Locking System Architecture
   2. ANSI Isolation Levels
   3. Deadlock and Livelock Conditions
   4. Locking Granularity
      1. Choosing the Appropriate Granularity
      2. Row Locking Requirements
      3. Row Locking Implications
   5. Lock Modes
   6. Measuring and Controlling Escalation
   7. Configuration Parameters
      1. Measuring Running System
      2. Calculating Optimal Settings
      3. Implementing Configuration Changes
         
         
5. Logging and Recovery (4-5 hours)
   1. Logging System Architecture
      1. Physical Logging
      2. Physiological Logging
      3. Recovery Server
      4. Archiver
      5. Transaction Log Implementation
   2. Consistency Points
   3. Fast Commit Servers
   4. Undo and Redo Recovery
   5. Configuration Parameters
      1. Measuring Running System
      2. Calculating Optimal Settings
      3. Implementing Configuration Changes
         
         
6. Server Tuning (10-12 hours)
   1. Operating System Environment
   2. Multi-threaded Servers
   3. Native-thread Servers
      1. Utilizing Multi-processor Systems
      2. I/O Slave Implications
   4. Multi-server Configurations
   5. Custom Server Classes
   6. Buffer Manager Organization
   7. Variable Page Sizes
   8. Server Log Files
      1. Generating and Analyzing Summary Reports
      2. Troubleshooting and Diagnostics
   9. Trace Points and Measurement Tools
   10. Configuration Parameters
       1. Measuring Running System
       2. Calculating Optimal Settings
       3. Implementing Configuration Changes
          
          
7. I/O System Tuning (2-3 hours)
   1. Ingres Locations
      1. Space Requirements
      2. Throughput Requirements
   2. Measuring Relative Disk Load
      1. Queue Length
      2. Response Time
   3. I/O Load Balancing
      
      
8. Memory System Tuning (2-3 hours)
   1. Memory Management Concepts
      1. Virtual Memory
      2. Shared Memory
      3. Page Faulting
   2. Ingres Memory Usage
   3. Tuning Strategies