前回に引き続きpostgreSQLのお話です。
Webアプリに関わらず、データベースを活用したシステムは運用後、様々な要因で性能が劣化してしまいます。アクセス数の増加やデータ容量の増加などが主な要因です。オープンソースのpostgreSQLは性能は素晴らしいですが、如何せんORACLなどの有償の物に比べると保守・監視するツールが乏しいのが現状です。今回はこれらの現状を克服するためのDBの監視SQL文をご紹介します。
データベース容量を知っておこう
サーバーに余力がある場合、ツイツイ放ったらかしになってしまいがちです。
いざ容量を確認して青ざめる前にきちんと把握することは重要です。
postgreSQLでは容量を確認するための関数が用意されていますので、こちらで確認してみましょう。
-- データベースサイズ
select pg_size_pretty(pg_database_size('database_name'));
-- テーブルサイズ
select pg_size_pretty(pg_relation_size('table_name'));
pg_size_prettyはサイズをKBやMBに変換して見やすくする為の関数です。
容量を確認するだけでも、ログの消し忘れなどのバグやHDD増強プランなどが見えてくるのではないでしょうか。
接続ユーザー数の確認
沢山の人がサイトを訪れてくれることは嬉しいことです。しかしデータベースは悲鳴を上げることになるでしょう。postgreSQLに設定した最大接続数を超えるとデータベースに接続不可能な状態になり、最悪の場合サイトを見る事が出来なくなります。そんなことが無いように余裕をもたせた値を設定する必要があります。以下SQLで接続数を確認することが出来ます。
-- 接続ユーザー数 select count(*) from pg_stat_activity;
このSQLは今現在接続しているユーザー数ですので1回発行したぐらいでは役には立ちません。5分に一回実行してグラフ化して接続ユーザー数を見込みましょう。最大接続数は、postgresql.confのmax_connectionsで変更できます。また同時にshared_buffersを見直すことをオススメします。もし「shared_buffers >= max_connections × 2」でなければ遅延が発生しますのでshared_buffersの値を増やしましょう。
postgreSQL標準の統計情報を確認する
postgreSQLには標準で統計情報を収集しています。これらを活用しない手はありません。メモリは足りているかインデックスは効いているかなどをザックリと知ることができます。もしここで紹介するSQLで値がとれない場合は設定を見直してください。
以下SQLでアクセス頻度の多いテーブルを順に表示することができます。
-- アクセス頻度の多いテーブル
select
relname,
coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0)+
coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)+coalesce(n_tup_del,0) as total,
coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0) as select,
coalesce(n_tup_ins,0) as insert,
coalesce(n_tup_upd,0) as update,
coalesce(n_tup_del,0) as delete
from pg_stat_user_tables
order by total desc;
このSQLでselect、insert、update、deleteが多いテーブルがどれか知る事ができます。
次のSQLはI/O回数表示しヒット率が悪い順に表示します。
-- I/O回数＆キャッシュヒット率
select
*,
(heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) as ritu
from pg_statio_all_tables
where heap_blks_hit >= 1
and schemaname = 'public' order by ritu;
こちらで取得したデータの中に「ritu」というカラムがありますが、これがキャッシュヒット率です。ちなみにキャッシュヒット率とは必要な情報がキャッシュに存在したかどうかとうの確率です。実運用しているデータベースでしたら80％以上になるのが好ましいです。著しくヒット率が低い場合はメモリ不足か非効率なデータが大量にある可能性があります。
次のSQLはインデックスが有効に働いているか確認するSQLです。
-- インデックス利用回数 select * from pg_stat_user_indexes ;
もし「idx_scan」「idx_tup_read」「inx_tup_fetch」が0。という場合はそのインデックスは全く使われていません。再検討しましょう。
※ 8.2以前はstats_row_leveを8.3以降はtrack_countsを有効にしてください。
最後に統計情報をクリアする為のSQLです。
-- 統計情報クリア select pg_stat_reset();
もし定期的に統計情報を取得しデータを保存するのであれば統計情報をクリアしてあげましょう。
健康状態を見える化しましょう
ご紹介したSQLで取得した情報は、常に監視しておくと良いと思います。
もしサーバー監視ツールを採用されているのでしたら、一度これらの情報も組み込んでみてはいかがでしょうか？
参考
［PostgreSQLウォッチ］ – ITpro
http://itpro.nikkeibp.co.jp/article/COLUMN/20051005/222300/?ST=oss&P=3
