MySQL

MySQL Performance Tuning

Learn about MySQL from Instructors with Real World Expertise.

A 4 day intermediate course teaching practical, safe and highly efficient ways to optimise the MySQL server.

MySQL Training Overview:

This MySQL Performance Tuning training will teach practical, safe and highly efficient ways to optimise performance for the MySQL Server. It will help you develop the skills needed to utilise tools for monitoring, evaluating and tuning.

Course Objectives:

  • Evaluate the architecture
  • Understand and use the tools
  • Configure the database for performance
  • Tune application and SQL code
  • Tune the server
  • Examine the storage engines
  • Assess the application architecture
  • Understand general tuning concepts

Target Audience:

  • Database Administrators
  • Developers
  • System Administrators

Prerequisites:

  • An ability to locate and execute commands on Linux
  • Experience with a MySQL Server instance and MySQL tools
  • Experience writing SQL retrieve, insert, modify and delete
  • MySQL for Database Administrators

Duration: 4 Days

Course Outline:

Introduction

Overview:

  • MySQL Overview
  • MySQL Products and Tools
  • MySQL Services and Support
  • MySQL Web Pages
  • MySQL Courses
  • MySQL Certification
  • MySQL Documentation

MySQL Server Tuning

Overview:
  • Major Components of the MySQL Server
  • MySQL Thread Handling
  • MySQL Memory Usage
  • Simultaneous Connections in MySQL
  • Reusing Threads
  • Effects of Thread Caching
  • Reusing Tables
  • Setting table_open_cache

MySQL Query Cache

Overview
  • MySQL Query Cache
  • When to Use the MySQL Query Cache
  • When NOT to Use the MySQL Query Cache
  • MySQL Query Cache Settings
  • MySQL Query Cache Status Variables
  • Improve Query Cache Results

InnoDB

Overview:

  • InnoDB Storage Engine
  • InnoDB Storage Engine Uses
  • Using the InnoDB Storage Engine
  • InnoDB Log Files and Buffers
  • Committing Transactions
  • InnoDB Table Design
  • SHOW ENGINE INNODB STATUS
  • InnoDB Monitors and Settings

MyISAM

Overview:
  • MyISAM Storage Engine Uses
  • MyISAM Table Design
  • Optimizing MyISAM
  • MyISAM Table Locks
  • MyISAM Settings
  • MyISAM Key Cache
  • MyISAM Full-Text Search

Other MySQL Storage Engines and Issues

Overview

  • Large Objects
  • MEMORY Storage Engine Uses
  • MEMORY Storage Engine Performance
  • Multiple Storage Engine Advantages
  • Single Storage Engine Advantages

Schema Design and Performance

Overview

  • Schema Design Considerations
  • Normalization and Performance
  • Schema Design
  • Data Types
  • Indexes
  • Partitioning

MySQL Query Performance

Overview

  • General SQL Tuning Best Practices
  • EXPLAIN
  • MySQL Optimizer
  • Finding Problematic Queries
  • Improve Query Executions
  • Locate and Correct Problematic Queries

Performance Tuning Extras

Overview

  • Configuring Hardware
  • Considering Operating Systems
  • Operating Systems Configurations
  • Logging
  • Backup and Recovery

Conclusion

Overview

  • Course Overview
  • Training and Certification Website
  • Course Evaluation
  • Q&A Session

Conclusion

Overview

  • Course Review
  • Training and Certification Website
  • Course Evaluation
  • QA Session

Subroutines and Modules

Overview

  • Subroutines
  • Subroutine return values
  • Subroutine return values: example
  • Passing subroutine arguments
  • Subroutine arguments
  • Getting at subroutine arguments
  • Lexical variables revisited
  • Find the bugs!
  • Modules
  • The library directories
  • Using subroutines from modules
  • Standard modules

Looking for something else? View all courses

Start Learning

Learn from Instructors with Real World Expertise.