Fun MySQL fact of the day: too much history

Yesterday, we discussed that InnoDB stores "old" record versions in an undo log, but we didn't discuss where undo logs are stored. While it's amusing to think it's turtles all the way down, it's actually a lot simpler: undo logs are stored inside InnoDB (and in redo logs, but that is a fun MySQL fact for another day).

Specifically, undo logs are stored inside the InnoDB system tablespace, which is, basically, a collection of internal InnoDB tables that are, in fact, stored on disk: ${datadir}/ibdata1. Why? This is, of course, necessary in order to roll-back an uncommitted transaction interrupted by a database crash or restart. Now, because this is a fun MySQL fact, let's explore this a little bit more by going back to yesterday's example, r.trx.

Let's say that transaction r.trx is held open with a couple of updates for something like 6 hours. Naturally, that doesn't seem like a big deal. But, if r.trx is in REPEATABLE READ (the default isolation), all modified records from all transactions >=r.trx must be retained in the undo logs. Because all of these undo log entries are stored to disk in the system tablespace, the ibdata1 file will grow. And grow. And grow until r.trx completes, at which time all the unneeded undo logs will finally be purged and the space freed-up for later use.

But what of ibdata1? Well, ibdata1 doesn't shrink. Ever. Even if it grows to 80% of your 10TB array. And if it is 80% of your available space, the only way to reclaim the space back is to dump and restore the entire database into a new instance. This definitely seems bug-ish, and was reported and validated as bug #1341 back in 2003, but if you ask me, I'd say the bug is in your code and the database is doing what databases do: storing data. There's a lot more details to cover on the topic, but for now: keep your transactions small and short-lived.