Detailed information
| Duration: | 3 Days |
|---|---|
| Price: | £1,195 (VAT will be added to this price) |
| Type: | Training |
| Method: | In a classroom |
| Accreditation: | See course outline far exam details |
| Prepares for: | This course prepares delegates to use this product in their everyday work environment. |
| Geared towards: | MySQL Performance & Tuning Course Overview This MySQL Performance & Tuning course is designed for Database Administrators who wish to monitor and tune the performance of MySQL servers and databases. The course provides practical experience in monitoring and tuning MySQL servers and databases. |
| Requirements: | Requirements Delegates must have a working knowledge of MySQL Database Administration. Note This MySQL Performance & Tuning course does not cover clustering (other than at overview level), replication or non-standard storage engines such as Falcon and PBXT. Pre-Requisite Courses MySQL Database Administration |
| Internship: | none |
| Students per class: | 10 |
Do you need further information?
Contact the person in charge , free and at no obligation, for information on how to enroll, enrollment limit/availability and more.
Course program
Skills Gained
The delegates will practise:
Developing a monitoring and tuning plan
Using monitoring and diagnostic tools
Using the Information Schema and Show commands to collect data for tuning
Understanding normalization and de-normalization and their effect on performance
Using server configuration and status variables
Understanding the output from the Explain command
Identifying and improving problem queries
Making efficient use of indexes
Monitoring and sizing memory caches
Monitoring locks
Tuning the MyISAM storage engine
Tuning the InnoDB storage engine
Tuning other standard storage engines
Comparing and tuning dump and load performance
Evaluating the use of partitioning for performance
Who will the Course Benefit?
Anyone who wishes to monitor and tune MySQL performance.
MySQL Performance & Tuning Training Course
Course Contents - DAY 1
Course Introduction
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: INTRODUCTION TO PERFORMANCE TUNING
Tuning Overview
Resolving Performance Issues
Recommended Approach to Tuning
Items to Evaluate
Where to look
Planning a Monitoring Routine
Building a New Database for Performance
Tuning an Existing Database
Setting Suitable Goals
Session 2: MYSQL TOOLS
Administration Tools
The Information Schema
Performance-related SHOW Commands
Benchmarking Tools
The MySQL Performance Schema
Exercises: Obtaining Performance Information
Session 3: SCHEMA DESIGN
Normalisation
De-normalisation
Data Types
Character Sets
Choosing Storage Engines
Exercises: Effects of Design on Performance
Session 4: STATEMENT TUNING
Overview of Statement Tuning
Identifying Problem Queries
The Optimizer
Explain
Explain Extended
Exercises: Identifying Problem Queries and Using Explain
Session 5: INDEXES
Index Overview
Types of Index
Index Tuning
Indexes and Joins
Exercises: Indexes and Performance
--------------------------------------------------------------------------------
MySQL Performance & Tuning Training Course
Course Contents - DAY 2
Session 6: SERVER CONFIGURATION AND MONITORING
Server Configuration Variables
Server Status Variables
Table Cache
Multi-Threading
Query Cache
Exercises: Setting and Interpreting Server Variables and Caching
Session 7: LOCKING
Types of Locking
Locking and Storage Engines
Effects of Locking on Performance
Exercises: Locking and Performance
Session 8: THE MYISAM ENGINE
Advantages and Disadvantages of MyISAM
How MyISAM Caches Data
MyISAM Formats
MyISAM Indexes
MyISAM Locking
The Key Cache
Tuning MyISAM
Exercises: Monitoring and Tuning the MyISAM Engine
Session 9: THE MERGE ENGINE
The Merge Engine Concept
Advantages and Disadvantages of The Merge Engine
Locking with the Merge Engine
Performance Issues with the Merge Engine
Exercises: Merge Engine Performance Versus MyISAM
--------------------------------------------------------------------------------
MySQL Performance & Tuning Training Course
Course Contents - DAY 3
Session 10: THE INNODB ENGINE
Transactions
Crash Recovery
Locking
Monitoring InnoDB
Caches and Buffers
Configuring Data Files
Configuring the Log Files
Exercises: InnoDB Configuration and Performance
Session 11: OTHER STORAGE ENGINES
Archive Engine
Memory Engine
Federated Engine
Blackhole Engine
CSV Engine
Overview of Clustering and Performance
Mixing Storage Engines
Exercises: Storage Engine Performance
Session 12: Dumping and Loading Data
SQL statements versus delimited data
Parameters affecting dump performance
Parameters affecting load performance
Exercises: Dump and load performance
Session 13: PARTITIONED TABLES
Partitioned tables concepts
Range partitioning
Hash partitioning
Key partitioning
List partitioning
Composite partitioning or subpartitioning
Partition Pruning
Exercises: Partitioned Table Performance
The delegates will practise:
Developing a monitoring and tuning plan
Using monitoring and diagnostic tools
Using the Information Schema and Show commands to collect data for tuning
Understanding normalization and de-normalization and their effect on performance
Using server configuration and status variables
Understanding the output from the Explain command
Identifying and improving problem queries
Making efficient use of indexes
Monitoring and sizing memory caches
Monitoring locks
Tuning the MyISAM storage engine
Tuning the InnoDB storage engine
Tuning other standard storage engines
Comparing and tuning dump and load performance
Evaluating the use of partitioning for performance
Who will the Course Benefit?
Anyone who wishes to monitor and tune MySQL performance.
MySQL Performance & Tuning Training Course
Course Contents - DAY 1
Course Introduction
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: INTRODUCTION TO PERFORMANCE TUNING
Tuning Overview
Resolving Performance Issues
Recommended Approach to Tuning
Items to Evaluate
Where to look
Planning a Monitoring Routine
Building a New Database for Performance
Tuning an Existing Database
Setting Suitable Goals
Session 2: MYSQL TOOLS
Administration Tools
The Information Schema
Performance-related SHOW Commands
Benchmarking Tools
The MySQL Performance Schema
Exercises: Obtaining Performance Information
Session 3: SCHEMA DESIGN
Normalisation
De-normalisation
Data Types
Character Sets
Choosing Storage Engines
Exercises: Effects of Design on Performance
Session 4: STATEMENT TUNING
Overview of Statement Tuning
Identifying Problem Queries
The Optimizer
Explain
Explain Extended
Exercises: Identifying Problem Queries and Using Explain
Session 5: INDEXES
Index Overview
Types of Index
Index Tuning
Indexes and Joins
Exercises: Indexes and Performance
--------------------------------------------------------------------------------
MySQL Performance & Tuning Training Course
Course Contents - DAY 2
Session 6: SERVER CONFIGURATION AND MONITORING
Server Configuration Variables
Server Status Variables
Table Cache
Multi-Threading
Query Cache
Exercises: Setting and Interpreting Server Variables and Caching
Session 7: LOCKING
Types of Locking
Locking and Storage Engines
Effects of Locking on Performance
Exercises: Locking and Performance
Session 8: THE MYISAM ENGINE
Advantages and Disadvantages of MyISAM
How MyISAM Caches Data
MyISAM Formats
MyISAM Indexes
MyISAM Locking
The Key Cache
Tuning MyISAM
Exercises: Monitoring and Tuning the MyISAM Engine
Session 9: THE MERGE ENGINE
The Merge Engine Concept
Advantages and Disadvantages of The Merge Engine
Locking with the Merge Engine
Performance Issues with the Merge Engine
Exercises: Merge Engine Performance Versus MyISAM
--------------------------------------------------------------------------------
MySQL Performance & Tuning Training Course
Course Contents - DAY 3
Session 10: THE INNODB ENGINE
Transactions
Crash Recovery
Locking
Monitoring InnoDB
Caches and Buffers
Configuring Data Files
Configuring the Log Files
Exercises: InnoDB Configuration and Performance
Session 11: OTHER STORAGE ENGINES
Archive Engine
Memory Engine
Federated Engine
Blackhole Engine
CSV Engine
Overview of Clustering and Performance
Mixing Storage Engines
Exercises: Storage Engine Performance
Session 12: Dumping and Loading Data
SQL statements versus delimited data
Parameters affecting dump performance
Parameters affecting load performance
Exercises: Dump and load performance
Session 13: PARTITIONED TABLES
Partitioned tables concepts
Range partitioning
Hash partitioning
Key partitioning
List partitioning
Composite partitioning or subpartitioning
Partition Pruning
Exercises: Partitioned Table Performance
Do you need clarification regarding the course program?
Contact the person in charge , free and at no obligation, for information on how to enroll, enrollment limit/availability and more.
Course location
- c/o YSES, The Enterprise Suite, 9th Floor Centre City Tower, 5-7 Hill Street, Birmingham B5 4UA - B5 4UACity and Borough of Birmingham (England)
- c/o ComputerWorld, Apex House, Westerleigh Business Park, Turner Drive, Yate, Bristol, BS37 5YX - BS37 5YCity of Bristol (England)
- 16 St Mary's Street, Edinburgh, EH1 1SU - EH1 1SUCity of Edinburgh (Scotland)
- Halsbury House, Chancellor Court, 21 The Calls, Leeds, LS2 7EH - LS2 7EHCity and Borough of Leeds (England)
- c/o YSES, St James Court, 30 Brown Street, Manchester, M2 1DH - M2 1DHCity and Borough of Manchester (England)
StayAhead Training
StayAhead Training established 1992, centres throughout the UK...... with a team of full time dedicated professional training consultants......
StayAhead Training has been established since 1992 and is one of the UK's leading independent providers of Oracle, Unix, Linux, MySQL, Java, PHP Web Deveopment, ITIL & Business Analysis training.
Scheduled courses are available in 6 centres throughout the UK including London, Birmingham, Manchester, Bristol, Leeds and Edinburgh. We also provide training on our client's premises, this training can be tailored to the specific needs of the client.
Our courses have been designed to ensure that people can fulfil their job role and also help prepare delegates for certification in Oracle, Red Hat, Solaris, MySQL, Linux, SUSE Linux, Java, ITIL, Business Analysis and HP-UX .
Working in association with both the Oracle (OUG) and Hewlett Packard (HPUG) User Groups, StayAhead Training have a reputation of providing high quality value for money training at a cost effective price.
Our quality is assured by a team of dedicated professional training consultants who work full time for StayAhead Training and who blend their training skills with a wealth of hands-on experience.
StayAhead Training has been established since 1992 and is one of the UK's leading independent providers of Oracle, Unix, Linux, MySQL, Java, PHP Web Deveopment, ITIL & Business Analysis training.
Scheduled courses are available in 6 centres throughout the UK including London, Birmingham, Manchester, Bristol, Leeds and Edinburgh. We also provide training on our client's premises, this training can be tailored to the specific needs of the client.
Our courses have been designed to ensure that people can fulfil their job role and also help prepare delegates for certification in Oracle, Red Hat, Solaris, MySQL, Linux, SUSE Linux, Java, ITIL, Business Analysis and HP-UX .
Working in association with both the Oracle (OUG) and Hewlett Packard (HPUG) User Groups, StayAhead Training have a reputation of providing high quality value for money training at a cost effective price.
Our quality is assured by a team of dedicated professional training consultants who work full time for StayAhead Training and who blend their training skills with a wealth of hands-on experience.
