InnoDBの表領域監視 - MySQL Casual Advent Calendar 2011

こんばんはこんばんは!!
myfinder です。

MySQL Casual Advent Calendar 2011 始まりました!!
1日目は言い出した自分から書きます。

よく Casualじゃない といわれのないツッコミを受ける MySQL Casual ですが、Casual Advent Calendar という名前の通りライターの皆さん自身が気軽に書けるネタでサクっとupすればOKです。
もちろんですが、綿密な検証に基づいたガチな記事も書ける方がいたら是非お願いします。

きわどいネタは id:kamipo さんや id:do_aki さんがきっとやってくれるので、お二人にお任せしましょう。

はじめに

MySQL5.5 からは InnoDB がデフォルトストレージエンジンになりました。
4.xや5.1以前を利用している方も、今となっては InnoDB を使わないのは敢えてそれ以外を選択する必要がある場合しかないんじゃないかと思います。
なので5.1以前を使っている場合、特に理由がなければ my.cnf に

[mysqld]
default-storage-engine=InnoDB

と書いておいたほうが捗るかと思います。

今回は InnoDB を運用していくときには避けて通れない 表領域の監視 について書きます。

今回の対象

Casual な MySQL が対象なので、innodb_data_file_path=ibdata1:10M:autoextend や innodb_file_per_table しているような MySQL は対象外です。
すなわち、最初からがっつりと表領域を確保して、全 schema で ibdata を共有している、というのが前提です。

my.cnf 的には下記のような感じです。

innodb_data_file_path=ibdata1:10G;ibdata2:10G;〜ibdata30:10G

「autoextendでディスク容量監視、いっぱいになったらexport/import」みたいなゆるふわな運用の場合はできれば上記に改めたほうがより安全な運用が出来るんじゃないかと思います。

表領域の基本的な話

id:nippondanji さんの blog にあるこことここの記事で割と言い尽くされているので、読んでいない人は急いで読んできてください。

監視の話

値を取る

上記の記事で既に

mysql> SHOW TABLE STATUS LIKE 'hoge';

で取れる、これを監視して云々カンヌンという話が出ております。
これ自体は重要な値なのですが、割合が知りたい という場合もあるんじゃないでしょうか。

表領域はサーバごとに違ったりして、いちいちどこかに書いておいて毎回突き合わせるのは面倒だし、もっと自動的にやれないかなという向きもあるかと思います。

パッと思いつく手として
/var/lib/mysql/ibdata/ 以下のファイルサイズ合計で割合を出せばいいんや
というのがあるかと思いますが、それだと ibdata が Sparse file として作成された場合正しい値が取れないので割合がおかしなことになってしまいます。

じゃあどうするかというと、原始的ではありますが my.cnf を parse するのが早いかなと思います。

grep innodb_data_file_path /etc/my.cnf | perl -MList::Util -MData::Dumper -ne 'chomp $_;my @filepath = split(/\=/, $_); my @files = split(/;/, $filepath[1]); @size = map { my $chunk = $_; $chunk =~ s/ibdata\d+:(\d+)./$1/; $chunk } @files; print List::Util::sum(@size), "\n";'

こんな感じに取ってきて、上記の SHOW TABLE STATUS で取ってきた値と突き合わせればよいでしょう。
こうすれば、ibdata の数が変わっても問題なく表領域全体の容量が取得できます。
(MやGで指定している場合はbyteに直すか、show table statusで取ってきた値の方を直してあげる感じです)

これで割合を出すのも簡単にできますね。

監視するには

Nagios Plugin化

Nagios Plugin化は簡単で、上記に書いたように

SHOW TABLE STATUS して残り領域を取得する
my.cnf を parse して全体容量を取得する

という二つの処理で得られた結果を元に

## result
my $result = ceil(($data_free/$table_space)*100);

if ($result > $threshold) {
    print "[WARN]$host: InnoDB Tablespace Usage - $result %";
    exit 1;
}
print "[OK]$host: InnoDB Tablespace Usage - $result %";
exit 0;

という感じで比較して閾値を超えたら WARN すれば、表領域枯渇前に状況をキャッチアップできます。

Cloudforecastでグラフ化

通知だけでなく、日々の傾向を掴みたいというニーズももちろんあると思います。
そんな時は Cloudforecast を使って Nagios Plugin で書いた処理と同じようなものを
lib/Cloudforecast/Data/Innodb.pm
に追加するか別途作成するといいと思います。

graphs 'usage', 'InnoDB Tablespace Usage';

〜取得する処理〜

__DATA__
@@ usage
DEF:my1=<%RRD%>:Use:AVERAGE
DEF:my2=<%RRD%>:Space:AVERAGE
AREA:my1#00C000:Use  
GPRINT:my1:LAST:Cur\: %4.1lf
GPRINT:my1:AVERAGE:Ave\: %4.1lf
GPRINT:my1:MAX:Max\: %4.1lf
GPRINT:my1:MIN:Min\: %4.1lf\c
STACK:my2#0000C0:Space 
GPRINT:my2:LAST:Cur\: %4.1lf
GPRINT:my2:AVERAGE:Ave\: %4.1lf
GPRINT:my2:MAX:Max\: %4.1lf
GPRINT:my2:MIN:Min\: %4.1lf\c

などなど。

おわりに

データベースの表領域は、BBSやblog, その他ユーザからの書き込みが多く、データを残さなければいけないようなサービスをやる際には必須の監視項目だと思います。
「通知」と「日々のトレンド可視化」ができてしまえば、表領域に怯えることなく InnoDB と付き合っていけるようになるので一安心ですね。

明日は id:riywo さんです。おたのしみに!!

まいんだーのはてなブログ

はてブなのはてブロなのどっちなの