This course is suitable for anyone who wishes to monitor and tune MySQL performance.

• Developing a monitoring and tuning plan
• Using monitoring and diagnostic tools
• Using the Information Schema and Show commands to collect data for tuning
• Understanding normalization and de-normalization and their effect on performance
• Using server configuration and status variables
• Understanding the output from the Explain command
• Identifying and improving problem queries
• Making efficient use of indexes
• Monitoring and sizing memory caches
• Monitoring locks
• Tuning the MyISAM storage engine
• Tuning the InnoDB storage engine
• Tuning other standard storage engines
• Comparing and tuning dump and load performance
• Evaluating the use of partitioning for performance

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

Session 3: SCHEMA DESIGN

• Normalisation
• De-normalisation
• Data Types
• Character Sets
• Choosing Storage Engines

Session 4: STATEMENT TUNING

• Overview of Statement Tuning
• Identifying Problem Queries
• The Optimizer
• Explain
• Explain Extended

Session 5: INDEXES

• Index Overview
• Types of Index
• Index Tuning
• Indexes and Joins

Session 6: SERVER CONFIGURATION AND MONITORING

• Server Configuration Variables
• Server Status Variables
• Table Cache
• Multi-Threading
• Query Cache

Session 7: LOCKING

• Types of Locking
• Locking and Storage Engines
• Effects of Locking on 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

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

Session 10: THE INNODB ENGINE

• Transactions
• Crash Recovery
• Locking
• Monitoring InnoDB
• Caches and Buffers
• Configuring Data Files
• Configuring the Log Files

Session 11: OTHER STORAGE ENGINES

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

Session 12: Dumping and Loading Data

• SQL statements versus delimited data
• Parameters affecting dump performance
• Parameters affecting load performance

Session 13: PARTITIONED TABLES

• Partitioned tables concepts
• Range partitioning
• Hash partitioning
• Key partitioning
• List partitioning
• Composite partitioning or subpartitioning
• Partition Pruning

• MYSD, MySQL for Developers
• PERL, Perl Programming
• APH, Apache Web Server
• PHPMWD, PHP Programming & MySQL for Web Development