大きなinnodbテーブルにcreate index

行数150M、サイズ550GBなんていう一枚テーブルをinnodbで作ってしまいました。4TB HDD使ってるのでまだまだ溜め込めますが、こんな量のデータを1つのテーブルに入れることが何か間違っているような気がする。

こうなるとインデックス無しではまともにデータは取り出せませんので、まだテーブルが小さかった頃に数個作ってありました。で、恐れていた事態、インデックスの追加。幸いにもMySQL 5.6ではテーブルをロックせずにインデックスを追加・削除できるようになったので、何日かかるかわからないけどcreate index文を実行。翌朝見ると異常終了orz

そういえば昨日の帰り際、MySQLのCPU使用率が10%前半に落ちてたな(コマンド開始時は30～50%)、メモリが足りないのかと思い、innodb_buffer_pool_size を2GB→6GBに増やして再挑戦。mysqldは順調に実メモリは消費しましたが、仮想メモリに手を出す様子はなし。

しばらく様子を見ていると、/の入っているHDD残量が少しずつ減っているのを発見。ですが前述の通り仮想メモリは増えてない。はて?

ネットで情報を探していると、たどり着いたのがこれ。

tmpdirについて少し補足します。MySQLがtmpdirに作成した一時ファイルはlsなどのコマンドでは見ることができません。これはMySQLが一時ファイルを作成したあと、すぐに削除してしまうためです。 LinuxなどのOSでは、ファイルを削除してもそのファイルをオープンしているプロセスがある限り実際の削除は行われません。逆に言えば、こうしておくことでプロセス終了時に一時ファイルが自動的にクリーンアップされるというわけです。lsofコマンドであればMySQLが作成した一時ファイルを確認することができます。

書かれている通りにlsofをやってみると

# lsof -p 2249
COMMAND  PID  USER   FD   TYPE             DEVICE     SIZE/OFF   NODE NAME
(省略)
mysqld  2249 mysql   39u   REG               0,39   5445255168   3534 /var/tmp/mysql.zUtW7u/ibDLGxdb (deleted)

たすかに～。/var/tmpにディレクトリを作っていてサイズが増えていっているけど、lsで/var/tmp/mysql.zUtW7uを見ても空っぽ。増える速度はおおよそ20GB/日(画像参照)。/の容量からして昨晩使い果たして落ちたという理由は合点がいく。

応急策としてiSCSIサーバから800GB融通してもらって/に追加。btrfsなのでインデックス作成終わったら外そうと思ってますが、今考えたら追加800GB単独でフォーマットして/var/tmpにマウントした方が安全だったな…

そのほかの環境