MySQL Performance Tuning

Versión para impresoraVersión para impresora

Código: D61820GC10
Total Horas: 24 horas (4 días)

Fechas previstas:
Curso  bajo demanda

Descripción del Curso

The MySQL[tm] 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.

 

Objetivo

  • 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

Contenido

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