Grails+postgresでDBチューニング

Grails+postgresでのチューニングで自分がやっている方法をざっくりとまとめ

・postgresからスロークエリの検出をする

postgresql.conf に設定

log_min_duration_statement = 5000ms

上記だと5秒以上かかった処理をログに書き出してくれます。
Hibernateから実行したクエリは以下のような感じで書き出されます。

LOG: duration: 7912.798 ms statement: EXECUTE [PREPARE: select book0_.id as book_1_ from BOOK book0_ where book0_.id=$1]

・クエリのコストを検出する

ログに出力されたクエリが実際にDBで検索にかかるコストを算出します。
SQLの部分( select ~）の前に explain をつけて実際にクエリを実行します。
（$1,$2等は実際の値に置き換えて下さい。）

explain select book0_.id as book_1_ from BOOK book0_ where book0_.id=1

すると以下のような情報が帰ってきます。

Seq Scan on book this_ (cost=0.00..32955.18 rows=1 width=2104)
Filter: (id = 1)

0~32955のコストが予想されています。

・indexによるチューニングを行う

10000を超えるようなコストはかなりの負荷になりますので、indexをつけてやります。
（実際にはidにindexは必要ありません、検索対象となる名称とかに置き換えて下さい）

CREATE INDEX id_idx
ON book
USING btree
(id);

再度 explain 付きのクエリを実行すると、以下のような結果が帰ってきます。

Bitmap Heap Scan on book this_ (cost=4.64..98.43 rows=1 width=2104)
Recheck Cond: (id = 1)
-> Bitmap Index Scan on id_idx (cost=0.00..4.64 rows=1 width=0)
Index Cond: (id = 1)

検索対象のフィールドにIndexがついていたため、indexを使って検索し、総コストが 4.64~98.43 となります。
これであればサクサクと検索が実行されます :)

postgresを対象に書きましたが、MySQLでもOracleでもexplainはありますので同じような流れでチューニング可能です。

クエリチューニングについて本気出して考えてみた

Grailsでアプリを作っていると、O/Rマッピングに頼りっぱなしで、重い所だけたまにHQLを書いていたのだが、
さすがにDBの規模が大きくなるとそうも言ってられないよなーと思い、クエリチューニングに乗り出す事にした。
（indexの最適な付け方も良くわからないし :D ）

まずは現在のボトルネックを探す。
今までは体感とシステム上で時間を計測してたけど、DBの事はDBに聞けって事でログ解析をする事に。
（ここらへんを参考にした）

postgresのログ解析には pgfouine と pqa というのがあるらしい、前者は php、後者は ruby だ。
これはもちろん pgfouine だろう！ php も ruby もよく知らないけど groovy 好きっ子としては ruby に行ったら負けかなと思っている。

ITProの記事を参考に、postgres のログを出力する設定をする

んで pgfouine を実行・・・！しようと思ったら動かない。GDというライブラリが必要らしい。
Macにデフォで入っている php にはこれが組み込まれていない。調べてみると MacPorts でインストールすると簡単らしい。
ということで MacPorts から php をインストール、、、長い、、、長い、、、しかもCPUフルに使いやがる、そういう事は先に言ってくれと。

無事インストールが終わった所で pgfouine を起動してみる。

php pgfouine.php -logtype stderr -file log/postgresql-xxxx-xx-xx_xxxxxx.log -format html-with-graphs -report vanfu_sql.html=overall,hourly,bytype,slowest,n-mosttime,n-mostfrequent,n-slowestaverage,n-mostfrequenterrors,tsung

とりあえず出力オプション全部付けてみた :p



おー、なんかかっこいいですね！
ちょっと試しで３分ほど適当にシステムを稼働させて集計した結果、SELECT件数が 4,179、、、うーんO/Rマッピングが遅いと言われるのがわかります。
まあ逆に考えれば1クエリ0.01秒の改善でも3分あたり40秒もの改善になるって事ですね。

とりあえず現状の確認方法まで
次回はGrails向けのチューニング方法をまとめようと思います。

人生に役立つかもしれないpostgresについての知識

今日もまたよくやるけど、よく忘れるメモ

postgresで外部から接続出来るようにする（ローカル内のpgAdminとかからサックリ操作出来るようにする）

pg_hba.conf でアクセス制限をつける。
下の方にある行を書き換える

サンプル

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         192.168.0.0 255.255.255.0 trust

日本語訳：とりあえず 192.168.0 シリーズからと、ローカルからなんでもばっちこいやー

postgresql.conf の設定

#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on; 
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all

の部分の listen_addresses がデフォだと localhost になるんで、ここのコメントアウトをはずし、 * （すべて許可）にする

で、postgresを再起動してやればOK