MySQL 8.0: Performance Tuning

COURSE TOPICS

Module 1 : Introduction

• Introduction
• Deomonstration: Finding information about MySQL courses and certification options
• Practice 1-0: Practice Overview
• Practice 1-1: Course Environment Overvie

 Module 2 : Performance Tuning Concepts

• Performance Tuning Concepts
• Troubleshooting Performance Issues
• Practice 2-1: Quiz – Performance Tuning

 Module 3 : Performance Tuning Tools

• Performance Tuning Tools
• Community Monitoring Tools
• Demonstration
• Practice 3-1: Using MySQL Command-Line Monitoring Tools
• Practice 3-2: Using MySQL Enterprise Monitor
• Practice 3-3: Using Benchmark Tools
• Practice 3-4: Using Linux System Monitoring Tools

 Module 4 : Performance Schema

• Performance Schema
• Configuration
• Querying Event Data
• Practice 4-1: Examining the Performance Schema Configuration
• Practice 4-2: Querying the Performance Schema
• Practice 4-3: Using sys to Work with the Performance Schema
• Practice 4-4: Using MySQL Workbench for Performance Schema Configuration, Monitoring, and Reporting

 Module 5 : General Server Tuning

• General Server Tuning
• Displaying MySQL Memory Usage
• Practice 5-1: Managing the Number of Client Connections
• Practice 5-2: Investigating the Effects of Multiple Simultaneous Connections
• Practice 5-3: Investigating the Effects of Thread Caching

Module 6: Tuning Tables, Files, and Logs

• Tuning Tables, Files, and Logs
• Practice 6-1: Sizing the Table Cache
• Practice 6-2: Sizing the Binary Log Cache
• Practice 6-3: Identifying l/O Hotspots with MySQL Workbench

Module 7: Tuning InnoDB

• Tuning InnoDB
• Measuring InnoDB Performance
• Practice 7-1: Investigating the Effects of Log Files on Transactions
• Practice 7-2: Using SHOW ENGINE INNODB STATUS
• Practice 7-3: Monitoring InnoDB Metrics in the Information Schema
• Practice 7-4: Evaluating InnoDB Buffer Pool Size

Module 8: Optimizing Your Schema

• Optimizing Your Schema
• InnoDB Table compression
• Practice 8-1: Comparing the Effects of Table Normalization on Query Performance
• Practice 8-2: Choosing the Correct Data Type
• Practice 8-3: Compressing Tables
• Practice 8-4: Partitioning

Module 9: Monitoring Queries

• Monitoring Queries
• Statement Status Variables
• Practice 9-1: Monitoring Statements
• Practice 9-2: Using the Slow Query Log
• Practice 9-3: Identifying Slow Queries with MySQL Enterprise Monitor Query Analyzer
• Practice 9-4: Identifying Slow Queries with sys Views
• Practice 9-5: Using MySQL Workbench Query Statistics

Module 10: Query Optimization

• Query Optimization
• Optimizer Trace
• Practice 10-1: Understanding the Query Execution Plan with the EXPLAIN Statement
• Practice 10-2: Improving the Performance of a Query
• Practice 10-3: Tracing the Optimizer

Module 11: Optimizing Locking Operations

• Optimizing Locking Operations
• Practice 11-1: Troubleshooting Blocked Queries
• Practice 11-2: Investigating Metadata Locks

Module 12: Tuning Replication

• Tuning Replication
• Practice 12-1: Diagnosing Replication Lag by Using Binary Log File Name and Position
• Practice 12-2: Diagnosing Replication Lag by Using GTID Sets

 Module 13: Conclusion

• Conclusion