GaussDB kernel系统表和视图的命名规则：
- 已有的或新增的系统表和视图以
pg、pgxc或gs为前缀； - 实现Oracle兼容的系统表或视图以
dba、all、user或pv为前缀。
GaussDB系统视图命名前缀与Oracle的对应关系：
adm_对应dba_：包含所有数据库对象的信息；db_对应all_：包含当前用户能够访问的所有数据库对象的信息；my_对应user_：包含当前用户拥有的所有数据库对象的信息。
本文涉及的所有系统表和视图都属于pg_catalog模式。
系统表
系统表记录了数据库内的元数据信息。大多数系统表的可读性都较差，因为很多列都是用oid表示，需要做多表关联才能查到实际名称。
通过系统表查到的元数据信息与当前连接到的数据库有关。
pg_database：数据库相关的元信息。
--查询数据库名、owner、字符集、排序规则、连接限制、兼容模式
gaussdb=> select oid,datname,datdba,encoding,datcollate,datconnlimit,datcompatibility from pg_database;
oid | datname | datdba | encoding | datcollate | datconnlimit | datcompatibility
-------+-----------+--------+----------+------------+--------------+------------------
1 | template1 | 10 | 7 | C | -1 | A
12810 | template0 | 10 | 7 | C | -1 | A
12815 | postgres | 10 | 7 | C | -1 | A
17153 | tpcc | 17148 | 7 | C | -1 | A
pg_namespace：模式相关的元信息。
gaussdb=> select oid,nspname from pg_namespace;
oid | nspname
-------+----------------------
99 | pg_toast
12821 | sys
11 | pg_catalog
2200 | public
...
16778 | root
16782 | metricUser
17150 | benchmarksql
25354 | u1
25358 | u2
pg_tablespace：表空间相关的元信息。
gaussdb=> select oid,spcname,spcowner,spcmaxsize from pg_tablespace;
oid | spcname | spcowner | spcmaxsize
-------+------------+----------+------------
1663 | pg_default | 10 |
1664 | pg_global | 10 |
17144 | tbs1 | 16776 |
17145 | tbs2 | 16776 |
17680 | testbs | 16776 |
17152 | tpcc | 17148 |
pg_class：记录数据库对象及其之间的关系。包括表、索引、序列、视图、物化视图、Toast表等。
--查询public模式下的所有数据库对象
select oid,relname,relowner,relkind,reltablespace,reltuples,reltoastrelid from pg_class
where relnamespace=(select oid from pg_namespace where nspname='public');
--查询public模式下的所有表
select oid,relname,relowner,relkind,reltablespace,reltuples,reltoastrelid from pg_class
where relnamespace=(select oid from pg_namespace where nspname='public')
and relkind='r';
--查询当前用户下所有表的行数和comments
select a.relname,a.reltuples,b.description
from pg_class a left outer join pg_description b
on b.objsubid=0 and a.oid=b.objoid
where a.relnamespace=(
select oid from pg_namespace where nspname='public'
) and a.relkind='r'
order by a.relname;
- reltuples对应表的行数，即元组数。
- reltoastrelid对应与该表关联的Toast表的oid。
- relkind字段值对应的数据库对象类型：
- r：普通表
- i：索引
- S：序列
- v：视图
- c：复合类型
- t：Toast表
- f：外表
- m：物化视图
- e：Stream对象
- o：Contview对象
pg_type：数据类型相关的元信息。
select oid,typname,typowner,typlen,typbyval,typtype from pg_type;
select * from pg_type where typname='char';
--创建自定义类型并查看信息
create type compfoo as (f1 int, f2 text);
select * from pg_type where typname='compfoo';
pg_attribute：表字段相关的元信息。
--查询字段所属表oid、字段名、字段类型oid
gaussdb=> select attrelid,attname,atttypid from pg_attribute where attrelid=(select oid from pg_class where relname='bmsql_config');
attrelid | attname | atttypid
----------+--------------+----------
17154 | xc_node_hash | 23
17154 | xc_node_id | 23
17154 | tableoid | 26
17154 | cmax | 29
17154 | xmax | 28
17154 | cmin | 29
17154 | xmin | 28
17154 | ctid | 27
17154 | cfg_name | 1043
17154 | cfg_value | 1043
gaussdb=> select oid,typname from pg_type where oid in (23,26,1043);
oid | typname
------+---------
23 | int4
26 | oid
1043 | varchar
tpcc=> \d bmsql_config
Table "public.bmsql_config"
Column | Type | Modifiers
-----------+-----------------------+-----------
cfg_name | character varying(30) | not null
cfg_value | character varying(50) |
Indexes:
"bmsql_config_pkey" PRIMARY KEY, btree (cfg_name) TABLESPACE tpcc
pg_index：索引相关的元信息。
gaussdb=> select indexrelid,indrelid,indnatts,indisunique,indisprimary,indisclustered from pg_index;
tpcc=> select indexrelid,indrelid,indnatts,indisunique,indisprimary,indisclustered from pg_index where indrelid=(select oid from pg_class where relname='bmsql_config');
indexrelid | indrelid | indnatts | indisunique | indisprimary | indisclustered
------------+----------+----------+-------------+--------------+----------------
17157 | 17154 | 1 | t | t | f
下面几个系统表仅在分布式场景下有效。
pgxc_node：集群节点信息。pgxc_group：节点组信息。pgxc_class：每张表的复制或分布信息。pgxc_slice：范围分布表、LIST分布表的信息。
系统视图
用户和角色
pg_roles：角色相关的元信息。初始化用户、以及具有sysadmin或createrole属性的用户可以查看全部角色的信息。其他用户只能查看自己的信息。
tpcc=> select rolname,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin,rolauditadmin from pg_roles;
rolname | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolauditadmin
--------------------------+------------+---------------+-------------+-------------+---------------
rdsAdmin | t | t | t | t | t
backupUser | t | f | f | t | f
repUser | t | f | f | t | f
root | t | t | t | t | f
metricUser | t | f | f | t | f
r1 | t | f | t | f | f
benchmarksql | t | f | f | t | f
u1 | t | f | f | t | f
r2 | t | t | t | t | f
u2 | t | t | t | t | f
pg_user：数据库用户信息。
tpcc=> select usename,usesysid,usecreatedb,usesuper from pg_user;
usename | usesysid | usecreatedb | usesuper
--------------+----------+-------------+----------
rdsAdmin | 10 | t | t
backupUser | 16768 | f | f
repUser | 16772 | f | f
root | 16776 | t | f
metricUser | 16780 | f | f
benchmarksql | 17148 | f | f
u1 | 25352 | f | f
r2 | 25349 | t | f
u2 | 25356 | t | f
表、索引、视图
pg_tables：当前数据库的所有表信息。
tpcc=> select schemaname,tablename,tableowner,tablespace,hasindexes,hasrules,hastriggers from pg_tables where schemaname='public';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+------------------+--------------+------------+------------+----------+-------------
public | bmsql_config | benchmarksql | | t | f | f
public | tab1 | benchmarksql | | f | f | f
public | bmsql_oorder | benchmarksql | | t | f | t
public | bmsql_new_order | benchmarksql | | t | f | t
public | bmsql_stock | benchmarksql | | t | f | t
public | bmsql_order_line | benchmarksql | | t | f | t
public | bmsql_history | benchmarksql | | t | f | t
public | bmsql_item | benchmarksql | | t | f | t
public | bmsql_customer | benchmarksql | | t | f | t
public | bmsql_district | benchmarksql | | t | f | t
public | bmsql_warehouse | benchmarksql | | t | f | t
--上面的tablespace字段为空说明是当前数据库的默认表空间
tpcc=> select a.datname,b.spcname from pg_database a,pg_tablespace b where a.dattablespace=b.oid and a.datname='tpcc';
datname | spcname
---------+---------
tpcc | tpcc
pg_indexes：当前数据库中的索引信息。
tpcc=> select schemaname,tablename,indexname,tablespace,indexdef from pg_indexes where tablename='bmsql_config';
schemaname | tablename | indexname | tablespace | indexdef
------------+--------------+-------------------+------------+----------------------------------------------------------------------------------------------
public | bmsql_config | bmsql_config_pkey | | CREATE UNIQUE INDEX bmsql_config_pkey ON bmsql_config USING btree (cfg_name) TABLESPACE tpcc
pg_views：当前数据库的所有视图信息。
tpcc=> select schemaname,viewname,viewowner,definition from pg_views where viewname='pg_views';
schemaname | viewname | viewowner | definition
------------+----------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pg_catalog | pg_views | rdsAdmin | SELECT n.nspname AS schemaname, c.relname AS viewname, pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'v'::"char");
锁信息
pg_locks：数据库实例中当前活跃进程的锁信息。
tpcc=> select locktype,database,relation,transactionid,pid,mode,granted from pg_locks;
locktype | database | relation | transactionid | pid | mode | granted
------------+----------+----------+---------------+-----------------+-----------------+---------
relation | 17153 | 12192 | | 139672151844608 | AccessShareLock | t
virtualxid | | | | 139672151844608 | ExclusiveLock | t
virtualxid | | | | 139672676202240 | ExclusiveLock | t
统计信息
pg_stats：存储在pg_statistic系统表里面的单列统计信息。
tpcc=> select schemaname,tablename,attname,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_bounds from pg_stats where tablename='bmsql_config';
schemaname | tablename | attname | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds
------------+--------------+-----------+-----------+------------+------------------+-------------------+--------------------------------------------------
public | bmsql_config | cfg_name | 11 | -1 | | | {nURandCC_ID,nURandCI_ID,nURandCLast,warehouses}
public | bmsql_config | cfg_value | 4 | -1 | | | {241,305,4745,656}
活动会话
pg_stat_activity：当前的活动会话和SQL执行信息。
tpcc=> select datname,pid,sessionid,usename,application_name,client_addr,client_hostname,
backend_start,xact_start,query_start,state_change,waiting,state,query_id,query from pg_stat_activity;
tpcc=> select datname,pid,sessionid,usename,application_name,client_addr,backend_start,waiting,state,query_id from pg_stat_activity;
datname | pid | sessionid | usename | application_name | client_addr | backend_start | waiting | state | query_id
----------+-----------------+-----------------+----------+------------------------+--------------+-------------------------------+---------+--------+------------------
tpcc | 139672151844608 | 139672151844608 | root | gsql | | 2024-11-12 13:13:39.663102+08 | f | active | 1688849863922598
postgres | 139672463861504 | 139672463861504 | rdsAdmin | cm_agent | 192.168.7.13 | 2024-11-08 11:13:06.970641+08 | f | idle | 0
postgres | 139672480642816 | 139672480642816 | rdsAdmin | cm_agent | 192.168.7.13 | 2024-11-08 11:13:05.968247+08 | f | idle | 0
postgres | 139672625805056 | 139672625805056 | rdsAdmin | WLMArbiter | | 2024-11-08 11:13:05.731287+08 | f | | 0
postgres | 139672676202240 | 139672676202240 | rdsAdmin | workload | | 2024-11-08 11:13:05.725783+08 | f | active | 0
postgres | 139672651036416 | 139672651036416 | rdsAdmin | WorkloadMonitor | | 2024-11-08 11:13:05.72374+08 | f | | 0
postgres | 139672915277568 | 139672915277568 | rdsAdmin | statement flush thread | | 2024-11-08 11:13:05.65861+08 | f | idle | 0
postgres | 139672984483584 | 139672984483584 | rdsAdmin | PercentileJob | | 2024-11-08 11:13:05.659311+08 | f | active | 0
postgres | 139672946145024 | 139672946145024 | rdsAdmin | Asp | | 2024-11-08 11:13:05.658526+08 | f | active | 0
postgres | 139673023276800 | 139673023276800 | rdsAdmin | WDRSnapshot | | 2024-11-08 11:13:05.658499+08 | f | idle | 1688849860263936
postgres | 139673107162880 | 139673107162880 | rdsAdmin | ApplyLauncher | | 2024-11-08 11:13:05.615372+08 | f | | 0
postgres | 139673065748224 | 139673065748224 | rdsAdmin | | | 2024-11-08 11:13:05.614698+08 | f | | 0
postgres | 139673040058112 | 139673040058112 | rdsAdmin | | | 2024-11-08 11:13:05.615099+08 | f | | 0
postgres | 139673334183680 | 139673334183680 | rdsAdmin | | | 2024-11-08 11:13:05.601549+08 | f | active | 0
postgres | 139673476253440 | 139673476253440 | rdsAdmin | JobScheduler | | 2024-11-08 11:13:05.6586+08 | f | active | 0
⭐️ pg_stat_activity视图中state字段的含义：
active：正在执行SQL。idle：等待新的客户端命令。idle in transaction：处于一个事务中，但当前并未执行SQL。idle in transaction (aborted)：处于一个事务中，但该事务中有个语句执行出错。fastpath function call：正在执行一个fastpath函数。disabled：该会话中track_activities被禁用。
常用查询：
--查看当前活跃连接
select datname,usename,client_addr,client_port from pg_stat_activity;
show max_connections
select count(*) from pg_stat_activity where state<>'idle';
select datname,usename,query from pg_stat_activity;
--查看执行耗时较长的语句
select current_timestamp - query_start as runtime,
datname,usename,sessionid,query from pg_stat_activity where state != 'idle' order by 1 desc;
等待事件
pg_thread_wait_status：工作线程及其辅助线程的阻塞等待信息。
tpcc=> select node_name,db_name,thread_name,query_id,sessionid,wait_status,wait_event,lockmode,block_sessionid from pg_thread_wait_status;
dn_6001 | tpcc | gsql | 1688849863993801 | 139672151844608 | none | none | |
dn_6001 | postgres | cm_agent | 0 | 139672463861504 | wait cmd | wait cmd | |
dn_6001 | postgres | cm_agent | 0 | 139672480642816 | wait cmd | wait cmd | |
dn_6001 | postgres | WLMArbiter | 0 | 139672625805056 | none | none | |
dn_6001 | postgres | workload | 0 | 139672676202240 | none | none | |
dn_6001 | postgres | WorkloadMonitor | 0 | 139672651036416 | none | none | |
dn_6001 | postgres | statement flush thread | 0 | 139672915277568 | none | none | |
dn_6001 | postgres | PercentileJob | 0 | 139672984483584 | none | none | |
dn_6001 | postgres | Asp | 0 | 139672946145024 | none | none | |
dn_6001 | postgres | WDRSnapshot | 1688849860263936 | 139673023276800 | HashAgg - build hash | HashAgg - build hash | |
dn_6001 | postgres | ApplyLauncher | 0 | 139673107162880 | none | none | |
dn_6001 | postgres | | 0 | 139673065748224 | none | none | |
dn_6001 | postgres | | 0 | 139673040058112 | none | none | |
dn_6001 | postgres | | 0 | 139673334183680 | none | none | |
dn_6001 | postgres | JobScheduler | 0 | 139673476253440 | none | none | |
⭐️ pg_thread_wait_status视图中wait_status字段的含义：
none：无等待事件；wait io：等待IO完成；wait cmd：等待读取网络通信包；acquire lock：等待加锁；acquire lwlock：等待获取轻量级锁。
具体等待事件可以结合wait_status和wait_event两个字段来分析。
References
【1】https://www.postgresql.org/docs/16/catalogs-overview.html
【2】https://www.postgresql.org/docs/16/views.html
【3】https://support.huaweicloud.com/intl/zh-cn/ae-ad-1-devg-dws/dws_04_0783.html
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
