One forgotten transaction. Eight hours. Everything slower.

transcript

6 in the morning. An analyst types begin, then a select. They never commit. By 2 in the afternoon, the whole database is slower.

6 in the morning. An export opens a snapshot. 10 o'clock. The sale starts. Millions of updates. Every update leaves an old version behind. By 2 in the afternoon, orders has grown by a third.

Here's the twist. That transaction was only reading. But its snapshot might still need any old version. So none can be cleaned up. On MySQL, purge is stuck instead. The undo log keeps growing.

So vacuum can't remove anything that snapshot might see. Dead versions pile up, in tables and indexes. Scans read more pages. Everything slowly gets slower.

First, alert on transaction age. Then set an idle in transaction timeout. And run exports on a replica, never the primary.

Commit regularly. Even when you only read. That's straight from MySQL's docs.

Full breakdown in the lecture.