MySQL「Error writing file」(Errcode: 28) の修正 – ディスク容量不足

エラーの内容

Error writing file '/tmp/MYxxxxxx' (Errcode: 28 - No space left on device)

このエラーは、クエリがソート、GROUP BY、ORDER BY、または大規模なJOINのために一時ファイルへの書き込みが必要な場合に、対象パーティションの空き容量がなくなったときに発生します。MySQLはこれらの一時ファイルをMYxxxxxxという名前でtmpdirに書き込みますが、Linuxではデフォルトで/tmpが使用されます。そのパーティションが100%に達すると、ディスクにアクセスするすべてのクエリがErrcode 28で即座に失敗します。リトライもグレースフルデグラデーションもなく、ただちに失敗するだけです。

ステップ1 – パーティションが実際にいっぱいかどうか確認する

df -h

/tmpまたは/のパーティションが100%になっていないか確認してください。それだけで終わらず、inodeも確認しましょう：

df -i

パーティションはバイト数が枯渇するずっと前にinodeを使い果たすことがあります。IUse%が100%を示している場合、対処法は同じです：ファイルを削除してください。新しいinodeが残っていない状態では、空きバイト数はまったく意味をなしません。

ステップ2 – 不要なファイルを探して削除する

クラッシュしたクエリが残した古いMySQL一時ファイルを削除する

# 先にMySQLを停止する — アクティブな一時ファイルは削除しないこと
sudo systemctl stop mysql

# 古い一時ファイルを削除する
sudo rm -f /tmp/MY*

# 再起動する
sudo systemctl start mysql

いっぱいになったパーティション上の最も大きなファイルを探す

sudo du -sh /* 2>/dev/null | sort -rh | head -20

負荷の高いサーバーでは、20〜50 GBのMySQLバイナリログ、ローテーションされていないアプリケーションログ、誰かが削除を忘れた古いバックアップダンプなどが見つかることがよくあります。

古いバイナリログをパージする（binlogが有効な場合）

-- binlogが使用しているディスク容量を正確に確認する
SHOW BINARY LOGS;

-- 3日以上前のログをパージする
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;

シェルから実行する場合：

sudo find /var/lib/mysql -name 'mysql-bin.*' -mtime +3 -delete

肥大化したログファイルを切り詰める

sudo truncate -s 0 /var/log/mysql/error.log
sudo journalctl --vacuum-size=200M

ステップ3 – tmpdirをより大きなパーティションに移動する（恒久的な対処法）

デフォルトのLinuxインストールでは/tmpパーティションが1〜5 GBしかないことが多く、MySQLが大きな結果セットを処理するとすぐにいっぱいになります。tmpdirを十分な空き容量のある場所に変更しましょう。

専用の一時ディレクトリを作成する

sudo mkdir -p /var/lib/mysql-tmp
sudo chown mysql:mysql /var/lib/mysql-tmp
sudo chmod 750 /var/lib/mysql-tmp

my.cnf / my.ini を更新する

[mysqld]
tmpdir = /var/lib/mysql-tmp

Ubuntu/Debianでは設定ファイルは/etc/mysql/mysql.conf.d/mysqld.cnfにあります。CentOS/RHELでは/etc/my.cnfです。

MySQLを再起動する

sudo systemctl restart mysql

ステップ4 – クエリが必要とする一時領域を削減する

今すぐ追加のディスク容量を確保できない場合は、より多くの処理をRAM上で行うことでMySQLの一時ファイル使用量を抑えることができます。

インメモリソートバッファを増やす

[mysqld]
tmp_table_size      = 256M
max_heap_table_size = 256M
sort_buffer_size    = 4M

これらはセッションレベルの値なので、MySQLを再起動せずに特定の重いクエリだけに適用することができます：

SET SESSION tmp_table_size = 268435456;
SET SESSION sort_buffer_size = 4194304;
-- その後、重いクエリを実行する

問題のあるクエリを特定する

SHOW PROCESSLIST;

State: Copying to tmp tableまたはCreating sort indexの状態にあるクエリを探してください — これらがディスクへのスピル候補です。それらに対してEXPLAINを実行してください。ソート列にインデックスが存在しない場合、それを追加するだけで一時ファイルの使用が完全になくなることがよくあります。

修正の確認

# 1. tmpdirが正しく設定されているか確認する
SHOW VARIABLES LIKE 'tmpdir';

# 2. 新しいtmpdirパーティションの空き容量を確認する
df -h /var/lib/mysql-tmp

# 3. 失敗していたクエリを再実行する — エラーなく完了するはず

クエリ実行中にリアルタイムで一時ファイルが作成される様子を確認したい場合は、次のコマンドが使えます：

watch -n1 'ls -lh /var/lib/mysql-tmp/'

再発防止策

100%ではなく80%でアラートを設定する — パーティションがいっぱいになった時点では、すでにクエリが失敗しています。対応できる時間を確保するため、tmpdirパーティションに80%でディスクアラートを設定してください。
バイナリログの自動削除を設定する — my.cnfにbinlog_expire_logs_seconds = 259200（3日）を追加してください。この設定がないと、binlogは無制限に蓄積し、書き込みの多いサーバーでは容易に20GB以上を消費します。
MySQLエラーログをローテーションする — /var/log/mysql/error.logのlogrotateを設定してください。負荷の高いサーバーでは1日に数百MBのログが生成されることもあり、ローテーションなしではすぐに積み上がります。
ディスクだけでなくクエリ自体を修正する — /tmpにギガバイト単位のデータを書き出すクエリは、たいていインデックスが不足しています。EXPLAINを実行し、フルテーブルスキャンを見つけて適切なインデックスを追加してください。それが根本的な原因を取り除く最善策です。