Infrastructure at your Service

Daniel Westermann

Monitoring tools for PostgreSQL – POWA

The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer.

For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor:

  • pg_stat_statements (see last post)
  • pg_stat_kcache: gathers statistics about reads and writes done by the file system layer
  • pg_qualstats: gathers statistics of predicates found in where statements and join clauses
  • btree_gist: provides GiST index operator classes that implement B-tree equivalent behavior for various data types

As pg_stat_statements is already installed in my PostgreSQL instance lets start by installing the pg_stat_kcache extension.

postgres@oel7:/var/tmp/ [PG3] unzip pg_stat_kcache-master.zip
postgres@oel7:/var/tmp/ [PG3] cd pg_stat_kcache-master
postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] make
postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] make install

As usual: quite easy. As with pg_stat_statements we need to adjust the shared_preload_libraries parameter to have the extension loaded:

(postgres@[local]:4445) [postgres] > show shared_preload_libraries;
 shared_preload_libraries 
--------------------------
 pg_stat_statements
(1 row)

Time: 0.230 ms
(postgres@[local]:4445) [postgres] > alter system set shared_preload_libraries=pg_stat_statements,pg_stat_kcache;
ALTER SYSTEM
Time: 2.995 ms

After the PostgreSQL instance was restarted:

postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ stop -m fast
postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ start

… the extension can be created:

(postgres@[local]:4445) [postgres] > show shared_preload_libraries;
     shared_preload_libraries      
-----------------------------------
 pg_stat_statements,pg_stat_kcache
(1 row)

(postgres@[local]:4445) [postgres] > create extension pg_stat_kcache;
CREATE EXTENSION
Time: 68.483 ms

(postgres@[local]:4445) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering
 pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

The next, and final, extension which should be installed is pg_qualstats. The procedure is almost the same:

postgres@oel7:/var/tmp/ [PG3] unzip pg_qualstats-master.zip
postgres@oel7:/var/tmp/ [PG3] cd pg_qualstats-master
postgres@oel7:/var/tmp/pg_qualstats-master/ [PG3] make
postgres@oel7:/var/tmp/pg_qualstats-master/ [PG3] make install

Again we’ll need to adjust shared_preload_libraries:

(postgres@[local]:4445) [postgres] > show shared_preload_libraries;
     shared_preload_libraries      
-----------------------------------
 pg_stat_statements,pg_stat_kcache
(1 row)

Time: 0.215 ms
(postgres@[local]:4445) [postgres] > alter system set shared_preload_libraries=pg_stat_statements,pg_stat_kcache,pg_qualstats;
ALTER SYSTEM
Time: 4.692 ms

Then restart the server:

postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ stop -m fast
postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ start

Finally create the extension:

(postgres@[local]:4445) [postgres] > show shared_preload_libraries;
             shared_preload_libraries             
--------------------------------------------------
 pg_stat_statements, pg_stat_kcache, pg_qualstats
(1 row)

Time: 0.285 ms
(postgres@[local]:4445) [postgres] > create extension pg_qualstats;
CREATE EXTENSION
Time: 143.439 ms
(postgres@[local]:4445) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals
 pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering
 pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

The btree_gist extension is there by default and we just need to add it:

postgres@[local]:4445) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals
 pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering
 pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

(postgres@[local]:4445) [postgres] > create extension btree_gist;
CREATE EXTENSION
Time: 21.112 ms
(postgres@[local]:4445) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST
 pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals
 pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering
 pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)

Having all the requirements available we now start by installing powa-archivist. This is the POWA extension that gathers the performance statistics. The usual steps apply:

postgres@oel7:/var/tmp/ [PG3] unzip powa-archivist-master.zip
postgres@oel7:/var/tmp/ [PG3] cd powa-archivist-master
postgres@oel7:/var/tmp/powa-archivist-master/ [PG3] make
postgres@oel7:/var/tmp/powa-archivist-master/ [PG3] make install

Again, adjust shared_preload_libraries:

(postgres@[local]:4445) [postgres] > show shared_preload_libraries;
             shared_preload_libraries             
--------------------------------------------------
 pg_stat_statements, pg_stat_kcache, pg_qualstats
(1 row)

Time: 0.243 ms
(postgres@[local]:4445) [postgres] > alter system set shared_preload_libraries=pg_stat_statements, pg_stat_kcache, pg_qualstats, powa;
ALTER SYSTEM
Time: 69.219 ms

Restart the server:

postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ stop -m fast
postgres@oel7:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ start

Create the extension:

(postgres@[local]:4445) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST
 pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals
 pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering
 pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)

(postgres@[local]:4445) [postgres] > create extension powa;
CREATE EXTENSION
Time: 742.831 ms
(postgres@[local]:4445) [postgres] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST
 pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals
 pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering
 pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 powa               | 3.0.0   | public     | PostgreSQL Workload Analyser-core
(6 rows)

At this point in time it is advisable to create a dedicated database for the powa repository and add all the extensions:

(postgres@[local]:4445) [postgres] > create database powa;
CREATE DATABASE
Time: 1664.653 ms
(postgres@[local]:4445) [postgres] > \c powa
You are now connected to database "powa" as user "postgres".
(postgres@[local]:4445) [powa] > create extension pg_stat_statements;
CREATE EXTENSION
Time: 25.448 ms
(postgres@[local]:4445) [powa] > create extension btree_gist;
CREATE EXTENSION
Time: 134.281 ms
(postgres@[local]:4445) [powa] > create extension pg_qualstats;
CREATE EXTENSION
Time: 25.683 ms
(postgres@[local]:4445) [powa] > create extension pg_stat_kcache;
CREATE EXTENSION
Time: 53.798 ms
(postgres@[local]:4445) [powa] > create extension powa;
CREATE EXTENSION
Time: 98.410 ms
(postgres@[local]:4445) [powa] > \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 btree_gist         | 1.0     | public     | support for indexing common datatypes in GiST
 pg_qualstats       | 0.0.7   | public     | An extension collecting statistics about quals
 pg_stat_kcache     | 2.0.2   | public     | Kernel cache statistics gathering
 pg_stat_statements | 1.2     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 powa               | 3.0.0   | public     | PostgreSQL Workload Analyser-core
(6 rows)

There are some configuration parameters that allow you to control the powa extension:

(postgres@[local]:4445) [postgres] > show powa.frequency;
 powa.frequency 
----------------
 5min
(1 row)

Time: 0.319 ms
(postgres@[local]:4445) [postgres] > show powa.retention;
 powa.retention 
----------------
 1d
(1 row)

Time: 0.241 ms
(postgres@[local]:4445) [postgres] > show powa.database;
 powa.database 
---------------
 powa
(1 row)

Time: 0.241 ms
(postgres@[local]:4445) [postgres] > show powa.coalesce;
 powa.coalesce 
---------------
 100
(1 row)

Time: 0.362 ms

So far for the work to be done inside the PostgreSQL instance. No we need the web interface. In general you can install the web interface anywhere. I’ll be doing it on the same host by installing the requirements first:

postgres@oel7: [PG3] sudo yum install python-pip python-devel

After that pip can be used to install the web interface:

postgres@oel7: [PG3] sudo pip install powa-web

We need to create small configuration file for the web interface:

postgres@oel7: [PG3] sudo echo "servers={
  'main': {
    'host': 'localhost',
    'port': '5432',
    'database': 'powa'
  }
}
cookie_secret=\"A_SECRET\" " > /etc/powa-web.conf

Once this is available the web interface can be started:

postgres@oel7:/var/tmp/powa-archivist-master/ [PG3] powa-web

You should be able to access the interface at port 8888:

powa1

powa2

powa3

powa4

After some time (you’ll need to give powa some time to collect) the dashboard will be populated:

powa5

If you select a database you can scroll down to the list of sql statements:
powa6

Clicking on one of these gives nice graphs (the following are all graphs for one statement):

powa7
powa8
powa9
powa10
powa11

Conclusion: POWA is a very nice tool for gathering and displaying statistics around a PostgreSQL instance. Especially that you can store all the statistics in a separate database and can control on how long you want to keep them makes it a very good choice. Traveling back in time to troubleshoot issues becomes very easy.

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure