pg_stat_monitor is a Query Performance Monitoring tool for PostgreSQL and supported in YugabyteDB.
It collects performance statistics and provides query performance insights in a single view and graphically in histogram.
These insights allow database users to understand query origins, execution, planning statistics and details, query information, and metadata.
CREATE EXTENSION command.
yugabyte=# CREATE EXTENSION pg_stat_monitor;
CREATE EXTENSION
yugabyte=# \dx *monitor*
List of installed extensions
Name | Version | Schema | Description
-----------------+---------+--------+-----------------------------------------------------------
pg_stat_monitor | 1.0 | public | track execution statistics of all SQL statements executed
(1 row)
The extension creates the pg_stat_monitor and pg_stat_monitor_settings views.
yugabyte=# \dv
List of relations
Schema | Name | Type | Owner
--------+--------------------------+------+----------
public | pg_stat_monitor | view | yugabyte
public | pg_stat_monitor_settings | view | yugabyte
(2 rows)
If I try to SELECT from either of the views, I get an error!
yugabyte=# SELECT COUNT(*) FROM pg_stat_monitor;
ERROR: pg_stat_monitor: must be loaded via shared_preload_libraries
yugabyte=# SELECT COUNT(*) FROM pg_stat_monitor_settings;
ERROR: pg_stat_monitor: must be loaded via shared_preload_libraries
In order for pg_stat_monitor to work properly, we have to load it when YugabyteDB starts. To do that, we’ll need to add it as a preloaded library via the ysql_pg_conf_csv gFlag.
Note: We can see which libraries are by default preloaded into YugabyteDB with the SHOW command:
yugabyte=# SHOW shared_preload_libraries;
shared_preload_libraries
-------------------------------------------------------
pg_stat_statements,yb_pg_metrics,pgaudit,pg_hint_plan
(1 row)
Let’s add pg_stat_monitor to the preload list by stopping YugabyteDB and restarting with the approprate gFlag setting.
[root@localhost ~]# yugabyted stop
Stopped yugabyted using config /root/var/conf/yugabyted.conf.
[root@localhost ~]# yugabyted start --tserver_flags=ysql_pg_conf_csv='{"shared_preload_libraries='pg_stat_monitor'"}' > start.log
[root@localhost ~]# ysqlsh -h 127.0.0.1 -U yugabyte
ysqlsh (11.2-YB-2.19.3.0-b0)
Type "help" for help.
yugabyte=# SHOW shared_preload_libraries;
shared_preload_libraries
-----------------------------------------------------------------------
pg_stat_statements,yb_pg_metrics,pgaudit,pg_hint_plan,pg_stat_monitor
(1 row)
Now I can SELECT from the views created by the pg_stat_monitor extension.
yugabyte=# SELECT COUNT(*) FROM pg_stat_monitor;
count
-------
7
(1 row)
yugabyte=# SELECT COUNT(*) FROM pg_stat_monitor_settings;
count
-------
13
(1 row)
P.S. I used yugabyted in the examples above, but the same applies to databases created by YugabyteDB Anywhere (YBA) and YugabyteDB Managed (YBM).
Have Fun!
