MariaDB Performance & Tuning

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: INTRODUCTION TO PERFORMANCE TUNING

• Tuning Overview
• Hardware optimization
• Increase RAM and use faster RAM
• Use more CPU cores
• Use a clustered database
• Optimize the operating system
• Use indexes to improve performance
• Optimize queries
• Optimize tables
• Assign suitable memory allocations
• Set values for system variables that affect performance
• Use benchmarking tool

Session 2: MARIADB PERFORMANCE TUNING TOOLS

• Tools that can assist with performance tuning
• The Information Schema
• Performance-related SHOW Commands to collect data for tuning
• Tools that can be used to benchmark databases
• Common benchmarks to use
• The BENCHMARK() function
• The mariadb-slap client load emulator
• Use session status variables to determine statement usage
• Use SHOW PROFILE to profile queries
• Obtaining performance information through the Performance Schema
• Obtaining performance information through the SYS Schema

Session 3: STATEMENT TUNING

• Overview of Statement Tuning
• Identifying and improve problem queries
• The Optimizer
• Understand the output from the Explain command
• Monitor queries using the Information Schema Processlist table
• Optimization strategies
• Optimizations for derived tables
• Filesort with small LIMIT optimization
• Limit rows examined
• Query limits and timeouts
• Abort statements that exceed a specific time to execute

Session 4: INDEXES

• An overview of MariaDB indexes
• Types of MariaDB indexes
• Make efficient usage of indexes
• Assess the size of an Index
• Resolve queries without accessing some tables referred to in the query
• Force query plans using index hints
• Find rows in a table using named indexes
• Ignore indexes using an index hint
• How indexes impact table joins
• InnoDB Cached Indexes Information

Session 5: SERVER CONFIGURATION AND MONITORING

• Set suitable values for server configuration variables
• Use server status variables to monitor performance
• Use table caching
• Store key distributions for a table with the ANALYZE TABLE command
• Reclaim unused space and defragment data with the OPTIMIZE TABLE command
• Use multi-threading
• Solve connection issues
• Query Cache

Session 6: THE INNODB ENGINE

• Transactions
• Monitoring InnoDB locks in MariaDB
• MariaDB disable deadlock detection
• Monitor the performance of the InnoDB engine
• Set and monitor caches and buffers
• Configuring data files for performance
• Configuring the log files for performance

Session 7: OVERVIEW OF CLUSTERING AND PERFORMANCE

• Advantages Performance Advantages of Clustering
• Performance Issues and Clustering
• The Galera Cluster
• The Percona XtraDB Cluster
• The FederatedX Engine

Session 8: DUMPING AND LOADING DATA

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

Session 9: PARTITIONING TABLES FOR PERFORMANCE

• The concept of partitioned tables
• How partitioning can improve performance
• Hash partitioning
• Key partitioning
• List partitioning
• Composite partitioning or subpartitioning
• Partition Pruning
• Adding, dropping and coalescing partitions
• Convert a non-partitioned table to a partitioned table