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:
- IP, application name, command type, relation name and status of the query will be logged.
- It can capture all queries including failures.
- Option to choose between actual query logging or normalised logging.
- pg_stat_monitor allows to capture the queries based on the given time interval using buckets.
- CPU time will be logged
Setup:
- Install percona releae package as mentioned.
- 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!!.