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:
After some time (you’ll need to give powa some time to collect) the dashboard will be populated:
If you select a database you can scroll down to the list of sql statements:
Clicking on one of these gives nice graphs (the following are all graphs for one statement):
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.