PERFORMANCE OPTIMIZATION FOUNDATIONS
1. Advanced
Configuration for Performance
- Tuning memory, cache, buffers
- Understanding innodb_buffer_pool_size,
query_cache, tmp_table_size
- Connection and thread tuning
2. InnoDB
Deep Dive
- InnoDB architecture and internals
- File structures, transactions, and
I/O performance
- Tuning InnoDB-specific parameters
3. Indexing
Strategies
- Choosing the right indexes
- Covering indexes and composite
indexes
- When to use full-text and spatial
indexes
4. Query
Optimization and Execution Plans
- Using EXPLAIN, ANALYZE, and SHOW
PROFILE
- Detecting inefficient queries
- Query rewriting and subquery
optimization
5.
Performance Schema and Monitoring Tools
- Enabling and using Performance
Schema
- Monitoring with sys schema and
custom scripts
- Third-party tools (Percona Toolkit,
PMM, etc.)
REPLICATION AND SCALING TECHNIQUES
1. MariaDB
Replication Deep Dive
- Master-Slave vs Master-Master
Replication
- Semi-sync and async replication
- GTID-based replication setup and
usage
2.
Replication Management and Monitoring
- Replication lag detection and
correction
- Handling failovers and auto-rejoin
- Backup and recovery in replicated
environments
3. Proxy and
Load Balancing Options
- Using MaxScale and HAProxy
- Read/write splitting and connection
routing
- Load balancing best practices
4. Backup
Strategies for HA
- Physical and logical backups
- Non-blocking backup with mariabackup
- Incremental and differential backup
strategies
5.
Performance Testing and Load Simulation
- Tools like mysqlslap, sysbench
- Creating realistic test scenarios
- Benchmarking for capacity planning
GALERA CLUSTER AND HIGH AVAILABILITY SETUP
1. Galera
Cluster Overview
- Galera architecture and synchronous
replication
- Use cases and limitations
- Comparison with traditional
replication
2.
Installing and Configuring Galera
- Node configuration and
prerequisites
- Setting up 3-node (or more)
clusters
- SST (State Snapshot Transfer) vs
IST (Incremental State Transfer)
3. Cluster
Management and Monitoring
- Starting, adding, and removing
nodes
- Troubleshooting cluster split and
quorum
- Monitoring with clustercheck,
scripts, and dashboards
4. Tuning
Galera for Performance
- Write-set optimizations
- Flow control settings and buffer
tuning
- Handling large transactions and
hotspots
5. Security
and Disaster Recovery in HA Setups
- SSL for replication and Galera
nodes
- Backup strategies in clustered
environments
- Disaster recovery planning and DR
testing
6. Final
Review and Hands-On
- Scenario-based clustering or
replication setup
- Troubleshooting and performance
review
- Best practices summary and future
learning paths.
