MySQL 8のUndo Tablespace最適化：History List Lengthの制御とディスク容量の回収 - ITFROMZERO - Share tobe shared! long-running transactionによってMySQL 8のUndo Tablespaceが肥大化した場合の実践的な対処法を解説。History List Lengthの異常検知、purge threadsの設定、undo tablespaceのtruncate、本番環境でのディスク容量回収まで網羅します。

Table of Contents

実際の問題：Undo Tablespaceがディスク容量を使い果たす

本番DBとして運用しているMySQL 8.0は約50GBのデータを抱えていたのですが、ある朝起きたら警告が届いていました。昨日まで20GB以上あったディスクの空き容量が2GBしかない。/var/lib/mysqlディレクトリ内のundo_001とundo_002がそれぞれ18GB以上に膨れ上がっていたのです。

似たような経験をしたことがある方、あるいはインデックスをしっかり張っているのにクエリがどんどん遅くなっていると感じている方、原因はundo logが蓄積したHistory List Length（HLL）の増大である可能性が高いです。

この問題は本番環境では珍しくありません。特に、長時間トランザクションを実行するバッチジョブがある場合や、開発者が本番DB上で直接デバッグしてcommit/rollbackを忘れてしまったときに起きやすいです。

原因：なぜHLLが増加し、undoが回収されないのか？

MySQL InnoDBはMVCC（Multi-Version Concurrency Control）を使い、複数のトランザクションがロックなしで同時にデータを読み取れるようにしています。行をUPDATEやDELETEするたびに、InnoDBは古いデータをすぐには削除せず、実行中のトランザクションが自分のスナップショットを一貫して参照できるよう、undo logに古いバージョンを書き込みます。

問題が起きるのは次のような場合です：

トランザクションを開いたままcommit/rollbackせずに長時間放置している
バッチジョブが1つのトランザクションで数百万行をUPDATEしている
レプリケーションのスレーブラグにより、スレーブがマスターの古いundo logを読み続けている

MySQLには古いundo logをクリアする責任を持つpurge threadがあります。しかし、どのトランザクションも参照しなくなった部分しかクリアできません。古いトランザクションが1つでも開いたままだと、purge threadは完全にブロックされ、HLLは際限なく増加し、undoファイルが膨れ上がります。

今すぐHLLを確認する：

-- History List Lengthを確認する
SHOW ENGINE INNODB STATUS\G
-- 「History list length XXXX」の行を探す

-- またはperformance_schemaから直接クエリする
SELECT
  NAME,
  SUBSYSTEM,
  COMMENT
FROM performance_schema.innodb_metrics
WHERE NAME = 'trx_rseg_history_len';

HLLが1,000未満であれば正常です。10,000を超えるとパフォーマンスへの影響が出始め、100,000を超えるとクエリが明らかに遅くなります。データを読み取るたびに、InnoDBは現在のトランザクションに正しいスナップショットを見つけるため、undo log内の大量の古いバージョンを走査しなければならないからです。

対処方法

1. purgeをブロックしているトランザクションを特定する

まず、最も長くundo logを保持しているトランザクションをすぐに探しましょう：

-- long-running transactionを探す（60秒以上実行中）
SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_query,
  trx_tables_in_use,
  trx_tables_locked
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started ASC;

-- 必要に応じてKILLするためのスレッドIDを探す
SELECT
  p.id AS process_id,
  p.user,
  p.host,
  p.db,
  p.command,
  p.time,
  p.info
FROM information_schema.PROCESSLIST p
JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 60;

数時間実行されているのに合理的な理由がないトランザクションがあれば、KILLしましょう：

KILL 12345; -- 12345は上で見つけたprocess_idに置き換える

2. purge threadsの数を増やす

デフォルトではMySQLはpurge threadsを4つしか使いません。書き込みの多いワークロードでは、増やすことでundo logのクリアを速めることができます：

-- 現在の設定を確認する
SHOW VARIABLES LIKE 'innodb_purge_threads';

-- 8に増やす（再起動不要、即時反映）
SET GLOBAL innodb_purge_threads = 8;

/etc/mysql/mysql.conf.d/mysqld.cnfに追記して永続化する：

[mysqld]
innodb_purge_threads = 8
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 300000

innodb_max_purge_lag_delayは、HLLが高くなったときにInnoDBがDML操作に課す最大遅延時間（マイクロ秒）を制限します。300000（0.3秒）に設定することで、purgeラグが大きくなってもシステムが完全にフリーズするのを防ぐことができます。

3. Undo Tablespaceをtruncateしてディスク容量を回収する

これはMySQL 8の新機能で、HLLを処理した後もundoファイルがディスク上で大きいままの場合に非常に役立ちます。

まず、undo tablespaceがいくつあるか確認しましょう：

-- undo tablespaceの状態を確認する
SELECT
  TABLESPACE_NAME,
  FILE_NAME,
  AUTOEXTEND_SIZE,
  TOTAL_EXTENTS,
  EXTENT_SIZE,
  (TOTAL_EXTENTS * EXTENT_SIZE) / 1024 / 1024 AS size_mb
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

MySQL 8はinnodb_undo_log_truncate変数による自動truncateをサポートしています：

-- 自動truncateを有効にする（MySQL 8ではデフォルトでON）
SET GLOBAL innodb_undo_log_truncate = ON;

-- truncateのしきい値を設定：undoファイルが128MBを超えたらtruncateする
SET GLOBAL innodb_max_undo_log_size = 134217728; -- 128MB

-- truncateの状態を確認する
SELECT
  NAME,
  COMMENT
FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%undo%truncate%';

設定ファイルに追記して永続化する：

[mysqld]
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 134217728
innodb_purge_rseg_truncate_frequency = 128

innodb_purge_rseg_truncate_frequencyは、purge threadがtruncateが必要かどうかを確認する頻度を決定します。値が小さいほどtruncateが頻繁に行われます（デフォルト：128）。

4. HLLを継続的に監視する

HLLがしきい値を超えたときにアラートを送るシンプルな監視スクリプトを作成する：

#!/bin/bash
# /usr/local/bin/check_mysql_hll.sh

MYSQL_CMD="mysql -u monitor_user -pYOUR_PASSWORD -N -e"
THRESHOLD=10000

HLL=$($MYSQL_CMD "SELECT COUNT FROM performance_schema.innodb_metrics WHERE NAME = 'trx_rseg_history_len'" 2>/dev/null)

if [ -z "$HLL" ]; then
  echo "ERROR: Cannot connect to MySQL"
  exit 2
fi

if [ "$HLL" -gt "$THRESHOLD" ]; then
  echo "WARNING: MySQL HLL = $HLL (threshold: $THRESHOLD)"
  # Telegram、Slackなどでアラートを送信する
  exit 1
fi

echo "OK: MySQL HLL = $HLL"
exit 0

5分ごとに実行するようcrontabに追加する：

*/5 * * * * /usr/local/bin/check_mysql_hll.sh >> /var/log/mysql_hll_check.log 2>&1

ベストプラクティス：予防と自動回収の組み合わせ

undo tablespaceの膨張インシデントを経験した後、次のプロセスが最も効果的だとわかりました：

ステップ1 — 最初からMySQLを設定する（mysqld.cnfに追記）：

[mysqld]
# Undo tablespaceの自動truncate
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 134217728

# 書き込みが多いワークロード向けにpurge threadsを増やす
innodb_purge_threads = 8
innodb_purge_rseg_truncate_frequency = 64

# HLLが高いときの遅延を制限する
innodb_max_purge_lag_delay = 300000

# 長時間実行されたトランザクションを自動でKILLする
# (MySQL 8.0.26+)
innodb_lock_wait_timeout = 50
wait_timeout = 28800
interactive_timeout = 28800

ステップ2 — アプリケーション層での制約追加：1つのトランザクションに多くの行を抱えさせないようにしましょう。100万行をUPDATEする必要がある場合は、1万行ずつのバッチに分割してください：

-- これの代わりに：
UPDATE orders SET status = 'archived' WHERE created_at < '2023-01-01';

-- バッチループを使う：
SET @batch_size = 10000;
repeat_loop: LOOP
  UPDATE orders
  SET status = 'archived'
  WHERE created_at < '2023-01-01'
    AND status != 'archived'
  LIMIT @batch_size;
  
  IF ROW_COUNT() = 0 THEN
    LEAVE repeat_loop;
  END IF;
  
  -- purge threadがクリアする時間を与えるために少しsleepする
  DO SLEEP(0.1);
END LOOP;

ステップ3 — undoファイルがすでに膨れ上がり、すぐに回収が必要な場合：アクティブなトランザクションが残っている場合、MySQL 8はundoファイルを自動的にすぐには縮小できません。緊急対応手順：

-- 1. 全てのlong-running transactionをKILLする
SELECT CONCAT('KILL ', p.id, ';')
FROM information_schema.PROCESSLIST p
JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 300;
-- 結果をコピーして各KILLコマンドを実行する

-- 2. 新しいundo tablespaceを作成する（MySQL 8.0.14+）
CREATE UNDO TABLESPACE undo_003
  ADD DATAFILE 'undo_003.ibu';

-- 3. 古いundo tablespaceをINACTIVEにしてtruncateする
ALTER UNDO TABLESPACE undo_001 SET INACTIVE;
ALTER UNDO TABLESPACE undo_002 SET INACTIVE;

-- 4. MySQLが自動でtruncateするのを待つ（状態を監視）
SELECT TABLESPACE_NAME, STATE
FROM information_schema.INNODB_TABLESPACES
WHERE TABLESPACE_TYPE = 'UNDO';
-- STATE = 'empty'になるまで待つ

-- 5. emptyになったら再アクティベートする
ALTER UNDO TABLESPACE undo_001 SET ACTIVE;
ALTER UNDO TABLESPACE undo_002 SET ACTIVE;

-- 6. 不要であれば一時tablespaceをDROPする
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
-- emptyになってからDROPする
DROP UNDO TABLESPACE undo_003;

この最適化により、本番データベースが大幅に安定しました。HLLを1,000以下に抑えたことで、クエリ時間が平均200msから40〜60msに短縮され、クリーンアップされないundo logによるディスクの侵食もなくなりました。

最も大切なことは、問題が起きてから対処するのではないということです。早めにHLL監視をアラートシステムに組み込み、バッチジョブのコードを見直して、いかなるトランザクションもロックを5分以上保持しないよう徹底しましょう。