Vimとボクと、ときどき、PHP

Vim好きなエンジニア(主にPHP開発)が不定期に技術情報やネタを書いていくブログです

Home » MySQLのスロークエリーログを掃除する

MySQLのスロークエリーログを掃除する

MySQLには、実行されたクエリを出力する「一般クエリーログ」や、実行完了するまでに一定以上の時間がかかったクエリを出力する「スロークエリーログ」といった、ログ出力を行う機能があります。そして、それらのログの出力先をテーブルにすることができ、後でログを参照する際にSQLによる絞り込みができるため、遅いクエリを特定したい場合などには非常に便利です。

ただ、ログは放っておくとどんどん溜まっていき、ディスク容量を圧迫するもとになりますので、定期的に掃除してやる必要があります。
ログの出力先をファイルにしておけば、logrotateなどで世代管理しつつ古いログを削除していくことができますが、テーブルにしている場合は溜まっていく一方です。「そんなのDELETE文を実行するバッチを書いてCronで定期的に実行してやればいいじゃん」と思うかもしれませんが、実はそこに落とし穴があり軽くハマりましたので、今回の記事のテーマにしているわけです。

概要

バッチの処理内容は下記の通り。単純なもんです。

  • 1日1回、Cron実行される。
  • スロークエリーログの古いレコードを削除する。(バッチ実行時点から1週間前までのレコードを残してそれより古いのは消す)
  • 削除したレコードをファイルに保存したりすることは、しない。

ぶつかった壁

最初は以下のようなシェルスクリプトを書きました。

ワンライナーでスッキリ簡単に書けたZE!とか思いながら、意気揚々とテスト実行してみたところ、なんかエラーが出たんですよ・・・。そして、ログは消えてなかったんですよ・・・。

原因は何?

MySQLリファレンスのこのページに書いてございました。
その部分を抜粋させていただくと、

INSERT、DELETE、および UPDATE をログテーブル上で使用することはできません。これらの操作は、サーバー自体の内部でのみ許可されます。

だそうです。つまり、ワンライナーでウハウハしようと思ってもダメということですね。

なお、

TRUNCATE TABLE は、ログテーブル上での有効な操作です。ログエントリを期限切れにするために使用できます。

こう書いてありますので、ログを全削除する場合は上記のやり方でもいけるのかもしれません。(試していません)

方法はあるのか?

困ったときは、とりあえずマニュアルを読むに限ります。
上記のページをよく読みますと、解決策が見えてきました。

まずは関連する部分を引用します。

CREATE TABLE、ALTER TABLE、および DROP TABLE はログテーブル上での有効な操作です。ALTER TABLE および DROP TABLE の場合、ログテーブルは使用中であってはならず、あとで説明するように無効にする必要があります。

ふむふむ、ログを一時的にでも無効にすれば、テーブルに対して変更を加える操作ができるのね。

ログテーブルを変更 (または削除) できるようにロギングを無効化するには、次の方法を使用することができます。この例では一般クエリーログを使用しており、スロークエリーログについての手順も類似していますが、slow_log テーブルおよび slow_query_log システム変数を使用します。

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;

ほうほう、これでgeneral_logテーブルに対してALTER TABLEを使えるわけですか。

RENAME TABLE は、ログテーブル上での有効な操作です。次の方法を使用して、(たとえばログローテーションを実行するために) ログテーブルを原子的に名前変更できます。

USE mysql;
DROP TABLE IF EXISTS general_log2;
CREATE TABLE general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;

へぇ、ログテーブルの名前は変更してもいいのね。

と、ここまで読んだところで、解決できそうな方法が見えてきました。つまり、

  1. スロークエリーログを一時的に無効にする
  2. ログテーブルの名前を変更する
  3. 名前を変更したログテーブルに対してDELETEを実行してやる
  4. ログテーブルの名前を戻す
  5. スロークエリーログを再度有効にする

この手順でやればできるんじゃないですかね?

解決策

シェルスクリプトをこんな風に変更してみました。

例で書いてあったことの合わせ技です。

結果は、うまく目的のレコードを削除することができました。一時的にログを無効にしているため、その瞬間に遅いクエリが実行されたらログに残らないことになりますが、そのへんは妥協することにしましょう。

ちなみに

MySQLリファレンスには一般クエリーログの例が書かれておりましたので、スロークエリーログを対象とするように適宜書き換えてやらねばなりませんでした。
その中で、general_logという変数をslow_query_logに書き換えていますが、この名前が最初分かりませんでした。「slow_logかな?」と思ってそうしてみても、エラーになったり。

ですので、変数名を確認する方法を書いておきますと、以下のコマンドで一応見れます。

USE mysql;
SHOW VARIABLES LIKE 'slow%';

これを実行しますと、

+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_launch_time    | 2                               |
| slow_query_log      | ON                              |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+---------------------+---------------------------------+

だいたいこんな感じで、該当する変数名が出てきます。ただ、変数名にある程度の見当がついていないと、LIKEによる絞り込みができないので探すのが大変になります。

Name of author

Name: よーすけ

Short Bio:

主にPHP開発をやってる社内PGです。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です