Aurora PostgreSQL Limitless Database 视图
下表列出了 Aurora PostgreSQL Limitless Database 的新视图。
注意
此表中列出的视图位于 rds_aurora
架构中。使用 Limitless Database 视图时,请确保包含完全限定的对象名称:rds_aurora
.
。object_name
Aurora PostgreSQL Limitless Database 视图 | 对应的 Aurora PostgreSQL 视图 |
---|---|
limitless_database | pg_database |
limitless_locks | pg_locks |
limitless_stat_activity | pg_stat_activity |
limitless_stat_all_indexes | pg_stat_all_indexes |
limitless_stat_all_tables | pg_stat_all_tables |
limitless_stat_database | pg_stat_database |
limitless_stat_progress_vacuum | pg_stat_progress_vacuum |
limitless_stat_statements | pg_stat_statements |
limitless_stat_subclusters | 无 |
limitless_stat_statements_info | pg_stat_statements_info |
limitless_statio_all_indexes | pg_statio_all_indexes |
limitless_statio_all_tables | pg_statio_all_tables |
limitless_tables | pg_tables |
limitless_table_collocations | 无 |
limitless_table_collocation_distributions | 无 |
以下示例提供了有关 Aurora PostgreSQL Limitless Database 视图的详细信息。有关 PostgreSQL 视图的更多信息,请参阅 PostgreSQL 文档中的 Viewing statistics
注意
如果有正在进行的事务,某些统计数据视图可能会返回不一致的结果。
- limitless_database
-
此视图包含有关数据库分片组中可用数据库的信息。例如:
postgres_limitless=> SELECT subcluster_id, subcluster_type, oid, datname, datacl FROM rds_aurora.limitless_database; subcluster_id | subcluster_type | oid | datname | datacl ---------------+-----------------+-------+--------------------+------------------------------------------------------------------------------------------------------------------------ 2 | router | 4 | template0 | {=c/rdsadmin,rdsadmin=CTc/rdsadmin} 2 | router | 5 | postgres | 2 | router | 16384 | rdsadmin | {rdsadmin=CTc/rdsadmin,rds_aurora_limitless_metadata_admin=c/rdsadmin,rds_aurora_limitless_heat_mgmt_admin=c/rdsadmin} 2 | router | 16477 | postgres_limitless | 2 | router | 1 | template1 | {=c/rdsadmin,rdsadmin=CTc/rdsadmin} 6 | shard | 4 | template0 | {=c/rdsadmin,rdsadmin=CTc/rdsadmin}
以下是输出参数:
-
subcluster_id
(文本)– 子集群(节点)的 ID -
subcluster_type
(文本)– 子集群(节点)、路由器或分片的类型
其余各列与
pg_database
中的相同。 -
- limitless_locks
-
此视图包含每个节点上每个进程的一行。它提供对数据库服务器中活动进程持有的锁的相关信息的访问。
例 使用两个事务创建锁的示例
在此示例中,我们在两台路由器上同时运行两个事务。
# Transaction 1 (run on router 1) BEGIN; SET search_path = public; SELECT * FROM customers; INSERT INTO customers VALUES (400,'foo','bar'); # Transaction 2 (run on router 2) BEGIN; SET search_path = public; ALTER TABLE customers ADD COLUMN phone VARCHAR;
第一个事务已开始运行。后续事务必须等到第一个事务完成。因此,第二个事务被锁屏蔽。为了检查其根本原因,我们运行一条命令,将
limitless_locks
与limitless_stat_activity
连接起来。# Run on router 2 SELECT distributed_session_id, state, usename, query, query_start FROM rds_aurora.limitless_stat_activity WHERE distributed_session_id in ( SELECT distributed_session_id FROM rds_aurora.limitless_locks WHERE relname = 'customers' ); distributed_session_id | state | usename | query | query_start ------------------------+---------------------+--------------------------+---------------------------------- -------------+------------------------------- 47BDE66E9A5E8477 | idle in transaction | limitless_metadata_admin | INSERT INTO customers VALUES (400,'foo','bar'); | 2023-04-13 17:44:45.152244+00 2AD7F370202D0FA9 | active | limitless_metadata_admin | ALTER TABLE customers ADD COLUMN phone VARCHAR; | 2023-04-13 17:44:55.113388+00 47BDE66E9A5E8477 | | limitless_auth_admin | <insufficient privilege> | 2AD7F370202D0FA9 | | limitless_auth_admin | <insufficient privilege> | 47BDE66E9A5E8477 | | limitless_auth_admin | <insufficient privilege> | 2AD7F370202D0FA9 | | limitless_auth_admin | <insufficient privilege> | (6 rows)
例 显式创建锁示例
在此示例中,我们显式创建了一个锁,然后使用
limitless_locks
视图来查看锁(省略了某些列)。BEGIN; SET search_path = public; LOCK TABLE customers IN ACCESS SHARE MODE; SELECT * FROM rds_aurora.limitless_locks WHERE relname = 'customers'; subcluster_id | subcluster_type | distributed_session_id | locktype | datname | relnspname | relname | virtualtransaction | pid | mode ---------------+-----------------+------------------------+----------+--------------------+------------+ ----------+--------------------+-------+----------------- 1 | router | 7207702F862FC937 | relation | postgres_limitless | public | customers | 28/600787 | 59564 | AccessShareLock 2 | router | 7207702F862FC937 | relation | postgres_limitless | public | customers | 28/600405 | 67130 | AccessShareLock 3 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 15/473401 | 27735 | AccessShareLock 4 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 13/473524 | 27734 | AccessShareLock 5 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 13/472935 | 27737 | AccessShareLock 6 | shard | 7207702F862FC937 | relation | postgres_limitless | public | customers | 13/473015 | 48660 | AccessShareLock (6 rows)
- limitless_stat_activity
-
此视图包含每个节点上每个进程的一行。它可用于跟踪整个系统的运行状况,并对耗时较长的流程进行分类。例如:
postgres=# SELECT subcluster_id, subcluster_type, distributed_session_id, distributed_session_state, datname, distributed_query_id FROM rds_aurora.limitless_stat_activity WHERE distributed_session_id='7E4CBBD3E1AF9ABA'; subcluster_id | subcluster_type | distributed_session_id | distributed_session_state | datname | distributed_query_id ---------------+-----------------+------------------------+---------------------------+--------------------+ ---------------------- 1 | router | 7E4CBBD3E1AF9ABA | coordinator | postgres_limitless | 2 | router | 7E4CBBD3E1AF9ABA | participant | postgres_limitless | -8224553981424021413 3 | shard | 7E4CBBD3E1AF9ABA | participant | postgres_limitless | -8224553981424021413 4 | shard | 7E4CBBD3E1AF9ABA | participant | postgres_limitless | -8224553981424021413 5 | shard | 7E4CBBD3E1AF9ABA | participant | postgres_limitless | -8224553981424021413 6 | shard | 7E4CBBD3E1AF9ABA | participant | postgres_limitless | -8224553981424021413 (6 rows)
以下是输出参数:
-
subcluster_id
(文本)– 此进程所属的子集群 ID。 -
subcluster_type
(文本)– 此进程所属的子集群的类型:router
或shard
。 -
distributed_session_id
(文本)– 此进程所属的分布式会话的 ID。 -
distributed_session_state
(文本)– 这是协调器进程、参与者进程还是独立/非分布式进程(显示为NULL
)。 -
datname
(文本)– 此进程所连接的数据库。 -
distributed_query_id
(bigint)– 来自协调器节点的父查询的查询 ID。如果是父查询,则此列为NULL
。协调器节点将分布式查询 ID 向下推送到参与者节点。因此,对于参与者节点,分布式查询 ID 和查询 ID 的值不同。
其余各列与
pg_stat_activity
中的相同。 -
- limitless_stat_all_indexes
-
此视图包含数据库分片组中索引的使用情况统计数据。例如:
postgres_limitless=> SELECT schemaname, relname, indexrelname, idx_scan FROM rds_aurora.limitless_stat_all_indexes WHERE relname LIKE 'orders_ts%' ORDER BY indexrelname LIMIT 10; schemaname | relname | indexrelname | idx_scan ------------+----------------+---------------------+---------- ec_sample | orders_ts00001 | orders_ts00001_pkey | 196801 ec_sample | orders_ts00002 | orders_ts00002_pkey | 196703 ec_sample | orders_ts00003 | orders_ts00003_pkey | 196376 ec_sample | orders_ts00004 | orders_ts00004_pkey | 197966 ec_sample | orders_ts00005 | orders_ts00005_pkey | 195301 ec_sample | orders_ts00006 | orders_ts00006_pkey | 195673 ec_sample | orders_ts00007 | orders_ts00007_pkey | 194475 ec_sample | orders_ts00008 | orders_ts00008_pkey | 191694 ec_sample | orders_ts00009 | orders_ts00009_pkey | 193744 ec_sample | orders_ts00010 | orders_ts00010_pkey | 195421 (10 rows)
- limitless_stat_all_tables
-
此视图包含数据库分片组中当前数据库中所有表的统计数据。这在跟踪清理操作和数据操纵语言(DML)操作时很有用。例如:
postgres_limitless=> SELECT subcluster_id, subcluster_type, relname, n_ins_since_vacuum, n_tup_ins, last_vacuum FROM rds_aurora.limitless_stat_all_tables WHERE relname LIKE 'orders_ts%' ORDER BY relname LIMIT 10; subcluster_id | subcluster_type | relname | n_ins_since_vacuum | n_tup_ins | last_vacuum ---------------+-----------------+----------------+--------------------+-----------+------------- 5 | shard | orders_ts00001 | 34779 | 196083 | 5 | shard | orders_ts00002 | 34632 | 194721 | 5 | shard | orders_ts00003 | 34950 | 195965 | 5 | shard | orders_ts00004 | 34745 | 197283 | 5 | shard | orders_ts00005 | 34879 | 195754 | 5 | shard | orders_ts00006 | 34340 | 194605 | 5 | shard | orders_ts00007 | 33779 | 192203 | 5 | shard | orders_ts00008 | 33826 | 191293 | 5 | shard | orders_ts00009 | 34660 | 194117 | 5 | shard | orders_ts00010 | 34569 | 195560 | (10 rows)
以下是输出参数:
-
subcluster_id
(文本)– 此进程所属的子集群 ID。 -
subcluster_type
(文本)– 此进程所属的子集群的类型:router
或shard
。 -
relname
(名称)– 表的名称。
其余各列与
pg_stat_all_tables
中的相同。 -
- limitless_stat_database
-
此视图包含数据库分片组中所有数据库的统计数据。每个节点的每个数据库返回一行。例如:
postgres_limitless=> SELECT subcluster_id, subcluster_type, datname, blks_read, blks_hit FROM rds_aurora.limitless_stat_database WHERE datname='postgres_limitless'; subcluster_id | subcluster_type | datname | blks_read | blks_hit ---------------+-----------------+--------------------+-----------+---------- 1 | router | postgres_limitless | 484 | 34371314 2 | router | postgres_limitless | 673 | 33859317 3 | shard | postgres_limitless | 1299 | 17749550 4 | shard | postgres_limitless | 1094 | 17492849 5 | shard | postgres_limitless | 1036 | 17485098 6 | shard | postgres_limitless | 1040 | 17437257 (6 rows)
以下是输出参数:
-
subcluster_id
(文本)– 此进程所属的子集群 ID。 -
subcluster_type
(文本)– 此进程所属的子集群的类型:router
或shard
。 -
datname
(文本)– 数据库的名称。
其余各列与
pg_stat_database
中的相同。 -
- limitless_stat_progress_vacuum
-
此视图包含有关正在进行的清理操作的信息。例如:
postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_progress_vacuum; -[ RECORD 1 ]----------+------------------ subcluster_id | 3 subcluster_type | shard distributed_session_id | A56D96E2A5C9F426 pid | 5270 datname | postgres nspname | public relname | customer_ts2 phase | vacuuming heap heap_blks_total | 130500 heap_blks_scanned | 100036 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples | 11184810 num_dead_tuples | 0 -[ RECORD 2 ]----------+------------------ subcluster_id | 3 subcluster_type | shard distributed_session_id | 56DF26A89EC23AB5 pid | 6854 datname | postgres nspname | public relname | sales_ts1 phase | vacuuming heap heap_blks_total | 43058 heap_blks_scanned | 24868 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuples | 8569523 num_dead_tuples | 0
以下是输出参数:
-
subcluster_id
(文本)– 此进程所属的子集群 ID。 -
subcluster_type
(文本)– 此进程所属的子集群的类型:router
或shard
。 -
distributed_session_id
(文本)– 启动清理操作的会话的标识符。 -
datname
(名称)– 正在进行清理的数据库。 -
nspname
(名称)– 正在清理的表的架构名称。如果被清理的表与用户连接的表不在同一个数据库中,则为null
。 -
relname
(名称)– 正在清理的表的名称。如果被清理的表与用户连接的表不在同一个数据库中,则为null
。
其余各列与
pg_stat_progress_vacuum
中的相同。 -
- limitless_stat_statements
-
此视图提供了一种跟踪在所有节点上运行的所有 SQL 语句的规划和运行统计数据的方法。
注意
必须安装 pg_stat_statements
扩展程序才能使用 limitless_stat_statements
视图。-- CREATE EXTENSION must be run by a superuser CREATE EXTENSION pg_stat_statements; -- Verify that the extension is created on all nodes in the DB shard group SELECT distinct node_id FROM rds_aurora.limitless_stat_statements LIMIT 10;
以下示例说明了
limitless_stat_statements
视图的用法。postgres_limitless=> SELECT subcluster_id, subcluster_type, distributedqueryid, username, dbname FROM rds_aurora.limitless_stat_statements; subcluster_id | subcluster_type | distributedqueryid | username | dbname ---------------+-----------------+---------------------+---------------------------------+------------------- 1 | router | | postgres | postgres_limitless 1 | router | | postgres | postgres_limitless 1 | router | | postgres | postgres_limitless 1 | router | 5103284549693281378 | aurora_limitless_metadata_admin | postgres_limitless 1 | router | | postgres | postgres_limitless 1 | router | | aurora_limitless_metadata_admin | postgres_limitless 1 | router | | postgres | postgres_limitless 1 | router | | postgres | postgres_limitless [...]
以下是输出参数:
-
subcluster_id
(文本)– 此进程所属的子集群 ID。 -
subcluster_type
(文本)– 此进程所属的子集群的类型:router
或shard
。 -
distributedqueryid
(bigint)– 来自协调器节点的父查询的查询 ID。如果是父查询,则此列为NULL
。协调器节点将分布式查询 ID 向下推送到参与者节点。因此,对于参与者节点,分布式查询 ID 和查询 ID 的值不同。 -
username
(名称)– 查询语句的用户。 -
dbname
(名称)– 运行查询的数据库。
其余列与 pg_stat_statements
中的列相同。 -
- limitless_stat_statements_info
-
此视图包含
limitless_stat_statements
视图的统计数据。每行都包含来自每个节点的 pg_stat_statements_info视图的数据。 subcluster_id
列标识每个节点。postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_statements_info; subcluster_id | subcluster_type | dealloc | stats_reset ---------------+-----------------+---------+------------------------------- 1 | router | 0 | 2023-06-30 21:22:09.524781+00 2 | router | 0 | 2023-06-30 21:21:40.834111+00 3 | shard | 0 | 2023-06-30 21:22:10.709942+00 4 | shard | 0 | 2023-06-30 21:22:10.740179+00 5 | shard | 0 | 2023-06-30 21:22:10.774282+00 6 | shard | 0 | 2023-06-30 21:22:10.808267+00 (6 rows)
以下是输出参数:
-
subcluster_id
(文本)– 此进程所属的子集群 ID。
其余列与 pg_stat_statements_info
中的列相同。 -
- limitless_stat_subclusters
-
此视图包含路由器与其他节点之间的网络统计数据。它包含每对路由器和其他节点的一行,例如:
postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_subclusters; orig_subcluster | orig_instance_az | dest_subcluster | dest_instance_az | latency_us | latest_collection | failed_requests | received_bytes | sent_bytes | same_az_requests | cross_az_requests | stat_reset_timestamp -----------------+------------------+-----------------+------------------+------------+-------------------------------+-----------------+----------------+------------+------------------+-------------------+------------------------------- 3 | us-west-2b | 2 | us-west-2a | 847 | 2024-10-07 17:25:39.518617+00 | 0 | 35668633 | 92090171 | 0 | 302787 | 2024-10-05 12:39:55.239675+00 3 | us-west-2b | 4 | us-west-2b | 419 | 2024-10-07 17:25:39.546376+00 | 0 | 101190464 | 248795719 | 883478 | 0 | 2024-10-05 12:39:55.231218+00 3 | us-west-2b | 5 | us-west-2c | 1396 | 2024-10-07 17:25:39.52122+00 | 0 | 72864849 | 172086292 | 0 | 557726 | 2024-10-05 12:39:55.196412+00 3 | us-west-2b | 6 | us-west-2c | 729 | 2024-10-07 17:25:39.54828+00 | 0 | 35668584 | 92090171 | 0 | 302787 | 2024-10-05 12:39:55.247334+00 3 | us-west-2b | 7 | us-west-2a | 1702 | 2024-10-07 17:25:39.545307+00 | 0 | 71699576 | 171634844 | 0 | 556278 | 2024-10-05 12:39:52.715168+00 2 | us-west-2a | 3 | us-west-2b | 868 | 2024-10-07 17:25:40.293927+00 | 0 | 35659611 | 92011872 | 0 | 302817 | 2024-10-05 12:39:54.420758+00 2 | us-west-2a | 4 | us-west-2b | 786 | 2024-10-07 17:25:40.296863+00 | 0 | 102437253 | 251838024 | 0 | 895060 | 2024-10-05 12:39:54.404081+00 2 | us-west-2a | 5 | us-west-2c | 1232 | 2024-10-07 17:25:40.292021+00 | 0 | 71990027 | 168828110 | 0 | 545453 | 2024-10-05 12:39:36.769549+00
以下是输出参数:
-
orig_subcluster
(文本)– 发起通信的路由器 ID -
orig_subcluster_az
(文本)– 发起方路由器的可用区(AZ) -
dest_subcluster
(文本)– 目标节点的 ID -
dest_subcluster_az
(文本)– 目标节点上次收集的可用区 -
latency_us
(bigint)– 上次收集的节点间网络延迟,以微秒为单位。如果无法访问该节点,则该值为0
。 -
latest_collection
(时间戳)– 最新可用区集合的时间戳和目标节点的延迟 -
failed_requests
(bigint)– 失败的内部请求的累积计数 -
received_bytes
(bigint)– 从该节点接收到的估计累积字节数 -
sent_bytes
(bigint)– 发送到该节点的估计累积字节数 -
same_az_requests
(bigint)– 当该节点与发起方路由器位于同一可用区时,向该节点发出的内部数据库请求的累积数量 -
cross_az_requests
(bigint)– 当该节点与发起方路由器位于不同可用区时,向该节点发出的内部数据库请求的累积数量 -
stat_reset_timestamp
(时间戳)-上次重置此视图的累积统计数据的时间戳
-
- limitless_statio_all_indexes
-
此视图包含数据库分片组中所有索引的输入/输出(I/O)统计数据。例如:
postgres_limitless=> SELECT * FROM rds_aurora.limitless_statio_all_indexes WHERE relname like'customers_ts%'; subcluster_id | subcluster_type | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit ---------------+-----------------+------------+-------------------+-------------------------------------+ --------------+-------------- 3 | shard | public | customers_ts00002 | customers_ts00002_customer_name_idx | 1 | 0 3 | shard | public | customers_ts00001 | customers_ts00001_customer_name_idx | 1 | 0 4 | shard | public | customers_ts00003 | customers_ts00003_customer_name_idx | 1 | 0 4 | shard | public | customers_ts00004 | customers_ts00004_customer_name_idx | 1 | 0 5 | shard | public | customers_ts00005 | customers_ts00005_customer_name_idx | 1 | 0 5 | shard | public | customers_ts00006 | customers_ts00006_customer_name_idx | 1 | 0 6 | shard | public | customers_ts00007 | customers_ts00007_customer_name_idx | 1 | 0 6 | shard | public | customers_ts00008 | customers_ts00008_customer_name_idx | 1 | 0 (8 rows)
- limitless_statio_all_tables
-
此视图包含数据库分片组中所有表的输入/输出(I/O)统计数据。例如:
postgres_limitless=> SELECT subcluster_id, subcluster_type, schemaname, relname, heap_blks_read, heap_blks_hit FROM rds_aurora.limitless_statio_all_tables WHERE relname LIKE 'customers_ts%'; subcluster_id | subcluster_type | schemaname | relname | heap_blks_read | heap_blks_hit ---------------+-----------------+------------+-------------------+----------------+--------------- 3 | shard | public | customers_ts00002 | 305 | 57780 3 | shard | public | customers_ts00001 | 300 | 56972 4 | shard | public | customers_ts00004 | 302 | 57291 4 | shard | public | customers_ts00003 | 302 | 57178 5 | shard | public | customers_ts00006 | 300 | 56932 5 | shard | public | customers_ts00005 | 302 | 57386 6 | shard | public | customers_ts00008 | 300 | 56881 6 | shard | public | customers_ts00007 | 304 | 57635 (8 rows)
- limitless_tables
-
此视图包含有关 Aurora PostgreSQL Limitless Database 中表的信息。
postgres_limitless=> SELECT * FROM rds_aurora.limitless_tables; table_gid | local_oid | schema_name | table_name | table_status | table_type | distribution_key -----------+-----------+-------------+-------------+--------------+-------------+------------------ 5 | 18635 | public | placeholder | active | placeholder | 6 | 18641 | public | ref | active | reference | 7 | 18797 | public | orders | active | sharded | HASH (order_id) 2 | 18579 | public | customer | active | sharded | HASH (cust_id) (4 rows)
- limitless_table_collocations
-
此视图包含有关并置的分片表的信息。
在以下示例中,
orders
和customers
表并置,users
和followers
表并置。并置的表具有相同的collocation_id
。postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocations ORDER BY collocation_id; collocation_id | schema_name | table_name ----------------+-------------+------------ 2 | public | orders 2 | public | customers 5 | public | users 5 | public | followers (4 rows)
- limitless_table_collocation_distributions
-
此视图显示了每个并置的密钥分配几率。
postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocation_distributions ORDER BY collocation_id, lower_bound; collocation_id | subcluster_id | lower_bound | upper_bound ----------------+---------------+----------------------+---------------------- 2 | 6 | -9223372036854775808 | -4611686018427387904 2 | 5 | -4611686018427387904 | 0 2 | 4 | 0 | 4611686018427387904 2 | 3 | 4611686018427387904 | 9223372036854775807 5 | 6 | -9223372036854775808 | -4611686018427387904 5 | 5 | -4611686018427387904 | 0 5 | 4 | 0 | 4611686018427387904 5 | 3 | 4611686018427387904 | 9223372036854775807 (8 rows)