Statistics Functions

pg_stat_get_activity(integer)

Description: Returns a record of a background process with a specific PID. When the parameter is NULL, it returns records of all active background processes. The result is a subset of the PG_STAT_ACTIVITY view, excluding the connection_info column.

Return type: SETOF record.

gs_stack()

Description: Obtains the stack information of a CN or DN process. The input parameters actor_name and tid need to be obtained through views or other functions. lwtid is not supported. This function is only supported by clusters of version 25.5.0 or later.

gs_stack() provides a more flexible and faster method for obtaining stacks than the gdb and gstack commands. It is recommended that administrators use this tool only during fault analysis and locating. It is not recommended that this tool be used as a routine monitoring tool.

Return value columns:

actor_name: name of the actor where the thread is located.
tid: thread ID.
lwtid: lightweight thread ID.
stack: stack information about the tid or lwtid thread.

The gs_stack() function can be used in the following ways:

Method 1: If the input parameter of the gs_stack() function is empty, the stacks of all threads on the CNs and DNs are printed.

You can use gsql to connect to a CN.
Obtaining the running stacks of all threads on CNs and all DNs takes a long time, which affects normal service running. Therefore, you are advised not to use this function frequently.

      
           select * from gs_stack();
  actor_name  |       tid       | lwtid  |                                                                                                                                                                                                                                                                                                                               
                                   stack                                                                                                                                                                                                                                                                                                                                 

--------------+-----------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 coordinator1 | 106927505866816 | 186762 | GsStackBacktrace(int) + 0x1a                                                                                                                                                                                                                                                                                                  

                                 +
              |                 |        | 0x7ffb45f6d1f0                                                                                                                                                                                                                                                                                                                

                                 +
              |                 |        | __poll + 0x4f                                                                                                                                                                                                                                                                                                                 

                                 +
              |                 |        | poll + 0xa0                                                                                                                                                                                                                                                                                                                   

                                 +
              |                 |        | poll(pollfd*, unsigned long, int, void (*)()) + 0x19e                                                                                                                                                                                                                                                                         

                                 +
              |                 |        | ServerLoop() + 0x570                                                                                                                                                                                                                                                                                                          

                                 +
              |                 |        | PmStartupThreads() + 0x167                                                                                                                                                                                                                                                                                                    

                                 +
              |                 |        | PostmasterMain(int, char**) + 0x22f                                                                                                                                                                                                                                                                                                                         
              |                 |        | 
 coordinator1 | 106927505998400 | 186773 | GsStackBacktrace(int) + 0x1a                                                                                                                                                                                                                                                                                                  
                                 +
              |                 |        | 0x7ffb45f6d1f0                                                                                                                                                                                                                                                                                                                

                                 +
              |                 |        | 0x7ffb45fb41aa                                                                                                                                                                                                                                                                                                                

                                 +
              |                 |        | pthread_cond_clockwait + 0x1e2                                                                                                                                                                                                                                                                                                

                                 +
              |                 |        | std::__condvar::wait_until(std::mutex&, int, timespec&) + 0x39

Method 2: Set the input parameter of the gs_stack(actor_name) function to the specified actor_name, indicating that the stacks of all threads on the actor named actor_name are printed.

You can use gsql to connect to a CN.
actor_name must be the name of a CN or DN. Otherwise, the error message "invalid actor_name" is displayed.
The value of actor_name can be obtained from other views or functions (for example, the pgxc_query_wait_status function).

      
           select * from gs_stack('coordinator1');
  actor_name  |       tid       | lwtid  |                                                                                                                                                                                                                                                                                                                               
                                   stack                                                                                                                                                                                                                                                                                                                                 

--------------+-----------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 coordinator1 | 106927505866816 | 186762 | GsStackBacktrace(int) + 0x1a                                                                                                                                                                                                                                                                                                  

                                 +
              |                 |        | 0x7ffb45f6d1f0                                                                                                                                                                                                                                                                                                                

                                 +
              |                 |        | __poll + 0x4f                                                                                                                                                                                                                                                                                                                 

                                 +
              |                 |        | poll + 0xa0                                                                                                                                                                                                                                                                                                                   

                                 +
              |                 |        | poll(pollfd*, unsigned long, int, void (*)()) + 0x19e                                                                                                                                                                                                                                                                         

                                 +
              |                 |        | ServerLoop() + 0x570                                                                                                                                                                                                                                                                                                          

                                 +
              |                 |        | PmStartupThreads() + 0x167                                                                                                                                                                                                                                                                                                    

                                 +
              |                 |        | PostmasterMain(int, char**) + 0x22f

Method 3: The input parameters of the gs_stack(actor_name, tid) function are actor_name and tid, indicating that the stack of the specified tid thread on the actor named actor_name is printed.

You can use gsql to connect to a CN.
actor_name must be the name of a CN or DN. Otherwise, the error message "invalid actorName" is displayed.
The values of actor_name and tid need to be obtained from other views or functions (for example, the pgxc_query_wait_status function).
Obtains the running stack of a specified thread in a specified CN or DN process. This method is recommended because it takes a short time and does not affect service running.

      
           select * from gs_stack('coordinator1',106927505998400);
  actor_name  |       tid       | lwtid  |                                                                                                                                                                                       stack                                                                                                                                   

--------------+-----------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------
 coordinator1 | 106927505998400 | 186773 | GsStackBacktrace(int) + 0x1a                                                                                                                                                                                                                                                                                                  
                                                   +
              |                 |        | 0x7ffb45f6d1f0                                                                                                                                                                                                                                                                                                                
                                                   +
              |                 |        | 0x7ffb45fb41aa                                                                                                                                                                                                                                                                                                                
                                                   +
              |                 |        | pthread_cond_clockwait + 0x1e2                                                                                                                                                                                                                                                                                                
                                                   +
              |                 |        | std::__condvar::wait_until(std::mutex&, int, timespec&) + 0x39                                                                                                                                                                                                                                                                
                                                   +
              |                 |        

(1 row)

gs_wlm_session_statistics()

Description: Retrieves monitoring information for SQL statements currently being executed. It does not capture monitoring information for completed SQL statements. Each row in the returned results indicates one statement.

Fields returned by the function:

Field	Type	Description
session_id	text	Session ID of a statement.
statement_id	text	Statement ID.
start_time	timestampt with zone	Start time of a statement.
duration	bigint	Statement running duration. Unit: ms.
estimate_total_time	bigint	Estimated statement running duration. Unit: ms.
estimate_left_time	bigint	Estimated remaining running duration of a statement. Unit: ms.
cn_actor_info	text	Serverless information of a CN.
dn_actor_info	text	Serverless information of a DN.
startup_coordinator_duration	bigint	Duration to start a CN. Unit: ms.
create_session_duration	bigint	Duration to create a session. Unit: ms.
enqueue_statement_duration	bigint	Duration to add a statement to a task queue. Unit: ms.
queue_duration	bigint	Queuing duration of a statement in a task queue. Unit: ms.
write_resultset_duration	bigint	Duration to write a statement to a result set. Unit: ms.
queryid	bigint	ID of a debug query.
schemaname	text	Schema name when a statement is executed.
query	text	Statement.
query_plan	text	Explain information.
tid	bigint	Thread ID.
parse_time	bigint	Time from parsing to execution start. Unit: ms.
estimate_memory	int	Estimated memory of a statement. Unit: MB.
min_dn_time	bigint	Minimum DN duration. Unit: ms.
max_dn_time	bigint	Maximum DN duration. Unit: ms.
average_dn_time	bigint	Average DN duration. Unit: ms.
dn_time_skew_percent	int	Skew percentage of DN duration. Unit: %.
dn_min_cpu_time	bigint	Minimum DN CPU duration. Unit: ms.
dn_max_cpu_time	bigint	Maximum DN CPU duration. Unit: ms.
dn_average_cpu_time	bigint	Average DN CPU duration. Unit: ms.
dn_total_cpu_time	bigint	Total DN CPU duration. Unit: ms.
dn_cpu_skew_percent	int	Skew percentage of DN CPU duration. Unit: %.
dn_min_peak_memory	int	Minimum peak memory of all DNs. Unit: MB.
dn_max_peak_memory	int	Maximum peak memory of all DNs. Unit: MB.
dn_average_peak_memory	int	Average peak memory of all DNs. Unit: MB.
dn_memory_skew_percent	int	Memory skew rate. Unit: %.
dn_spill_)num	int	Number of DNs with disk spills.
dn_spill_count	int	Number of DNs with disk spills.
dn_min_spill_size	bigint	Maximum spill size. Unit: MB.
dn_max_spill_size	bigint	Minimum spill size. Unit: MB.
dn_average_spill_size	bigint	Average spill size. Unit: MB.
dn_spill_skew_percent	int	Spill skew rate. Unit: %.
dn_write_disk_total_size	bigint	Total size of data written to disks on a DN. Unit: MB.
dn_write_disk_max_size	bigint	Maximum amount of data written to disks on a DN. Unit: MB.
dn_write_disk_min_size	bigint	Minimum amount of data written to disks on a DN. Unit: MB.
dn_spill_obs_total_size	bigint	Total amount of data spilled to OBS by a DN. Unit: MB.
dn_spill_obs_max_size	bigint	Maximum amount of data spilled to OBS by a DN. Unit: MB.
dn_spill_obs_min_size	bigint	Minimum amount of data spilled to OBS by a DN. Unit: MB.
dn_write_obs_total_size	bigint	Total amount of data directly written to OBS by a DN. Unit: MB.
dn_write_obs_max_size	bigint	Maximum amount of data directly written to OBS by a DN. Unit: MB.
dn_write_obs_min_size	bigint	Minimum amount of data directly written to OBS by a DN. Unit: MB.
cn_spill_count	int	Number of spills from a CN.
cn_spill_size	bigint	Total size of data spilled from a CN. Unit: MB.
cn_spill_obs_size	bigint	Amount of data spilled to OBS by a CN. Unit: MB.
cn_write_obs_size	bigint	Amount of data directly written to OBS by a CN. Unit: MB.
cn_write_obs_disk_size	bigint	Amount of data spilled to local disks by a CN. Unit: MB.
disk_cache_hit_ratio	numeric	Disk cache hit rate.
disk_cache_disk_read_size	bigint	Read size of disk cache. Unit: byte.
disk_cache_disk_write_size	bigint	Write size of disk cache. Unit: byte.
disk_cache_remote_read_size	bigint	Remote read size of disk cache. Unit: byte.
disk_cache_remote_read_time	bigint	Count of remote reads from disk cache. Unit: times.
vfs_scan_bytes	bigint	Bytes scanned by the OBS v file system. Unit: byte.
vfs_remote_read_bytes	bigint	Bytes remotely read by the OBS v file system. Unit: byte.
disk_cache_load_time	bigint	Local load time of disk cache. Unit: μs.
disk_cache_conflict_count	bigint	Hash collision count of disk cache blocks. Unit: times.
disk_cache_error_count	bigint	Error count of disk cache. Unit: times.
disk_cache_error_code	bigint	Error code of disk cache.
obs_io_req_avg_rtt	bigint	Average round-trip time of OBS I/O requests. Unit: μs.
obs_io_req_avg_latency	bigint	Average latency of OBS I/O requests. Unit: μs.
obs_io_req_latency_gt_1s	bigint	Count of OBS I/O request delays exceeding 1s. Unit: times.
obs_io_req_latency_gt_10s	bigint	Count of OBS I/O request delays exceeding 10s. Unit: times.
obs_io_req_count	bigint	Number of OBS I/O requests. Unit: times.
obs_io_req_count	bigint	Number of OBS I/O request retries. Unit: times.
obs_io_req_rate_limit_count	bigint	Rate limit count of OBS I/O requests. Unit: times.

pg_get_actor_stat()

Description: Obtains statistics about some monitoring items, including creating a CN, starting a CN, creating a session, adding a statement to a queue, executing a statement, writing a result set, obtaining the execution status, and retrieving the execution result. The statistics are recorded each time one of these events occurs.

Return type:

Field	Type	Description
event_name	text	Name of a monitoring item.
object_name	text	Monitoring item ID.
start_time	timestampt with zone	Start time of a monitoring item.
end_time	timestampt with zone	End time of a monitoring item.
comment	text	Content of the comment.