問題：単純なクリーンアップ作業での失敗
最近、本番環境のログテーブルから古いデータを削除しようとしました。テーブルには約4,000万行のデータがあり、6ヶ月以上前のものをすべて削除したいと考えていました。単純な作業だと思い、次のクエリを実行しました。
DELETE FROM activity_logs WHERE log_date < '2023-06-01';
5分ほどは何の問題もないように見えましたが、突然操作が失敗し、次のエラーが発生しました。
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
データベース自体は停止していませんでしたが、クリーンアップ作業は完全に行き詰まってしまいました。
原因
InnoDB のロック処理は、他の多くのデータベースエンジンとは異なります。メモリを節約するために数千の行ロックを1つのテーブルロックに昇格させるのではなく、InnoDB はすべての行ロックを個別に追跡します。これらのロックは InnoDB Buffer Pool（バッファプール）内に保持されます。
各行ロックは約150バイトのメモリを消費します。一度に1,000万行を削除しようとすると、MySQL はロックのメタデータを保存するためだけにバッファプール内に約1.5GBの空き容量を必要とします。もし innodb_buffer_pool_size がデフォルトの128MBのような小さな値に設定されている場合、エンジンはメモリ不足に陥り、システムメモリを保護するためにプロセスを強制終了します。
即効性のある解決策：バッファプールサイズの拡張
サーバーのRAMに余裕がある場合、最も手っ取り早い解決方法は、ロックテーブルに余裕を持たせることです。バッファプールサイズを動的に増やすことで、当面のボトルネックを解消できることがよくあります。
1. 現在の割り当てを確認する
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS size_mb;
2. 動的にサイズを増やす (MySQL 5.7.5以降)
例えば、システム全体のRAMが16GBで、バッファプールが1GBしか設定されていない場合、再起動なしで安全に4GBまで増やすことができます。
SET GLOBAL innodb_buffer_pool_size = 4294967296;
OSのメモリ使用量に注意してください。利用可能な物理RAMよりも高い値を設定すると、Linux の OOM キラーが作動し、MySQL サービス全体がクラッシュする可能性があります。
より良い解決策：バッチ処理（分割実行）
RAMを増やすのは一時的なしのぎに過ぎません。大規模なデータセットを扱うプロフェッショナルな方法は、作業を小さく管理可能な単位に分割することです。バッチ処理を行うことで、ロックテーブルの満杯を防ぎ、他のユーザーに対するデータベースの応答性も維持できます。
方法1：DELETEループ
1回で大量の DELETE を行う代わりに、一度に5,000行ずつ処理するループを使用します。これにより、MySQL は各チャンク（塊）ごとにトランザクションをコミットし、ロックを解放できます。
-- SQLクライアントまたはスクリプトで実行
REPEAT
DELETE FROM activity_logs
WHERE log_date < '2023-06-01'
LIMIT 5000;
SELECT ROW_COUNT() INTO @rows;
-- わずかなスリープを入れることで、高負荷なサーバーでのCPUスパイクを防ぎます
DO SLEEP(0.05);
UNTIL @rows = 0 END REPEAT;
方法2：プライマリキーの範囲指定による更新
数百万行を更新する場合、大きなオフセットを伴う LIMIT の使用は避けてください。時間が経つにつれて処理が遅くなるためです。代わりに、特定のID範囲をターゲットにします。これはクラスター化インデックスを直接使用するため、大幅に高速化されます。
-- 10,000件ずつのID範囲で処理
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01' AND id BETWEEN 1 AND 10000;
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01' AND id BETWEEN 10001 AND 20000;
設定変更の永続化
サーバーの再起動後も設定を維持するには、my.cnf または my.ini ファイルを更新します。データベース専用サーバーの場合、一般的な目安として全RAMの70〜80%をバッファプールに割り当てます。
[mysqld]
# 8GB RAMを搭載したサーバーの例
innodb_buffer_pool_size = 6G
# 並行性を高めるため、1GBにつき1インスタンスを使用
innodb_buffer_pool_instances = 6
検証
バッチ処理が正しく機能しているかを確認するには、次回の大きな操作中にこのコマンドを実行します。
SHOW ENGINE INNODB STATUS\G
TRANSACTIONS セクションを確認してください。「row locks」のカウントが数百万まで上昇するのではなく、低い数値（バッチサイズ程度）に留まっていることを確認します。カウントが10,000未満であれば安全圏です。
まとめチェックリスト
- **応急処置：** RAMに余裕がある場合は `innodb_buffer_pool_size` を増やす。
- **根本的な解決策：** クエリを書き換え、5,000〜10,000行ずつのチャンクでデータを処理する。
- **安全策：** `innodb_row_lock_current_waits` を監視し、クエリがクラッシュする前にロックのボトルネックを特定する。