MySQL Performance Tuning (SQL-4402)

Description:

The MySQL[tm] 5.0 Performance Tuning course is designed for Database Administrators and others who wish to monitor and tune MySQL. This course will prepare each student with the skills needed to utilize tools for monitoring, evaluating and tuning. Students 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.

Students who can benefit from this course:

• Database Administrators who currently maintain MySQL server(s)
Database developers and programmers who currently design and/or interact with MySQL database(s)
• System Administrators whose systems currently interact with MySQL server(s) or who also work with MySQL Administrators

Objectives:

• Develop a tuning strategy
• Display knowledge of MySQL architecture
• Display knowledge and ability to use diagnostic tools
• Display knowledge and ability to use tuning tools
• Write queries against the INFORMATION_SCHEMA database and be able to decipher the metadata obtained
• Understand the relational database model and both the positive and negative implications on performance
• Display, decipher and edit server configuration variables to improve performance of your applications
• Display and analyze status variables to ensure that their applications are utilizing the settings in the most effective manner
• Write queries that take advantage of the MySQL 5.0 performance enhancements dealing with queries and indexing
• Decide which of the storage engines could be used in their specific application needs
• Evaluate the application architecture for efficient design, structure, caching, number of connections and other factors affecting performance
• Evaluate hardware and OS for effects on performance
• Evaluate techniques for loading data into the database and effects on performance

Prerequisites:

Required Prerequisites:
Students who wish to attend this course, should have attended the MySQL 5.0 for DBAs course or have similar knowledge:
Have previously worked with a MySQL Server instance and database and have working knowledge of MySQL architecture
Can use MySQL tools to connect to the MySQL Server
Have knowledge of general SQL statement structure and basic SQL tuning principles
MySQL for Database Administrators

Suggested Prerequisites:
Can define table structures, Index structures, constraints a

Topics:

Introduction
MySQL Overview, Products, Services and Enterprise Services
Supported Operating Services
MySQL Certification Program
Training Curriculum Paths
MySQL Website
Installing MySQL
Installing the world Database

MySQL Architecture
MySQL Architecture Overview (High Level)
Client/Server Architecture
Storage Engine Concept
Data Directory
Data Storage
Locks

MySQL Performance Tools
Overview
Benchmarking Tools
Administration Tools
Specific SHOW commands

Schema Design
Data Modeling
Normalization
Denormalization
Data Types
Partitioning

Indexing
Indexes
MySQL Index Types
Index Optimizations

Statement Tuning
General SQL Tuning
EXPLAIN
MySQL Optimizer
Finding Problematic Queries
Caching and MySQL
Choose an Architecture

Caching
Caching Benefits
Non-MySQL Caches
Query Cache
MEMORY/MyISAM Tables as Cache

MySQL Server Configuration
Server Connection Parameters
Table Cache and Multi-threading Issues
Individual Thread Parameters
General Monitoring
Table Handlers

MyISAM
Where to use
Row Storage Format
Indexing
Locking
Key Cache
MyISAM Tuning
Other optimization tools for MyISAM
MERGE Storage Engine

InnoDB
Transactions
Internal Buffers
Indexes
Crash Recovery
Best Practices
Tuning
Comparing InnoDB and MyISAM

Alternate Storage Engines
MEMORY, FEDERATED, ARCHIVE, CSV, BLACKHOLE, Falcon
Performance Issues with Mutliple Storage Engines
Creating a Custom Storage Engine
MySQL Cluster

Conclusion
Course Overview
Training and Certification Website
Course Evaluation
Thank You!
QA Session