Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: INTRODUCTION TO PERFORMANCE TUNING

• Tuning Overview
• Resolving Performance Issues
• Recommended Approach to Tuning
• Items to Evaluate
• Where to look
• Planning a Monitoring Routine
• Building a New Database for Performance
• Tuning an Existing Database
• Setting Suitable Goals

Session 2: MYSQL TOOLS

• Administration Tools
• The Information Schema
• Performance-related SHOW Commands
• Benchmarking Tools
• The MySQL Performance Schema
• Exercises: Obtaining Performance Information

Session 3: SCHEMA DESIGN

• Normalisation
• De-normalisation
• Data Types
• Character Sets
• Choosing Storage Engines
• Exercises: Effects of Design on Performance

Session 4: STATEMENT TUNING

• Overview of Statement Tuning
• Identifying Problem Queries
• The Optimizer
• Explain
• Explain Extended
• Exercises: Identifying Problem Queries and Using Explain

Session 5: INDEXES

• Index Overview
• Types of Index
• Index Tuning
• Indexes and Joins
• Exercises: Indexes and Performance

Session 6: SERVER CONFIGURATION AND MONITORING

• Server Configuration Variables
• Server Status Variables
• Table Cache
• Multi-Threading
• Query Cache
• Exercises: Setting and Interpreting Server Variables and Caching

Session 7: LOCKING

• Types of Locking
• Locking and Storage Engines
• Effects of Locking on Performance
• Exercises: Locking and Performance

Session 8: THE MYISAM ENGINE

• Advantages and Disadvantages of MyISAM
• How MyISAM Caches Data
• MyISAM Formats
• MyISAM Indexes
• MyISAM Locking
• The Key Cache
• Tuning MyISAM
• Exercises: Monitoring and Tuning the MyISAM Engine

Session 9: THE MERGE ENGINE

• The Merge Engine Concept
• Advantages and Disadvantages of The Merge Engine
• Locking with the Merge Engine
• Performance Issues with the Merge Engine
• Exercises: Merge Engine Performance Versus MyISAM

Session 10: THE INNODB ENGINE

• Transactions
• Crash Recovery
• Locking
• Monitoring InnoDB
• Caches and Buffers
• Configuring Data Files
• Configuring the Log Files
• Exercises: InnoDB Configuration and Performance

Session 11: OTHER STORAGE ENGINES

• Archive Engine
• Memory Engine
• Federated Engine
• Blackhole Engine
• CSV Engine
• Overview of Clustering and Performance
• Mixing Storage Engines
• Exercises: Storage Engine Performance

Session 12: Dumping and Loading Data

• SQL statements versus delimited data
• Parameters affecting dump performance
• Parameters affecting load performance
• Exercises: Dump and load performance

Session 13: PARTITIONED TABLES

• Partitioned tables concepts
• Range partitioning
• Hash partitioning
• Key partitioning
• List partitioning
• Composite partitioning or subpartitioning
• Partition Pruning
• Exercises: Partitioned Table Performance