MySQL Performance & Tuning

Who will the Course Benefit?

Anyone who wishes to monitor and tune MySQL performance for MySQL 5.6,5.7 and 8 databases.

Course Objectives

To provide the skills necessary to monitor and tune MySQL performance.

MySQL Performance & Tuning Training Course

Course Contents - DAY 1

Course Introduction

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

Session 1: INTRODUCTION TO PERFORMANCE TUNING

• Tuning Overview
• Tuning Levels
• 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 PERFORMANCE TUNING TOOLS

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

Session 3: SCHEMA DESIGN

• Normalisation
• De-normalisation
• Naming Conventions
• Load Generation,Stress Testing and Benchmarking Tools
• Selecting Data Types
• 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
• Exercises: Identifying Problem Queries and Using Explain

Session 5: INDEXES

• Index Overview
• Index Size
• Types of Index
• Index Tuning
• Indexes and Joins
• Descending Indexes
• Invisible Indexes
• InnoDB Cached Indexes Information
• Exercises: Indexes and Performance

MySQL Performance & Tuning Training Course

Course Contents - DAY 2

Session 6: SERVER CONFIGURATION AND MONITORING

• Server Configuration Variables
• Server Status Variables
• Table Cache
• Multi-Threading
• Connection Issues
• 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
• Monitoring InnoDB Locks in MySQL 5,7 and 8
• MySQL 8 Disable Deadlock Detection
• Exercises: Locking and Performance

Session 8: THE INNODB ENGINE

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

MySQL Performance & Tuning Training Course

Course Contents - DAY 3

Session 9: OTHER STORAGE ENGINES

• MyISAM Engine
• Merge Engine
• Archive Engine
• Memory Engine
• Blackhole Engine
• CSV Engine
• The Spider Engine
• The ColumnStore Engine
• The MyRocks Engine
• Mixing Storage Engines
• Exercises: Storage Engine Performance

Session 10: OVERVIEW OF CLUSTERING AND PERFORMANCE

• Advantages Performance Advantages of Clustering
• Performance Issues and Clustering
• The NDBCluster Engine
• The Galera Cluster
• The Percona XtraDB Cluster
• MySQL InnoDB Cluster
• The Federated Engine
• The FederatedX Engine
• Overview of Other High Availability Techniques
• NOSQL and Mencached Overview

Session 11: DUMPING AND LOADING DATA

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

Session 12: PARTITIONED TABLES

• Partitioned tables concepts
• Range partitioning
• Hash partitioning
• Key partitioning
• List partitioning
• Composite partitioning or subpartitioning
• Partition Pruning
• MySQL 8 Partitioning new features
• Exercises: Partitioned Table Performance

Requirements

Delegates must have a working knowledge of MySQL Database Administration.

This course is run on a Linux operating system,a basic knowledge of Linux/UNIX is recommended but is not essential.

Further Learning

• Perl Programming
• Apache Web Server
• PHP & MySQL for Web Development