Choose your language:

France
Germany
Hong Kong
India
Ireland
Japan
Malaysia
Netherlands
New Zealand
Singapore
Sweden
United Kingdom
United States

MySQL Performance and Tuning

Course Code

RT646

Duration

4 Days

Experience with a MySQL server
Can use MySQL tools
Have knowledge of general SQL statement structure and basic SQL tuning principles
Working knowledge of Linux/Unix operating systems
This course provides practical, safe, highly efficient ways to optimize performance for the MySQL Server. This course will prepare each participant with the skills needed to utilize tools for monitoring, evaluating and tuning. Participants will evaluate the architecture, learn to use the tools, configure the database for performance, tune application and SQL code, tune the server, examine the storage engines, assess the application architecture, and learn general tuning concepts.
This course is designed for individuals who are Database Administrators, Developers or System Administrators

Upon completion of this course, participants will be able to:

  • Explain the basics of performance tuning
  • Use performance tuning tools
  • Tune the MySQL Server instance to improve performance
  • Improve performance of tables based on the storage engine being used
  • Implement proper Schema Design to improve performance
  • Improve the performance of MySQL Queries
  • Describe additional items related to performance tuning
Introduction
MySQL Overview
MySQL Products and Tools
MySQL Services and Support
MySQL Web Pages
MySQL Documentation

Performance Tuning Basics
Performance
Areas to Tune
Performance Tuning Terminology
Benchmark Planning
Tuning Steps
General Tuning Session
Deploying MySQL and Benchmarking

Performance Tuning Tools
MySQL Monitoring Tools
Open Source Community Monitoring Tools
Benchmark Tools
Stress Tools

MySQL Server Tuning
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
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
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
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
Large Objects
MEMORY Storage Engine Uses
MEMORY Storage Engine Performance
Multiple Storage Engine Advantages
Single Storage Engine Advantages

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

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

Performance Tuning Other Areas
Configuring Hardware
Considering Operating Systems
Operating Systems Configurations
Logging
Backup and Recovery
Send Us a Message
Choose one