pg_statsinfo 1.2

Summary of pg_statsinfo

pg_statsinfo consists of 3 modules.

target_db module : Installing in monitoring target DB (Traget DB). It makes some Views to getting necessary information
stats_info module : Installing in snapshot management DB (Snapshot DB). It perfomes getting snapshot and store it.
reporter module : For generating reports from snapshot

Monitoring target DB and snapshot management DB are in the following relations. pg_statsinfo can collect "multi" DB snapshot on "multi host".

You can set Target DB and Snapshot DB in same DB Cluster. From now on, tutorials are written with an assumption that above two DB are in same DB cluster.

Acquirable information

Mainly information are following items

-- pg_statsinfo1.0 (for PG8.3)

DB load profile
Fragmented table Worst 10
Much Access Table Top10
Tables density
Device usage of each tablespace、pg_xlog、archive_dir
Long transaction processes
autovacuum、checkpoint information
Statistics information of all tables and indexes

-- pg_statsinfo1.1 (for PG8.4) (as well as the above, pg_statsinfo will acquire the following information.)

Long time ran & Many executed SQL Top 10
Many executed function Top 10
Fetched rows per SQL

Following is a part of report file.


----------------------------------------
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

Snapshot information size

pg_statsinfo stores the snapshot information in PostgreSQL Tables. One snapshot size is about 500kB - 600kB. This size will increase with increase number of user table. You are recommended to get a snapshot per 30min, and generate report each day.

Load of snapshot acquisition and generate report

Their processing are lightwight. We could get snapshot and generate report during benchmark-test (TPC-W 1600tps) without influence on performance. But You are recommended to generate reports in low load time.

Prerequisite for using

Requirements

pg_statsinfo operation check is done on following environment.

OS : RedHat Enterprise Linux 5(i386、x86_64)
DBMS : PostgreSQL 8.4.x、PostgreSQL 8.3.x
Others : Perl-Text-Template-1.44、Perl-DBD-Pg-1.49、Perl-DBI-1.52

Necessary modules

Reporter module requires Perl-Text:Template、Perl-DBI、Perl-DBD. Please check whether they are installed or not.

-- check exapmle --
$ 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

If they are not installed, please get sources or RPM packeages and install them. You can get RPM packages from following Link. (If you want to install pg_statsinfo by RPM package, you have to install perl modlues by RPM package.)

Text-Template-1.44
Perl-DBD-1.49 is in RHEL Install CD,or this URL
Perl-DBI-1.52 is in RHEL Install CD,or this URL

# 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

Install and setup

You can have a choice between using source or using RPM package.

About schema

pg_statsinfo make own tables, views and functions in "statsinfo" schema on snapshot store database and in "targetdb" schema on monitoring target database. So, you have to access these objects with schema name like "statsinfo.t_db_conn". Or please add "statsinfo" schema to search_path of statsinfo user like following command.

psql -d 
ALTER USER user_name SET search_path = statsinfo, "$user", public;

Then, create role with superuser-privilege on monitoring-target-DB.

$ psql -d 
CREATE ROLE some_user SUPERUSER;

Getting snapshot

Register the connection information to Target DB into Snapshot DB

Connect to Snapshot DB, and regist the connection information to Target DB by inserting information into "t_dbconn" table.

t_dbconn descrition
       Column       |  Type   | description
--------------------+---------+-------------------
 dbid               | bigint  | DB's ID. It is sequence. Please set "default" on inserting.
 dbname             | text    | Target DB name.
 hostname           | text    | Host name. Set IP or name
 port               | integer | Port number of DB Cluster. Normally 5432.
 username           | text    | User who connect to Traget DB. Required superuser-privilege.
 password           | text    | Password to connect Target DB. It is stored with encryption.
 is_snapshot_target | boolean | Flag whether targte the automatic snapshot or not. As will hereinafter be described in detail.

Follwoing example is user "postgres" will connect to "target" DB on DB cluster active on "5432" on "localhost" host by using "passwd".

INSERT INTO statsinfo.t_dbconn VALUES (default, 'target', 'localhost', 5432, 'postgres', 'passwd', 't');
INSERT 1 0

Get snapshot

Getting snapshot is executed by "statsinfo.get_stats()" procedure. get_stats() can have four different argments.

SELECT get_stats() -- no-args -- Getting snapshot for each DB that is_snapshot_target is 't'.
SELECT get_stats(DBID) -- dbid -- Getting snapshot for DB relevant specified dbid.
SELECT get_stats('description') -- description -- Add description to snapshot. Getting snapshot target DB is same as no-arges specified.
SELECT get_stats(DBID, 'description') -- dbid, description -- Add description to snapshot. Getting snapshot target DB is same as dbid specified.

Following example is getting snapshot with no-args specified and dbid specified.

$ psql stats
=# SELECT statsinfo.get_stats();
 get_stats
-----------
 t
(1 row)

=# SELECT statsinfo.get_stats(1);
 get_stats
-----------
 t
(1 row)

Return 't' if successed. DBID is confirmed by following 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

Check history of taken snapshot

Snapshot history is confirmed by using "snapshot_history" view. "snapshot_id" and "snapshot_date" information are required when you generate report or snapshot deletion.

=# 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 |

Generate report

Generate report is executed by using "pg_make_report.pl". One report is generating for each DB. You can customize outoput information. pg_make_report.pl options are following..
pg_make_report.pl [connection options..] [snapshot range options] [template option] [DB option]

connection option(essential other than output-prefix )

--host=host_name # Host name has Snapshot DB
--dbname=db_name # Snapshot DB name
--port=port_number # port number to connect to Snapshot DB
--user=user name # Usre name can connect to Snapshot DB
--output-prefix=output location # default is stdout

snapshot range options(please choose "id" or "date")

--startid=Start snapshot id
--endid=End snapshot idト
--startdate=Start snapshot timestamp
--enddate=End snapshot timestamp

template option (please choose template file)

--template=template file

DB option (You can narrow down to target DB in snapshot range option. not essential.)

--targetdbid=Target DBID
--targetstring=Target DB name, host name and port number(format hostname:port/dbname)

others

--help (show usage)
--version (show version)

Note:

Generate report requires over two snapshot
DB relevant startid and endid is same one.
If you scpecified startdate-enddate, DBs which have multi snapshot in the range are generated report

Now, we have two template file. If you use PostgreSQL8.4, please use *84.tmpl file to generate report.

pg_statsreport_summary83/84.tmpl

Only output summary information

pg_statsreport83/84.tmpl

Summary and detail information about all tables and indexes

Following expample are typical way to generate report.

# id specified
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
# timestamp specified
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
# timestamp and dbid specified
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
# timestamp and hostname, port number and target dbname specified
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

About how to read report、please see this documentation.

Snapshoto deletion

Please delete old snapshot at regular intervals. Deletion snapshot is executed by using "delete_stats()". delete_stats() delete snapshot which specified snapshot id. Return 't' if success.

=# SELECT statsinfo.delete_stats(11);
 delete_stats
--------------
 t
(1 row)

If you want to delete some snapshot in specified date range, use snapshot_hisyory view.


# In case that we delete some snapshot older than certain date (ex '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 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)

Uninstall

On Uninstall, delete statsinfo schema on snapshot-store-DB and targetdb schema on monitoring-target-DB. Please use uninstall_stats_info.sql and uninstall_target_db.sql.

## In case you install pg_statsinfo by 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

If you use log-filter, stop PostgreSQL server and restart without log-filter. And if you use RPMs, uninstall pg_statsinfo-*.rpm by using "rpm -e"

restriction and reminder

pg_statsinfo make lock file under "/tmp" directory, and pg_statsinfo does not delete it automatically

tmpwatch daemon will delete it or please delete it manually

Filtering informations are sometimes saved in invalid format to "$PGDATA/pg_statslog/*.log" on PostgreSQL crash

You may catch the error on getting snapshot above situation. So, please move "$PGDATA/pg_statslog/*.log" to the other space.

If you dump/restore the monitoring target DB with pg_dump, you have to re-register contrib/adminpack on monitoring target DB

Because the adminpack registers functions on pg_catalog schema which does not dump wiht pg_dump

Latest Changelog

pg_statsinfo-reporter-1.2.0-2

Files for PG84 was in the reporter-rpm-package for PG83

If you use RPM, please replace old-package by pg_statsinfo-reporter-1.2.0-2.pg83
If you use source code, please replace old-souce by pg_statsinfo-1.2.0_reporter83.tar.gz

1.2.0

Merge source files of 8.3 and 8.4
Make autovacuum-log.done and checkpoint-log.done removing transactional
Add foreign keys for the snapshot-id or the host-snapshot-id to each tables

These foreign keys simplify to delete snapshot records

Cange the schemaname of target database into "targetdb" from "statsinfo"

There are not the differences of how to use previous versions.

Project Top page