When you query the PostgreSQL performance views, like
pg_stat_statements in YugabyteDB, you see only the view from the current node you are connected to. If you want a global view of the cluster, you need to query all of them.
Here is how to create a global view using the PostgreSQL Foreign Data Wrapper.
Note that I prefix all objects I create with
gv$, meaning "global view", (this is familiar to Oracle RAC users). The following scripts re-creates the objects by dropping them, so be careful if you already use the
gv$ namespace. I use
\gexec as I described in Generate SQL Script in PostgreSQL. To be safe, I create all that in a gv$database
For each tablet server visible in
yb_servers(), I create the FDW server and user mapping for my current user to be able to connect to it:
create database gv$; \c gv$ create extension if not exists postgres_fdw; select format(' create server if not exists "gv$%1$s" foreign data wrapper postgres_fdw options (host %2$L, port %3$L, dbname %4$L) ', host, host, port, current_database()) from yb_servers(); \gexec select format(' drop user mapping if exists for admin server "gv$%1$s" ',host) from yb_servers(); \gexec select format(' create user mapping if not exists for current_user server "gv$%1$s" --options ( user %2$L, password %3$L ) ',host, 'admin', 'bsky-social-axqcu-h6eed') from yb_servers(); \gexec
Here, as I'm connected as a superuser, I don't need to provide the password and this is why the
options is in comment. If you are using this on YugabyteDB Managed, you need to provide the
pg_stat_statements from each server into their own schema:
select format(' drop schema if exists "gv$%1$s" cascade ',host) from yb_servers(); \gexec select format(' create schema if not exists "gv$%1$s" ',host) from yb_servers(); \gexec select format(' import foreign schema "pg_catalog" limit to ("pg_stat_activity","pg_stat_statements","pg_stat_database") from server "gv$%1$s" into "gv$%1$s" ', host) from yb_servers(); \gexec
You can list the views created:
For each one, I create a global view to concatenate them:
with views as ( select distinct foreign_table_name from information_schema.foreign_tables t, yb_servers() s where foreign_table_schema = format('gv$%1$s',s.host) ) select format('drop view if exists "gv$%1$s"', foreign_table_name) from views union all select format('create or replace view "gv$%2$s" as %1$s', string_agg( format(' select %2$L as gv$host, %3$L as gv$zone, %4$L as gv$region, %5$L as gv$cloud, * from "gv$%2$s".%1$I ', foreign_table_name, host, zone, region, cloud) ,' union all '), foreign_table_name ) from views, yb_servers() group by views.foreign_table_name ; \gexec
That's all. Now I can query
gv$pg_stat_statements which have an additional column
gv$cloud to identify the server, zone, region and cloud provider.
select now()-query_start "start",state, substr(query, 1, 30), gv$host, gv$zone, datname, application_name, usename, client_hostname from gv$pg_stat_activity where state is not null order by now()-query_start ;
I also added
pg_stat_databases where not all columns are relevant to YugabyteDB, but the sum of commits and rollbacks per cloud region and zone can be interesting:
select sum(xact_commit) commits,sum(xact_rollback) rollbacks, gv$cloud, gv$region, gv$zone, gv$host from gv$pg_stat_database group by gv$cloud, gv$region, gv$zone, gv$host order by 1;
YugabyteDB is elastic and resilient. When the nodes are added or removed, you should run the scripts above again. Note that this is a temporary solution and such views will probably be implemented in the future. The global statistics are also visible from REST API and User Interface (YugabyteDB Managed portal for the managed service,
yugabyted UI for Open Source).