In OceanBase AI Database, a wait event is used to mark an active session that is in a waiting state. Wait events record the state of worker threads, including whether they are performing CPU calculations or waiting. The threads can be waiting for I/O, network, or critical section access. By recording and statistically analyzing wait events, performance bottlenecks in the system can be identified, especially in high-concurrency and small-query scenarios. Wait events play an important role in performance analysis and help optimize system performance.
Scenarios
Typical scenarios in which the causes of an exception are located based on wait events:
The CPU utilization stays low regardless of the increasing system workload. This is often caused by concurrency bottlenecks in the system, such as resource contention in frequently accessed critical sections. You can locate the cause based on the top N wait events that wait the longest time in these critical sections.
In general, the response time (RT) of a business SQL statement is less than 1 ms. However, occasionally, the RT may spike to greater than 100 ms. This is usually because the execution of the SQL statement is stuck somewhere. You can locate the cause based on the recorded information about the wait events during the execution of the SQL statement, especially the details of the wait event that takes the longest time.
The system suddenly stops the execution of an SQL statement. This is usually because some sessions are blocked by factors such as lock contention when you query frequently accessed rows and deadlocks caused by unknown reasons. You can locate the cause based on the recorded details of the current wait event in the session.
Attributes of a wait event
Each wait event contains the following attributes:
- Wait event name.
- Wait time: the duration of the wait event.
- parameter1(p1), parameter2(p2), parameter3(p3): Each wait event has three additional wait event parameters that provide more information about the wait event, helping you understand the context and cause of the wait.
Wait event types
Wait events can be classified based on the resources or operations they represent. This classification helps database administrators more easily identify the problem areas when diagnosing and resolving performance issues:
Wait event type |
Wait category ID |
Description |
|---|---|---|
| OTHER | 100 | Wait events that do not belong to other categories. |
| APPLICATION | 101 | Wait events caused by client-side code. |
| CONFIGURATION | 102 | Wait events caused by insufficient resource configuration of the database or instance. |
| ADMINISTRATIVE | 103 | Wait events caused by user commands entered by the database administrator. |
| CONCURRENCY | 104 | Wait events for internal database resources. |
| COMMIT | 105 | Wait events related to log commit. |
| IDLE | 106 | Wait events for idle sessions, namely, sessions in a non-active state. |
| NETWORK | 107 | Wait events related to network communication. |
| USER_IO | 108 | Wait events for user I/O. |
| SYSTEM_IO | 109 | Wait events for background process I/O. |
| CLUSTER | 111 | Wait events related to clusters. |
At present, OceanBase AI Database supports more than 300 wait events, such as events related to network, scheduling, critical section, latches, clusters, transaction commit, user I/O, and system I/O. These events are classified into 11 categories. User I/O wait events, such as a page fault wait, are directly triggered by users, while system I/O wait events, such as a major compaction wait, are triggered by background tasks. The monitoring information related to wait events is classified into wait statistics and wait details, which are displayed in four views. Wait statistics views display the number and time of wait events at the session and tenant levels. We recommend that you view the wait statistics on the visual dashboard of an external monitoring system. Wait details views display details of wait events at the session level. One wait details view displays the details of the current wait events in each session or, if no wait events in the current session, the details of the last wait event. The other wait details view displays the details of the last 10 historical wait events in each session.
View wait events
You can query the V$EVENT_NAME view for all wait events and their parameters in OceanBase AI Database. In the system tenant, you can execute an SQL statement to list all wait events and their categories in alphabetical order. Here is an example:
obclient> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;
The views for querying wait events in OceanBase AI Database are as follows:
View |
Description |
|---|---|
V$EVENT_NAME |
Displays all wait events and their details in the current tenant. |
GV$SYSTEM_EVENT |
Displays a summary of wait events in the current tenant. |
GV$SESSION_EVENT |
Displays a summary of wait events from the session perspective. It displays the statistics of all wait events that occurred in all sessions. Specifically, it shows the total number of times that wait events occurred, the total waiting time, the average waiting time, and so on. Note that this view does not display the p1, p2, and p3 parameters. |
GV$SESSION_WAIT |
Displays details about the most recent or current wait event of all sessions. This view provides session-level wait details. |
GV$SESSION_WAIT_HISTORY |
Displays details about the most recent 10 wait events of each session. This view provides historical wait details at the session level. |
V$OB_ACTIVE_SESSION_HISTORY |
Displays the active session history (ASH) of the current OBServer node in the current tenant. |
GV$OB_ACTIVE_SESSION_HISTORY |
Displays the ASH of all OBServer nodes in the current tenant. |
Wait statistics
Wait statistics provide information such as the total number of waits, total wait time, total number of timeouts, and average wait time of all wait events. You can use wait statistics for performance optimization.
The GV$SYSTEM_EVENT view describes the important statistics of a tenant-level wait event in the following fields:
Field |
Description |
|---|---|
CON_ID |
The ID of the tenant. |
SVR_IP |
The IP address of the node. |
WAIT_CLASS |
The class of the wait event. |
EVENT_ID |
The ID of the wait event. |
EVENT |
The name of the wait event. |
TOTAL_WAITS |
The total number of waits for the event. |
TIME_WAITED |
The total wait time of the event. |
TOTAL_TIMEOUTS |
The total number of wait timeouts for the event. |
AVERAGE_WAIT |
The average wait time for the event. |
The following example shows how to query the statistics of wait events in a tenant:
obclient> select tenant_id, tenant_name, sum(total_waits) as total_waits, sum(time_waited_micro) / 1000000 as time_waited_seconds
from v$system_event join DBA_OB_TENANTS
on v$system_event.con_id = DBA_OB_TENANTS.tenant_id
where v$system_event.wait_class <> 'IDLE' and DBA_OB_TENANTS.tenant_type!='META'
group by tenant_name;
The query result is as follows:
+-----------+---------------+-------------+---------------------+
| tenant_id | tenant_name | total_waits | time_waited_seconds |
+-----------+---------------+-------------+---------------------+
| 1 | sys | 14669140176 | 522012421.8556 |
| 1008 | mq_t1 | 3234084699 | 32159617.6839 |
+-----------+---------------+-------------+---------------------+
2 rows in set (0.06 sec)
Wait details
Wait details provide information such as the wait start time, current status, and remaining time of a specific wait event. You can use wait details for troubleshooting. To help locate the causes of a wait, OceanBase AI Database allows you to pass three parameters to the wait details. For example, to locate the causes of a latch-triggered wait event, you can record in the wait details the latch applier, latch mode, and current latch holder. When the wait event is triggered, these details are displayed.
The GV$SESSION_WAIT view describes the important details of a session-level wait event in the following fields:
Field |
Description |
|---|---|
SID |
The ID of the session. |
WAIT_CLASS |
The class of the wait event. |
EVENT |
The name of the wait event. |
P1TEXT, P1, P2TEXT, P2, P3TEXT, P3 |
The three parameters allowed in the wait details. |
STATE |
The current state. |
WAIT_TIME_MICRO |
The wait time, in microseconds. |
TIME_REMAINING_MICRO |
The remaining wait time, in microseconds. |
The following example shows the wait details of a session in a waiting state:
obclient> select * from GV$SESSION_WAIT where STATE='WAITING' limit 1 \G
The query result is as follows:
*************************** 1. row ***************************
SID: 3222786366
CON_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
EVENT: px loop condition wait
P1TEXT: address
P1: 140578612358640
P2TEXT:
P2: 0
P3TEXT:
P3: 0
WAIT_CLASS_ID: 104
WAIT_CLASS#: 4
WAIT_CLASS: CONCURRENCY
STATE: WAITING
WAIT_TIME_MICRO: 926
TIME_REMAINING_MICRO: 74
TIME_SINCE_LAST_WAIT_MICRO: 0
1 row in set (0.06 sec)
Common wait events
Currently, OceanBase AI Database has approximately 309 wait events. The commonly used ones are listed below:
Wait event name |
Wait event ID |
Event category |
Description |
|---|---|---|---|
| sync rpc | 13000 | NETWORK | After the database sends a synchronization RPC request, it waits for the response from the synchronization RPC. |
| das wait remote response | 13002 | NETWORK | During SQL distributed execution, the database waits for the remote DAS task to return the result. |
| wait for network request in queue | 13004 | NETWORK | The database system has received a session request, but the request is waiting to be processed by a worker thread. |
| db file data read | 10001 | USER_IO | The user's SQL request reads from disk and waits for data to return. |
| memstore memory page alloc wait | 11015 | SYSTEM_IO | User SQL needs to be written to the MemStore, waiting for the MemStore to allocate write space. The usual reason is that a minor compaction is not complete, leaving no MemStore available. |
| db file compact read | 11001 | SYSTEM_IO | Waits for a disk read to complete during a minor compaction. |
| db file compact write | 11002 | SYSTEM_IO | Waits for a disk write to complete during a minor compaction. |
| palf read | 11016 | SYSTEM_IO | The database reads the CLOG and waits for disk I/O to complete. |
| palf write | 11017 | SYSTEM_IO | The database writes clogs and waits for disk write to complete. |
| async commiting wait | 16018 | COMMIT | Waits for the asynchronous log commit to complete, which means that the clogs are committed through the OceanBase AI Database consensus protocol. |
| sleep: wait refresh schema | 30100 | CONFIGURATION | During database execution, schema information needs to be obtained and the schema must be refreshed to the specified version. |
| mysql response wait client | 13001 | NETWORK | OceanBase AI Database waits for the client to finish sending the result set. When OceanBase AI Database interacts with the client over the MySQL protocol, if OceanBase AI Database returns a result set other than the last one to the client, the return process is synchronous. Therefore, OceanBase AI Database must wait synchronously for the result set to be sent before it can proceed to the next operation. |
| async rpc proxy condition wait | 15111 | NETWORK | OceanBase AI Database waits for the results of asynchronous network communications between internal nodes. |
| exec inner sql wait | 30000 | OTHER | OceanBase AI Database waits for the results of SQL queries that access internal tables to return during execution, such as queries to retrieve user table locations and schema information. |
| sync get gts timestamp wait | 18101 | CONCURRENCY | During user SQL execution, it waits to synchronously obtain a Global Timestamp Service (GTS) timestamp. |
Parameter descriptions for common wait events
In the system tenant, you can execute an SQL statement to list all wait events and their categories in alphabetical order. Here is an example:
obclient> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;
The parameter descriptions for common wait events are as follows:
Wait event name |
Description |
Wait event type |
Parameter 1 |
Parameter 2 |
Parameter 3 |
|---|---|---|---|---|---|
| wait remove partition | Partition GC Waiting for Transaction Module Deletion | ADMINISTRATIVE | tenant_id | table_id | partition_id |
| wait end stmt | Wait for Statement End | CLUSTER | rollback | trans_hash_value | physic_plan_type |
| wait start stmt | Statement Wait Enabled | CLUSTER | trans_hash_value | physic_plan_type | participant_count |
| wait end trans | Transaction Commit Waiting | COMMIT | rollback | trans_hash_value | participant_count |
| async rpc proxy condition wait | Upper-layer Encapsulated Asynchronous RPC Wait Events | CONCURRENCY | address | - | - |
| dag worker condition wait | Background Dag Thread Waiting for Events | CONCURRENCY | address | - | - |
| latch: alive server tracer lock wait | Lock Wait for OBServer Status in OBAliveServerMap Operation Memory | CONCURRENCY | address | number | tries |
| latch: alloc object lock wait | Lock Wait Events Internally Managed by OceanBase malloc for Small Blocks of Memory | CONCURRENCY | address | number | tries |
| latch: clog cascading info lock wait | Waiting Events for Clog Sub-Parent Cascade Module Lock | CONCURRENCY | address | number | tries |
| latch: clog locality lock wait | Lock for protecting locality information within clogs | CONCURRENCY | address | number | tries |
| latch: clog task lock wait | Lock of log_task in the clog module | CONCURRENCY | address | number | tries |
| latch: config lock wait | Parameter Lock Wait Events | CONCURRENCY | address | number | tries |
| latch: default drw lock wait | DRWLock wait event. DRWLock is a read/write lock suitable for read-heavy, write-light workloads. | CONCURRENCY | address | number | tries |
| latch: default mutex wait | Default MUTEX Wait Event | CONCURRENCY | address | number | tries |
| latch: default recursive mutex wait | Default Recursive MUTEX Wait Event | CONCURRENCY | address | number | tries |
| latch: default spin lock wait | Default spin lock wait event | CONCURRENCY | address | number | tries |
| latch: default spin rwlock wait | Default Spin Read/Write Lock Wait Event | CONCURRENCY | address | number | tries |
| latch: id map node lock wait | ID map wait event. The ID map is used to map integers to pointers. | CONCURRENCY | address | number | tries |
| latch: server maintaince lock wait | Lock Wait During OBServer Operations on RS | CONCURRENCY | address | number | tries |
| latch: server status lock wait | Read and write lock waits when operating on the server_statuses of an RS record | CONCURRENCY | address | number | tries |
| latch: unit manager lock wait | Lock Wait for Data Operations by Unit Manager | CONCURRENCY | address | number | tries |
| latch: zone infos rw lock wait | Lock Wait for Data Operations by OBZoneManagerBase | CONCURRENCY | address | number | tries |
| latch: zone manager maintaince lock wait | Lock Wait for Data Operations by Zone Manager | CONCURRENCY | address | number | tries |
| partition table updater condition wait | Condition wait during task processing by OBUniqTaskQueue | CONCURRENCY | address | - | - |
| rebalance task mgr condition wait | Condition Wait of OBDRTaskMgr During Disaster Recovery Task Processing | CONCURRENCY | address | - | - |
| reentrant thread condition wait | Condition wait during task processing by OBReentrantThread | CONCURRENCY | address | - | - |
| rpc session handler condition wait | Stream RPC Waiting for Event | CONCURRENCY | address | - | - |
| th worker condition wait | Multi-Tenant Thread Suspension Waiting for Events | CONCURRENCY | address | - | - |
| thread idling condition wait | RootService Idle Thread Wait Event | CONCURRENCY | address | - | - |
| sched idle | Multi-Tenant Thread Waiting for Task Execution | IDLE | wait start timestamp | - | - |
| mysql response wait client | Time Spent Waiting for Client Response | NETWORK | - | - | - |
| sync rpc | Synchronize RPC Waiting Events | NETWORK | pcode | size | - |
| sched wait | Multi-tenant threads are suspended due to large query execution | SCHEDULER | req type | req start timestamp | wait start timestamp |
| bloomfilter build read | Backend BloomFilter data read creation | SYSTEM_IO | fd | offset | size |
| db file compact read | Compaction task data read | SYSTEM_IO | fd | offset | size |
| db file compact write | Data Written in Compaction Tasks | SYSTEM_IO | fd | offset | size |
| db file index build read | Data Reading after Index Creation | SYSTEM_IO | fd | offset | size |
| db file index build write | Index creation and data write | SYSTEM_IO | fd | offset | size |
| db file migrate read | Data Migration Read | SYSTEM_IO | fd | offset | size |
| db file migrate write | Data Migration Write | SYSTEM_IO | fd | offset | size |
| memstore memory page alloc wait | Waiting Time Triggered by MemStore Write Throttling | SYSTEM_IO | cur_mem_hold | sleep_interval | cur_ts |
| db file data read | User data reading | USER_IO | fd | offset | size |
| interm result disk read | Intermediate result data read | USER_IO | fd | offset | size |
| interm result disk write | Intermediate result data write | USER_IO | fd | offset | size |
| row store disk read | SQL Operator Read from Stored Data | USER_IO | fd | offset | size |
| row store disk write | SQL operator data write to disk | USER_IO | fd | offset | size |
| build index scheduler condition wait | Waiting Time between Two DDL Task Schedules | CONCURRENCY | address | - | - |
| debug sync condition wait | Meaningful in ErrSim debug mode, meaningless in user scenarios | CONCURRENCY | address | - | - |
| inner connection pool condition wait | Condition Wait in Internal Connection Pool | CONCURRENCY | address | - | - |
| latch: bandwidth throttle lock wait | Lock Wait Due to Bandwidth Throttling | CONCURRENCY | address | number | tries |
| latch: default bucket lock wait | Wait Time for Bucket Lock, Unclassified | CONCURRENCY | address | number | tries |
| latch: election group trace recorder lock wait | Lock protecting election group trace statistics | CONCURRENCY | address | number | tries |
| latch: group migrate lock wait | Lock Wait During Batch Migration | CONCURRENCY | address | number | tries |
| latch: group migrate task lock wait | Task Lock Wait During Batch Migration | CONCURRENCY | address | number | tries |
| latch: io queue lock wait | Lock wait during I/O queue read | CONCURRENCY | address | number | tries |
| latch: migrate lock wait | Lock Wait During Migration | CONCURRENCY | address | number | tries |
| latch: multi tenant lock wait | Lock Wait in Multi-Tenant Management | CONCURRENCY | address | number | tries |
| latch: rebuild retry list lock wait | Waiting for a Rebuild Task | CONCURRENCY | address | number | tries |
| latch: session trace recorder lock wait | Lock Wait Recorded in Session Trace | CONCURRENCY | address | number | tries |
| latch: tenant lock wait | Lock Wait Managed Within a Tenant | CONCURRENCY | address | number | tries |
| latch: timezone lock wait | Tenant's timezone structure lock is waiting. The tenant operation is locked. | CONCURRENCY | address | number | tries |
| oblog part mgr schema version wait | The logic for Liboblog to wait for the schema version to refresh | CONCURRENCY | - | - | - |
| server object pool arena lock wait | Lock Wait in LOB Cache Structure | USER_IO | fd | offset | size |
| async commiting wait | Clog Async Commit Waiting Event | COMMIT | - | - | - |
| exec inner sql wait | Inner SQL Waiting for Events | OTHER | wait inner sql class | inner session id | - |
| sleep: wait refresh schema | Wait for Schema Refresh to Specified Version | CONFIGURATION | sleep_interval | schema_version | - |
| palf throttling sleep | Log Thread Throttling | USER_IO | sleep_interval | - | - |
| palf read | Log Thread Reads Logs | SYSTEM_IO | fd | offset | size |
| palf write | Log Thread Writes Logs | SYSTEM_IO | fd | offset | size |
| das wait remote response | SQL Distributed Execution: Database Waiting for Remote DAS Task to Return Results | NETWORK | - | - | - |
| wait for network request in queue | The database system has received a session request, but the request is waiting to be processed by a worker thread. | NETWORK | pcode | level | priority |
| retry: insufficient px worker wait | Retry due to insufficient PX thread resources | CONFIGURATION | degree of parallel | number of px workers required | the total number of idle PX workers currently |
| retry: wait GTS ready | Retry Caused by Waiting for GTS Ready | CLUSTER | error code | ls leader addr | ls leader port |
| retry: wait replica readable | Retry due to unreadable replica | CLUSTER | ls_id | tablet_id | - |
| retry: wait schema cache load | Retries Caused by Loading Schema Cache | CLUSTER | error code | table id | table schema version |
| retry: wait location cache load | Retry Caused by Loading Location Cache | CLUSTER | error | ls_id | - |
| retry: other retry wait | Retries Caused by Other Reasons | CLUSTER | error | - | - |
| row lock wait | Wait for row lock | APPLICATION | lock holder tx id | data seq number | holder lock time |