MySQL internals and advanced troubleshooting
You study InnoDB’s pages, redo, undo and lock structures and solve crash recovery, corruption, deadlock and memory problems in real failure scenarios.
Level 501 · 14 lessons · 12 practice labs
Lessons
Inside InnoDB
- InnoDB architecture: memory and on-disk structures
- The clustered index and secondary indexes
- The undo log, purge and long transactions
- The doublewrite buffer and redo
- Short task: find why the history list keeps growing
Failures
- The crash recovery process
- Corrupted tables and forced recovery mode
- Analysing deadlock reports
- Running out of memory and OOM
- Disk full: recovering safely
- Short task: bring a server that won’t start back up
Scale
- Changing billion-row tables
- Sharding approaches in MySQL
- Capstone: the midnight on-call incident
Practice labs
- Read the history list length: Find the history list length in the InnoDB status output and explain what it means.
- Find a transaction left open: Find a session left open for hours that makes the history list grow and close it safely.
- Measure and reduce purge lag: Show purge falling behind under heavy updates and reduce the lag with settings.
- Read the crash recovery steps: Kill the process, restart and interpret the redo apply steps in the error log.
- Rescue data from a table with a bad page: Start forced recovery at the lowest level and export readable rows from a damaged table.
- Bring back a server that won't start: Work from the error log to start a server with a bad config and a missing file without losing data.
- Decode the latest deadlock report: Write which two transactions waited for which locks in the latest deadlock in the InnoDB status.
- Find the cause of running out of memory: Calculate how per-connection buffers pushed total memory over the limit on a MySQL killed by the OS.
- Recover a server safely on a full disk: On a server that cannot write because the disk is full, clear the right files and restore service without data loss.
- Add a column to a big table without locking: Use the instant add-column method on a multi-million-row table and measure lock time.
- Add an index with an online schema change tool: Add an index to a big table with the shadow-table method without stopping live traffic.
- Choose and test a sharding key: Compare two sharding keys for Mavikuş Kargo's shipments table against query patterns and test at small scale.
About this course
You study InnoDB’s pages, redo, undo and lock structures and solve crash recovery, corruption, deadlock and memory problems in real failure scenarios.
What you will learn
- Inside InnoDB
- Failures
- Scale
Prerequisites
You should be experienced in the topics of the same path up to level 401.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 501; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.