MariaDB Database Administration

Modules

Hide all

Introduction to Performance Tuning (10 topics)

• Tuning Overview
• Tuning Levels
• 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 Performance Tuning Tools (7 topics)

• Administration Tools
• The Information Schema
• Performance-related SHOW Commands
• Benchmarking Tools
• The MariaDB Performance Schema
• MonYog
• Exercises: Obtaining Performance Information

Schema Design (9 topics)

• Normalisation
• De-normalisation
• Naming Conventions
• Load Generation, Stress Testing and Benchmarking Tools
• Selecting Data Types
• Data Types
• Character Sets
• Choosing Storage Engines
• Exercises: Effects of Design on Performance

Statement Tuning (6 topics)

• Overview of Statement Tuning
• Identifying Problem Queries
• The Optimizer
• Explain
• Explain Extended
• Exercises: Identifying Problem Queries and Using Explain

Indexes (6 topics)

• Index Overview
• Index Size
• Types of Index
• Index Tuning
• Indexes and Joins
• Exercises: Indexes and Performance

Server Configuration and Monitoring (7 topics)

• Server Configuration Variables
• Server Status Variables
• Table Cache
• Multi-Threading
• Connection Issues
• Query Cache
• Exercises: Setting and Interpreting Server Variables and Caching

Locking (4 topics)

• Types of Locking
• Locking and Storage Engines
• Effects of Locking on Performance
• Exercises: Locking and Performance

The InnoDB Engine (8 topics)

• Transactions
• Crash Recovery
• Locking
• Monitoring InnoDB
• Caches and Buffers
• Configuring Data Files
• Configuring the Log Files
• Exercises: InnoDB Configuration and Performance

Other Storage Engines (11 topics)

• MyISAM Engine
• Merge Engine
• Archive Engine
• Memory Engine
• Blackhole Engine
• CSV Engine
• The Spider Engine
• The ColumnStore Engine
• The MyRocks Engine
• Mixing Storage Engines
• Exercises: Storage Engine Performance

Overview of Clustering and Performance (10 topics)

• Advantages Performance Advantages of Clustering
• Performance Issues and Clustering
• The NDBCluster Engine
• The Galera Cluster
• The Percona XtraDB Cluster
• MySQL InnoDB Cluster
• The Federated Engine
• The FederatedX Engine
• Overview of Other High Availability Techniques
• NOSQL and Mencached Overview

Dumping and Loading Data (4 topics)

• SQL statements versus delimited data
• Parameters affecting dump performance
• Parameters affecting load performance
• Exercises: Dump and load performance

Partitioned Tables (8 topics)

• Partitioned tables concepts
• Range partitioning
• Hash partitioning
• Key partitioning
• List partitioning
• Composite partitioning or subpartitioning
• Partition Pruning
• Exercises: Partitioned Table Performance