clean undo tablespace mysql undo_001 undo_002

07/04/2024

Par défaut il y a 2 fichiers : undo_001 et undo_002 que l'on trouve dans le dossier data de mysql.

Le truc c'est qu'ils grossissent à chaque grosse transaction, puis se vident lorsqu'elle est terminée, mais l'espace reste alloué sur le disque.

Avec cette requête on peut les voir, ainsi que l'espace qu'ils occupent et l'espace libre à l'intérieur.

SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';

Voici la liste des transactions actives :

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';

Pour vider les fichiers undo :

-- create a new one
CREATE UNDO TABLESPACE temp_undo_003 ADD DATAFILE 'temp_undo_003.ibu';

-- empty the innodb_undo_001
ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
-- wait for the state become 'empty'
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'innodb_undo_001';
ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;

-- empty the innodb_undo_002
ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
-- wait for the state become 'empty'
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME = 'innodb_undo_002';
ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;

-- remove the temp
ALTER UNDO TABLESPACE temp_undo_003 SET INACTIVE;
DROP UNDO TABLESPACE temp_undo_003;

Raccourcis