When you query the PostgreSQL performance views, like pg_stat_activity
or 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 format()
and \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 admin
password.
I import pg_stat_activity
and 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: \d gv$*.*
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_activity
and gv$pg_stat_statements
which have an additional column gv$host
,gv$zone
, gv$region
, gv$cloud
to identify the server, zone, region and cloud provider.
For example:
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).
Top comments (0)