MySQL Performance Tuning (SQL-4402)

Cel

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

Wymagania

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 (SQL-4502)

 

Trener

Certified Trainer

W ramach uczestnictwa zapewniamy Państwu

• organizacje szkolenia oraz zapewnienie trenera
• testy sprawdzające poziom wiedzy i umiejętności poziomu umiejętności – pozwalające na uczestnictwo na szkoleniu osób o zbliżonym poziomie umiejętności (na życzenie Klienta)
• materiały dydaktyczne w tym skrypt odpowiedni do określonego poziomu
• samodzielne stanowisko komputerowe
• catering podczas przerw (kawa, herbata, ciastka)
• lunch
• certyfikat ukończenia szkolenia
• bezpłatne konsultacje z Wykładowcą 2 tygodnie od ukończenia szkolenia
• testy sprawdzające poziom wiedzy i umiejętności poziomu umiejętności po ukończeniu szkolenia (na życzenie klienta)

Program

1. 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
2. MySQL Architecture
   • MySQL Architecture Overview (High Level)
   • Client/Server Architecture
   • Storage Engine Concept
   • Data Directory
   • Data Storage
   • Locks
3. MySQL Performance Tools
   • Overview
   • Benchmarking Tools
   • Administration Tools
   • Specific SHOW commands
4. Schema Design
   • Data Modeling
   • Normalization
   • Denormalization
   • Data Types
   • Partitioning
5. Indexing
   • Indexes
   • MySQL Index Types
   • Index Optimizations
6. Statement Tuning
   • General SQL Tuning
   • EXPLAIN
   • MySQL Optimizer
   • Finding Problematic Queries
   • Caching and MySQL
   • Choose an Architecture
7. Caching
   • Caching Benefits
   • Non-MySQL Caches
   • Query Cache
   • MEMORY/MyISAM Tables as Cache
8. MySQL Server Configuration
   • Server Connection Parameters
   • Table Cache and Multi-threading Issues
   • Individual Thread Parameters
   • General Monitoring
   • Table Handlers
9. MyISAM
   • Where to use
   • Row Storage Format
   • Indexing
   • Locking
   • Key Cache
   • MyISAM Tuning
   • Other optimization tools for MyISAM
   • MERGE Storage Engine
10. InnoDB
    • Transactions
    • Internal Buffers
    • Indexes
    • Crash Recovery
    • Best Practices
    • Tuning
    • Comparing InnoDB and MyISAM
11. Alternate Storage Engines
    • MEMORY, FEDERATED, ARCHIVE, CSV, BLACKHOLE, Falcon
    • Performance Issues with Mutliple Storage Engines
    • Creating a Custom Storage Engine
    • MySQL Cluster
12. Conclusion
    • Course Overview
    • Training and Certification Website
    • Course Evaluation
    • Thank You!
    • QA Session