Oracle MySQL Performance Tuning 5.6 Training

Introduction

• Course Introduction
• MySQL Overview
• MySQL Products and Tools
• MySQL Web Resources
• MySQL Courses and Certification
• MySQL Services and Support

Performance Tuning Concepts

• Introduction to Performance Tuning
• Performance Tuning Terminology
• Benchmarking
• Tuning
• Deploying and Maintaining MySQL

Performance Tuning Tools

• MySQL Monitoring Tools
• MySQL Enterprise Monitor
• MySQL Utilities
• Community Monitoring Tools
• Linux Tools
• Benchmarking Tools

Performance Schema

• Performance Schema
• Configuring Performance Schema
• Using MySQL Workbench for Performance Monitoring
• The MySQL sys Schema

Memory, Connections, and Threads

• Major Components of the MySQL Server
• Tuning the MySQL server
• Connections
• Thread Reuse

Tables, Files, and Logs

• Table Caching
• Files and File Descriptors
• Binary Logs

Statement Monitoring, Sort Buffer and Query Cache

• SQL Statement Monitoring
• Sizing the Sort Buffer
• The MySQL Query Cache

InnoDB Storage Engine

• List the key benefits of the InnoDB storage engine
• Describe how InnoDB uses log files and buffers
• Explain the SHOW ENGINE INNODB STATUS output
• Use InnoDB monitors
• Access key InnoDB metrics in Information Schema
• Tune InnoDB settings for best performance

Schema Design and Performance

• Schema Design Considerations
• Normalization and Performance
• Data Types
• Indexes
• InnoDB Table Compression
• Partitioning

Query Optimization

• MySQL Query Processing
• Understanding the Query Plan
• Using EXPLAIN
• Improving Query Performance
• Indexing
• MySQL Enterprise Monitor Query Analyzer

Troubleshooting Performance Issues

• Key Steps in Troubleshooting
• Establishing the Nature of the Problem
• Troubleshooting Locks
• Troubleshooting Locks with Information Schema
• Identifying Slow Queries
• Troubleshooting Specific Queries

Optimizing MySQL for Your Application Environment

• Improving Connector Performance
• Improving InnoDB Performance with the Memcached NoSQL API
• Backup Performance
• Improving Database Performance with Replication
• Improving Application Performance with MySQL Cluster

Hardware Optimization

• Hardware Limitations in MySQL
• Storage Devices
• MySQL and Virtualization
• Database-Driven Website Reference Architectures