MySQL 8 undo log not truncating after excessive growth

Solution 1:

After spending a few days fighting with an ever-growing undo tablespace, we finally figured it out so I'm gonna share the results:

SELECT NAME, SPACE_TYPE, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE SPACE_TYPE = 'Undo' ORDER BY NAME;

We had 2/2 active (default) undo tablespaces. 1 was around 1GB and other one was 90GB and growing.

Per docs:

Rollback segments residing in the selected undo tablespace are made inactive so that they are not assigned to new transactions. Existing transactions that are currently using rollback segments are permitted to finish.

That part was the key. To my understanding, all transactions need to finish before cleanup can take place.

We looked for all running transactions:

SELECT trx.trx_id,
       trx.trx_started,
       trx.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id
WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 1 SECOND
  AND ps.user != 'system_user';

In the list of transaction, there was one that was 2 days old.

If you have performance schema enabled, you can get the process and query that holds it:

SELECT *
FROM performance_schema.threads
WHERE processlist_id = thread_id;

We killed that process and everything recovered within 20 minutes. All storage was reclaimed back.