Infrastructure at your Service

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.

[email protected]:/var/tmp/ [PG3] unzip pg_stat_kcache-master.zip
[email protected]:/var/tmp/ [PG3] cd pg_stat_kcache-master
[email protected]:/var/tmp/pg_stat_kcache-master/ [PG3] make
[email protected]:/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:

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

Time: 0.230 ms
([email protected][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:

[email protected]:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ stop -m fast
[email protected]:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ start

… the extension can be created:

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

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

([email protected][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:

[email protected]:/var/tmp/ [PG3] unzip pg_qualstats-master.zip
[email protected]:/var/tmp/ [PG3] cd pg_qualstats-master
[email protected]:/var/tmp/pg_qualstats-master/ [PG3] make
[email protected]:/var/tmp/pg_qualstats-master/ [PG3] make install

Again we’ll need to adjust shared_preload_libraries:

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

Time: 0.215 ms
([email protected][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:

[email protected]:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ stop -m fast
[email protected]:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ start

Finally create the extension:

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

Time: 0.285 ms
([email protected][local]:4445) [postgres] > create extension pg_qualstats;
CREATE EXTENSION
Time: 143.439 ms
([email protected][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:

[email protected][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)

([email protected][local]:4445) [postgres] > create extension btree_gist;
CREATE EXTENSION
Time: 21.112 ms
([email protected][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:

[email protected]:/var/tmp/ [PG3] unzip powa-archivist-master.zip
[email protected]:/var/tmp/ [PG3] cd powa-archivist-master
[email protected]:/var/tmp/powa-archivist-master/ [PG3] make
[email protected]:/var/tmp/powa-archivist-master/ [PG3] make install

Again, adjust shared_preload_libraries:

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

Time: 0.243 ms
([email protected][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:

[email protected]:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ stop -m fast
[email protected]:/var/tmp/pg_stat_kcache-master/ [PG3] pg_ctl -D /u02/pgdata/PG3/ start

Create the extension:

([email protected][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)

([email protected][local]:4445) [postgres] > create extension powa;
CREATE EXTENSION
Time: 742.831 ms
([email protected][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:

([email protected][local]:4445) [postgres] > create database powa;
CREATE DATABASE
Time: 1664.653 ms
([email protected][local]:4445) [postgres] > \c powa
You are now connected to database "powa" as user "postgres".
([email protected][local]:4445) [powa] > create extension pg_stat_statements;
CREATE EXTENSION
Time: 25.448 ms
([email protected][local]:4445) [powa] > create extension btree_gist;
CREATE EXTENSION
Time: 134.281 ms
([email protected][local]:4445) [powa] > create extension pg_qualstats;
CREATE EXTENSION
Time: 25.683 ms
([email protected][local]:4445) [powa] > create extension pg_stat_kcache;
CREATE EXTENSION
Time: 53.798 ms
([email protected][local]:4445) [powa] > create extension powa;
CREATE EXTENSION
Time: 98.410 ms
([email protected][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:

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

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

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

Time: 0.241 ms
([email protected][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:

[email protected]: [PG3] sudo yum install python-pip python-devel

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

[email protected]: [PG3] sudo pip install powa-web

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

[email protected]: [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:

[email protected]:/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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure