MariaDB Performance and Tuning — A 3 day course
Synopsis
This MariaDB Performance & Tuning course is designed for Database Administrators who wish to monitor and tune the performance of MariaDB servers and databases. The course provides practical experience in monitoring and tuning MariaDB servers and databases.
Note
This MariaDB Performance & Tuning course does not cover clustering (other than at overview level), replication or non-standard storage engines such as PBXT.
Objectives
To provide the skills necessary to monitor and tune MariaDB 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
Contents
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
MariaDB Tools
- Administration Tools
- The Information Schema
- Performance-related SHOW Commands
- Benchmarking Tools
- The MariaDB Performance Schema
- Exercises: Obtaining Performance Information
Schema Design
- Normalisation
- De-normalisation
- Data Types
- Character Sets
- Choosing Storage Engines
- Exercises: Effects of Design on Performance
Statement Tuning
- Overview of Statement Tuning
- Identifying Problem Queries
- The Optimizer
- Explain
- Explain Extended
- Exercises: Identifying Problem Queries and Using Explain
Indexes
- Index Overview
- Types of Index
- Index Tuning
- Indexes and Joins
- Exercises: Indexes and Performance
Server Configuration & Monitoring
- Server Configuration Variables
- Server Status Variables
- Table Cache
- Multi-Threading
- Query Cache
- Exercises: Setting and Interpreting Server Variables and Caching
Locking
- Types of Locking
- Locking and Storage Engines
- Effects of Locking on Performance
- Exercises: Locking and Performance
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
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
The INNODB Engine
- Transactions
- Crash Recovery
- Locking
- Monitoring InnoDB
- Caches and Buffers
- Configuring Data Files
- Configuring the Log Files
- Exercises: InnoDB Configuration and Performance
Other Storage Engines
- Archive Engine
- Memory Engine
- Federated Engine
- Blackhole Engine
- CSV Engine
- Aria Engine
- Overview of Clustering and Performance
- Mixing Storage Engines
- Exercises: Storage Engine Performance
Dumping & Loading Data
- SQL statements versus delimited data
- Parameters affecting dump performance
- Parameters affecting load performance
- Exercises: Dump and load performance
Partitioned Tables
- Partitioned tables concepts
- Range partitioning
- Hash partitioning
- Key partitioning
- List partitioning
- Composite partitioning or subpartitioning
- Partition Pruning
- Exercises: Partitioned Table Performance
Publicly scheduled dates, locations, and prices
Select a date to enquire about availability of places. For dates after 26 October 2022, please call 0333 210 0140 or use our contact form to enquire about places and availability.
London — £1395 (+VAT)
Suitable For
Anyone who wishes to monitor and tune MariaDB performance.
Follow-On Courses
Prerequisites
Delegates must have a working knowledge of .MariaDB Database Administration or .MySQL Database Administration.