今回はSQLチューニングのお話です。
まずは重い処理を見つけるための方法です。
開発時点で効率の抜群なSQLが作れたらよいのですがそうもいきません。
また、想定外に一部のテーブルが肥大化して処理が重くなってしまうこともあります。肝心なことはそのSQLでアプリを利用する方が不快な思いをする前に問題のあるSQLを探し出し、前もってチューニングすることです。
pgFouineとは
PostgreSQLから出力されたログを解析するツールです。
解析結果をレポートにしてWEBに表示することが出来るので、ログを黙視でみるより遥かに楽ができます。
必要なもの
PHPがインストールされているマシン
マシンのroot権限
インストール
pgFouine – a PostgreSQL log analyzer
上記のサイトからダウンロードします。
Download pgFouine 1.×というところから好みのファイルをダウンロードして展開します。展開する場所はどこでも構いませんがWEBで表示することからWebのルートパスが通っているところ付近が簡単で良いと思います。
展開したディレクトリの中にpgfouine.phpというファイルがあります。
このファイルが本体になります。コマンドラインで簡単に実行できるようにプログラムの1行目にPHPのパスが書かれています。
#! /usr/bin/php -qC
この部分を環境に合わせてください。
Postgresの設定を変更する
pgFouineが解析可能なログを出力する必要があります。
私が利用しているのはPostgreSQL8.3なのですが、次のように設定しています。
postgresql.conf
log_destination = ’syslog’ redirect_stderr = off silent_mode = on log_line_prefix = ‘%t [%p]: [%l-1] ‘
また、出力したいSQLを指定します。
log_min_duration_statement = 3s
この設定で3秒以上掛かったSQLをログに残します。
この数値はマシンスペックと相談して決めてください。
もしローカル開発環境で利用するのであれば「0」で全てのSQLを出力してしまってもいいかもしれません。
実際にサービスが稼働しているマシンであれば始めは少し大きめの値にしておいて、徐々に値を小さくしていくのが良い方法です。だいたい平均の処理時間から30％ほどの時間に設定してあげると良いそうです。
syslogの設定
syslog.confを設定します。
local0.* -/var/log/pgsql *.info;mail.none;authpriv.none;cron.none;local0.none /var/log/messages
何をしているかというとログの出力先を設定し、PostgreSQLのログイン情報をmessagesに出力しないようにしています。
これで動作環境は整いましたのでそれぞれのサービスを再起動させてログが増えるのを待ちます。
pgFouineを使ってみる
pgFouineはバッチプログラムです。以下のようにコマンドで実行することができます。
./pgfouine.php -file /var/log/pgsql > sample_default.html
これでsample_default.htmlという解析レポートが出来上がります。
ただ、このツール結構処理が重いですのでリアルタイムの監視には向きません。
夜中CPUが空いている時間に流してレポートを日付単位に出力するなど工夫が必要です。
