In production environments, long execution times and lack of transparency during DDL (Data Definition Language) operations are common issues. To address this, OceanBase Database V4.1 introduced the DDL real-time execution progress monitoring feature, which displays DDL execution progress information in the GV$SESSION_LONGOPS view. For more information, see GV$SESSION_LONGOPS.
This topic describes how to monitor the DDL execution process and diagnose common issues. It uses three typical scenarios to illustrate the monitoring capabilities of GV$SESSION_LONGOPS: creating an index, adding a primary key, and dropping a column. Creating an index is a standalone DDL task, while adding a primary key and dropping a column involve parent-child relationships, with the latter using a non-sorting method for data completion.
To monitor DDL execution progress, you need to create two sessions: one for executing DDL operations and the other for querying the GV$SESSION_LONGOPS view. Both sessions log in to the system as normal tenants.
You can execute the following statement to view the fields in the GV$SESSION_LONGOPS view:
SELECT * FROM oceanbase.gv$session_longops\G
Observe index creation
The most time-consuming part of the index creation process is the data completion phase for the index. In some cases, the index creation process may be affected by concurrent transactions. Below are explanations for monitoring progress during the two key phases: waiting for transactions to complete and data completion.
Waiting-for-transactions-to-complete phase
*************************** 1. row ***************************
SID: -1
TRACE_ID: YE5186458A15C-0005EF63AE10FBBB-0-0
OPNAME: create index
TARGET: __idx_500005_i1
SVR_IP: xxx.xx.xxx.xx
SVR_PORT: 58648
START_TIME: 2022-12-09
ELAPSED_SECONDS: 7
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2022-12-09
MESSAGE: TENANT_ID: 1004, TASK_ID: 2, STATUS: WAIT TRANS END, PENDING_TX_ID: 76
During this phase, the MESSAGE field displays WAIT TRANS END, and the ID of the first unfinished transaction is displayed (for example, PENDING_TX_ID: 76). You can query the __all_virtual_trans_stat view for more information about the transaction by using the transaction ID as the trans_id parameter.
Data completion phase
*************************** 1. row ***************************
SID: -1
TRACE_ID: YE5186458A15C-0005EF60F182F228-0-0
OPNAME: create index
TARGET: __idx_500008_i1
SVR_IP: xxx.xx.xxx.xx
SVR_PORT: 58648
START_TIME: 2022-12-09
ELAPSED_SECONDS: 38
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2022-12-09
MESSAGE: TENANT_ID: 1004, TASK_ID: 6, STATUS: REPLICA BUILD, ROW_SCANNED: 10000000, ROW_SORTED: 19771966, ROW_INSERTED: 0
During this phase, the MESSAGE field contains the following key information:
- TENANT_ID: the tenant identifier
- TASK_ID: the DDL task identifier
- STATUS: the current execution status.
REPLICA BUILDindicates that data is being completed. - ROW_SCANNED: the number of rows scanned from the primary table
- ROW_SORTED: the number of sorted rows
- ROW_INSERTED: the number of rows written to the index table
Since the sorting process may involve multiple rounds of merging operations, the value of ROW_SORTED is typically greater than those of ROW_SCANNED and ROW_INSERTED.
Columnar storage version features
With the introduction of the columnar storage version, monitoring during the data completion phase has changed. The system now distinguishes between columnstore tables and rowstore tables. Tables created before the columnar storage version, as well as hybrid row-column tables, are treated as rowstore tables.
Differences in the data completion process:
- Rowstore tables: Data is written to a temporary file and then directly written to macroblocks.
- Columnstore tables: Data is written to temporary files and then further written into columnar SSTables. The total number of rows is calculated as column_group_cnt × row_count.
Here is an example of a MESSAGE message during the data completion phase:
- Rowstore tables:
TENANT_ID: 1004, TASK_ID: 4514, STATUS: REPLICA BUILD, ROW_SCANNED: 779, ROW_SORTED: 0, ROW_INSERTED: 0 - Columnstore tables:
TENANT_ID: 1004, TASK_ID: 6064, STATUS: REPLICA BUILD, ROW_SCANNED: 100000, ROW_SORTED: 200000, ROW_INSERTED_TMP_FILE: 100000, ROW_INSERTED_SSTABLE: 500000 out of 500000 done
Observe primary key addition
The process of adding a primary key differs from index creation. During the data completion phase of adding a primary key, the GV$SESSION_LONGOPS view displays two records: one record represents the index completion on the main table, and the other represents changes to the main table itself. The meanings of the fields are similar to those in the index creation process.
*************************** 1. row ***************************
SID: -1
TRACE_ID: YE5186458A15C-0005EF60F182F22F-0-0
OPNAME: create index
TARGET: __idx_500030_i1
SVR_IP: xxx.xx.xxx.xx
SVR_PORT: 58648
START_TIME: 2022-12-09
ELAPSED_SECONDS: 6
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2022-12-09
MESSAGE: TENANT_ID: 1004, TASK_ID: 8, STATUS: REPLICA BUILD, ROW_SCANNED: 5518389, ROW_SORTED: 0, ROW_INSERTED: 0
*************************** 2. row ***************************
SID: -1
TRACE_ID: YE5186458A15C-0005EF60F182F22F-0-0
OPNAME: add primary key
TARGET: t1
SVR_IP: xxx.xx.xxx.xx
SVR_PORT: 58648
START_TIME: 2022-12-09
ELAPSED_SECONDS: 63
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2022-12-09
MESSAGE: TENANT_ID: 1004, TASK_ID: 7, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 8 ]
Observe column deletion
*************************** 1. row ***************************
SID: -1
TRACE_ID: YE5186458A15C-0005EF60F182F240-0-0
OPNAME: drop column
TARGET: t1
SVR_IP: xxx.xx.xxx.xx
SVR_PORT: 58648
START_TIME: 2022-12-09
ELAPSED_SECONDS: 26
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2022-12-09
MESSAGE: TENANT_ID: 1004, TASK_ID: 13, STATUS: COPY DEPENDENT OBJECTS, CHILD TASK IDS: [ 14 ]
*************************** 2. row ***************************
SID: -1
TRACE_ID: YE5186458A15C-0005EF60F182F240-0-0
OPNAME: create index
TARGET: __idx_500076_i1
SVR_IP: xxx.xx.xxx.xx
SVR_PORT: 58648
START_TIME: 2022-12-09
ELAPSED_SECONDS: 8
TIME_REMAINING: 0
LAST_UPDATE_TIME: 2022-12-09
MESSAGE: TENANT_ID: 1004, TASK_ID: 14, STATUS: REPLICA BUILD, ROW_SCANNED: 10000000, ROW_SORTED: 213098, ROW_INSERTED: 0
The data completion phase for column deletion does not involve a sorting stage. Therefore, the MESSAGE field only contains information about ROW_SCANNED and ROW_INSERTED. Similar operations include adding non-generated columns in intermediate positions.