Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

In MySQL 8.x and MariaDB — the workhorses behind countless web, e-commerce and SaaS platforms — performance and reliability depend directly on how well the team understands the InnoDB storage engine. Poor primary-key choices, undersized buffer pools or unmonitored replication lag quickly surface as outages under peak traffic.

This course covers architectural differences between MySQL and MariaDB, InnoDB internals (Clustered Index, Secondary Indexes, Buffer Pool, Redo/Undo Logs), query and index design, bottleneck diagnosis with EXPLAIN and Performance Schema, isolation levels and gap/next-key locking, hot physical backups, and GTID-based replication and high availability (InnoDB Cluster / Galera).

Who Should Attend?

  • Backend developers building high-traffic applications on MySQL or MariaDB
  • DBAs and DevOps engineers responsible for MySQL/MariaDB tuning, backups and replication
  • Teams operating managed cloud instances (Amazon RDS/Aurora, Azure, Cloud SQL) or on-prem clusters
  • Prerequisite: foundational SQL and relational database knowledge

Key Takeaways

  • Design primary keys and composite secondary indexes that work with InnoDB's Clustered Index layout
  • Diagnose and fix slow queries using the Slow Query Log, EXPLAIN FORMAT=TREE/JSON and Performance Schema
  • Resolve deadlocks by understanding Next-Key and Gap Locks under Repeatable Read isolation
  • Tune innodb_buffer_pool_size, redo log capacity and flushing behaviour for production workloads
  • Run hot backups with Percona XtraBackup / Mariabackup and configure GTID replication

Curriculum

MySQL 8.x / MariaDB Architecture and InnoDB Internals
  • Connection handling, the SQL optimizer and pluggable storage engines (InnoDB focus)
  • InnoDB's Clustered Index layout: why random UUID primary keys fragment pages and hurt writes
  • How the InnoDB Buffer Pool, Change Buffer, Doublewrite Buffer, Redo Log and Undo Log work together
Schema Design, Indexing and Query Optimisation
  • Data types, character sets (utf8mb4 and collations), JSON and indexed Generated Columns
  • The leftmost-prefix rule, Covering Indexes, Index Condition Pushdown (ICP) and Invisible Indexes
  • Reading EXPLAIN / EXPLAIN ANALYZE plans and fixing Using filesort and temporary table bottlenecks
Transactions, Locking Behaviour and Concurrency
  • Repeatable Read vs Read Committed in InnoDB and how MVCC Read Views work
  • Record Locks, Gap Locks, Next-Key Locks and Insert Intention Locks explained
  • Analysing deadlocks and lock waits via SHOW ENGINE INNODB STATUS and performance_schema.data_locks
Server Tuning, Security and Performance Schema Monitoring
  • Essential my.cnf parameters: innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, sync_binlog, max_connections
  • Account security, Slow Query Log analysis and diagnosing bottlenecks with Performance Schema and the sys schema
Backups, Binary Logs, Replication and High Availability
  • Logical dumps (mysqldump / MySQL Shell) vs hot physical backups (Percona XtraBackup / Mariabackup)
  • ROW-based Binary Logs, Point-in-Time Recovery and GTID asynchronous/semi-synchronous replication
  • High-availability topologies: MySQL InnoDB Cluster (Group Replication + Router) and MariaDB Galera Cluster

Other Courses in This Category

  • Oracle Database Architecture, SQL Tuning and Administration

    Oracle Multitenant (CDB/PDB) architecture, SGA/PGA memory and background processes, tablespace/segment management, B-Tree/Bitmap indexes, partitioning, AWR/ASH tuning, RMAN backups and Data Guard essentials.

    Databases Corporate
    Live Course
    5 modules
    View
  • MongoDB Document Data Modelling, Aggregation and Operations

    BSON and document schema design patterns (embedding vs referencing), the Aggregation Pipeline, compound/multikey indexes and the ESR rule, multi-document ACID transactions, Change Streams, Replica Sets and Sharding.

    Databases Corporate
    Live Course
    5 modules
    View
  • In-Memory Data Structures, Caching and Messaging with Redis

    Redis data structures (Strings, Hashes, Lists, Sets, Sorted Sets, Streams, HyperLogLog), caching patterns (cache-aside, stampede protection), distributed locks, rate limiting, Lua/pipelining, RDB/AOF persistence and Redis Cluster.

    Databases Corporate
    Live Course
    5 modules
    View
  • Distributed NoSQL Databases with Apache Cassandra

    Masterless ring architecture, consistent hashing, tunable consistency (LOCAL_QUORUM), LSM-Tree/SSTable storage internals, query-driven CQL data modelling and cluster operations — for high-write distributed systems.

    Databases Corporate
    Live Course
    5 modules
    View
  • Search and Real-Time Analytics with Elasticsearch and OpenSearch

    Inverted index and Lucene internals, text analysis (tokenizers/filters), mapping design, Query DSL and BM25 relevance tuning, Aggregations, hybrid/vector search and cluster lifecycle management (ILM/ISM).

    Databases Corporate
    Live Course
    5 modules
    View
  • Graph Databases, Data Modelling and Cypher with Neo4j

    Labeled Property Graph modelling, the Cypher query language and variable-length path traversals, index-free adjacency, PROFILE/EXPLAIN tuning, Graph Data Science (PageRank, Louvain) and Knowledge Graph/GraphRAG patterns.

    Databases Corporate
    Live Course
    5 modules
    View

Get in touch