docs/en/pgperf_intro.rst

Introduction to PgPerf package
==============================

This chapter is intended to give a brief introduction to the PgPerf snapshot package.

Installing PgPerf package
-------------------------

To install the PgPerf package, ``pgperf_snapshot_install.sql`` script needs to be executed on a database, where the DBA wants to take performance snapshots, to create ``pgperf`` schema, snapshot functions, and snapshot tables.

.. code-block:: none

   psql -f pgperf_snapshot_install<VERSION>.sql <DBNAME>

Uninstalling PgPerf package
---------------------------

To uninstall the PgPerf package, ``pgperf_snapshot_uninstall.sql`` script needs to be executed on the database where the PgPerf package has been installed.

.. code-block:: none

   psql -f pgperf_snapshot_uninstall.sql <DBNAME>

Once ``pgperf_snapshot_uninstall.sql`` is executed on a database, it would drops``pgperf`` schema, snapshot functions and snapshot tables installed in the database.

Taking a performance snapshot with using PgPerf package
-------------------------------------------------------

In order to take a performance snapshot, a SQL function ``pgperf.create_snapshot()`` needs to be called.

Once ``pgperf.create_snapshot()`` function is called with specifying a snapshot level, a snapshot of the several performance statistics would be taken, and a snapshot id associated with the performance snaphsot would be returned.

In the following example, a snapshot level is specified as ``4``, and then, ``1005`` is returned as the snapshot id.

::

 postgres=# SELECT pgperf.create_snapshot(4);
  create_snapshot
 -----------------
             1005
 (1 row)
 
 postgres=#


Showing a list for snapshots
----------------------------

``pgperf.snapshot`` table would hold a list of the snapshots.

By querying the table, snapshot ids and those timestamps can be obtained as following.

::

 postgres=# SELECT * FROM pgperf.snapshot;
  sid |             ts             | level
 -----+----------------------------+-------
    0 | 2015-04-11 19:11:24.04428  |     1
    1 | 2015-04-11 19:11:24.060965 |     2
    2 | 2015-04-11 19:11:24.110034 |     4
 (3 rows)
 
 postgres=#


Deleting a snapshot
-------------------

To delete a snapshot, ``pgperf.delete_snapshot()`` function with specifying a snapshot id can be used.

In the following example, a snapshot associated with snaphsot id ``2`` is going to be dropped.

::

 postgres=# SELECT pgperf.delete_snapshot(2);
  delete_snapshot
 -----------------
  t
 (1 row)
 
 postgres=# SELECT * FROM pgperf.snapshot;
  sid |             ts             | level
 -----+----------------------------+-------
    0 | 2015-04-11 19:11:24.04428  |     1
    1 | 2015-04-11 19:11:24.060965 |     2
 (2 rows)
 
 postgres=#


Taking snapshots as a routine
-----------------------------

To take a snapshot as a routine, ``pgperf.create_snapshot()`` function needs to be called with using a cron-like tool.

A shell script,``get_snapshot.sh``, which is contained in the package, executes ``pgperf.create_snapshot()`` function on the specified database or all databases, which can be connected by the script (and not a template database).

In the following example, the script is taking a snapshot for ``postgres`` database.

::

$ ./get_snapshot.sh postgres

On the other hand, the script is taking a snapshot for every database in the following example.

::

$ ./get_snapshot.sh

By setting a crontab, ``get_snapshot.sh`` can be called to take snapshots periodically.

A crontab shown in below is intended to take performance snapshots of all the databases with calling ``get_snapshot.sh`` script in every 10 minutes.

::

 0-59/10 * * * *       /path/to/get_snapshot.sh > /dev/null 2>&1


Purging snapshots
-----------------

``pgperf.purge_snapshots()`` function can be used to purge older snapshots at once.

In the following example, snapshots, which are older than 1 week or more, are going to be purged.

::

 postgres=# SELECT sid,ts FROM pgperf.snapshot ORDER BY ts LIMIT 1;
  sid |             ts
 -----+----------------------------
    2 | 2012-10-21 18:20:01.238885
 (1 row)
 
 postgres=# SELECT now(),pgperf.purge_snapshots('1 weeks');
               now              | purge_snapshots
 -------------------------------+-----------------
  2012-10-29 14:57:04.092243+09 |             121
 (1 row)
 
 postgres=# SELECT sid,ts FROM pgperf.snapshot ORDER BY ts LIMIT 1;
  sid |            ts
 -----+--------------------------
  123 | 2012-10-22 15:00:01.8397
 (1 row)
 
 postgres=#