Load pg_stat_monitor when YugabyteDB starts

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. 

pg_stat_monitor is installed via the 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!

Lucy and Santa