MariaDB 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
• • 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

MariaDB Performance & Tuning Training Course

Course Contents - DAY 2

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