MariaDB Performance & Tuning

Who will the Course Benefit?

Anyone who needs to monitor and tune the performance of MariaDB databases.

Course Objectives

To provide the skills necessary to monitor and tune MariaDB database performance.

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

Requirements

Delegates must have a working knowledge of MariaDB Database Administration.

This course is run on a Linux operating system,a basic knowledge of Linux/UNIX is recommended but is not essential.

Further Learning

• Apache Web Server