リレーショナルデータベースのTEMP落ちとは？
はじめに
データベース運用において「TEMP落ち」という言葉を聞いたことがありますか？これはワーキングメモリ（作業用メモリ）が不足することで発生する問題です。本記事では、TEMP落ちの原因と対策について詳しく解説します。
TEMP落ちとは
TEMP落ちとは、DBMSにおけるクエリ実行時に、ワーキングメモリ（作業用メモリ）が不足し、一時ファイル（tempファイル）にデータを退避させて処理を行うことです。
HDDやSSDといったストレージの一時領域を利用するため、クエリが極端に遅くなるケースがあります。
DBMSにおけるメモリの役割
DBMSでは、メモリを効率的に活用してパフォーマンスを向上させています：
データキャッシュ
- ディスクにあるデータの一部を保持
- SELECTクエリに対して、メモリ内にデータがあればそれを返す
- なければディスクに取りに行く
ログバッファ
- INSERT、UPDATE、DELETEなどの更新系クエリを溜める
- 更新処理は非同期で行われ、ログバッファへの書き込み（コミット）のタイミングでユーザーに完了を通知
ワーキングメモリ
- データキャッシュやログバッファとは別の領域
- ソートや結合（ハッシュ）などで用いられるメモリ
- ソートの際にデータ量が大きすぎてこのメモリから溢れた場合、ストレージの一時領域を利用してクエリを実行する
- これがTEMP落ちの正体
一時テーブルスペースが使用される処理
ワーキングメモリが不足した際に、以下の処理で一時テーブルスペースが使用されます：
- ORDER BY句の実行時：ソート処理のための一時的なデータ格納
- GROUP BY句の実行時：集計処理のための一時的なデータ格納
- JOIN処理時：結合処理のための一時的なデータ格納
- DISTINCT句の実行時：重複除去処理のための一時的なデータ格納
- サブクエリの実行時：中間結果の一時的な格納
発生原因
1. 不適切なSQLクエリ
-- 問題のあるクエリ例
SELECT * FROM large_table
ORDER BY column1, column2, column3, column4, column5;
大量のデータに対して複数列でのソートを行うと、ワーキングメモリを大量に消費し、TEMP落ちが発生します。
2. インデックスの不足
適切なインデックスが存在しない場合、データベースエンジンは全件スキャンや大量のソート処理を実行し、ワーキングメモリを過度に使用します。
3. 統計情報の不正確
統計情報が古い場合、クエリオプティマイザが不適切な実行計画を選択し、効率的でない処理が実行されることがあります。
4. 同時実行数の増加
複数のセッションが同時に大量のソート処理を実行すると、ワーキングメモリの競合が発生します。
5. ワーキングメモリの設定不足
DBMSの設定でワーキングメモリが小さく設定されている場合、簡単にTEMP落ちが発生します。
対策方法
1. SQLクエリの最適化
インデックスの活用
-- インデックスを活用したクエリ
CREATE INDEX idx_large_table_columns ON large_table(column1, column2, column3);
SELECT * FROM large_table
WHERE column1 = 'value'
ORDER BY column2, column3;
LIMIT句の使用
-- 必要な件数のみ取得
SELECT * FROM large_table
ORDER BY column1
LIMIT 1000;
2. ワーキングメモリの監視
使用量の確認
-- Oracleの場合
SELECT tablespace_name,
bytes/1024/1024 as MB,
maxbytes/1024/1024 as MAX_MB
FROM dba_temp_files;
-- PostgreSQLの場合
SELECT schemaname, tablename, attname, n_distinct
FROM pg_stats
WHERE schemaname = 'pg_temp';
3. 設定の調整
ワーキングメモリの拡張
-- PostgreSQLの場合
-- postgresql.conf
work_mem = 64MB -- ソート処理用メモリ
maintenance_work_mem = 256MB -- メンテナンス用メモリ
-- MySQLの場合
-- my.cnf
sort_buffer_size = 64M
join_buffer_size = 64M
一時テーブルスペースの拡張
-- Oracleの場合
ALTER TABLESPACE temp ADD TEMPFILE '/path/to/temp02.dbf' SIZE 1G;
4. アプリケーションレベルの対策
- ページネーションの実装：大量データを分割して取得
- 非同期処理の活用：長時間のクエリをバックグラウンドで実行
- キャッシュの活用：頻繁に実行されるクエリ結果をキャッシュ
監視とアラート
監視項目
- ワーキングメモリの使用率
- 一時テーブルスペースの使用率
- 長時間実行クエリの検出
- メモリ使用量の監視
- 同時実行数の監視
アラート設定例
-- 使用率が80%を超えた場合のアラート
SELECT tablespace_name,
ROUND((bytes/maxbytes)*100, 2) as usage_percent
FROM dba_temp_files
WHERE (bytes/maxbytes)*100 > 80;
統計情報の更新
適切な実行計画が選択されるためには、データが大きく更新された際に統計情報の更新を行うことが重要です：
-- MySQLの場合
ANALYSE TABLE スキーマ名.テーブル名;
-- PostgreSQLの場合
ANALYSE スキーマ名.テーブル名;
まとめ
TEMP落ちは、ワーキングメモリの不足により発生する問題です。重要なポイントは：
- SQLクエリの最適化：インデックスの活用と効率的なクエリ設計
- ワーキングメモリの適切な設定：DBMSの設定パラメータの調整
- 定期的な監視：メモリ使用率とパフォーマンス指標の監視
- 統計情報の更新：クエリオプティマイザが適切な実行計画を選択できるよう統計情報を最新に保つ
- アプリケーションレベルの対策：ページネーションやキャッシュの活用
これらの対策を実装することで、TEMP落ちによるシステム障害を防ぎ、安定したデータベース運用を実現できます。