资源池化的函数
ss_buffer_ctrl()
描述:显示buffer对应的buf_ctrl上记录的信息
返回值类型:record
示例1:查询特定的buffer的buf_ctrl信息
openGauss=# select * from ss_buffer_ctrl() where bufferid = 20; bufferid | is_remote_dirty | lock_mode | is_edp | force_request | need_flush | buf_id | state | pblk_relno | pblk_blkno | pblk_lsn | seg_fileno | seg_blockno ----------+-----------------+-----------+--------+---------------+------------+--------+-------+------------+------------+----------+------------+------------- 20 | 0 | 1 | 0 | 0 | 0 | 19 | 32 | 0 | 4294967295 | 0 | 2 | 4375 (1 row)
示例2:查询DMS页面分布式锁不为NULL的buffer数量
openGauss=# select count(*) from ss_buffer_ctrl() where lock_mode > 0; count ------- 258 (1 row)
ss_txnstatus_cache_stat()
描述:返回事务信息缓存和事务信息获取的相关统计信息。注意相关count统计项并不是精确值,本统计函数的目的是性能调优分析,而非精确统计。
返回值类型:record
vcache_gets:事务信息从变量缓存获取的次数
hcache_gets:事务信息从哈希缓存获取的次数
nio_gets:事务信息从网络I/O获取的次数
avg_hcache_gettime_us:从哈希缓存获取的平均耗时,单位us
avg_nio_gettime_us:从网络I/O获取的平均耗时,单位us
cache_hit_rate:缓存命中率
hcache_eviction:缓存淘汰计数
avg_eviction_refcnt:每个缓存条目被淘汰前的平均被引用次数示例: 主备TPCC写/读,备机查询事务信息缓存统计项。
openGauss=# select * from ss_txnstatus_cache_stat(); vcache_gets | hcache_gets | nio_gets | avg_hcache_gettime_us | avg_nio_gettime_us | cache_hit_rate | hcache_eviction | avg_eviction_refcnt -------------+-------------+----------+-----------------------+--------------------+-----------------+-----------------+--------------------- 263809 | 782159 | 275012 | 1.73883698838727 | 756.186410047562 | .79181213947221 | 0 | 0 (1 row)
query_node_reform_info()
描述:查询集群reform相关统计信息。
返回值类型:record
reform_node_id:数据库节点编号,取值范围:[0, 63]
reform_type:集群发生reform的类型,取值范围:[Normal reform、Failover、Switchover]
reform_start_time:reform开始的时间
reform_end_time:reform结束的时间
is_reform_success:reform是否成功
redo_start_time:日志回放开始的时间
redo_end_time:日志回放结束的时间
xlog_total_bytes:总共回放的日志量
hashmap_construct_time:hashmap构造的时间
action:节点的动作,取值范围:[kick off、join in、stable]
约束:该函数只能在数据库主节点进行查询;查询结果中备节点的信息仅支持查看reform_node_id和action,其他信息无效
示例1:备节点退出集群
openGauss=# select * from query_node_reform_info(); reform_node_id | reform_type | reform_start_time | reform_end_time | is_reform_success | redo_start_time | redo_end_time | xlog_total_bytes | hashmap_construct_time | action ----------------+---------------+--------------------------+--------------------------+-------------------+-----------------+---------------+------------------+------------------------+---------- 0 | Normal reform | 2023-09-21 16:37:06.520 | 2023-09-21 16:37:06.568 | t | - | - | -1 | - | stable 1 | - | - | - | t | - | - | -1 | - | kick off (2 rows)
示例2:备节点加入集群
openGauss=# select * from query_node_reform_info(); reform_node_id | reform_type | reform_start_time | reform_end_time | is_reform_success | redo_start_time | redo_end_time | xlog_total_bytes | hashmap_construct_time | action ----------------+---------------+--------------------------+--------------------------+-------------------+-----------------+---------------+------------------+------------------------+--------- 0 | Normal reform | 2023-09-21 16:37:46.414 | 2023-09-21 16:37:47.817 | t | - | - | -1 | - | stable 1 | - | - | - | t | - | - | -1 | - | join in (2 rows)
示例3:主节点故障,集群failover
openGauss=# select * from query_node_reform_info(); reform_node_id | reform_type | reform_start_time | reform_end_time | is_reform_success | redo_start_time | redo_end_time | xlog_total_bytes | hashmap_construct_time | action ----------------+-------------+--------------------------+--------------------------+-------------------+--------------------------+--------------------------+------------------+--------------------------+---------- 0 | - | - | - | t | - | - | -1 | - | kick off 1 | Failover | 2023-09-21 16:56:45.893 | 2023-09-21 16:56:50.702 | t | 2023-09-21 16:56:50.282 | 2023-09-21 16:56:50.385 | 288 | 2023-09-21 16:56:50.385 | stable (2 rows)
示例4:集群主动进行switchover
openGauss=# select * from query_node_reform_info(); reform_node_id | reform_type | reform_start_time | reform_end_time | is_reform_success | redo_start_time | redo_end_time | xlog_total_bytes | hashmap_construct_time | action ----------------+-------------+--------------------------+--------------------------+-------------------+-----------------+---------------+------------------+------------------------+-------- 0 | Switchover | 2023-09-21 16:59:25.646 | 2023-09-21 16:59:29.182 | t | - | - | -1 | - | stable 1 | - | - | - | t | - | - | -1 | - | stable (2 rows)
query_page_distribution_info(relname TEXT, forkNum INT4, blockNum INT4)
描述:根据
relname + forkNum + blockNum
查询页面在集群中的分布信息。返回值类型:record
instance_id:数据库节点id,取值范围:[0, 63]
is_master:是否是页面master
is_owner:是否是页面owner
is_copy:是否持有副本
lock_mode:节点持有的锁模式
mem_lsn:页面在内存中的lsn
disk_lsn:页面在磁盘上的lsn
is_dirty:内存中的页面是否是脏页
约束:该函数只能在数据库主节点进行查询
示例:向表中插入一行数据,然后立即查询页面的分布信息
openGauss=# create table tb(id int); CREATE TABLE openGauss=# insert into tb values(0);select * from query_page_distribution_info('tb', 0, 0); INSERT 0 1 instance_id | is_master | is_owner | is_copy | lock_mode | mem_lsn | disk_lsn | is_dirty -------------+-----------+----------+---------+----------------+------------+------------+---------- 0 | t | t | f | Exclusive lock | 1975555208 | 1975555112 | t (1 row)
dss_io_stat(duration INT4)
描述:统计在给定时间间隔内IO读写速度和次数,通过
duration
指定时间间隔,单位秒。返回值类型:record
read_kilobyte_per_sec:给定时间内DSS读取数据的速度,单位KB/s
write_kilobyte_per_sec:给定时间内DSS写入数据的速度,单位KB/s
io_times:给定时间间隔内DSS IO的调用次数
约束:
duration < 60
示例:主节点向表中写入数据,查询2秒内DSS IO的统计信息
openGauss=# select * from dss_io_stat(2); read_kilobyte_per_sec | write_kilobyte_per_sec | io_times -----------------------+------------------------+---------- 404 | 25664 | 3158 (1 row)