pg_stat_monitor :: Younger sibling of pg_stat_statements with more knowledge

We all know the famous tool to analyse the queries in PostgreSQL pg_stat_statements which was introduced in version 8.x and we use since then. Percona announced a tool called pg_stat_monitor which comes with additional features that cannot be fulfilled by pg_stat_statements.

pg_stat_statements comes along with contrib package, whereas pg_stat_monitor with percona release package.

Advantages of pg_stat_monitor over pg_stat_statements:

1. IP, application name, command type, relation name and status of the query will be logged.
2. It can capture all queries including failures.
3. Option to choose between actual query logging or normalised logging.
4. pg_stat_monitor allows to capture the queries based on the given time interval using buckets.
5. CPU time will be logged

Setup:

1. Install percona releae package as mentioned.
2. Enable the repo

percona-release setup ppg13

3. Install pg_stat_monitor

yum install percona-pg-stat-monitor13

4. Make a entry in shared_preloaded_libraries and a restart

shared_preloaded_libraries='pg_stat_monitor'

5. Create extension in the specific database

create EXTENSION pg_stat_monitor;

We can control the behaviour of pg_stat_monitor using the below settings,

The stats can be filtered based on our needs,

Logging actual query:

alter system SET pg_stat_monitor.pgsm_normalized_query=off;

After reloading the config, we can see the actual query is logging with parameters.

Error logging:

We can also monitor the errors along with the error code and the state.

Query plan:

alter system SET pg_stat_monitor.pgsm_enable_query_plan=on;

After enabling the option, the query plan also will be logged along with the query.

CPU monitoring:

The queries spent more time in the CPU for processing can be monitored using pg_stat_monitor.

Stats from pg_stat_monitor can be easily integrated with the tools used in any organisation and can increase the productivity.

Happy monitoring!!.