MySQL - Out of memory 対策！？
Updated:
WindowsXP + Ruby + MySQL で大量（あるテーブルでは数千万件）のデータを処理していると、以下のようなエラーが発生しました。
Out of memory (Needed 8164 bytes)
MySQL 側の問題と思い、MySQL のログを確認してみると同じようなエラーを出力していました。
[ERROR] Out of memory;
check if mysqld or some other process uses all available memory;
if not, you may have to use 'ulimit' to allow mysqld
to use more memory or you can add more swap space
バッファが足りなくなってるようです。
MySQL をインストールした際に添付されている設定ファイルのサンプル（物理メモリが 1 ～ 2 GBの場合のサンプル「my-huge.ini」）を参照してみると、以下が例として掲載されています。
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
MySQL サーバにログイン後、上記の項目の現状を確認してみると、以下のようになっていました。 ※テストで使用した MySQL サーバはメモリ 4GB（実質3.25GB）の WindowsXP 上に構築したものです。 ※”show variables;” で関連項目のみ抜粋。
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| key_buffer_size | 8384512 |
| max_allowed_packet | 1048576 |
| table_open_cache | 400 |
| sort_buffer_size | 2097144 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| myisam_sort_buffer_size | 8388608 |
| thread_cache_size | 0 |
| query_cache_size | 25165824 |
+-------------------------+----------+
調べてみると、これらの中でも key_buffer_size がパフォーマンスに影響してくるようです。 確かに、key_buffer_size が全然違います。
当方の MySQL サーバでは設定ファイルで上記の項目を設定しなかったため、とりあえず設定してみました。 ※但し、後述する方法でも有効だと思いますが当方の環境の場合、別に原因がありました。 最後までご覧ください！
※MySQL の設定ファイルは my.ini （ Linux なら my.cnf ）
- key_buffer_size = 512M インデックスブロック用に使用するバッファのサイズ。 インデックスを使うクエリ（つまりほとんど全ての）はこの値によってパフォーマンスが大きく変わります。 可能限り大きく設定することが推奨されているようです。
- max_allowed_packet = 1M 最大パケットサイズ。
- table_open_cache = 512 開いたテーブルのファイルポインタ。 同時接続数×テーブル数が最低限必要。
- sort_buffer_size = 2M それぞれのスレッドがソート時に使用するバッファのサイズ。
- read_buffer_size = 2M シーケンシャル（全件）セレクトを行うときにスレッドが割り当てるバッファサイズ。
- read_rnd_buffer_size = 8M ソートされたレコードを読み出すときのバッファサイズ。
- myisam_sort_buffer_size = 64M REPAIR TABLE 文実行時など、MyISAMテーブルのインデックスがソートされるときに割り当てられるバッファのサイズ。
- thread_cache_size = 8 再利用のためにキャッシュ可能なスレッドの数。
- query_cache_size = 128M クエリキャッシュで確保するメモリ（Byte数）。
※key_buffer_size はサンプル（384MB）より多めの 512MB にした。 ※query_cache_size はサンプル（32MB）より多めの 128MB にした。 ※query_cache_limit = 1MB で設定設定していたものも、query_cache_limit = 4MB とした。
また、使用しているテーブルに適切にインデックスが設定されていない（SELECT する際に WHERE で指定しているカラムをインデックス設定していなかった）ことも影響していると考え、インデックス設定もしました。
さらに、レコードの存在チェックを、
SELECT COUNT(*) FROM FUGA WHERE a = 999
とした結果が 0 であるかどうかで判断していたものを、
SELECT hoge FROM FUGA WHERE a = 999
とした結果が空であるかどうかで判断するうように変更してみました。 ちなみに、インデックス設定したので、
SELECT COUNT(hoge) FROM FUGA WHERE a = 999
とした結果が 0 であるかどうかで判断してもパフォーマンスは上がるはずです。
マシンのメモリ使用状態によると思いますが、正常に処理できたり、Out of memory になったりします。 （当方は、とりあえずこの処理を行う時は他のアプリ（特に開発作業に使った IDE ）を起動させないようにしています）
と、ここまではおそらく一般的な対応方法です。 しかし、今回のこの方法でも場合によっては（メモリの利用状況によっては）「Out of memory」 が発生していました。
さらに、調査してみたところ、テンポラリファイルの置き場所に問題があったようです。
今回使用したマシンは、4GB のメモリを搭載した 32Bit版 WindowsXP マシンで、（32Bit版 WindowsXP では）メモリが実質 3.25GB しか使用できないことから、余った約 700MB を RAM ディスクとして使用しています。 そして更に、各種アプリの速度アップのため、TEMP をハードディスク上からこの RAM ディスクに変更していました。 MySQL 側で設定していなかった場合、デフォルトで TEMP のフォルダが使用されてしまいます。 ですから、アプリの起動状況によっては TEMP がすぐにいっぱいになって、「Out of memory」を起こしていたのです。 と言うわけで、MySQL 設定ファイル my.ini（Linux なら my.cnf ）の [mysqld] セクションに以下の記述を追加しました。
tmpdir="E:/TEMP"
※Eドライブに TEMP フォルダが存在する場合。
これで、問題解決です。 （でも、これがベストの対策だったかは不明！）
ちなみに、今回は Windows 上の MySQL についてでしたが、MySQL なら Linux でも BSD でも同じです。 （Mac はよく知らないけど、BSD(Darwin) 系だから多分 OK？）
以上。
Comments