Schedule icon
Query icon
OutputValues icon
Log icon
If icon
SlackIncomingWebhook icon
Pause icon
Loop icon
LoopUntil icon
Fail icon

Truncate bloated MySQL InnoDB undo tablespaces with a re-read gate

Find MySQL undo tablespaces that grew past a size limit, cycle them inactive then active behind an approval gate, then confirm the file really shrank.

Categories
DataInfrastructure

Findings

Measured on MySQL 8.0.46 with innodb_undo_log_truncate=OFF. Four undo tablespaces started at 16777216 bytes each. Twelve bulk UPDATE passes over a 30000 row table, run while one REPEATABLE READ transaction held a consistent snapshot, took every one of them to 117440512 bytes. They stayed there. MySQL will not shrink an undo tablespace while automatic truncation is off, so the bytes are gone until somebody cycles the file by hand. One SET INACTIVE plus SET ACTIVE cycle took a file from 117440512 bytes back to 16777216 bytes in under three seconds, once purge was free to advance. With the snapshot still open the same cycle left the file at 117440512 bytes in state inactive for more than thirty seconds, because purge could not pass the undo records the read view still needed. That is why this flow re-reads the size and fails the run on no change, instead of trusting the ALTER statement.

Safety

MySQL keeps a hard floor of two active undo tablespaces. ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE on a server with exactly two is refused with ERROR 3655 (HY000): Cannot set innodb_undo_001 inactive since there would be less than 2 undo tablespaces left active. The plan refuses the batch unless three or more are active, so the statement never reaches a server that cannot take it. The refusal path is a normal outcome here, not an error: a stock MySQL ships two undo tablespaces, which means a stock MySQL cannot be truncated this way until an operator adds a third. Every cycle ends with SET ACTIVE even when the truncation did not finish. A tablespace left inactive permanently cuts the undo capacity of the server, which is a worse outcome than the bloat.

How it works

  1. detect joins information_schema.INNODB_TABLESPACES to information_schema.FILES on FILE_TYPE = 'UNDO LOG' to list every undo tablespace with its file on disk, its current FILE_SIZE, its state, the count of active undo tablespaces, the live value of innodb_undo_log_truncate plus the age of the oldest open transaction.
  2. plan marks an active tablespace above max_size_mb as bloated. It targets at most max_truncate_per_run of them. It refuses the whole batch on any of three conditions: fewer than three undo tablespaces active, one already held out of service, a name that is not a plain identifier.
  3. log_status prints the inventory, the targets plus the mode so a report run is readable on its own.
  4. Bloat found posts to Slack in every mode. A disk cost an operator cannot see is a disk cost nobody reclaims.
  5. With auto_remediate true, dry_run false plus an approval typed into the Pause, a Loop with concurrencyLimit: 1 cycles one tablespace at a time: SET INACTIVE, then a LoopUntil that waits for InnoDB to empty the file, then SET ACTIVE.
  6. reverify reads every undo tablespace again. The run fails unless every targeted file is smaller than before and back in state active. The failure message names the open read view as the usual cause.

What you get

  • A size inventory of every undo tablespace with the file path an operator can check with ls.
  • The two facts that explain the bloat in the same report: whether automatic truncation is off plus how long the oldest transaction has been open.
  • Space handed back to the filesystem in bytes, measured rather than claimed.
  • A refusal that protects the two tablespace floor before MySQL has to reject the statement.
  • A failed run whenever a cycle did not shrink the file, with the cause named.

Who it's for

DBAs plus platform engineers running MySQL 8 where a batch job, a stuck replica or an idle session in a transaction has inflated the undo tablespaces while the disk never came back. Anyone who has found a 40 GB undo_002 on a 100 GB volume knows the shape of this.

Why orchestrate this with Kestra

Reclaiming undo is a sequence, not a statement. Take a file out of service, wait for purge to drain it, put it back, then prove the bytes returned. Each step can stall for reasons outside the database, so the wait needs a bound plus the result needs a check. Kestra carries the approval gate, the bounded wait, the serialised loop plus the re-read in one versioned flow. The same file runs from a schedule in report mode or by hand in remediate mode. The gate matters because the write here carries no information. ALTER UNDO TABLESPACE returns success the moment it marks the file inactive, long before anything has been truncated. The truncation may never happen at all. Only the second read knows.

Tested end to end

Run on Kestra 2.0.5 against MySQL 8.0.46 with innodb_undo_log_truncate=OFF and four undo tablespaces.

  • Healthy: all four undo tablespaces at 16777216 bytes, limit 64 MiB. 0 bloated, no Slack, no statement issued.
  • Bloated: twelve bulk UPDATE passes under an open snapshot took all four to 117440512 bytes. Detection reported 4 bloated with the largest at 112 MiB.
  • dry_run=true: reported the target and the statement it would run. MySQL's own CLI showed the file unchanged at 117440512 bytes.
  • auto_remediate=true, dry_run=false, approval true: the target went from 117440512 bytes to 16777216 bytes, 100663296 bytes handed back, state active, re-read confirmed.
  • Approval declined: no statement ran, the file stayed at 117440512 bytes.
  • Floor refusal: with only two active undo tablespaces the run refused the batch, logged the CREATE UNDO TABLESPACE next step plus issued nothing.
  • Re-verify failure: with a transaction holding a read view through the whole wait, the cycle could not empty the file. The tablespace was put back in service at its old size, the re-read caught it plus the run failed.
  • Failure path: an unreachable JDBC URL failed detect and fired the errors Slack task.

Prerequisites

MySQL 8.0.14 or later, which is where ALTER UNDO TABLESPACE arrived. Three or more undo tablespaces if you want the remediation to be able to run at all. Reporting works on any server.

Secrets

  • MYSQL_USERNAME plus MYSQL_PASSWORD: a user with PROCESS to read the undo inventory plus the privileges ALTER UNDO TABLESPACE needs to cycle one.
  • SLACK_WEBHOOK_URL: incoming webhook for the bloat alert plus the failure alert.

Inputs

  • mysql_url: JDBC URL of the server to guard.
  • use_secret_auth: true in production. False only for a disposable test server with inline credentials.
  • max_size_mb: size limit in MiB above which an active undo tablespace is reported. Default 64 against a 16 MiB fresh file.
  • max_truncate_per_run: how many to cycle in one run. Default 1.
  • truncate_timeout: bound on the wait for InnoDB to empty the file. Default PT2M.
  • dry_run: true by default, reports without issuing anything.
  • auto_remediate: false by default. Both flags plus a typed approval are needed before any statement runs.

Quick start

  1. Add MYSQL_USERNAME, MYSQL_PASSWORD plus SLACK_WEBHOOK_URL as secrets.
  2. Run with the defaults. You get the inventory, the bloated count plus nothing changed.
  3. Check innodb_undo_log_truncate in the log. A 0 means MySQL will never shrink these files on its own, which is the setting that created the problem.
  4. If the count of active undo tablespaces is two, add a third with CREATE UNDO TABLESPACE extra_undo ADD DATAFILE 'extra_undo.ibu', then run again.
  5. To reclaim, run with auto_remediate=true plus dry_run=false, then type true into the paused task. Read the before and after bytes in the log.
  6. Enable the Schedule trigger once the report looks right. It stays pinned to report mode.

How to extend

  • Swap the Slack task for PagerDuty, Discord or an HTTP call to your own endpoint.
  • Raise max_truncate_per_run on a server with many undo tablespaces, keeping concurrencyLimit: 1 so the floor is never at risk.
  • Add a task that sets innodb_undo_log_truncate=ON plus a sane innodb_max_undo_log_size, so MySQL recycles the files without a flow. Keep this guard for the servers where that is not allowed.
  • Add a kill step for the transaction named by oldest_trx_seconds, behind its own approval, for the case where an idle session is the thing holding purge back.
  • Point the detect query at several servers with a Loop over a list of JDBC URLs.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.