pg_statsinfo 1.2

プロジェクトのTopページへ

pg_statsinfoの概要

pg_statsinfoは、3つのモジュールから構成されています。 監視対象DBとスナップショット管理DBは以下の様な関係にあります。pg_statsinfoは、「複数」の「異なるホスト」に存在するDBの情報を 一元的に集約できるようになっています。
注意:現状のpg_statsinfoは、複数の「スナップショット管理DB」を用意し、単一の監視対象DBから情報を取得することができません。



監視対象DBとスナップショット管理DBは、同じPostgreSQLインスタンス(DBクラスタ)に同居 していても構いません。以降は、全ての機能が同居している構成でのインストール方法と使い方を記述していきます。

どんな情報が取得できるのか?

主な取得可能情報を以下に示します。

・pg_statsinfo-1.0 (8.3対応ver)

・pg_statsinfo-1.1 (8.4対応ver) (上記に加え、以下の情報を取得)
以下は、取得したスナップショットをレポート化したものの一部分です。

----------------------------------------
DB Activity Information
----------------------------------------
Database Size              : 52440684
Connection Average Number  : 1
Database Frozen Xid(Age)   : 5233
Commit Number              : 5049
Rollback Number            : 0
Block Read                 : 921
Block Hit                  : 106000
Cache Hit Ratio            : 99.14
Return Tuples              : 197067
Fetched Tuples             : 18468
INSERT Tuples              : 5006
UPDATE Tuples              : 15007
DELETE Tuples              : 0
Total User Table Number    : 4


---------------------------------------------------------------------
Instance Disk Usage Information
---------------------------------------------------------------------
Space_name      Device_ID  Device_name          Location                                       USED        AVAIL    TOTAL    RATIO
---------------------------------------------------------------------------------------------------------------------------------
pg_default      2080      /dev/sdc              /array1/data                                   112 GB      474 GB   586 GB   19.16
pg_global       2080      /dev/sdc              /array1/data                                   112 GB      474 GB   586 GB   19.16
<archive_dir>   2097      /dev/sdd1             /var/lib/pgsql/archive_dir/                    32 MB       952 MB   984 MB   3.25
<pg_xlog>       2051      /dev/root             /array1/data/pg_xlog                           27 GB       38 GB    64 GB    41.82


---------------------------------------
Fragmented Table worst 10
---------------------------------------
Table name                        dead record   live record   dead record ratio
-----------------------------------------------------------------------------
accounts                          4476          300000        1.49
pg_statistic                      7             361           1.94
pg_amop                           0             347           0.00
pg_type                           0             289           0.00
pg_proc                           0             2164          0.00
pg_class                          0             239           0.00
pg_shdepend                       0             1             0.00
pg_cast                           0             191           0.00
pg_namespace                      0             6             0.00
pg_index                          0             93            0.00


---------------------------------------
Much Access Table Top 10
---------------------------------------
Table name                        seq scanread  records       records per scan
--------------------------------------------------------------------------------
tellers                           5462          150000        27.46
branches                          8088          15003         1.85
pg_class                          23            5497          239.00
pg_attribute                      2             3696          1848.00
pg_index                          5             465           93.00
pg_database                       10            41            4.10
pg_am                             8             8             1.00
pg_tablespace                     2             4             2.00
accounts                          0             0             0.00
pg_authid                         1             0             0.00


情報量はどれくらいになるのか?

pg_statsinfoは、スナップショット情報をPostgreSQLのテーブル情報として蓄積します。1回のスナップショット取得で、 おおよそ 500kB - 600kB 程度の情報量となります。ユーザテーブルが多くなると、若干増加します。お勧めは、30分に1回程度のスナップショットの 取得を行い、毎日のレポート作成をしておくと良いです。

スナップショットの取得やレポート取得の処理は重いのか?

どちらも軽い処理です。DBT-1、TPC-Wの2つのベンチマークで、1600TPS以上の負荷時にも性能への影響なく 情報の取得やレポート取得が可能でした。ただし、一般的にレポート処理は夜間等の低負荷時に実施するほうが良いでしょう。

使用における前提条件

動作環境

pg_statsinfoは、以下の環境で動作確認をしています。

必要となるモジュール

PerlモジュールのText:Template、Perl-DBI、Perl-DBDがレポート作成機能に必要となります。それらがインストール済みか確認して下さい。
-- 確認例 --
$ rpm -qa | grep Text-Template
perl-Text-Template-1.44-1.2.el5.rf
$ rpm -qa | grep DBD
perl-DBD-Pg-1.49-1.fc6
$ rpm -qa | grep DBI
perl-DBI-1.52-1.fc6
もしなければ、それぞれのモジュールを取得しインストールします。下記はRPMパッケージの入手先とインストール例です。 (pg_statsinfoをRPMでインストールする場合は、それぞれのモジュールをRPMでインストールして下さい)
# rpm -i perl-Text-Template-1.44-1.2.el5.rf.noarch.rpm
# rpm -i perl-DBD-Pg-1.49-1.fc6.x86_64.rpm
# rpm -i perl-DBI-1.52-1.fc6.x86_64.rpm

インストールと各種準備

インストール方法などについてはソースとRPMパッケージの2通りがあります。

スキーマについて

pg_statsinfo独自のテーブルやビュー、関数などは、全て"statsinfo"スキーマ(スナップショット管理DB)と"targetdb"スキーマ(監視対象DB)に作成されます。 そのため、pg_statsinfoのテーブル等にアクセスする際は、"statsinfo.tablename"の様にスキーマを明示するか、 pg_statsinfo用のスーパーユーザに対し、下記のコマンドでsearch_pathに"statsinfo"スキーマを加えておくと良いです。
psql -d <スナップショット管理DB>
ALTER USER user_name SET search_path = statsinfo, "$user", public;
また、監視対象DBにも接続用のユーザを作成しておきます。スナップショット取得時に、スナップショット管理DBから監視対象DBへアクセスするためです。スーパーユーザ権限が必要です。
$ psql -d <監視対象DB>
CREATE ROLE some_user SUPERUSER;

スナップショットの取得

監視対象DBへの接続情報の登録

スナップショット管理DBへ接続し、監視対象DBへの接続情報を登録します。接続情報は、"statsinfo.t_dbconn"テーブルへの INSERT処理で登録します。
t_dbconn の各カラムの説明
       Column       |  Type   | description
--------------------+---------+-------------------
 dbid               | bigint  | DBのIDです。シーケンスになっており、INSERT時は'default'を指定して下さい。
 dbname             | text    | 監視対象のDB名です。
 hostname           | text    | ホスト名です。IPかホスト名を指定します。
 port               | integer | DBクラスタのポート番号です。通常は5432です。
 username           | text    | 監視対象DBへ接続するユーザ名です。スーパーユーザ権限が必要です。
 password           | text    | 監視対象DBへの接続パスワードです。暗号化されて格納されます。
 is_snapshot_target | boolean | 一括スナップショット取得の対象にするかのフラグです。詳細は後述。
以下は、"localhost"の ポート"5432"のDBクラスタの"target"データベースへ、"postgres"ユーザで"passwd"というパスワードで接続するための設定方法です。
INSERT INTO statsinfo.t_dbconn VALUES (default, 'target', 'localhost', 5432, 'postgres', 'passwd', 't');
INSERT 1 0

スナップショットの取得

スナップショットの取得は、ストアドプロシージャの"statsinfo.get_stats()"関数で実施します。get_stats()関数は4種類の 引数を指定できます。 以下は、引数なしと、DBID=1のDBを対象にしたスナップショットの取得例です。
$ psql stats
=# SELECT statsinfo.get_stats();
 get_stats
-----------
 t
(1 row)

=# SELECT statsinfo.get_stats(1);
 get_stats
-----------
 t
(1 row)
成功すれば't'が返ります。なお、DBIDは以下のSQLで確認できます。
=# SELECT dbid, dbname, hostname, port, username  FROM statsinfo.t_dbconn;
 dbid | dbname |   hostname   | port | username
------+--------+--------------+------+----------
    1 | target | localhost    | 5432 | postgres
    2 | user1  | 172.20.16.99 | 5433 | admin

スナップショット情報の確認

スナップショット情報の確認は、"snapshot_history"ビューで確認可能です。スナップショットID(snapshot_id)と、 スナップショット取得日時(snapshot_date)は、レポート作成時やスナップショットの削除時に指定する必要があります。 その際はこのビューで確認して下さい。
=# SELECT * FROM statsinfo.snapshot_history ;
 snapshot_id |     snapshot_date      | hostname  | dbname | port | level | description
-------------+------------------------+-----------+--------+------+-------+-------------
           1 | 2009-03-04 15:20:02+09 | localhost | target | 5432 |     0 |
           2 | 2009-03-04 15:20:32+09 | localhost | target | 5432 |     0 |
           3 | 2009-03-04 15:20:33+09 | localhost | target | 5432 |     0 |

レポート取得

レポートの取得は、pg_make_report.plで行い、DB毎に1つのレポートが出来上がります。テンプレートファイルで出力をカスタマイズできます。 オプションは以下の通りです。
pg_make_report.pl [connection options..] [snapshot range options] [template option] [DB option] オプションについては下記の点に注意して下さい。 テンプレートファイルについては、以下の2種類のものを用意しています。PostgreSQLバージョンが8.4系の場合は*84.tmplを使用して下さい。 以下は、代表的なレポートの生成実施例です。
# id指定
pg_make_report.pl --host=localhost --dbname=stats --port=5432 --user=postgres --output-prefix=/tmp/report.log \
 --startid=1 --endid=10 --template=$REPORT/pg_statsreport.tmpl
# 時間指定
pg_make_report.pl --host=localhost --dbname=stats --port=5432 --user=postgres --output-prefix=/tmp/report.log \
 --startdate='2009-03-01 10:00:00' --enddate='2009-03-02 10:00:00' --template=$REPORT/pg_statsreport.tmpl
# 時間指定とDBID指定
pg_make_report.pl --host=localhost --dbname=stats --port=5432 --user=postgres --output-prefix=/tmp/report.log \
 --startdate='2009-03-01 10:00:00' --enddate='2009-03-02 10:00:00' --targetdbid=1 --template=$REPORT/pg_statsreport.tmpl
# 時間指定とホスト、ポート番号、DB名指定
pg_make_report.pl --host=localhost --dbname=stats --port=5432 --user=postgres --output-prefix=/tmp/report.log \
 --startdate='2009-03-01 10:00:00' --enddate='2009-03-02 10:00:00' --targetstring=localhost:5432/target_db --template=$REPORT/pg_statsreport.tmpl
レポートの見方については、こちらでサマリ情報の見方を解説しています。

スナップショットの削除

古くなったスナップショットは定期的に削除します。削除にはstatsinfo.delete_stats()関数を使用します。delete_stats()に 削除対象のスナップショットIDを指定すると削除されます。成功すると't'を返します。
=# SELECT statsinfo.delete_stats(11);
 delete_stats
--------------
 t
(1 row)
ある期間のものをまとめて削除したい時は、snapshot_history ビューと組み合わせて下さい。

# ある日時(下記の例では '2009-03-04 15:21:00')より古いスナップショットを消したい時。
=# SELECT * FROM statsinfo.snapshot_history ;
 snapshot_id |     snapshot_date      | hostname  | dbname | port | level | description
-------------+------------------------+-----------+--------+------+-------+-------------
           1 | 2009-03-04 15:20:02+09 | localhost | target | 5432 |     0 |
           2 | 2009-03-04 15:20:32+09 | localhost | target | 5432 |     0 |
           3 | 2009-03-04 15:20:33+09 | localhost | target | 5432 |     0 |
           4 | 2009-03-04 15:20:40+09 | localhost | target | 5432 |     0 |
           5 | 2009-03-04 15:21:35+09 | localhost | target | 5432 |     0 |
(5 rows)

=# SELECT statsinfo.delete_stats(snapshot_id) FROM statsinfo.snapshot_history 
   WHERE snapshot_date <='2009-03-04 15:21:00';
 delete_stats
--------------
 t
 t
 t
 t
(4 rows)

=# SELECT * FROM statsinfo.snapshot_history ;
 snapshot_id |     snapshot_date      | hostname  | dbname | port | level | description
-------------+------------------------+-----------+--------+------+-------+-------------
           5 | 2009-03-04 15:21:35+09 | localhost | target | 5432 |     0 |
(1 row)

アンインストール

アンインストール時には、「スナップショット管理DB」のstatsinfoスキーマと「監視対象DB」のtargetdbスキーマを削除します。 uninstall_stats_info.sql、uninstall_target_db.sqlを使います。
## RPMでインストールした場合
$ psql -f /usr/share/pgsql/contrib/pgstatsinfo/uninstall_stats_info.sql -d stats
$ psql -f /usr/share/pgsql/contrib/pgstatsinfo/uninstall_target_db.sql -d target
ログフィルターを使用している場合は、PostgreSQLを一度停止させ、ログフィルター無しの状態でPostgreSQLを起動させます。 RPMで導入している場合は、rpm -e コマンドでRPMをアンインストールします。 以上でアンインストール作業は終了です。

pg_statsinfoの制約と注意点

変更履歴


プロジェクトのTopページへ