Course Code: 19012

MySQL Performance Tuning

Class Dates:
7/19/2021
Length:
4 Days
Cost:
$2995.00
Class Time:
Technology:
Developer
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • This course provides experienced MySQL DBAs with knowledge of how and why server settings affect performance, and when and how to tune them. It teaches students how to monitor their MySQL server and identify areas that impact performance, such as slow queries and replication lag, and how to diagnose and fix these issues.

    Learn To:

    Troubleshoot common performance problems.
    Benchmark your MySQL server.
    Tune MySQL server settings.
    Investigate performance metrics with a range of system databases, command-line utilities, and graphical tools.
    Design your databases and queries for optimal performance.
    Diagnose and fix replication lag.
  • Audience
  • Who Can Benefit
    Administrator
    Architect
    Database Administrator
    Developer
    Systems Administrator

Prerequisites

  • Suggested Prerequisite:
    Familiarity with the Performance Schema
    Experience of MySQL Enterprise Monitor
    Required Prerequisite:
    MySQL for Database Administrators Ed 4
    DBA experience
    Knowledge of Linux
    Familiarity with the mysql command-line client and tools

Course Details

  • Performance Tuning Concepts
  • Terminology
  • Benchmarking
  • Troubleshooting performance issues
  • Tuning steps
  • Deploying and maintaining MySQL
  • Performance Tuning Tools
  • MySQL Monitoring Tools
  • Oracle DB monitoring tools
  • Community monitoring tool
  • Linux tools
  • Benchmarking tools
  • Performance Schema
  • What is Performance Schema
  • Schema overview
  • Configuration
  • The Instance and Connection Tables
  • Querying event data
  • The sys schema
  • MySQL Workbench performance tools
  • General Server Tuning
  • How MySQL uses memory
  • Calculating maximum MySQL memory usage
  • Managing connections
  • Tuning threads
  • Tuning Tables, Files, and Logs
  • Sizing the table cache
  • Managing tables and files
  • Tuning the binary logs
  • Tuning InnoDB
  • What is InnoDB
  • How does InnoDB work
  • Tuning InnoDB buffer pool and log files
  • Measuring InnoDB performance
  • Optimizing Your Schema
  • Schema design considerations
  • Indexes
  • InnoDB table compression
  • Partitioning tables
  • Monitoring Queries
  • Identifying candidate queries for optimization
  • Server logs
  • Statement status variables
  • Performance Schema and sys
  • Graphical tools
  • Query Optimization
  • How the optimizer works
  • Understanding the query plan
  • Optimizing queries
  • Optimizing Locking Operations
  • How MySQL locking works
  • InnoDB lock types
  • Metadata locks
  • Viewing lock information with SQL statements
  • Investigating locks by querying system databases
  • Tuning Replication
  • Replication overview
  • Understanding replication lag
  • Diagnosing replication lag
  • Resolving replication lag
  • Conclusion
  • Course Goals Recap
  • MySQL Training at Oracle University
  • MySQL Websites
  • Your Evaluation
  • QA Session
  • Conclusion
  • Course Overview
  • Training and Certification Website
  • Course Evaluation
  • Thank You!
  • QA Session