お手軽にMySQLのバイナリーログを集計するスクリプトのはなし
Tweet
こんにちは、DBAです。
おもむろにDiskの使用量がぐいっと上がって、ぎゃーなんかバイナリーログが溜まってるー、なんてことがたまにあります。
何らかの要因でサービスが大人気を博し、「おおっとこれはいきなりシャーディングの機運でござるか？ドゥフフフｗｗｗ」なんてことならうれしい限りなんですが、
∧＿∧ ﾊﾟｰﾝ （ ・∀・） はやくめをさませ！ ⊂彡☆))Д´)
まずは調べてみないとわかりませんよね。
という訳でどこが書き込みまくられているのかを調べる必要があったり、仮に本当のユーザートラフィックでドゥフフフ状態だったとしても、User Generated Contentsだとそれは特定のユーザーに依存するものだったりして裏だけは取っておきたかったりするわけです。
ということで都内のDBA 1.000人御用達のスクリプトを紹介します。
my_script/mysqlbinlog_lister.pl at master · yoku0825/my_script
やってることはシンプルで、標準出力から入力されたバイナリーログの出力を時間やテーブル単位でまとめて出力するだけです。
pt-query-digest –type binlog だとどうにも出力が多すぎてパッとみにくかったり、”# Time range: 2015-06-28 21:13:22 to 2015-06-29 14:40:48″ なんて勝手にまとめてくれちゃったりして時間の推移が見にくくてですね。。
git cloneしてきて、こんな風に使います。
$ mysqlbinlog --start-datetime-"2015-06-29 07:00:00" --stop-datetime="2015-06-29 07:59:59" mysql-bin.xxxxx | ./my_script.git/mysqlbinlog_lister.pl --cell=h --group-by="time,table" 150629 7 table_0 sum 706 150629 7 table_1 sum 1592 150629 7 table_2 sum 703 150629 7 table_3 sum 634 150629 7 table_4 sum 622 150629 7 table_5 sum 864 150629 7 table_6 sum 648 150629 7 table_7 sum 789 150629 7 table_8 sum 1112 150629 7 table_9 sum 718
まず--cell=“h” --group-by=“time,table”でざっくり見ると、あー、table_1だけなんか他のテーブルに比べて多くね？ とかいうのが判ります。table_8もちょっと多めか、とか。
–group-byは"time", “time,table”, “table”, “all” の4つの値が取れます。SQLのGROUP BYっぽい動きをします。"all"の場合はGROUP BY time, table, statementみたいな動きをします。オススメは"time,table"です(暗黙のデフォルトにしよう後で)
–cellは時間を丸める粒度を"h", “m”, “10m”, “s"から選べます。mysqlbinlog自体の–start-datetime, –stop-datetimeと組み合わせて、幅広く24時間ぶんくらいを探す時は"h”, 当たりをつけた後に時間を絞り込んで"m"とかで見ます。
$ mysqlbinlog --start-datetime-"2015-06-29 07:00:00" --stop-datetime="2015-06-29 07:59:59" mysql-bin.xxxxx | ./my_script.git/mysqlbinlog_lister.pl --cell=10m --group-by="all" | grep table_1 150629 7:0 table_1 replace 210 150629 7:1 table_1 replace 205 150629 7:2 table_1 replace 224 150629 7:3 table_1 replace 300 150629 7:4 table_1 replace 338 150629 7:5 table_1 replace 315
特に7時台のどこかでスパイクしてるわけじゃなさそうですね。ステートメントはREPLACEだけでした(スクリプト的にはINSERT, REPLACE, UPDATE, DELETEだけ対応してます)
先週分のと見比べてみましょうか。
$ mysqlbinlog --start-datetime-"2015-06-22 07:00:00" --stop-datetime="2015-06-22 07:59:59" mysql-bin.xxxxx | ./my_script.git/mysqlbinlog_lister.pl --cell=10m --group-by="all" | grep table_1 150622 7:0 table_1 replace 170 150622 7:1 table_1 replace 190 150622 7:2 table_1 replace 233 150622 7:3 table_1 replace 209 150622 7:4 table_1 replace 241 150622 7:5 table_1 replace 262
全体的に20%くらい増えてるっぽいですね。 スパムかな これからは掲示板の時代ってことですねわかりますん！
と、このくらいまであたりをつけたら、最後に直接バイナリーログからこのREPLACEステートメントをゴリゴリ抽出してやればクエリーの主まで特定できます。
お手軽にMySQLのバイナリーログの中身を確認するスクリプトの紹介でした。
