COURSE OBJECTIVE:
Course Objectives To provide the skills necessary to monitor and tune MariaDB database performance.
TARGET AUDIENCE:
Who will the Course Benefit? Anyone who needs to monitor and tune the performance of MariaDB databases.
COURSE PREREQUISITES:
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.
COURSE CONTENT:
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
FOLLOW ON COURSES:
Further Learning Apache Web Server