HomeMogDBMogDB StackUqbar
v3.0

Documentation:v3.0

Supported Versions:

PG_THREAD_WAIT_STATUS

PG_THREAD_WAIT_STATUS allows you to test the block waiting status about the backend thread and auxiliary thread of the current instance.

Table 1 PG_THREAD_WAIT_STATUS columns

Name Type Description
node_name text Current node name
db_name text Database name
thread_name text Thread name
query_id bigint Query ID. The value of this column is the same as that of debug_query_id.
tid bigint Current thread ID
sessionid bigint Current session ID
lwtid integer Lightweight thread ID
psessionid bigint Parent session ID
tlevel integer Level of the streaming thread
smpid integer Concurrent thread ID
wait_status text Waiting status of the current thread. For details about the waiting status, see [Table 2](#Waiting status list).
wait_event text If wait_status is acquire lock, acquire lwlock, or wait io, this column describes the lock, lightweight lock, and I/O information, respectively. If wait_status is not any of the three values, this column is empty.
locktag text Information about the lock that the current thread is waiting for
lockmode text Lock mode that the current thread is waiting to obtain The values include modes in the table-level lock, row-level lock, and page-level lock.
block_sessionid bigint ID of the session that blocks the current thread from obtaining the lock
global_sessionid text Global session ID

The waiting statuses in the wait_status column are as follows:

Table 2 Waiting status list

Value Description
none Waiting for no event
acquire lock Waiting for locking until the locking succeeds or times out
acquire lwlock Waiting for a lightweight lock
wait io Waiting for I/O completion
wait cmd Waiting for reading network communication packets to complete
wait pooler get conn Waiting for the pooler to obtain the connection
wait pooler abort conn Waiting for the pooler to terminate the connection
wait pooler clean conn Waiting for the pooler to clear connections
pooler create conn: [nodename], total N Waiting for the pooler to set up a connection. The connection is being established with the node specified by nodename, and there are N connections waiting to be set up.
get conn Obtaining the connection to other nodes
set cmd: [nodename] Waiting for running the SET, RESET, TRANSACTION BLOCK LEVEL PARA SET, or SESSION LEVEL PARA SET statement on the connection. The statement is being executed on the node specified by nodename.
cancel query Canceling the SQL statement that is being executed through the connection
stop query Stopping the query that is being executed through the connection
wait node: nodename, total N, [phase] Waiting for receiving data from a connected node. The thread is waiting for data from the plevel thread of the node specified by nodename. The data of N connections is waiting to be returned. If phase is included, the possible phases are as follows:
- begin: The transaction is being started.
- commit: The transaction is being committed.
- rollback: The transaction is being rolled back.
wait transaction sync: xid Waiting for synchronizing the transaction specified by xid
wait wal sync Waiting for the completion of WAL of synchronization from the specified LSN to the standby instance
wait data sync Waiting for the completion of data page synchronization to the standby instance
wait data sync queue Waiting for putting the data pages that are in the row-store or the CU in the column-store into the synchronization queue
flush data: nodename, [phase] Waiting for sending data to the plevel thread of the node specified by nodename. If phase is included, the possible phase is wait quota, indicating that the current communication flow is waiting for the quota value.
stream get conn: [nodename], total N Waiting for connecting to the consumer object of the node specified by nodename when the stream flow is initialized. There are N consumers waiting to be connected.
wait producer ready: nodename, total N Waiting for each producer to be ready when the stream flow is initialized. The thread is waiting for the procedure of the plevel thread on the nodename node to be ready. There are N producers waiting to be ready.
synchronize quit Waiting for the threads in the stream thread group to quit when the stream plan ends
wait stream nodegroup destroy Waiting for destroying the stream node group when the stream plan ends
wait active statement Waiting for job execution under resource and load control
analyze: [relname], [phase] The thread is doing ANALYZE to the relname table. If phase is included, the possible phase is autovacuum, indicating that the database automatically enables the AutoVacuum thread to execute ANALYZE.
vacuum: [relname], [phase] The thread is doing VACUUM to the relname table. If phase is included, the possible phase is autovacuum, indicating that the database automatically enables the AutoVacuum thread to execute VACUUM.
vacuum full: [relname] The thread is doing VACUUM FULL to the relname table.
create index An index is being created.
HashJoin - [ build hash | write file ] The HashJoin operator is being executed. In this phase, you need to pay attention to the execution time-consuming.
- build hash: The HashJoin operator is creating a hash table.
- write file: The HashJoin operator is writing data to disks.
HashAgg - [ build hash | write file ] The HashAgg operator is being executed. In this phase, you need to pay attention to the execution time-consuming.
- build hash: The HashAgg operator is creating a hash table.
- write file: The HashAgg operator is writing data to disks.
HashSetop - [build hash | write file ] The HashSetop operator is being executed. In this phase, you need to pay attention to the execution time-consuming.
- build hash: The HashSetop operator is creating a hash table.
- write file: The HashSetop operator is writing data to disks.
Sort | Sort - [fetch tuple |write file] The Sort operator is used for sorting. fetch tuple indicates that the Sort operator is obtaining tuples, and write file indicates that the Sort operator is writing data to disks.
Material | Material - write file The Material operator is being executed. write file indicates that the Material operator is writing data to disks.
NestLoop The NestLoop operator is being executed.
wait memory Waiting for obtaining the memory
wait sync consumer next step Waiting for the consumer to execute the stream operator
wait sync producer next step Waiting for the producer to execute the stream operator

If wait_status is acquire lwlock, acquire lock, or wait io, there is an event performing I/O operations or waiting for obtaining the corresponding lightweight lock or transaction lock.

The following table describes the corresponding wait events when wait_status is acquire lwlock. If wait_event is extension, the lightweight lock is dynamically allocated and is not monitored.

Table 3 List of wait events corresponding to lightweight locks

wait_event Description
ShmemIndexLock Used to protect the primary index table, a hash table, in shared memory.
OidGenLock Used to prevent different threads from generating the same OID.
XidGenLock Used to prevent two transactions from obtaining the same transaction ID.
ProcArrayLock Used to prevent concurrent access to or concurrent modification on ProcArray shared arrays.
SInvalReadLock Used to prevent concurrent execution with invalid message deletion.
SInvalWriteLock Used to prevent concurrent execution with invalid message write and deletion.
WALInsertLock Used to prevent concurrent execution with WAL insertion.
WALWriteLock Used to prevent concurrent write from a WAL buffer to a disk.
ControlFileLock Used to prevent concurrent read/write or concurrent write/write on the pg_control file.
CheckpointLock Used to prevent multi-checkpoint concurrent execution.
CLogControlLock Used to prevent concurrent access to or concurrent modification on the Clog control data structure.
SubtransControlLock Used to prevent concurrent access to or concurrent modification on the subtransaction control data structure.
MultiXactGenLock Used to allocate a unique MultiXact ID in serial mode.
MultiXactOffsetControlLock Used to prevent concurrent read/write or concurrent write/write on pg_multixact/offset.
MultiXactMemberControlLock Used to prevent concurrent read/write or concurrent write/write on pg_multixact/members.
RelCacheInitLock Used to add a lock before any operations are performed on the init file when messages are invalid.
CheckpointerCommLock Used to send file flush requests to a checkpointer. The request structure needs to be inserted to a request queue in serial mode.
TwoPhaseStateLock Used to prevent concurrent access to or modification on two-phase information sharing arrays.
TablespaceCreateLock Used to check whether a tablespace already exists.
BtreeVacuumLock Used to prevent VACUUM from clearing pages that are being used by B-tree indexes.
AutovacuumLock Used to access the autovacuum worker array in serial mode.
AutovacuumScheduleLock Used to distribute tables requiring VACUUM in serial mode.
AutoanalyzeLock Used to obtain and release resources related to a task that allows for autoanalyze execution.
SyncScanLock Used to determine the start position of a relfilenode during heap scanning.
NodeTableLock Used to protect a shared structure that stores database node information.
PoolerLock Used to prevent two threads from simultaneously obtaining the same connection from a connection pool.
RelationMappingLock Used to wait for the mapping file between system catalogs and storage locations to be updated.
AsyncCtlLock Used to prevent concurrent access to or concurrent modification on the sharing notification status.
AsyncQueueLock Used to prevent concurrent access to or concurrent modification on the sharing notification queue.
SerializableXactHashLock Used to prevent concurrent read/write or concurrent write/write on a sharing structure for serializable transactions.
SerializableFinishedListLock Used to prevent concurrent read/write or concurrent write/write on a shared linked list for completed serial transactions.
SerializablePredicateLockListLock Used to protect a linked list of serializable transactions that have locks.
OldSerXidLock Used to protect a structure that records serializable transactions that have conflicts.
FileStatLock Used to protect a data structure that stores statistics file information.
SyncRepLock Used to protect Xlog synchronization information during primary/standby replication.
DataSyncRepLock Used to protect data page synchronization information during primary/standby replication.
CStoreColspaceCacheLock Used to add a lock when CU space is allocated for a column-store table.
CStoreCUCacheSweepLock Used to add a lock when CU caches used by a column-store table are cyclically washed out.
MetaCacheSweepLock Used to add a lock when metadata is cyclically washed out.
ExtensionConnectorLibLock Adds a lock when a specific dynamic library is loaded or uninstalled in ODBC connection initialization scenarios.
SearchServerLibLock Used to add a lock on the file read operation when a specific dynamic library is initially loaded in GPU-accelerated scenarios.
LsnXlogChkFileLock Used to serially update the Xlog flush points for primary and standby servers recorded in a specific structure.
ReplicationSlotAllocationLock Used to add a lock when a primary server allocates stream replication slots during primary/standby replication.
ReplicationSlotControlLock Used to prevent concurrent update of stream replication slot status during primary/standby replication.
ResourcePoolHashLock Used to prevent concurrent access to or concurrent modification on a resource pool table, a hash table.
WorkloadStatHashLock Used to prevent concurrent access to or concurrent modification on a hash table that contains SQL requests from the primary node of the database side.
WorkloadIoStatHashLock Used to prevent concurrent access to or concurrent modification on a hash table that contains I/O information of the current database node.
WorkloadCGroupHashLock Used to prevent concurrent access to or concurrent modification on a hash table that contains Cgroup information.
OBSGetPathLock Used to prevent concurrent read/write or concurrent write/write on an OBS path.
WorkloadUserInfoLock Used to prevent concurrent access to or concurrent modification on a hash table that contains user information about load management.
WorkloadRecordLock Used to prevent concurrent access to or concurrent modification on a hash table that contains requests received by the primary node of the databases during adaptive memory management.
WorkloadIOUtilLock Used to protect a structure that records iostat and CPU load information.
WorkloadNodeGroupLock Used to prevent concurrent access to or concurrent modification on a hash table that contains node group information in memory.
JobShmemLock Used to protect global variables in the shared memory that is periodically read during a scheduled task.
OBSRuntimeLock Used to obtain environment variables, for example, GASSHOME.
LLVMDumpIRLock Used to export the assembly language for dynamically generating functions.
LLVMParseIRLock Used to compile and parse a finished IR function from the IR file at the start position of a query.
CriticalCacheBuildLock Used to load caches from a shared or local cache initialization file.
WaitCountHashLock Used to protect a shared structure in user statement counting scenarios.
BufMappingLock Used to protect operations on a shared-buffer mapping table.
LockMgrLock Used to protect the information about a common lock structure.
PredicateLockMgrLock Used to protect the information about a lock structure that has serializable transactions.
OperatorRealTLock Used to prevent concurrent access to or concurrent modification on a global structure that contains real-time data at the operator level.
OperatorHistLock Used to prevent concurrent access to or concurrent modification on a global structure that contains historical data at the operator level.
SessionRealTLock Used to prevent concurrent access to or concurrent modification on a global structure that contains real-time data at the query level.
SessionHistLock Used to prevent concurrent access to or concurrent modification on a global structure that contains historical data at the query level.
CacheSlotMappingLock Used to protect global CU cache information.
BarrierLock Used to ensure that only one thread is creating a barrier at a time.
dummyServerInfoCacheLock Used to protect a global hash table where the information about MogDB connections is cached.
RPNumberLock Used by a database node on a computing MogDB to count the number of threads for a task where plans are being executed.
ClusterRPLock Used to control concurrent access on MogDB load data maintained in a CCN of MogDB.
CBMParseXlogLock Used to protect the lock used when CBM parses Xlogs.
RelfilenodeReuseLock Used to prevent the link to a reused column attribute file from being canceled by mistake.
RcvWriteLock Used to prevent concurrent call of WalDataRcvWrite.
PercentileLock Used to protect global percentile buffers.
CSNBufMappingLock Used to protect CSN pages.
UniqueSQLMappingLock Used to protect a unique SQL hash table.
DelayDDLLock Used to prevent concurrent DDL operations.
CLOG Ctl Used to prevent concurrent access to or concurrent modification on the Clog control data structure.
Async Ctl Used to protect asynchronization buffers.
MultiXactOffset Ctl Used to protect SLRU buffers of a MultiXact offset.
MultiXactMember Ctl Used to protect SLRU buffer of a MultiXact member.
OldSerXid SLRU Ctl Used to protect SLRU buffers of old transaction IDs.
ReplicationSlotLock Used to protect a replication slot.
PGPROCLock Used to protect the PGPROC structure.
MetaCacheLock Used to protect meta caches.
DataCacheLock Used to protect data caches.
InstrUserLock Used to protect a user hash table.
BadBlockStatHashLock Used to protect the hash table global_bad_block_stat
BufFreelistLock Used to ensure the atomicity of free list operations in the shared buffer.
CUSlotListLock Used to control concurrent operations on the slot of the column-store buffer.
AddinShmemInitLock Used to protect the initialization of the shared memory object.
AlterPortLock Used to protect the coordinator node from changing the registration port number.
FdwPartitionCaheLock Management lock of the buffer of the HDFS partitioned table.
DfsConnectorCacheLock Management lock of the DFSConnector buffer.
DfsSpaceCacheLock Management lock of the HDFS tablespace management buffer.
FullBuildXlogCopyStartPtrLock Used to protect Xlog copy operations in the full build.
DfsUserLoginLock Used for HDFS user login and authentication.
LogicalReplicationSlotPersistentDataLock Used to protect data in the replication slot during logical replication.
WorkloadSessionInfoLock Used to protect the access to the session info memory hash table of the load management module.
InstrWorkloadLock Used to protect the access to the memory hash table that stores load management statistics.
PgfdwLock Used by the management instance to establish a connection to the foreign server.
InstanceTimeLock Used to obtain time information of sessions in an instance.
XlogRemoveSegLock Used to protect Xlog segment file recycling.
DnUsedSpaceHashLock Used to update space usage information corresponding to a session.
CsnMinLock Used to calculate CSNmin.
GPCCommitLock Used to protect the addition of the global Plan Cache hash table.
GPCClearLock Used to protect the clearing of the global plan cache hash table.
GPCTimelineLock Used to protect the timeline check of the global plan cache hash table.
TsTagsCacheLock Used to manage the time series tag cache.
InstanceRealTLock Used to protect the update of the hash table that stores shared instance statistics.
CLogBufMappingLock Used to manage the cache of commit logs.
GPCMappingLock Used to manage the global plan cache.
GPCPrepareMappingLock Used to manage the global plan cache.
BufferIOLock Used to protect I/O operations on pages in the shared buffer.
BufferContentLock Used to protect the read and modification of the page content in the shared buffer.
CSNLOG Ctl Used for CSN log management.
DoubleWriteLock Used to manage doublewrite operations.
RowPageReplicationLock Used to manage data page replication of row-store.
extension Other lightweight locks

The following table describes the corresponding wait events when wait_status is wait io.

Table 4 List of wait events corresponding to I/Os

wait_event Description
BufFileRead Reads data from a temporary file to a specified buffer.
BufFileWrite Writes the content of a specified buffer to a temporary file.
ControlFileRead Reads the pg_control file, mainly during database startup, checkpoint execution, and primary/standby verification.
ControlFileSync Flushes the pg_control file to a disk, mainly during database initialization.
ControlFileSyncUpdate Flushes the pg_control file to a disk, mainly during database startup, checkpoint execution, and primary/standby verification.
ControlFileWrite Writes the pg_control file, mainly during database initialization.
ControlFileWriteUpdate Updates the pg_control file, mainly during database startup, checkpoint execution, and primary/standby verification.
CopyFileRead Reads a file during file copying.
CopyFileWrite Writes a file during file copying.
DataFileExtend Writes a file during file extension.
DataFileFlush Flushes a table data file to a disk.
DataFileImmediateSync Flushes a table data file to a disk immediately.
DataFilePrefetch Reads a table data file asynchronously.
DataFileRead Reads a table data file synchronously.
DataFileSync Flushes table data file modifications to a disk.
DataFileTruncate Truncates a table data file.
DataFileWrite Writes a table data file.
LockFileAddToDataDirRead Reads the postmaster.pid file.
LockFileAddToDataDirSync Flushes the postmaster.pid file to a disk.
LockFileAddToDataDirWrite Writes PID information into the postmaster.pid file.
LockFileCreateRead Read the LockFile file %s.lock.
LockFileCreateSync Flushes the LockFile file %s.lock to a disk.
LockFileCreateWRITE Writes PID information into the LockFile file %s.lock.
RelationMapRead Reads the mapping file between system catalogs and storage locations.
RelationMapSync Flushes the mapping file between system catalogs and storage locations to a disk.
RelationMapWrite Writes the mapping file between system catalogs and storage locations.
ReplicationSlotRead Reads a stream replication slot file during a restart.
ReplicationSlotRestoreSync Flushes a stream replication slot file to a disk during a restart.
ReplicationSlotSync Flushes a temporary stream replication slot file to a disk during checkpoint execution.
ReplicationSlotWrite Writes a temporary stream replication slot file during checkpoint execution.
SLRUFlushSync Flushes the pg_clog, pg_subtrans, and pg_multixact files to a disk, mainly during checkpoint execution and database shutdown.
SLRURead Reads the pg_clog, pg_subtrans, and pg_multixact files.
SLRUSync Writes dirty pages into the pg_clog, pg_subtrans, and pg_multixact files, and flushes the files to a disk, mainly during checkpoint execution and database shutdown.
SLRUWrite Writes the pg_clog, pg_subtrans, and pg_multixact files.
TimelineHistoryRead Reads the timeline history file during database startup.
TimelineHistorySync Flushes the timeline history file to a disk during database startup.
TimelineHistoryWrite Writes to the timeline history file during database startup.
TwophaseFileRead Reads the pg_twophase file, mainly during two-phase transaction commit and restoration.
TwophaseFileSync Flushes the pg_twophase file to a disk, mainly during two-phase transaction commit and restoration.
TwophaseFileWrite Writes the pg_twophase file, mainly during two-phase transaction commit and restoration.
WALBootstrapSync Flushes an initialized WAL file to a disk during database initialization.
WALBootstrapWrite Writes an initialized WAL file during database initialization.
WALCopyRead Read operation generated when an existing WAL file is read for replication after archiving and restoration.
WALCopySync Flushes a replicated WAL file to a disk after archiving and restoration.
WALCopyWrite Write operation generated when an existing WAL file is read for replication after archiving and restoration.
WALInitSync Flushes a newly initialized WAL file to a disk during log reclaiming or writing.
WALInitWrite Initializes a newly created WAL file to 0 during log reclaiming or writing.
WALRead Reads data from Xlogs during redo operations on two-phase files.
WALSyncMethodAssign Flushes all open WAL files to a disk.
WALWrite Writes a WAL file.
WALBufferAccess WAL Buffer access (for performance reasons, only the number of accesses are counted in the kernel code, and the access time is not counted).
WALBufferFull When the WAL Buffer is full, write wal file related processing.
DoubleWriteFileRead Doublewrites and reads a file.
DoubleWriteFileSync Doublewrites a file and forcibly flushes files to disks.
DoubleWriteFileWrite Doublewrites a file and writes a file.
PredoProcessPending Waits for the playback of other records to complete during parallel log playback.
PredoApply Waits for other threads to play back logs to the LSN of the current thread during parallel log playback.
DisableConnectFileRead Reads the HA lock fragment logic file.
DisableConnectFileSync Forcibly flushes the HA lock fragment logic file to disks.
DisableConnectFileWrite Writes the HA lock fragment logic file.

The following table describes the corresponding wait events when wait_status is acquire lock.

Table 5 List of wait events corresponding to transaction locks

wait_event Description
relation Adds a lock to a table.
extend Adds a lock to a table being scaled out.
partition Adds a lock to a partitioned table.
partition_seq Adds a lock to a partition of a partitioned table.
page Adds a lock to a table page.
tuple Adds a lock to a tuple on a page.
transactionid Adds a lock to a transaction ID.
virtualxid Adds a lock to a virtual transaction ID.
object Adds a lock to an object.
cstore_freespace Adds a lock to idle column-store space.
userlock Adds a lock to a user.
advisory Adds an advisory lock.
Copyright © 2011-2024 www.enmotech.com All rights reserved.