MySQL Articles

Logical backups scale badly by default. Field notes on where mysqldump stops working, what mydumper and myloader change, and the honest boundary where physical backups win.

Triggers or binlog streaming? Field notes from running both gh-ost and pt-online-schema-change in production: cut-over risk, throttling behavior, foreign key walls, and when you need neither.

Long-running and idle transactions quietly block DDL, hold locks, and stall purge until the whole instance suffers. Here is how I detect them with innodb_trx and decide what to kill.

Gap locks make InnoDB transactions collide over rows that do not exist. These are the patterns I use to shrink the lock footprint under REPEATABLE READ, and when READ COMMITTED is the honest answer.

Deadlocks are not random noise; each one is a recorded collision with a cause. This is the repeatable workflow I use to capture, parse, and permanently fix InnoDB deadlocks.

MySQL 8.0 moved row-lock diagnosis to performance_schema.data_locks and data_lock_waits. Here is how I read those tables, use sys.innodb_lock_waits, and build a blocker tree during an incident.

A single idle transaction can make an innocent ALTER TABLE queue the entire application behind it. This is my working process for diagnosing MySQL metadata lock waits and running DDL safely.

The most repeatable plan regression in MySQL: add LIMIT to an ORDER BY query and the optimizer abandons your selective index for the ordering one. Why it happens, how to spot it, and the fixes that hold.

Most bad MySQL plans trace back to one number: a row estimate built from twenty sampled pages. Field notes on innodb_stats_persistent, ANALYZE TABLE, eq_range_index_dive_limit, and 8.0 histograms.

optimizer_switch is where MySQL hides two decades of query-planner history, and a handful of its flags cause real production regressions. Field notes on the ones that bite and how to test them one at a time.