pg_stat_activity from all servers in YugabyteDB
Tags: yugabytedb, distributed, sql, database
Published: 2023-08-25 11:12:21

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](https://www.yugabyte.com/blog/generate-sql-script-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:
```sql
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:
```sql
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:
```sql
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:
```sql
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
;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v667hd4fguxj913r5k46.png)

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:
```sql
 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;
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bdvrkygvg7bchwg23je7.png)

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).