InnoDB の圧縮を使うときの運用

mysql solaris memo

.@nippondanji さんにブログにまとめろと言われた気がするのだけど、あんま大したネタではないです。しかも、この作業は失敗する可能性を見越していたのであまり作業ログを取ってなかった...。

ので、ちょっと疑問に思った点を幾つか書いておこうかと思います。

でかい InnoDB なテーブル

とあるテーブルが大きくなってしまい、運用がめんどくさくて困ってました。

mysql> show create table hoge\G
*************************** 1. row ***************************
       Table: hoge
Create Table: CREATE TABLE `hoge` (
  `date` date NOT NULL,
  `key` int(10) unsigned NOT NULL,
  `value` double NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`,`key`),
  KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin

pk に突っ込み入りそうな気もするけど、アプリごとテーブル構造を作り直すのは別件で考えるとして、たったの 12 億レコードくらいで 120GB くらい行っちゃうんですね。いや、だいたい計算どおりではあるんだけど。

べつに text があるわけでもないので、これを圧縮したところで改善するとは思えなかったんだけど、頑張って Antelope から Barracuda に変えました。 KEY_BLOCK_SIZE は 8 です。

alter table

すなおに

ALTER TABLE hoge ENGINE=InnoDB ROW_FORMAT=compressed KEY_BLOCK_SIZE=8;

とやってあげました。しかし、終わんない。とにかく終わんない。

結果、ひたすら待って 120 時間くらいかかりました。 ALTER は 1 CPU (スレッド) しか使ってくれないので、他の人が暇そうにしてるのが残念です。

ZFS で hybrid storage の威力を実感する

このデータベースはふだん Linux 上のそんなにディスク容量がないマシンに置いてあるものなんですが、そこの上で ALTER するのは無理があると思ったので、別のサーバに移して作業しました。

環境は Solaris11, 3 面 mirror な udev の 14 stripe に Sun Flash F20 を log x3, cache x1 と振り分けてました。 MySQL は Solaris Studio 12.3 で -xtarget=native でこんぱいるした 5.5.10 。メモリ 128GB 中 64 GB を InnoDB に、のこり 64 GB を ZFS に渡してあげたかんじ。

ということで iopattern (Solaris11 では DTrace Toolkit は /usr/dtrace/DTT に移動しましたよ) とかで見てるやると、無難に Random I/O が大変そう。

そこで、始めちゃった ALTER を止めるわけではなく、すこしでも I/O 負荷を下げてあげるべく ZFS の cache に入れていたフラッシュディスクをログデバイスに移動します。これは MySQL の知る世界ではないので、動かしたままやればいける。

% sudo zpool remove tank c7t1d0; sudo zpool remove tank c7t2d0
% sudo zpool add tank log c7t1d0 c7t2d0

zpool status -v を見てると、3つの log device に 50-60MB/s くらい書いて、適当なタイミングで 1GB 弱をディスクに書いているようです。 1 つのときより log に書いてる量が増えてるので、ちゃんと調査してないですが速くはなっているはず。

結果

120GB くらいあったテーブルが 57GB くらいに減ってくれました。カラム名から想像できるとおり、圧縮しても日常の SELECT の負荷はそんな気にしなくて良いです。 bulk で insert したとき遅いのは気にすべきだけど、まあなんとか。

気になるところ (1)

私が大好きな MyISAM では、こうやって頑張って圧縮したテーブルをファイルベースでコピーしてあげれば、圧縮コストは1回で済みます。 ZFS なら snapshot とって send するとかすればいいんです。しかし InnoDB だとそうは行きません。 innodb_file_per_table=1 にしたところで、 frm と ibd をコピっただけじゃ動かないわけです。

なので、元のサーバに移すために mysqldump なんてやってしまったら台無しです。なので、元のサーバにはデータディレクトリまるごと戻せるような状態にまでしないといけません。

そんなとき役に立つはずなのが、有償の InnoDB Hot Backup のはずです。 .@meijik さんによると、 MyISAM のとき同様にファイルベースでコピーしてうまいことやってくれるもののようです。

が、ファイルごとコピーするにしても backup 以外にもいろいろやりたいことはあるので、実際のところどういう運用が可能になるのかわからないんですよね。良いツールだと思うので、どっかにいい事例紹介とかないのかな。

気になるところ (2)

実際のところ、これで「サイズが大きい」問題が解決する理由は ibd ファイルを gzip したのと同じようなもんです。なので、たとえば standby 的な slave で定期的に mysqld とめて datadir を tar.gz して保存しておく場合、 tar.gz のサイズがほとんど小さくならないことに気をつける必要があります。

ALTER 直後は覚えてるからいいんですけど、今後またデータが増えて行ってまた分割なりなんなりを考えなきゃいけなくなったときに、(空きスペース的に) まったく身動き取れない状況に陥るリスクが有ることを見越しておく必要があります。

気になるところ (3)

ちょっと話はそれますが、 ALTER 中に他の CPU が暇そうにしてると、思わず pbind してやりたくなるのが人情ってものです。しかし、 MySQL の thread id と LWP の id の対応がよく分からない。開いてるファイルとか truss してやれば「だいたいこれだな」と目星はつきますが、正しく知る方法っていいのないのでしょうか。

まとめ

I/O サイズを減らす目的ではなく、純粋にテーブルサイズが大きくなった時の逃げ道として InnoDB の圧縮を考える場合、無駄なデータが沢山入ってるなどの「本質的に圧縮が効く」場合で無い限り、けっこう運用負荷が上がるだけなんじゃないかなー、と思ったのでした。