SYSSTAT metrics are one of the common diagnostic monitoring information items. They collect key performance values of system running, such as the number of SQL statement executions, execution time, and I/O count. These metrics cover all core components of OceanBase AI Database, including clusters, tenants, and hosts.
Source of metrics
The data of metrics is stored in the [G]V$SYSSTAT view. The key fields are described as follows:
Field name |
Description |
|---|---|
| CON_ID | Tenant ID |
| SVR_IP | Node IP |
| STAT_ID | Metric ID |
| NAME | Metric name |
| CLASS | Metric category |
| VALUE | Metric value |
| VALUE_TYPE | Value type of the metric:
|
Query metrics
Take the [G]V$SYSSTAT view as an example to query all metrics in the SQL category.
obclient> SELECT STAT_ID, CLASS, NAME FROM V$STATNAME;
Take the GV$SYSSTAT view as an example to query all metrics in the SQL category:
obclient> SELECT CLASS,STAT_ID,NAME,VALUE_TYPE FROM GV$SYSSTAT WHERE CLASS=8;
Many metrics are related to internal implementations and may change or be deleted. The actual situation prevails.
Metric categories
OceanBase AI Database supports more than 600 metrics in 15 categories, covering key execution information such as the network, request queue, transaction, SQL, cache, storage, resource, and log. The categories are as follows:
Category ID |
Category name |
Category description |
|---|---|---|
| 1 | NETWORK | Network |
| 2 | QUEUE | Request queue |
| 4 | TRANS | Transaction |
| 8 | SQL | SQL |
| 16 | CACHE | Cache |
| 32 | STORAGE | Storage |
| 64 | RESOURCE | Resource |
| 128 | DEBUG | Log |
| 256 | CLOG | CLOG |
| 512 | ELECT | Election |
| 1024 | OBSERVER | System |
| 2048 | RS | Root Service |
| 3072 | Time Model | Time Model |
| 4096 | TABLEAPI | TableAPI |
| 8192 | WR | WR |
The following describes the queries and corresponding metric descriptions for several commonly used categories.
Network
You can query network-related metrics using the following example:
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=1 and stat_id in (10000,10001,10002,10003,10004,10005,10006);
The result is as follows:
+-------+---------+----------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+----------------------+------------+
| 1 | 10000 | rpc packet in | ADD_VALUE |
| 1 | 10001 | rpc packet in bytes | ADD_VALUE |
| 1 | 10002 | rpc packet out | ADD_VALUE |
| 1 | 10003 | rpc packet out bytes | ADD_VALUE |
| 1 | 10004 | rpc deliver fail | ADD_VALUE |
| 1 | 10005 | rpc net delay | ADD_VALUE |
| 1 | 10006 | rpc net frame delay | ADD_VALUE |
+-------+---------+----------------------+------------+
7 rows in set (0.06 sec)
Common network monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 10000 | rpc packet in | The cumulative number of RPC packets received by the tenant. The count increments by 1 whenever the tenant receives an RPC request or receives a response packet after sending an RPC request. |
| 10001 | rpc packet in bytes | The cumulative number of bytes of RPC packets received by the tenant, in bytes. This value is calculated by adding the byte size of each RPC request received by the tenant or each response packet received after sending an RPC. |
| 10002 | rpc packet out | The cumulative number of RPC packets sent by the tenant. The count increments by 1 each time a tenant sends an RPC request or responds to an RPC request received. |
| 10003 | rpc packet out bytes | The cumulative number of bytes in RPC packets sent by the tenant, in bytes. This is the sum of the byte count of an RPC request sent by the tenant and the byte count of the response packet sent after receiving an RPC request. |
| 10004 | rpc deliver fail | The total number of failures in forwarding RPC requests to the tenant's work queue. This count increments by 1 whenever an RPC request fails to be forwarded to the tenant queue during IO forwarding. |
| 10005 | rpc net delay | The cumulative network transmission delay of RPC requests received by a tenant, in microseconds. After an RPC I/O thread parses an RPC request, it obtains the current timestamp, subtracts the sending timestamp obtained from the packet header to get the difference, and adds this value to RPC_NET_DELAY. |
| 10006 | rpc net frame delay | The cumulative network framework processing time received by a tenant for RPC requests, in microseconds. When an RPC IO forwards an RPC request to the tenant queue, it obtains the current timestamp, subtracts the timestamp recorded when the RPC packet was parsed, and adds the difference to RPC_NET_FRAME_DELAY. |
| 10007 | mysql packet in | The cumulative number of SQL request packets received by the tenant. This value is incremented by 1 each time a network thread decodes a complete SQL request packet. |
| 10008 | mysql packet in bytes | The cumulative size of SQL request packets received by the tenant, in bytes. The size of the original data packets received by the network thread, obtained according to protocol rules. |
| 10009 | mysql packet out | The cumulative number of response packets for an SQL request. When a network thread returns a packet, the counter increments by 1 for each encoded complete packet. |
| 10010 | mysql packet out bytes | The cumulative value of the bytes returned in SQL requests for a tenant, in bytes. The encoding size is accumulated during the encoding of the response set. |
| 10011 | mysql deliver fail | The cumulative number of times that tenant SQL login requests failed to be placed into the login queue and other requests failed to be placed into the worker thread queue. For login requests, this value increments by 1 if the login request thread queue is full. For other requests, this value increments by 1 if the tenant worker thread queue is full. |
| 10012 | rpc compress original packet cnt | The cumulative number of RPC requests sent by the tenant that specify compression. This count increments by 1 whenever an RPC request is sent with the compressor_type parameter set. |
| 10013 | rpc compress compressed packet cnt | The cumulative number of compressed RPC requests sent by the tenant. When an RPC is sent, if the compressor_type parameter is set and the compression succeeds (the compressed byte count is less than the original byte count), the counter increments by 1. |
| 10014 | rpc compress original size | The RPC request sent by the tenant specifies the original byte size of the request to be compressed, in bytes. When an RPC is sent, if its compressor_type field is set, the original byte size of the uncompressed RPC packet is accumulated in the RPC_COMPRESS_ORIGINAL_SIZE metric. |
| 10015 | rpc compress compressed size | The cumulative size of compressed RPC requests sent by the tenant, in bytes. When an RPC is sent, if the compressor_type parameter is set and the compression succeeds, the compressed size is accumulated in the RPC_COMPRESS_COMPRESSED_SIZE metric. |
| 10016 | rpc stream compress original packet cnt | Deprecated. Reserved only for code. The unit is times. |
| 10017 | rpc stream compress compressed packet cnt | Deprecated. Reserved only for code. The unit is times. |
| 10018 | rpc stream compress original size | Deprecated. Reserved only for code. Unit: bytes. |
| 10019 | rpc stream compress compressed size | Deprecated. Reserved only for code. Unit: bytes. |
Request queue
You can query request queue-related metrics using the following example.
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=2 and stat_id in (20000,20001,20002);
The result is as follows:
+-------+---------+-----------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+-----------------------+------------+
| 2 | 20000 | request enqueue count | ADD_VALUE |
| 2 | 20001 | request dequeue count | ADD_VALUE |
| 2 | 20002 | request queue time | ADD_VALUE |
+-------+---------+-----------------------+------------+
3 rows in set (0.07 sec)
Common request queue monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 20000 | request enqueue count | Indicates the total number of requests in the queue for a tenant. The count increments by 1 each time a request is placed into the worker thread queue. |
| 20001 | request dequeue count | Indicates the total number of requests that have exited the queue for a tenant. The count increments by 1 each time a request exits the worker thread queue. |
| 20002 | request queue time | The total time spent by all request queues of a tenant, in microseconds. When a tenant worker thread receives a request, it calculates the queue time by subtracting the time when the request entered the queue from the current time, and this value is accumulated in the REQUEST_QUEUE_TIME metric. |
Transaction
You can query transaction-related metrics using the following example.
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=4 and stat_id in (30000,30001,30002,30005,30006,30007,30008,30009,30010,30011,30012,30013,30200,30201,30202,30203,30204,30205,30206,30207,30208,30209,30210,30211,30212,30213,30214,30215,30216,30217,30218,30219);
The result is as follows:
+-------+---------+----------------------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+----------------------------------+------------+
| 4 | 30000 | trans commit log sync time | ADD_VALUE |
| 4 | 30001 | trans commit log sync count | ADD_VALUE |
| 4 | 30002 | trans commit log submit count | ADD_VALUE |
| 4 | 30005 | trans start count | ADD_VALUE |
| 4 | 30006 | trans total used time | ADD_VALUE |
| 4 | 30007 | trans commit count | ADD_VALUE |
| 4 | 30008 | trans commit time | ADD_VALUE |
| 4 | 30009 | trans rollback count | ADD_VALUE |
| 4 | 30010 | trans rollback time | ADD_VALUE |
| 4 | 30011 | trans timeout count | ADD_VALUE |
| 4 | 30012 | trans local trans count | ADD_VALUE |
| 4 | 30013 | trans distribute trans count | ADD_VALUE |
+-------+---------+----------------------------------+------------+
32 rows in set (0.013 sec)
Common transaction monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 30000 | trans commit log sync time | The cumulative time consumed by a transaction's clogs being successfully committed to a majority of replicas on this server, in microseconds. When the majority of replicas have successfully processed the clogs and the callback to the transaction layer is triggered, the current time is subtracted from the time when the clogs were successfully committed to calculate the time spent by the majority. This calculated time is then accumulated to this statistic. |
| 30001 | trans commit log sync count | The number of times the majority of clogs have been successfully committed for this tenant on this server. After a majority of clogs are successfully committed, a callback is sent to the transaction layer, which increments the counter upon receiving the callback. |
| 30002 | trans commit log submit count | The number of clogs submitted by the tenant on this server. The count increments each time a clog is successfully submitted (not guaranteed to be a majority). |
| 30005 | trans start count | The number of transactions initiated by the tenant on this server. This statistic increases each time a transaction is successfully initiated on the OBServer node where the session is created. |
| 30006 | trans total used time | The cumulative transaction time of the tenant on this server, in microseconds. After each transaction is completed on the OBServer node where the session is created, the cumulative transaction time is added to this statistic. |
| 30007 | trans commit count | The number of successfully committed transactions for this tenant on the machine. The count increases by 1 each time a transaction is successfully committed on the OBServer node connected to the session. |
| 30008 | trans commit time | The cumulative transaction commit time of the tenant on this server, in microseconds. After each successful transaction commit on the OBServer node where the session is created, the cumulative commit time is added to this statistic. |
| 30009 | trans rollback count | The number of transactions successfully rolled back for this tenant on the server. This statistic increases each time a transaction is successfully rolled back on the OBServer node where the session was created. |
| 30010 | trans rollback time | Transaction rollback time of the tenant on the server, in microseconds. After each successful rollback on the OBServer node where the session is created, the cumulative rollback time is added to this statistic. |
| 30011 | trans timeout count | The number of transactions that time out for this tenant on the server. This statistic increments each time a transaction times out on the OBServer node where the session is created. |
| 30012 | trans local trans count | The number of single-phase transactions (including both commits and rollbacks) ended for this tenant on the server. This count is incremented whenever the number of participants equals 1 after a transaction is ended on the OBServer node where the session was created. |
| 30013 | trans distribute trans count | The number of distributed transactions terminated on this server for the tenant (including both commit and rollback terminations). Each time a transaction is terminated on the OBServer node where the session was created, the number of participants is checked. If the number of participants is greater than 1, this counter is incremented. |
| 30017 | trans without participant count | The number of read-only transactions terminated on this server for the tenant (including both commits and rollbacks). This count is incremented whenever the number of participants equals 0 after a transaction is terminated on the OBServer node where the session was created. |
| 30018 | redo log replay count | The number of redo logs replayed for a transaction within a tenant. The count increases by 1 each time the redo logs of that transaction are replayed. |
| 30019 | redo log replay time | The cumulative time consumed for replaying redo logs of transactions within a tenant, in microseconds. Each time redo logs are replayed, the time consumed for that replay is recorded and added to this statistic. |
| 30020 | prepare log replay count | The number of prepared logs rolled back within the tenant. The count increases by 1 each time a prepared log is rolled back. |
| 30021 | prepare log replay time | The cumulative time consumed in replaying prepare logs for transactions within a tenant, in microseconds. Each time prepare logs are replayed, the time consumed for that replay is recorded and added to this statistic. |
| 30022 | commit log replay count | The number of times the transaction commit logs have been rolled back within the tenant. The count increases by 1 each time the transaction commit logs are rolled back. |
| 30023 | commit log replay time | The cumulative time consumed in replaying commit logs within a tenant. Each time commit logs are replayed, the time spent on that replay is recorded and added to this statistic. |
| 30024 | abort log replay count | The number of abort logs rolled back within a tenant. The count increases by 1 each time an abort log is rolled back for a transaction. |
| 30025 | abort log replay time | The cumulative time consumed in replaying abort logs for transactions within a tenant, in microseconds. Each time an abort log is replayed, the time spent on that replay is recorded and added to this statistic. |
| 30026 | clear log replay count | The number of cleared logs for transactions rolled back within the tenant. This count increases by 1 each time a transaction's cleared logs are rolled back. |
| 30027 | clear log replay time | The cumulative time consumed in replaying clear logs for transactions within a tenant, in microseconds. Each time clear logs are replayed, the time consumed for that replay is recorded and added to this statistic. |
| 30053 | gts request total count | The total number of GTS requests processed by the GTS server. The count is incremented when the GTS server finishes processing a GTS request. |
| 30054 | gts acquire total time | For GTS acquisition in asynchronous scenarios, this metric indicates the total waiting time for obtaining GTS, in microseconds. The waiting time is incremented when GTS is acquired. This feature is not yet implemented, so this metric is currently meaningless. |
| 30056 | gts acquire total wait count | For async GTS retrieval scenarios, this is the total number of GTS retrievals. The count increments by 1 when a GTS is retrieved. |
| 30063 | gts wait elapse total time | For async GTS retrieval scenarios, this is the total waiting time in microseconds for data pushed by GTS. The waiting time increases when GTS pushes data. |
| 30065 | gts wait elapse total wait count | Total number of times waiting for data pushed by GTS in async GTS access scenarios. Unit: microseconds. The count increments by 1 when data is pushed by GTS. |
| 30077 | trans early lock release enable count | Deprecated. The unit is times. |
| 30078 | trans early lock release unable count | Deprecated. The unit is times. |
| 30079 | read elr row count | Total number of rows for which the row lock is released in advance during write operations to the MemTable. During write operations to the MemTable, this is the number of data rows for which the row lock is successfully released in advance. |
| 30080 | local trans total used time | The cumulative duration of single-phase transactions (including both commit and rollback) ended for this tenant on the machine, in microseconds. After each transaction is ended on the OBServer node where the session was created, the number of participants is checked. If the number of participants equals 1, the transaction duration is accumulated to this statistic. |
| 30081 | distributed trans total used time | The cumulative duration of distributed transactions ended on this server for the tenant, in microseconds. This statistic is incremented each time a transaction is ended on the OBServer node where the session is created, if the number of participants exceeds 1. |
| 30082 | tx data hit mini cache count | The number of times that a transaction data table is hit in the MINI Cache during read operations. The MINI Cache is the highest-speed cache for TxData, and a higher hit rate indicates better performance. (The MINI Cache is a request-level cache, with one per Access Context.) The counter increments by 1 each time a TxData record is read from the MINI Cache. |
| 30083 | tx data hit kv cache count | The number of times a read transaction data hit the KVCache. The performance of the KVCache is slightly weaker than that of the MINI Cache. Most read requests are expected to hit the KV or MINI Cache. The counter increments by 1 each time a TxData record is read from the KVCache. |
| 30084 | tx data read tx ctx count | The number of times the transaction context table is hit when reading transaction data. The counter increments by 1 each time a TxData record is read from the transaction context table. |
| 30085 | tx data read tx data memtable count | The number of times that a read from the transaction data table's MemTable is required when the cache does not hit during transaction data reads. The counter increments by 1 each time a TxData record is read from the transaction data table's MemTable. |
| 30086 | tx data read tx data sstable count | The number of times TxData is read from the transaction data table's SSTable when reading transaction data. (Reading TxData from an SSTable is the least performant operation, so this metric is not expected to be high.) The counter increments by 1 each time a TxData record is read from the transaction data table's SSTable. |
| 30200 | xa start total count | For XA transactions, the total number of XA_START statements, including both successful and failed ones. The count increments by 1 after the transaction-side logic completes the XA_START operation. |
| 30201 | xa start total used time | For XA transactions, the total execution time of the XA_START statement, including both successful and failed cases, in microseconds. After the transaction side completes the XA_START logic, it adds the consumption time for local processing. |
| 30202 | xa start with rpc total count | The total number of XA_START statements executed remotely for XA transactions, including only successful ones. When the transaction side completes the XA_START logic, if the XA_START is executed remotely and succeeds, the count is incremented by 1. |
| 30203 | failed xa start total count | Total number of failed executions of the XA_START statement for XA transactions. This count increments by 1 when the XA_START logic fails to execute on the transaction side. |
| 30204 | xa end total count | The total number of executions of the XA_END statement for XA transactions, including both successful and failed ones. The count increments by 1 after the transaction-side logic of XA_END is executed. |
| 30205 | xa end total used count | For an XA transaction, the total execution time of the XA_END statement, in microseconds. After the transaction side completes the XA_END logic, the local processing time is added. |
| 30206 | xa end with rpc total count | The total number of successfully executed XA_END statements remotely for XA transactions. The count increments when the XA_END logic is successfully executed remotely after the transaction-side execution is complete. |
| 30207 | failed xa end total count | Total number of failures in executing the XA_END statement for XA transactions. The count increments by 1 when an error occurs during the execution of the XA_END statement on the transaction side. |
| 30208 | xa prepare total count | For XA transactions, the total number of XA_PREPARE statements, including both successful and failed ones. The count increments after the transaction-side logic completes the XA_PREPARE statement. |
| 30209 | xa prepare total used time | For XA transactions, the total execution time of the XA_PREPARE statement, including both successful and failed cases, in microseconds. After the transaction side completes the XA_PREPARE logic, it adds the consumption time for local processing. |
| 30210 | xa prepare with rpc total count | The total number of XA_PREPARE statements executed remotely for XA transactions, including only successful ones. The count increments by 1 if an XA_PREPARE statement is executed remotely and succeeds after the transaction-side XA_START logic is completed. |
| 30211 | failed xa prepare total count | Total number of XA_PREPARE statements that failed for XA transactions. This count is incremented when the XA_PREPARE logic fails to execute on the transaction side. |
| 30212 | xa commit total count | For XA transactions, the total number of XA_COMMIT statements executed, including both successful and failed ones. The count increments by 1 after the transaction-side completes the XA_COMMIT logic. |
| 30213 | xa commit total used time | For XA transactions, the total execution time of the XA_COMMIT statement, in microseconds. After the transaction side completes the XA_COMMIT logic, this value is increased by the consumption time for local processing. |
| 30214 | xa commit with rpc total count | For XA transactions, the total number of successfully executed XA_COMMIT statements remotely. The count increments when the XA_COMMIT logic is executed locally and the transaction commits successfully. |
| 30215 | failed xa commit total count | The total number of failed executions of the XA_COMMIT statement for XA transactions. This count increments when an error occurs during the execution of the XA_COMMIT statement by the transaction side. |
| 30216 | xa rollback total count | For XA transactions, the total number of XA_ROLLBACK statements, including both successful and failed ones. The count increments by 1 after the transaction-side logic of XA_ROLLBACK is executed. |
| 30217 | xa rollback total used time | For XA transactions, the total execution time of the XA_ROLLBACK statement, including both successful and failed cases, in microseconds. After the transaction-side logic of XA_ROLLBACK is executed, the local processing time is added. |
| 30218 | xa rollback with rpc total count | The total number of remotely executed XA_ROLLBACK statements for XA transactions, including only successful ones. This value is incremented when an XA_ROLLBACK statement is remotely executed and succeeds after the transaction side has completed the XA_START logic. |
| 30219 | failed xa rollback total count | The total number of failed executions of the XA_ROLLBACK statement for XA transactions. This count is incremented when the XA_ROLLBACK logic fails to execute on the transaction side. |
| 30220 | started xa trans count | For XA transactions, the total number of successfully started XA transactions. The count increments by 1 when an XA transaction is successfully started on the transaction side. |
| 30221 | read only xa trans total count | Total number of read-only XA transactions. This count is incremented when a transaction is committed as a read-only XA transaction. |
| 30222 | xa trans with one phase commit total count | The total number of XA transactions with one-phase commit. This count increments by 1 when an XA transaction is committed using one-phase commit on the transaction side. |
| 30223 | inner sql total count in xa statement | For an XA transaction, this is the total number of inner SQL statements executed during the XA logic execution. When the transaction side executes the XA logic, the count increments by 1 after each inner SQL statement is executed. |
| 30224 | total count of inner sql (latency >= 10ms) in xa statement | For XA transactions, the total number of INNER SQL statements whose execution time exceeds 10 ms during the execution of XA logic. When the transaction side executes XA logic, if the execution time of an INNER SQL statement exceeds 10 ms, the count is incremented by 1. |
| 30225 | total count of inner sql (latency >= 20ms) in xa statement | For XA transactions, the total number of INNER SQL statements whose execution time exceeds 20 ms during the execution of XA logic. When the transaction side executes XA logic, if the execution time of an INNER SQL statement exceeds 20 ms, the count is incremented by 1. |
| 30226 | inner sql total used time in xa statement | For XA transactions, this is the total execution time of inner SQL statements in microseconds during the execution of XA logic. When the transaction side executes XA logic, it increments the time consumed by inner SQL statements after each inner SQL statement is executed. |
| 30227 | inner rpc total count in xa statement | For XA transactions, the total number of RPCs in the execution of XA logic. The count increments by 1 each time an RPC is called during the execution of XA logic on the transaction side. |
| 30228 | total count of inner rpc (latency >= 10ms) in xa statement | For XA transactions, the total number of RPCs whose execution time exceeds 10 ms in the XA logic. This counter increments when an RPC execution time exceeds 10 ms during the execution of XA logic on the transaction side. |
| 30229 | total count of inner rpc (latency >= 20ms) in xa statement | For XA transactions, the total number of RPCs whose execution time exceeds 20 ms during the execution of XA logic. When the transaction side executes XA logic, if the execution time of an RPC exceeds 20 ms, the count is incremented by 1. |
| 30230 | inner rpc total used time in xa statement | For an XA transaction, this is the total time consumed by RPCs during the execution of XA logic, in microseconds. When executing XA logic on the transaction side, the consumption time increases after each RPC call. |
| 30231 | dblink trans total count | Total number of DBlink transactions. The count increments by 1 when a DBlink transaction is started. |
| 30232 | failed dblink trans total count | Total number of failed DBlink transactions. The count increments when a DBlink transaction is initiated and fails. |
| 30233 | dblink trans promotion total count | The total number of times a regular transaction is upgraded to a DBlink transaction. The count increments by 1 when a regular transaction is upgraded to a DBlink transaction. |
| 30234 | dblink trans callback total count | Total number of transaction calls restored via reverse link for DBlink transactions. The count increments by 1 when a transaction is restored via reverse link. |
| 30235 | dblink trans commit total count | Total number of DBlink transaction commits. The count increments by 1 when a DBlink transaction executes a COMMIT statement. |
| 30236 | dblink trans commit total used time | For a DBlink transaction, this is the total time consumed by the transaction commit, in microseconds. When a DBlink transaction executes a commit statement, the commit time is added. |
| 30237 | failed dblink trans commit total count | The total number of failed commits for DBlink transactions. The count increments when a commit statement is executed in a DBlink transaction and fails. |
| 30238 | dblink trans rollback total count | Total number of rollbacks for DBlink transactions. The count increments by 1 when a DBlink transaction executes a Rollback statement. |
| 30239 | dblink trans rollback total used time | For DBlink transactions, the total time consumed for rolling back a DBlink transaction, in microseconds. This value is incremented when the DBlink transaction executes a Rollback statement. |
| 30240 | failed dblink trans rollback total count | The total number of failed DBlink transaction rollbacks. This count increments when a DBlink transaction executes a Rollback statement and fails. |
SQL
You can query SQL-related metrics using the following example.
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=8 and stat_id in (40000, 40001, 40002, 40003, 40004, 40005, 40006, 40007, 40008, 40009, 40010, 40011, 40012, 40018, 40019);
The result is as follows:
+-------+---------+-----------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+-----------------------+------------+
| 8 | 40000 | sql select count | ADD_VALUE |
| 8 | 40001 | sql select time | ADD_VALUE |
| 8 | 40002 | sql insert count | ADD_VALUE |
| 8 | 40003 | sql insert time | ADD_VALUE |
| 8 | 40004 | sql replace count | ADD_VALUE |
| 8 | 40005 | sql replace time | ADD_VALUE |
| 8 | 40006 | sql update count | ADD_VALUE |
| 8 | 40007 | sql update time | ADD_VALUE |
| 8 | 40008 | sql delete count | ADD_VALUE |
| 8 | 40009 | sql delete time | ADD_VALUE |
| 8 | 40018 | sql other count | ADD_VALUE |
| 8 | 40019 | sql other time | ADD_VALUE |
| 8 | 40010 | sql local count | ADD_VALUE |
| 8 | 40011 | sql remote count | ADD_VALUE |
| 8 | 40012 | sql distributed count | ADD_VALUE |
+-------+---------+-----------------------+------------+
15 rows in set (0.08 sec)
Common SQL monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 40000 | sql select count | Number of SQL SELECT executions. |
| 40001 | sql select time | Total SQL SELECT execution time, in microseconds. |
| 40002 | sql insert count | The total number of INSERT statements issued by users. |
| 40003 | sql insert time | Total time consumed by user-issued INSERT statements. |
| 40004 | sql replace count | Total number of REPLACE statements issued by the user. |
| 40005 | sql replace time | Total time consumed by the REPLACE statements issued by the user. |
| 40006 | sql update count | Total number of UPDATE statements issued by users. |
| 40007 | sql update time | Total time consumed by user-initiated UPDATE statements, in microseconds. |
| 40008 | sql delete count | The total number of DELETE statements issued by the user. |
| 40009 | sql delete time | Total time consumed by user-initiated DELETE statements, in microseconds. |
| 40010 | sql local count | Number of SQL LOCAL executions. |
| 40011 | sql remote count | Number of SQL REMOTE executions. |
| 40012 | sql distributed count | The number of distributed executions of the SQL statement. |
| 40013 | active sessions | The number of active sessions. This count increases by 1 for each new connection and decreases by 1 for each disconnected connection. |
| 40014 | single query count | Total number of statements sent over a non-multiple query protocol. |
| 40015 | multiple query count | Total number of statements sent over the multiple query protocol. |
| 40016 | multiple query with one stmt count | The number of statements sent using the multi-query protocol but actually containing only one SQL statement. |
| 40018 | sql other count | The number of executions of SQL statements other than SELECT, INSERT, REPLACE, UPDATE, DELETE, COMMIT, and ROLLBACK. |
| 40019 | sql other time | Total execution time of SQL statements other than SELECT, INSERT, REPLACE, UPDATE, DELETE, COMMIT, and ROLLBACK, in microseconds. |
| 40020 | ps prepare count | Number of PREPARE statements executed by PREPARE STATEMENT. |
| 40021 | ps prepare time | Total time taken by PREPARE STATEMENT to execute the prepare statement, in microseconds. |
| 40022 | ps execute count | Number of times the prepared statement has been executed. |
| 40023 | ps close count | Number of CLOSE operations performed during PREPARE STATEMENT execution. |
| 40024 | ps close time | Total time taken to execute the CLOSE statement in PREPARE STATEMENT, in microseconds. |
| 40025 | sql commit count | The number of COMMIT operations performed by SQL statements. |
| 40026 | sql commit time | Total time taken to execute COMMIT, in microseconds. |
| 40027 | sql rollback count | Number of Rollback executions. |
| 40028 | sql rollback time | Total rollback time, in microseconds. |
| 40030 | opened cursors current | The number of currently open cursors. The count increases by 1 when a cursor is opened and decreases by 1 when a cursor is closed. |
| 40031 | opened cursors cumulative | The cumulative number of cursors opened. Each time a cursor is opened, the count increases by 1. |
| 40100 | sql inner select count | The total number of SELECT statements issued by the inner SQL[1]. |
| 40101 | sql inner select time | Total execution time of SELECT statements issued by the inner SQL, in microseconds[1]. |
| 40102 | sql inner insert count | The total number of INSERT statements issued by the inner SQL[1]. |
| 40103 | sql inner insert time | Total time consumed by INSERT statements issued by INNER SQL, in microseconds[1]. |
| 40104 | sql inner replace count | Total number of REPLACE statements issued by the inner SQL[1]. |
| 40105 | sql inner replace time | Total time consumed by REPLACE statements issued by INNER SQL, in microseconds[1]. |
| 40106 | sql inner update count | The total number of UPDATE statements issued by INNER SQL[1]. |
| 40107 | sql inner update time | The total time consumed by the UPDATE statement issued by INNER SQL, in microseconds[1]. |
| 40108 | sql inner delete count | The total number of DELETE statements issued by INNER SQL[1]. |
| 40109 | sql inner delete time | The total time consumed by the DELETE statement issued by INNER SQL, in microseconds[1]. |
| 40110 | sql inner other count | Total number of other inner SQL statements[1]. |
| 40111 | sql inner other time | Total time consumed by other INNER SQL statements, in microseconds[1]. |
| 40112 | user logons cumulative | The cumulative number of logins of the user. |
| 40113 | user logouts cumulative | The cumulative number of times a user has logged out. This number increments by 1 each time the user disconnects from the session link. |
| 40114 | user logons failed cumulative | The cumulative number of failed login attempts by the user. |
| 40115 | user logons time cumulative | Cumulative login time of the user, in microseconds. |
| 40116 | sql local execute time | Total SQL LOCAL execution time, in microseconds. |
| 40117 | sql remote execute time | Total SQL REMOTE execution time, in microseconds. |
| 40118 | sql distributed execute time | Total time for distributed execution of SQL statements, in microseconds. |
| 40119 | sql fail count | The number of SQL execution failures. |
| 40120 | inner sql local count | Number of times INNER SQL LOCAL is executed. |
| 40121 | inner sql remote count | Number of times an INNER SQL statement is executed remotely. |
| 40122 | inner sql distributed count | The number of times an INNER SQL statement is executed in distributed mode. |
Note [1]: INNER SQL refers to SQL statements that are not sent by users but are internally generated by OceanBase AI Database, typically used to query or modify internal tables.
Cache
You can query KVCache-related metrics using the following example.
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=16 and stat_id in (50000, 50001, 50004, 50005, 50008, 50009, 50010, 50011, 50037, 50038,120001,120008,120009);
The result is as follows:
+-------+---------+-------------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+-------------------------+------------+
| 16 | 50000 | row cache hit | ADD_VALUE |
| 16 | 50001 | row cache miss | ADD_VALUE |
| 16 | 50004 | bloom filter cache hit | ADD_VALUE |
| 16 | 50005 | bloom filter cache miss | ADD_VALUE |
| 16 | 50008 | block cache hit | ADD_VALUE |
| 16 | 50009 | block cache miss | ADD_VALUE |
| 16 | 50010 | location cache hit | ADD_VALUE |
| 16 | 50011 | location cache miss | ADD_VALUE |
| 16 | 50037 | tablet ls cache hit | ADD_VALUE |
| 16 | 50038 | tablet ls cache miss | ADD_VALUE |
| 16 | 120001 | tablet ls cache size | SET_VALUE |
| 16 | 120008 | user row cache size | SET_VALUE |
| 16 | 120009 | bloom filter cache size | SET_VALUE |
+-------+---------+-------------------------+------------+
14 rows in set (0.07 sec)
The GV$SYSSTAT view describes the hit rate of KVCache. To further explore the actual memory usage of each module in KVCache, you can query the GV$OB_KVCACHE view. For example:
obclient> select DBA_OB_TENANTS.tenant_id,tenant_name, svr_ip,svr_port, cache_name, cache_size
from GV$OB_KVCACHE, DBA_OB_TENANTS where DBA_OB_TENANTS.tenant_type!='META' and DBA_OB_TENANTS.tenant_id = GV$OB_KVCACHE.tenant_id limit 10;
Common cache monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 50000 | row cache hit | The table_get operator counts the number of times a data row is hit in the cache during prefetching. In the lookup_in_cache function, this counter increments if the data row is found in the cache. |
| 50001 | row cache miss | The number of times the table get operator prefetches data rows without hitting the cache. In the lookup_in_cache function, this field is incremented if the data row is not in the cache. |
| 50004 | bloom filter cache hit | The macroblock Bloom filter cache hit count indicates that the SQL statement uses a pre-built macroblock Bloom filter2. |
| 50005 | bloom filter cache miss | The number of cache misses for the macroblock Bloom filter indicates that the corresponding macroblock Bloom filter has not been constructed.2 |
| 50006 | bloom filter filts | The number of effective filter passes for the macroblock Bloom filter, where rows corresponding to each other do not exist2. |
| 50007 | bloom filter passes | Number of unfiltered macroblock Bloom filters, indicating that the corresponding row may contain 2. |
| 50008 | block cache hit | The number of times a microblock hit the block cache during retrieval. When retrieving a microblock, it is checked whether the microblock is in the cache. If it hits the cache, this metric increments by 1. |
| 50009 | block cache miss | The number of times that a microblock was not found in the block cache during retrieval. When retrieving a microblock, it is checked whether the microblock is in the cache. If the cache miss occurs, this counter is incremented by 1. |
| 50010 | location cache hit | The number of location cache hits. This includes the number of cache hits for ls_location_cache and tablet_ls_cache. When querying the mapping between tablets and logstreams or the location information of a logstream, if valid data is successfully retrieved from the local cache, the statistic value is incremented by 1. This event is triggered in the get method of ObTabletLSService and ObLSLocationService. |
| 50011 | location cache miss | The number of location cache misses. This metric counts the number of misses in the ls_location_cache and tablet_ls_cache caches. It increments when valid data cannot be obtained from the local cache (e.g., the cache does not exist, has expired, or is invalid) while querying the mapping between tablets and logstreams or the location information of a logstream. It also increments in the get methods of ObTabletLSService and ObLSLocationService when is_cache_hit is false. |
| 50012 | location cache wait | The number of times a thread is blocked and waits when the concurrency for obtaining locations in OceanBase Location Cache reaches the upper limit. When the concurrency for obtaining location information reaches the maximum limit (max_count), threads must wait on the semaphore. The count increments by 1 each time a thread enters a waiting state in the ObLocationSem::acquire method. This event is triggered when has_wait is true, and it is used to control the concurrency for obtaining location information, preventing excessive concurrent requests from putting pressure on the system. |
| 50013 | location cache get hit from proxy virtual table | The number of location cache hits obtained from the __all_virtual_proxy_schema table. When tablet location information is queried through a proxy virtual table (ObAllVirtualProxySchema), the value increments by 1 if the mapping between tablets and log streams and the log stream location can be successfully retrieved from the cache. |
| 50014 | location cache get miss from proxy virtual table | The number of location cache misses when reading from the __all_virtual_proxy_schema table. When tablet location information is queried through a proxy virtual table (ObAllVirtualProxySchema), the value increments by 1 if the mapping between a tablet and its log stream or the log stream location cannot be retrieved from the cache. |
| 50017 | location nonblock get hit | Indicates the number of hits for non-blocking reads from the ls_location_cache and tablet_ls_cache. When the nonblock_get method is used to query the mapping between a tablet and its log streams or the location of a log stream, the value increments by 1 if valid data is successfully retrieved from the local cache. It is triggered in the ObTabletLSService::nonblock_get and ObLSLocationService::nonblock_get methods when OB_SUCC(ret) is true. Unlike the regular get method, nonblock_get does not wait for cache updates and returns the result immediately. |
| 50028 | kvcache sync wash time | The time consumed by flushing the cache memory when insufficient memory is available, in microseconds. This value is added to the corresponding time when try_flush_washable_mb is called and cache flushing is performed. |
| 50029 | kvcache sync wash count | The number of times the cache is flushed to memory when insufficient memory is available. This value increments by 1 whenever the try_flush_washable_mb function is called and cache flushing is performed. |
| 50033 | fuse row cache hit | The table get operator counts the number of times a non-MEMTable row is hit in the cache. In the get_fuse_row_cache function, it determines whether the row corresponding to the rowkey is in the cache. If a hit occurs, this counter is incremented by 1. |
| 50034 | fuse row cache miss | The number of times the table get operator failed to hit the cache when reading non-MEMTABLE rows. In the get_fuse_row_cache function, this field increments by 1 if the row corresponding to the rowkey is not in the cache. |
| 50035 | schema cache hit | The number of times a schema is successfully retrieved from the schema cache. When a schema is queried, the value increments by 1 if the corresponding schema information is successfully retrieved from the local cache. It is triggered in the ObSchemaCache::get_schema method when the cache_.get() call succeeds. |
| 50036 | schema cache miss | The number of times a schema cannot be obtained from the schema cache. When a schema is queried, the value increments by 1 if the corresponding schema information cannot be retrieved from the local cache (the cache does not exist, has expired, or is invalid). It is triggered in the ObSchemaCache::get_schema method when the cache_.get() call fails. |
| 50037 | tablet ls cache hit | The number of times a cache hit occurs when reading a tablet. In the get_from_cache_ function, this field is incremented if the tablet is found in the cache. |
| 50038 | tablet ls cache miss | The number of times that a tablet is not found in the cache when being read. In the get_from_cache function, this field increments by 1 if the tablet is not in the cache. |
| 50045 | opt table stat cache hit | The hit rate of the table/partition-level statistics cache. If a hit occurs, the statistics are directly read from memory. The unit is percentage. KVCache measures the hit rate of the table/partition-level statistics cache. |
| 50046 | opt table stat cache miss | The miss rate of the table/partition-level statistics cache. If a cache miss occurs, statistics must be loaded from the internal table into memory. The unit is percentage. KVCache records the miss rate of the table/partition-level statistics cache. |
| 50047 | opt column stat cache hit | The hit rate of the column-level statistics cache. If a cache hit occurs, the statistics are directly read from memory. The unit is percentage. KVCache records the hit rate of the column-level statistics cache. |
| 50048 | opt column stat cache miss | The miss rate of the column-level statistics cache. If a cache miss occurs, statistics must be loaded from the internal table into memory. The unit is percentage. KVCache statistics track the miss rate of the column-level statistics cache. |
| 50049 | tmp page cache hit | The number of page cache hits for temporary files during reads. Temporary files on shared storage do not use block caching; instead, they are organized in an 8KB page cache. Before reading data, the system first checks the KVCache. If a hit is found, the data is read from the KVCache and the hit count is incremented by 1. |
| 50050 | tmp page cache miss | The number of cache misses for temporary files during page reads. Temporary files on shared storage do not use block caching; instead, they are cached in 8KB pages. Before reading data, the system first checks the KVCache. If a miss occurs, an I/O request is sent, and the miss count is incremented by 1. |
| 50051 | tmp block cache hit | The number of hits when reading temporary files from a 2 MB block cache. Before reading data from disk, the system first checks if the data is cached in the KVCache. If a hit occurs, the data is read directly from the cache and the hit count is incremented by 1. |
| 50052 | tmp block cache miss | The number of misses in the block cache for temporary files during read operations. |
| 50055 | opt ds stat cache hit | The cache hit rate of statistics collected by dynamic sampling. If a hit occurs, the result in memory is directly read. The unit is percentage. KVCache statistics cache hit rate of dynamic sampling statistics. |
| 50056 | opt ds stat cache miss | The cache miss rate of statistics collected by dynamic sampling. If a cache miss occurs, a sampling SQL query is initiated to collect the data. The unit is percentage. KVCache records the cache hit rate of statistics collected by dynamic sampling. |
| 50057 | storage meta cache hit | The number of hits in the secondary and tertiary metadata cache. Some larger metadata (greater than 1 KB), such as table schemas and multiple SSTables, is stored on data tablets. These metadata can be accessed on demand and do not need to reside in memory all the time. Therefore, we manage the memory lifecycle of these structures using a KVCache approach. These two metrics are the hit statistics for the metadata KVCache. |
| 50058 | storage meta cache miss | The number of misses in the secondary and tertiary metadata cache. Some larger metadata (over 1 KB), such as table schemas and multiple SSTables, is stored on data tablets. These metadata can be accessed on demand and do not need to reside in memory constantly. Therefore, we manage the memory lifecycle of these structures using a KVCache approach. These two metrics are the hit statistics for the metadata KVCache. |
| 50059 | tablet cache hit | The number of cache hits for storing tablet shards. A tablet, or data shard, is a common structure used in many processes. When a tenant supports a large number of partitions, the limited memory cannot ensure that all tablets are resident in memory. Therefore, we use KVCache to manage and maintain the memory lifecycle of tablets. These metrics are related to the cache hit rate of tablets. |
| 50060 | tablet cache miss | The number of cache misses for storing tablet shards. A tablet, or data shard, is a common structure used in many processes. When a tenant supports a large number of partitions, limited memory cannot guarantee that all tablets are resident in memory. Therefore, we use KVCache to manage the memory lifecycle of tablets. These metrics are related to the cache hit rate of tablets. |
| 50061 | schema history cache hit | Number of schema history cache hits. The schema history cache stores the mapping from schema_type, tenant_id, schema_id, schema_version to the exact schema version in the history_cache table, avoiding repeated queries of system tables for the exact version number. The value increments by 1 if the exact schema version is successfully retrieved from the history cache. |
| 50062 | schema history cache miss | Number of schema history cache misses. The schema history cache stores the mapping from schema_type, tenant_id, schema_id, schema_version to the exact schema version in the history_cache table, avoiding repeated queries of system tables for the exact version number. The value increments by 1 if the exact schema version cannot be obtained from the history cache (the cache does not exist, has expired, or is invalid). |
| 50063 | opt system stat cache hit | The cache hit rate of the cost model system parameters. If a hit occurs, the cached result in memory is directly read. The unit is percentage. KVCache statistics for the cost model parameter cache hit rate. |
| 50064 | opt system stat cache miss | The hit rate of the cost model system parameter cache. If a cache miss occurs, parameters related to the cost model need to be loaded from an internal table. The unit is percentage. KVCache records the miss rate of the cost model parameter cache. |
| 50065 | log kv cache hit | The number of hits in the cold cache. When reading logs from PALF, if the hot cache does not provide a hit, it attempts to read from the cold cache. If the requested log is found in the cold cache, this statistic increments by 1. |
| 50066 | log kv cache miss | The number of misses in the cold cache. When reading logs from PALF, if the hot cache does not provide a hit, it attempts to read from the cold cache. If the requested log is not in the cold cache, this statistic increments by 1. |
| 50067 | data block cache miss | The number of times that a data microblock is fetched without hitting the block cache. When fetching a data microblock, it is checked whether the microblock is in the cache. If the cache is not hit, this counter increments by 1. |
| 50068 | index block cache miss | The number of times that the block cache was not hit when retrieving intermediate index microblocks. When retrieving intermediate index microblocks, it is determined whether the data microblock is in the cache. If the cache is not hit, this counter increments by 1. |
| 50069 | multi version fuse row cache hit | The number of times the table get operator hit the cache when reading rows from a materialized view.get_fuse_row_cacheIn the function, if the table is a materialized view, it checks whether the row corresponding to the rowkey is in the cache. If a hit is found, the count for that item is incremented by 1. |
| 50070 | multi version fuse row cache miss | The number of times that the table GET operator failed to hit the cache when reading rows from a materialized view.get_fuse_row_cacheIn the function, if the table is a materialized view, it checks whether the row corresponding to the rowkey is in the cache. If the cache does not hit, this counter is incremented by 1. |
| 50071 | backup index cache hit | Number of cache hits for backup index. Backup indexes are placed in the cache. During restore, the cache is read first; if not found, the backup media is read. |
| 50072 | backup index cache miss | Number of cache misses for backup index. Backup indexes are placed in the cache. During restore, the cache is read first; if not found, the backup media is read. |
| 50073 | backup meta cache hit | Number of cache hits for backup metadata. Backup metadata is cached. During restore, the system first reads from the cache; if the data is not found, it reads from the backup media. |
| 50074 | backup meta cache miss | Number of cache hits for backup metadata. Backup metadata is cached. During restore, the system first reads from the cache; if the data is not found, it reads from the backup media. |
| 50075 | truncate info cache hit | The number of times the cache is hit when reading truncate info. In the get_truncate_info_array function, this field indicates whether the truncate info is in the cache. If the cache is hit, this value is incremented by 1. |
| 50076 | truncate info cache miss | The number of times that the cache was not hit when reading truncate info. In the get_truncate_info_array function, this field is incremented if the truncate info is not in the cache. |
| 120001 | tablet ls cache size | The cache size of the tablet, in bytes. The tablet cache is used in the get_from_cache function. |
| 120002 | table stat cache size | The memory space occupied by table/partition-level statistics cache, in bytes. This statistic is collected by the KVCache module. |
| 120003 | table col stat cache size | The memory space occupied by the column-level statistics cache, in bytes. The KVCache module collects statistics on the memory space used by the column-level statistics cache. |
| 120004 | index block cache size | The cache size used by the microblock of the intermediate layer index, in bytes. In the get_micro_block_handle function, the microblock of the intermediate layer index is used.index_block_cache_. |
| 120006 | user block cache size | The cache size used by the data microblock, in bytes. In the get_micro_block_handle function, an intermediate layer is used to index microblocks.index_block_cache_. |
| 120008 | user row cache size | The cache size in bytes used by the table get operator when prefetching data rows. This cache is utilized by the lookup_in_cache function. |
| 120009 | bloom filter cache size | The total size of the macroblock Bloom filter cache, in bytes. User INSERT statements require checking for primary key conflicts. When the data to be checked resides on disk, a common performance optimization to avoid I/O overhead is to build a Bloom filter for the involved data range. This series of metrics are related to the Bloom filter cache. |
| 120010 | log kv cache size | The size of logs read from the cold cache, in bytes. When reading logs from PALF, if the hot cache does not hit, it attempts to read from the cold cache. If the requested log is in the cold cache, the size of the successfully read log from the cold cache is accumulated. |
| 240001 | ss_micro_cache hit count | In shared storage mode, this metric records the total number of times all user requests hit the local microblock cache. When a user request requires accessing microblock data, if the data is retrieved from the local microblock cache, it counts as a hit, and the value of this statistic is incremented by one. |
| 240002 | ss_micro_cache miss count | In shared storage mode, the total number of times all user requests fail to hit the local microblock cache. When a user request needs to access microblock data and the data is not obtained from the local microblock cache, it is considered a miss, and the value of this statistic is incremented by one. |
| 240003 | ss_micro_cache failure count of adding cache | The total number of failures in attempting to add microblock data to the local microblock cache under shared storage mode. This statistic increments by one when adding microblock data to the local microblock cache fails during a new microblock write operation. |
| 240004 | ss_micro_cache failure count of getting cache | The total number of errors that occurred when attempting to retrieve a specified microblock from the local microblock cache in shared storage mode. This statistic increments by one when an exception fails to retrieve the specified microblock data from the local microblock cache (a cache miss is not considered a failure). |
| 240005 | ss_micro_cache major_compaction prewarm count | In shared storage mode, the number of successful additions to the local microblock cache during a major compaction warm-up scenario. During warm-up, hot microblock data is attempted to be added to the local microblock cache. If a microblock is successfully added to the cache (or if the microblock already exists), this statistic value is incremented by one. |
| 240006 | ss_micro_cache migrate prewarm count | The number of successful additions to the local microblock cache during warm-up scenarios for migration replication or cross-zone disaster recovery. During warm-up for migration replication or cross-zone disaster recovery, hot microblock data is attempted to be added to the local microblock cache. If this microblock data is successfully added to the cache (or if the microblock data already exists, it is also considered a success), this statistic value is incremented by one. |
| 240007 | ss_micro_cache replica prewarm count | The number of successful additions to the local microblock cache during warm-up between leader/follower replicas in shared storage mode. During warm-up, hot microblock data is attempted to be added to the local microblock cache. If a microblock is successfully added to the cache (or if it already exists), this statistic value is incremented by 1. |
| 240008 | ss_micro_cache ddl prewarm count | The number of times a microblock is successfully added to the local microblock cache during DDL preheating in shared storage mode. During DDL preheating, hot microblock data is attempted to be added to the local microblock cache. If a microblock is successfully added to the cache (or if the microblock already exists in the cache), this statistic value is incremented by one. |
| 240009 | ss_micro_cache total hold micro_block count | In shared storage mode, the total amount of micro_meta held in memory by the local microblock cache (including both valid and invalid Meta). For each microblock added to the local microblock cache, a metadata entry called micro_meta is created in memory. Subsequently, as data is evicted due to expiration or other operations, microblock data may become invalid. This statistic represents the total amount of all Meta entries in memory. |
| 240010 | ss_micro_cache total hold micro_block size | In shared storage mode, it indicates the total size of microblock data (including valid and invalid meta) held in memory for microblocks corresponding to micrometa entries in the local microblock cache, in bytes. For each microblock added to the local microblock cache, a metadata entry called micrometa is created in memory. Subsequently, as data is evicted due to expiration or other operations, microblock data may become invalid. This statistic represents the total size of microblock data corresponding to all micrometa entries in memory. |
| 240011 | ss_micro_cache hit total bytes | In shared storage mode, the total data size in bytes that all user requests hit the local microblock cache. If data is retrieved from the local microblock cache when a user request needs to access microblock data, it is considered a hit, and the value of this statistic increases by micro_size. |
| 240012 | ss_micro_cache miss total bytes | In shared storage mode, this metric indicates the total data size in bytes that all user requests failed to retrieve from the local microblock cache. When a user request requires accessing microblock data and the data is not obtained from the local microblock cache, it is considered a miss, and the value of this metric increases by micro_size. |
| 240013 | ss_micro_cache major_compaction prewarm bytes | In shared storage mode, during a major compaction warm-up scenario, this metric indicates the total size in bytes of all microblocks successfully added to the local microblock cache. During warm-up, hot microblock data is attempted to be added to the local microblock cache. If a microblock is successfully added to the cache (or if it already exists), this statistic value increases by micro_size. |
| 240014 | ss_micro_cache migrate prewarm bytes | In shared storage mode, during warm-up scenarios for migration replication or cross-zone disaster recovery, this metric indicates the total size in bytes of all microblocks successfully added to the local microblock cache. During warm-up for migration replication or cross-zone disaster recovery, hot microblock data is attempted to be added to the local microblock cache. If a microblock is successfully added to the cache (or if it already exists, it is also considered successful), this statistic value increases by micro_size. |
| 240015 | ss_micro_cache replica prewarm bytes | In shared storage mode, during leader/follower replica warm-up, the total size in bytes of all microblocks successfully added to the local microblock cache. During warm-up, leader/follower replicas attempt to add hot microblock data to the local microblock cache. If a microblock is successfully added to the cache (or if it already exists), this statistic value increases by micro_size. |
| 240016 | ss_micro_cache ddl prewarm bytes | In shared storage mode, this is the total size in bytes of all microblocks successfully added to the local microblock cache during DDL warm-up. During DDL warm-up, hot microblock data is attempted to be added to the local microblock cache. If a microblock data is successfully added to the cache (or if the microblock data already exists, it is also considered successful), this statistic value increases by micro_size. |
| 240019 | ss_micro_cache total used disk size | In shared storage mode, this metric indicates the size of disk space already used by the local microblock cache, in bytes. The local microblock cache occupies a block of disk space by pre-allocating large files. This disk space is logically managed as 2 MB data blocks. This statistic represents the total size of these data blocks that have been used. |
| 240024 | ss_micro_cache add micro_block count of common_io | In shared storage mode, the total number of microblocks added to the local microblock cache based on user requests. This value increments by 1 whenever an attempt is made to cache a microblock pulled from object storage locally based on a user request. |
| 240025 | ss_micro_cache get micro_block count of common_io | In shared storage mode, the total number of times microblocks are fetched from the tenant's local microblock cache based on user requests. This value increments by 1 whenever an attempt is made to read microblock data from the local cache based on a user request (regardless of whether the read is successful). |
| 240026 | ss_micro_cache total new added micro_block count | In shared storage mode, the total number of times a microblock is added to the local microblock cache based on a user request. When microblock data needs to be added to the local microblock cache based on a user request, if the microblock does not currently exist in the cache, it is considered the first addition, and the value of this statistic increases by one. |
| 240027 | ss_micro_cache new added micro_block count of common_io | In shared storage mode, the total number of times a microblock is successfully added to a tenant's local microblock cache based on a user request. This value increments by 1 whenever an object pulled from storage is successfully cached to local disk based on a user request, and no information about that microblock existed in the cache prior to the addition. |
| 240028 | ss_micro_cache total evicted micro_block count | The number of ghost micro-meta entries accumulated and deleted from the local microblock cache in shared storage mode. The eviction algorithm used by the local microblock cache is the ARC cache replacement algorithm. When a microblock needs to be evicted, its corresponding data is deleted, and the micro-meta entry becomes a ghost micro-meta entry. This metric shows the total number of micro-meta entries that have been changed to ghost micro-meta entries. |
| 240029 | ss_micro_cache total deleted micro_block count | The number of deleted ghost micro-meta entries accumulated in the local microblock cache under shared storage mode. The eviction algorithm used by the local microblock cache is the ARC cache replacement algorithm. If a microblock is evicted but its micro-meta is retained, that micro-meta is referred to as a ghost micro-meta. This statistic represents the total number of deleted ghost micro-meta entries. |
| 240030 | ss_micro_cache released phy_block count by reorgan_task | The total number of data blocks released during microblock reorganization tasks in shared storage mode. The disk space for the local microblock cache is managed in 2 MB increments. When disk space is full, data blocks with low usage have their valid data aggregated into new data blocks to free up space. This statistic represents the total number of "net-released data blocks". |
| 240031 | ss_micro_cache storage_cache_policy prewarm count | In shared storage mode, the number of successful additions to the local microblock cache in manual hotspot data warming scenarios. During manual hotspot data warming, attempts are made to add hotspot microblock data to the local microblock cache. If a microblock is successfully added to the cache (or if the microblock already exists), this statistic value is incremented by one. |
| 240032 | ss_micro_cache storage_cache_policy prewarm bytes | In shared storage mode, under the scenario of manually specifying hot data for preheating, this statistic indicates the total size in bytes of all microblocks successfully added to the local microblock cache. During manual hot data preheating, attempts are made to add hot microblock data to the local microblock cache. If a microblock is successfully added to the cache (or if it already exists), this statistic value increases by micro_size. |
| 245001 | ss_micro_cache micro_meta used memory size | The memory size consumed by the local microblock cache in shared storage mode, in bytes. This is the total memory size consumed by the microblock meta data and other components within the local microblock cache. |
| 245002 | ss_micro_cache total alloc disk size | In shared storage mode, the disk space allocated to the local microblock cache, in bytes. When initializing the tenant's local microblock cache module, a disk space size is calculated and allocated to the local microblock cache. |
| 245011 | ss_micro_cache hot micro_block lack count | In shared storage mode, the number of microblock data entries missing from the local microblock cache that are stored in the KVCache. A scheduled task periodically scans all microblocks in the KVCache and compares them with those in the local microblock cache. Each time the task runs, this statistic starts at 0 and increments by one for each microblock that exists in the KVCache but not in the local microblock cache. |
| 245012 | ss_micro_cache read iops of common_io | In shared storage mode, it indicates the number of times per second that microblock data is read from the local microblock cache based on user requests. The metric calculates the total number of attempts to read microblock data from the local microblock cache within 2 seconds based on user requests, and then computes the IOPS. |
| 245013 | ss_micro_cache read throughput of common_io | In shared storage mode, it indicates the amount of data read from the local microblock cache per second based on user requests, in bytes. The total size of microblock data attempted to be read from the local microblock cache within 2 seconds based on user requests is calculated, and then the throughput per second is determined. |
| 245014 | ss_micro_cache write iops of common_io | In shared storage mode, the number of local microblock cache writes per second based on user requests. The total number of attempts to write microblock data to the local microblock cache within 2 seconds is counted, and then the IOPS per second is calculated. |
| 245015 | ss_micro_cache write throughput of common_io | In shared storage mode, it indicates the amount of data written to the local microblock cache per second based on user requests, in bytes. The total size of microblock data attempted to be written to the local microblock cache within 2 seconds based on user requests is calculated, and then the throughput per second is determined. |
| 245016 | ss_micro_cache read iops of prewarm_io | In shared storage mode, the number of reads from the local microblock cache per second based on preheated requests. The metric counts the total number of attempts to read microblock data from the local microblock cache within 2 seconds based on preheated requests, and then calculates the IOPS. |
| 245017 | ss_micro_cache read throughput of prewarm_io | In shared storage mode, it indicates the amount of data read per second from the local microblock cache based on preheated requests, in bytes. It calculates the total size of microblock data attempted to be read from the local microblock cache within 2 seconds based on preheated requests, and then computes the throughput per second. |
| 245018 | ss_micro_cache write iops of prewarm_io | In shared storage mode, it indicates the number of times per second that preheated requests write data to the local microblock cache. The metric counts the total number of attempts to write microblock data to the local microblock cache within 2 seconds based on preheated requests, and then calculates the IOPS. |
| 245019 | ss_micro_cache write throughput of prewarm_io | In shared storage mode, it indicates the amount of data written to the local microblock cache per second based on preheated requests, in bytes. It calculates the total size of microblock data attempted to be written to the local microblock cache within 2 seconds based on preheated requests, and then computes the throughput per second. |
| 245020 | ss_micro_cache valid micro_block count | In shared storage mode, this is the total number of valid microblocks held in the local microblock cache. The total number of valid microblocks in the cache is updated each time microblock data is added to or removed from the local microblock cache. |
| 245021 | ss_micro_cache valid micro_block size | In shared storage mode, the total size of valid microblocks held in the local microblock cache, in bytes. This value is updated each time microblock data is added to or removed from the local microblock cache. |
| 245022 | ss_macro_cache max capacity of the META_FILE type | In shared storage mode, the maximum disk cache capacity for meta-type macroblocks in the macroblock cache, in bytes3. |
| 245023 | ss_macro_cache META_FILE used disk size | In shared storage mode, the disk size used by meta-type macroblocks in the macroblock cache is in bytes. The total size of meta-type macroblocks in the macroblock cache3. |
| 245024 | ss_macro_cache META_FILE hit count | In shared storage mode, this metric records the number of hits for meta-type macroblocks in the macroblock cache. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245025 | ss_macro_cache META_FILE miss count | The number of misses for meta-type macroblocks in the macroblock cache under shared storage mode. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245026 | ss_macro_cache META_FILE hit bytes | In shared storage mode, this is the total size of metadata macroblocks hit in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache, if the macroblock is hit and its type is Meta, this value increases by the size of the read data3. |
| 245027 | ss_macro_cache META_FILE miss bytes | In shared storage mode, this is the total size of metadata macroblocks that did not hit the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache and it does not hit, if the macroblock type is Meta, this value increases by the size of the read3. |
| 245028 | ss_macro_cache max capacity of the TMP_FILE type | In shared storage mode, this parameter specifies the maximum disk cache capacity for macroblocks of temporary file types in the macroblock cache, in bytes. Different types of caches in the macroblock cache can share this capacity. Therefore, this value equals the disk size of the macroblock cache allocated to the tenant minus a reserved size3. |
| 245029 | ss_macro_cache TMP_FILE used disk size | In shared storage mode, the disk size used by macroblocks of temporary file types in the macroblock cache, in bytes. The total size of macroblocks of temporary file types in the macroblock cache is 3. |
| 245030 | ss_macro_cache TMP_FILE hit count | In shared storage mode, this metric indicates the number of hits for macroblocks that are temporary files in the macroblock cache. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type.3 |
| 245031 | ss_macro_cache TMP_FILE miss count | The number of misses for macroblocks that are temporary files in the macroblock cache under shared storage mode. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245032 | ss_macro_cache TMP_FILE hit bytes | In shared storage mode, this is the total size of macroblocks that are temporary file type macroblocks and hit in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache, if the macroblock is hit and the macroblock type is temporary file type, this value increases by the size of the read3. |
| 245033 | ss_macro_cache TMP_FILE miss bytes | In shared storage mode, this is the total size of temporary file-type macroblocks that did not hit the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache and it does not hit, if the macroblock type is temporary file type, this value increases by the size of the read3. |
| 245034 | ss_macro_cache max capacity of the MACRO_BLOCK type | In shared storage mode, this parameter specifies the maximum disk cache capacity for macroblocks of the general macroblock type in the macroblock cache, in bytes. The cache capacities of different types in the macroblock cache can be mutually occupied. Therefore, this value equals the disk size of the macroblock cache allocated to the tenant minus a reserved size3. |
| 245035 | ss_macro_cache MACRO_BLOCK used disk size | In shared storage mode, the disk size used by macroblocks of regular types in the macroblock cache is in bytes. The total size of macroblocks of regular types in the macroblock cache 3. |
| 245036 | ss_macro_cache MACRO_BLOCK hit count | In shared storage mode, this counter records the number of hits for macroblocks of regular types in the macroblock cache. Each time a macroblock is requested from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245037 | ss_macro_cache MACRO_BLOCK miss count | The number of misses for macroblocks of regular types in the macroblock cache under shared storage mode. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245038 | ss_macro_cache MACRO_BLOCK hit bytes | In shared storage mode, this is the total size of macroblocks of the normal type hit in the macroblock cache, in bytes. Each time you attempt to read a macroblock from the macroblock cache, if the cache is hit and the macroblock is of the normal type, this value is incremented by the size of the read data3. |
| 245039 | ss_macro_cache MACRO_BLOCK miss bytes | In shared storage mode, this is the total size of misses for macroblocks of the normal type in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache and the cache miss, and the macroblock type is normal, this value increases by the size of the read3. |
| 245040 | ss_macro_cache max capacity of the HOT_TABLET_MACRO_BLOCK type | In shared storage mode, the maximum disk cache capacity for hot macroblock types in the macroblock cache, in bytes. The cache capacities of different types in the macroblock cache can be reallocated to each other. Therefore, this value equals the disk size of the macroblock cache allocated to the tenant minus a reserved size3. |
| 245041 | ss_macro_cache HOT_TABLET_MACRO_BLOCK used disk size | In shared storage mode, the disk size used by hot macroblocks in the macroblock cache, in bytes. The total size of hot macroblocks in the macroblock cache is 3. |
| 245042 | ss_macro_cache HOT_TABLET_MACRO_BLOCK hit count | In shared storage mode, this counter records the number of hits for hotspot macroblocks in the macroblock cache. Each time you attempt to retrieve a macroblock from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245043 | ss_macro_cache HOT_TABLET_MACRO_BLOCK miss count | The number of misses for hot macroblock types in the macroblock cache under shared storage mode. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245044 | ss_macro_cache HOT_TABLET_MACRO_BLOCK hit bytes | In shared storage mode, this is the total size of hot macroblocks hit in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache, if the macroblock is hit and the macroblock type is hot, this value increases by the size of the read3. |
| 245045 | ss_macro_cache HOT_TABLET_MACRO_BLOCK miss bytes | In shared storage mode, this is the total size of hot macroblocks that did not hit the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache and it does not hit, if the macroblock type is a hot type, this value increases by the size of the read3. |
| 245046 | ss_macro_cache private macro hit count | In shared storage mode, the number of private macroblock cache hits 3. |
| 245047 | ss_macro_cache private macro miss count | The number of misses for private macroblocks in the macroblock cache under shared storage mode. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245048 | ss_macro_cache private macro hit bytes | In shared storage mode, this is the total size of private-type macroblocks hit in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache, if the cache is hit and the macroblock type is private, this value increases by the size of the read3. |
| 245049 | ss_macro_cache private macro miss bytes | In shared storage mode, the total size of misses for private macroblocks in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache and it misses, if the macroblock type is private, this value increases by the size of the read3. |
| 245050 | ss_macro_cache shared macro hit count | In shared storage mode, this counter records the number of hits for macroblocks of the shared type in the macroblock cache. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245051 | ss_macro_cache shared macro miss count | In shared storage mode, the number of misses for shared-type macroblocks in the macroblock cache. Each time a macroblock is attempted to be fetched from the macroblock cache, the hit/miss count is incremented based on the macroblock type3. |
| 245052 | ss_macro_cache shared macro hit bytes | In shared storage mode, this is the total size of shared-type macroblocks hit in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache, if the macroblock is hit and the macroblock type is shared, this value increases by the size of the read3. |
| 245053 | ss_macro_cache shared macro miss bytes | In shared storage mode, this is the total size of misses for shared-type macroblocks in the macroblock cache, in bytes. Each time a macroblock is attempted to be read from the macroblock cache and it misses, if the macroblock type is shared, this value increases by the size of the read3. |
| 245054 | external table disk cache hit count | The number of hits in the external table data disk cache. This feature is supported in both SN and SS modes. Each time an external table is queried, if the disk caching function (enabled by default) is enabled, this value increments by 1 for each macroblock hit. After enabling external table disk caching, external table data is split into chunks of macroblocks and stored on disk. |
| 245055 | external table disk cache miss count | The number of misses in the external table data disk cache. This metric is supported in both SN and SS modes. Each time an external table is queried, if the disk cache feature (enabled by default) is enabled, this value increments by 1 for each macroblock that is not hit. |
| 245056 | external table disk cache hit bytes | The total size of data hits in the external table disk cache. This feature is supported in both SN and SS modes, and the unit is bytes. Each time an external table is queried, if the disk cache (enabled by default) is enabled, this value increases by the size of each macroblock hit. |
| 245057 | external table disk cache miss bytes | The total size of misses in the external table data disk cache, supported in both SN and SS modes, in bytes. Each time an external table is queried, if the disk caching feature (enabled by default) is enabled, this value increases by the size of each macroblock that is missed. |
| 245058 | ss_macro_cache total alloc disk size | In shared storage mode, the disk space allocated to the local macroblock cache, in bytes. When initializing the tenant's local macroblock cache module, a disk space size is calculated and allocated to the local macroblock cache. |
Note [2]: User INSERT statements need to check for primary key conflicts. When the data to be checked is on disk, a common performance optimization technique to avoid I/O is to build a Bloom filter for the involved data range.
Note [3]: Macroblock cache can be categorized by cache type into META/TMP_FILE/MACRO/HOT_MACRO. Caches of different types within the macroblock cache can coexist, so this value equals the disk size allocated for the tenant's macroblock cache minus a reserved portion.
Storage
You can query storage-related metrics using the following example.
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=32 and stat_id in (60000, 60001, 60002, 60003, 60004, 60005, 60019, 60020, 60021, 60022, 60023, 60024,130000,130001,130002,130004);
The result is as follows:
+-------+---------+--------------------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+--------------------------------+------------+
| 32 | 60000 | io read count | ADD_VALUE |
| 32 | 60001 | io read delay | ADD_VALUE |
| 32 | 60002 | io read bytes | ADD_VALUE |
| 32 | 60003 | io write count | ADD_VALUE |
| 32 | 60004 | io write delay | ADD_VALUE |
| 32 | 60005 | io write bytes | ADD_VALUE |
| 32 | 60019 | memstore read lock succ count | ADD_VALUE |
| 32 | 60020 | memstore read lock fail count | ADD_VALUE |
| 32 | 60021 | memstore write lock succ count | ADD_VALUE |
| 32 | 60022 | memstore write lock fail count | ADD_VALUE |
| 32 | 60023 | memstore wait write lock time | ADD_VALUE |
| 32 | 60024 | memstore wait read lock time | ADD_VALUE |
| 32 | 130000 | active memstore used | SET_VALUE |
| 32 | 130001 | total memstore used | SET_VALUE |
| 32 | 130002 | major freeze trigger | SET_VALUE |
| 32 | 130004 | memstore limit | SET_VALUE |
+-------+---------+--------------------------------+------------+
16 rows in set (0.06 sec)
Common storage monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 60000 | io read count | Indicates the total IOPS for read I/O operations. After each read I/O operation is completed, the IO_READ_COUNT metric is incremented by 1. |
| 60001 | io read delay | Represents the total latency of read I/O operations, in microseconds. The IO_READ_DELAY metric accumulates the hardware service latency for each read I/O operation. This latency is measured from the time the request is dispatched to the disk until the disk completes processing and returns the result. |
| 60002 | io read bytes | Represents the total bandwidth for read I/O operations. This statistic counts the total amount of data read, in bytes. You can calculate the average read I/O per second by analyzing the change over a period of time. After each read I/O operation is completed, its read bandwidth is measured and accumulated into the IO_READ_BYTES metric. |
| 60003 | io write count | Indicates the total IOPS for write I/O operations. After each write I/O operation is completed, IO_WRITE_COUNT is incremented by 1. |
| 60004 | io write delay | Represents the total latency of write I/O operations, in microseconds. The IO_READ_DELAY metric accumulates the hardware service latency for each write I/O operation. This latency is measured from the time the request is dispatched to the disk until the disk completes processing and returns a response. |
| 60005 | io write bytes | Represents the total bandwidth for write I/O operations. This statistic counts the total amount of data written, in bytes. You can calculate the average value based on the change over a period of time. After each write I/O operation is completed, its write bandwidth is measured and accumulated into the IO_WRITE_BYTES metric. |
| 60006 | memstore scan count | The number of range queries on the MemTable. This value is incremented when a range query starts on the MemTable. |
| 60007 | memstore scan succ count | The number of successful range queries on the MemTable. This value increments by 1 after a successful range query on the MemTable. |
| 60008 | memstore scan fail count | The number of failed range queries on the MemTable. This value increments by 1 whenever a range query fails on the MemTable for any reason. |
| 60009 | memstore get count | Number of point queries in the MemTable. This value is incremented when a point query starts in the MemTable. |
| 60010 | memstore get succ count | The number of successful point queries in the MemTable. This value increments by 1 after a successful point query in the MemTable. |
| 60011 | memstore get fail count | The number of point queries that fail in the MemTable. This value increments by 1 whenever a point query fails in the MemTable for any reason. |
| 60012 | memstore apply count | The number of times the MemTable has been flushed to disk. This value increments by 1 when a flush to disk starts. |
| 60013 | memstore apply succ count | The number of successful MemTable writes. This value increments by 1 after a successful MemTable write. |
| 60014 | memstore apply fail count | The number of MemTable write failures. This value increments by 1 whenever a MemTable write fails for any reason. |
| 60016 | memstore get time | The time consumed in a point query of the MemTable, in microseconds. This value increases by the time spent on the point query, regardless of whether the query succeeds or fails. |
| 60017 | memstore scan time | The time consumed by a range query on the MemTable, in microseconds. Regardless of whether the range query succeeds or fails, this value includes the time spent on the query. |
| 60018 | memstore apply time | The time consumed in writing to the MemTable, in microseconds. Regardless of whether the write to the MemTable succeeds or fails, this value records the time spent on the write operation. |
| 60019 | memstore read lock succ count | The number of rows successfully read from the MemTable. This value increments by 1 after each row is successfully read from the MemTable. |
| 60020 | memstore read lock fail count | The number of rows that failed to be read from the MemTable due to a read lock conflict timeout. During a read from the MemTable, if the data being read is in the process of being committed but its commit status and version number are uncertain (for example, it is in a state where the log is pending commitment or the distributed transaction is pending resolution, which lasts for a very short time), the read operation waits on the read lock. If the read fails due to this conflict timeout, this value is incremented by 1. |
| 60021 | memstore write lock succ count | The number of rows successfully written to the MemTable. During the write process to the MemTable, lock conflicts, lost updates, and primary key conflicts are verified. If no such conflicts occur, this value is incremented by 1.
NoteRows that pass verification in the MemTable may still fail verification when written to the SSTable. |
| 60022 | memstore write lock fail count | The number of rows that failed to be written to the MemTable due to conflicts. During the write process to the MemTable, lock conflicts, lost updates, and primary key conflicts are detected. If any of these conflicts occur, this value is incremented by 1.
NoteThis does not include checks performed on SSTables. |
| 60023 | memstore wait write lock time | The wait time in microseconds for a request awakened after a lock conflict during MemTable write. During the MemTable write process, if a lock conflict occurs, the request is placed into the lock conflict queue. This value includes the time from when the request was placed in the queue until it is awakened. |
| 60024 | memstore wait read lock time | The wait time for a read lock conflict on a MemTable, in microseconds. During a read from a MemTable, if the data being read is committed but its commit status and version number are uncertain (for example, it is in a state where the log is pending commitment or the distributed transaction is pending completion, which lasts only a short time), the thread waits on the read lock. This value includes the wait time. |
| 60027 | io prefetch micro block count | Total number of I/O operations for microblock cache. Query path optimization loads microblocks from disk into memory via prefetching, which is stored in the microblock cache. This metric indicates the total number of times the system loads microblocks from disk. Combined with the microblock cache hit rate on internal tables, it can measure the effectiveness of query performance optimization in the system. |
| 60028 | io prefetch micro block bytes | The total number of bytes of I/O operations in the microblock cache, in bytes. Query path optimization loads microblocks from disk into memory—namely, the microblock cache—via prefetching. This metric represents the total number of bytes of disk microblocks loaded by the system. Combined with the microblock cache hit rate on internal tables, it can measure the effectiveness of query performance optimization in the system. |
| 60031 | storage read row count | The number of rows after the fuse operator that are processed in a non-pushdown path of the table scan operator, in rows. The value increments by 1 each time the process_fuse_row function is called. |
| 60032 | storage delete row count | The affected row sub-item of DML statements refers to the actual number of valid rows deleted. The statistic increments by 1 each time a row is deleted in the storage engine. |
| 60033 | storage insert row count | The affected row sub-item of DML statements refers to the actual number of valid rows inserted. The statistic increments by 1 each time a row is inserted into the storage engine. |
| 60034 | storage update row count | The number of rows updated by the UPDATE operation. When the update_rows function is called, this value is incremented by the number of rows updated. |
| 60037 | memstore row purge count | Deprecated. |
| 60038 | memstore row compaction count | The number of times sparse multi-version compactions on a MemTable row are consolidated into a compact version. A sparse multi-version is stored in a MemTable row, recording only the changed columns. When the number of versions increases, MemTable generates a compact single version to improve read performance. This value increments by 1 each time a compact single version is generated. |
| 60056 | memstore read row count | The number of rows read from the MemTable before filtering. This value increments by 1 each time a row is read from the MemTable. |
| 60057 | ssstore read row count | The number of rows in major SSTables, minor SSTables, and mini SSTables read from non-compaction paths; the number of rows in SSTables that follow the compaction path includes filtered rows, which is equivalent to the total number of rows within the specified range. The row count for row-based storage includes rows skipped by skip index pre-fetching, while columnar storage does not include such skipped rows. The number of rows in SSTables that follow the non-compaction path includes filtered rows but excludes uncommitted rows, rolled-back rows, and multi-version rows within microblocks. |
| 60058 | memstore write bytes | The amount of data successfully written to the MemTable, in bytes. After data is successfully written to the MemTable, the size of the primary key and the data volume are tallied and incremented accordingly. |
| 60068 | memstore write lock wakenup count in lock_wait_mgr | The number of times a request is awakened after a lock conflict occurs during a write to the MemTable. During a write to the MemTable, if a lock conflict arises, the request is placed in the lock conflict queue. If the request is awakened, this value is incremented by 1. |
| 60077 | get row effect read | The number of valid rows read from a microblock by the table get operator. In the ObMicroBlockRowGetter::get_block_row function, the counter increments by 1 if the row flag of the read row is not DF_NOT_EXIST. |
| 60078 | get row empty read | The number of invalid rows read from a microblock by the table get operator. In the ObMicroBlockRowGetter::get_block_row function, the counter increments by 1 if the row flag of the read row is DF_NOT_EXIST. |
| 60083 | memstore write lock wait timeout count | The number of times a timeout occurs without waking up from a lock conflict during MemTable write. During the writing process to a MemTable, if a lock conflict arises, the request is placed into the lock conflict queue. If the request times out before waking up, this value is incremented by 1. |
| 60084 | accessed data micro block count by io | The number of microblocks that require I/O operations because they were not found in the cache. This value increases by 1 whenever a data microblock is fetched via I/O instead of from the cache. |
| 60085 | data micro block cache hit | The number of data microblocks that hit the block cache when fetched. When fetching data microblocks, it determines whether the microblock is in the cache. If a cache hit occurs, this metric increments by 1. |
| 60086 | accessed index micro block count by io | The number of microblocks that require I/O operations when retrieving intermediate-layer index microblocks. This value is incremented by 1 if a microblock is not retrieved from the cache but requires an I/O operation during the retrieval of intermediate-layer index microblocks. |
| 60087 | index micro block cache hit | The number of times a block cache hit occurs when fetching an intermediate index microblock. When fetching an intermediate index microblock, it is determined whether the microblock is in the cache. If a cache hit occurs, this metric increments by 1. |
| 60088 | blockscaned data micro block count | The number of microblocks opened via the pushdown path, including both rowstore and columnstore microblocks that skip pre-fetching via the skip index. For columnstore tables with multiple CGs, the microblock counts are cumulative. Unit: microblocks. Rowstore: When opening a microblock, check if the pushdown path is available; if so, increment this count by 1. Columnstore: When opening a microblock via the pushdown path, increment this count by 1. |
| 60089 | blockscaned row count | The total number of rows within the specified range that are opened in microblocks under the pushdown path. This includes filtered rows (since there are no uncommitted, rolled-back rows, or multi-version rows within a microblock that can be pushed down; for rowstore, there are no cross-rowkey multi-version rows, while for columnstore, cross-columnstore multi-version rows may exist). For rowstore, this also includes rows skipped by skip index; for columnstore, it does not. The number of rows from multiple CGs in a columnstore table is not cumulative. Unit: number of rows.
|
| 60090 | storage filtered row count | Total rows filtered by pushdown or non-pushdown filters. When filtering, add the number of rows skipped. Rowstore includes rows skipped by skip index pre-fetching, while columnstore does not. |
| 60091 | minor ssstore read row count | The number of minor SSTables and mini SSTables read from the pushed-down path or non-pushed-down path, in rows. The number on the pushed-down path includes filtered rows, which is equivalent to the total number of rows within the specified range for the minor/mini SSTables that follow the pushed-down path. The number on the non-pushed-down path includes filtered rows but excludes uncommitted rows, rolled-back rows, and multi-version rows within microblocks. |
| 60092 | major ssstore read row count | The number of major SSTables read from the pushdown and non-pushdown paths. The number for the pushdown path includes filtered rows, which is equivalent to the total number of rows within the range in the major SSTables that follow the pushdown path. Rowstore includes skipped rows by skip index, while columnstore does not. The number for the non-pushdown path includes filtered rows but excludes uncommitted rows, rolled-back rows, and multi-version rows within microblocks. |
| 60093 | storage waiting throttle time | To dynamically match the system's minor compaction rate, when the minor compaction speed cannot keep up with memory write speeds, memory allocation for certain modules is throttled. This parameter counts the total duration of these memory allocation throttles, in microseconds. When the memory usage of modules such as MemTable (TxData, Mds) exceeds a certain threshold, the thread sleeps for a period after each memory allocation. The total duration of these sleeps is accumulated into this statistic. |
| 69000 | backup io read count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69001 | backup io read bytes | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69002 | backup io write count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69003 | backup io write bytes | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69010 | backup delete count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69012 | backup io read delay | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69013 | backup io write delay | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69017 | backup delete delay | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69019 | backup io list count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69020 | backup io read failed count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69021 | backup io write failed count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69022 | backup io delete failed count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69023 | backup io list failed count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69024 | backup io tagging count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 69025 | backup io tagging failed count | This metric is deprecated. Please check this metric in the I/O Manager. |
| 130000 | active memstore used | The size of the active memstore on a node, in bytes. Memstores are used for replay and write operations on a node. When the usage of a memstore reaches a certain level, it is frozen. All unfrozen memstores are called active memstores. |
| 130001 | total memstore used | The total memory used by the tenant's memstores on the node, in bytes. This memory usage includes both active and frozen memstores, that is, the total memory used by all memstores of the tenant on this node. |
| 130002 | major freeze trigger | The size of the memstore that triggers a freeze, in bytes. In the get_tenant_memstore_cond function, this value is obtained by adding memstore_freeze_trigger to max_cached_memstore_size. |
| 130004 | memstore limit | The upper limit of memory that the tenant's memstore can use on the node, in bytes. You can set the upper limit by using memstore_limit_percentage and _memstore_limit_percentage. memstore_limit_percentage is at the cluster level, and _memstore_limit_percentage is at the tenant level. _memstore_limit_percentage has a higher priority than memstore_limit_percentage. If neither parameter is specified, the memstore available ratio is 40% for tenants smaller than 8 GB and 50% for tenants larger than 8 GB. |
| 240022 | object storage io head count | The number of HEAD operations on object storage. This count increments by 1 when a HEAD request is initiated for object storage. |
| 240023 | object storage io head fail count | The count of failed HEAD operations for object storage. This count increments by 1 when a HEAD request to object storage fails. |
Resource
You can query resource-related metrics using the following example.
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=64 and stat_id in (140002,140003,140005,140006);
The result is as follows:
+-------+---------+-----------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+-----------------+------------+
| 64 | 140002 | max memory size | SET_VALUE |
| 64 | 140003 | memory usage | SET_VALUE |
| 64 | 140005 | max cpus | SET_VALUE |
| 64 | 140006 | cpu usage | SET_VALUE |
+-------+---------+-----------------+------------+
4 rows in set (0.06 sec)
Common resource monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 140001 | min memory size | The memory specification of the tenant, which determines the memory upper limit of the tenant. The OmtNodeBalancer background thread periodically obtains the latest min_memory of the tenant from the DBA_OB_UNIT_CONFIGS table, and then updates it to the Limit of ObTenantMemoryMgr. |
| 140002 | max memory size | The memory specification of the tenant, which determines the memory upper limit of the tenant, in bytes. The OmtNodeBalancer background thread periodically obtains the latest min_memory of the tenant from the DBA_OB_UNIT_CONFIGS table, and then updates it to the Limit of ObTenantMemoryMgr. |
| 140003 | memory usage | The memory used by the tenant, in bytes. ObTenantMemoryMgr maintains a memory value in real time to record the actual memory usage of the tenant. |
| 140004 | min cpus | The CPU specification of the tenant. It determines the number of resident worker threads for the tenant and the number of threads for some background modules. The OmtNodeBalancer background thread periodically obtains the latest max_cpu from the DBA_OB_UNIT_CONFIGS table and synchronizes it to the multi-tenant system. The OmtNodeBalancer background thread periodically adjusts the number of worker threads based on the synchronized max_cpu. |
| 140005 | max cpus | The CPU specification of the tenant. It determines the number of resident worker threads for the tenant and the number of threads for some background modules. When cgroup is enabled, it determines the CPU usage upper limit of tenant threads. The OmtNodeBalancer background thread periodically obtains the latest max_cpu from the DBA_OB_UNIT_CONFIGS table and synchronizes it to the multi-tenant system. The OmtNodeBalancer background thread periodically adjusts the number of worker threads based on the synchronized max_cpu. |
| 140006 | cpu usage | The thread usage rate of the tenant's worker threads, reflecting the busyness of the tenant's worker threads, in percentage. The OmtNodeBalancer background thread calculates the ratio of the time spent processing requests by tenant worker threads to the total time every 10 seconds, normalizes it, and multiplies it by the tenant's min_cpu as the cpu_usage statistical value. |
| 140008 | observer memory used size | The actual memory usage of the OBServer node, in bytes. AChunkMgr maintains a real-time memory value that records the actual memory usage of the OBServer node (which is less than or equal to the actual memory occupancy). |
| 140009 | observer memory free size | The chunk cache of the OBServer node. AChunkMgr maintains a memory value in real time to record the cache size of the OBServer node. The cache represents the amount of memory allocated but not actually used by the OBServer node. |
| 140010 | is mini mode | Indicates whether the OBServer node process is in mini mode. An OBServer node is considered to be in mini mode if its memory is less than 12 GB. |
| 140011 | observer memory hold size | The memory occupied by the OBServer node, including cache and all user data, in bytes. AChunkMgr maintains a real-time memory value that records the memory usage of the OBServer node. |
| 140012 | worker time | Total time spent by tenant worker threads in a busy state, in microseconds. The OmtNodeBalancer background thread collects statistics on the total request-handling time of tenant worker threads every 10 seconds and accumulates it into the worker_time value. |
| 140013 | cpu time | The total CPU time consumed by tenant worker threads and background threads, in microseconds.
|
| 140014 | effective observer memory limit | The maximum memory for an OBServer node, in bytes. This value is node-level and can only be queried by the sys tenant.
|
| 140015 | effective system memory | Reserved memory and hidden sys memory for 500 tenants, in bytes. By default, an appropriate value is automatically determined based on the memory_limit parameter; you can also explicitly specify it using the system_memory parameter. |
| 140016 | effective hidden sys memory | The memory cap for the sys tenant, in bytes. Each OBServer node allocates a portion of its memory for the sys tenant. By default, this value is dynamically determined based on the system_memory parameter; users can also explicitly specify it using the _hidden_sys_tenant_memory parameter. |
| 140017 | max session num | Maximum number of sessions allowed for a tenant. The maximum number of sessions allowed for a tenant is calculated based on the tenant's memory size. |
| 140018 | kvcache hold | The memory size occupied by the KVCache, in bytes. The memory manager collects statistics on the memory usage of the KVCache. |
| 140019 | unmanaged memory size | The memory size directly read from the mmap by the library function, in bytes. It is a global memory value that records in real time the memory requested by library function calls to mmap/unmap. This refers to the memory requested by third-party library functions called on the OBServer node through mmap. |
| 140020 | hidden sys data disk size | In shared storage mode, the disk size configured for the hidden sys tenant is displayed in bytes.
|
Based on the preceding monitoring items, you can easily obtain the tenant's CPU utilization and memory usage:
- Tenant CPU utilization = cpu usage (stat_id = 140013) / max cpus (stat_id = 140005)
- Tenant thread usage = cpu usage (stat_id = 140006) / max cpus (stat_id = 140005)
- Tenant memory usage = memory usage (stat_id = 140003) / max memory size (stat_id = 140002)
Log
You can query system log-related metrics using the following example.
obclient> select distinct CLASS,STAT_ID,NAME,VALUE_TYPE from GV$SYSSTAT where class=128 and stat_id in (160001,160002,160004,160019,160020,160021,160022,160023,160024,160025,160026,160027,160028,160029,160030,160031,160032,160033,160034,160035,160036);
The result is as follows:
+-------+---------+---------------------------------------+------------+
| CLASS | STAT_ID | NAME | VALUE_TYPE |
+-------+---------+---------------------------------------+------------+
| 128 | 160001 | oblogger log bytes | SET_VALUE |
| 128 | 160002 | election log bytes | SET_VALUE |
| 128 | 160004 | oblogger total log count | SET_VALUE |
| 128 | 160019 | async error log dropped count | SET_VALUE |
| 128 | 160020 | async warn log dropped count | SET_VALUE |
| 128 | 160021 | async info log dropped count | SET_VALUE |
| 128 | 160022 | async trace log dropped count | SET_VALUE |
| 128 | 160023 | async debug log dropped count | SET_VALUE |
| 128 | 160024 | async log flush speed | SET_VALUE |
| 128 | 160025 | async generic log write count | SET_VALUE |
| 128 | 160026 | async user request log write count | SET_VALUE |
| 128 | 160027 | async data maintain log write count | SET_VALUE |
| 128 | 160028 | async root service log write count | SET_VALUE |
| 128 | 160029 | async schema log write count | SET_VALUE |
| 128 | 160030 | async force allow log write count | SET_VALUE |
| 128 | 160031 | async generic log dropped count | SET_VALUE |
| 128 | 160032 | async user request log dropped count | SET_VALUE |
| 128 | 160033 | async data maintain log dropped count | SET_VALUE |
| 128 | 160034 | async root service log dropped count | SET_VALUE |
| 128 | 160035 | async schema log dropped count | SET_VALUE |
| 128 | 160036 | async force allow log dropped count | SET_VALUE |
+-------+---------+---------------------------------------+------------+
24 rows in set (0.052 sec)
Common log monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 70000 | refresh schema count | Number of successful schema refreshes. The value increments each time a schema refresh is successfully completed when OceanBase AI Database needs to refresh a schema. In the ObServerSchemaService::refresh_schema method, the operation supports both full refresh (refresh_full_schema) and incremental refresh (refresh_increment_schema) to keep the local metadata cache consistent with system tables. |
| 70001 | refresh schema time | Total time consumed by successful schema refreshes, in microseconds. When OceanBase AI Database refreshes a schema, the total time of the entire refresh process is recorded. In the ObServerSchemaService::refresh_schema method, when OB_SUCC(ret) is true, the time difference from the start to the end of the method is accumulated. This includes all overhead of both full refresh (refresh_full_schema) and incremental refresh (refresh_increment_schema). |
| 70002 | inner sql connection execute count | Number of inner SQL executions. The count increments by 1 for each executed inner SQL statement. |
| 70003 | inner sql connection execute time | Total execution time of inner SQL statements, in microseconds. The total execution time is the cumulative sum of the execution times of inner SQL statements. |
| 70006 | log stream table operator get count | Number of times LS information is successfully read and loaded from the __all_ls_meta_table table. When ObLSTableOperator obtains log stream information from the __all_ls_meta_table table, the value increments by 1 each time ls_info is successfully obtained. In the ObLSTableOperator::get method, the operator can obtain log stream information from memory, RPC, or persistent tables for location caching and metadata management. |
| 70007 | log stream table operator get time | Total time consumed to successfully read and load LS information from the __all_ls_meta_table table, in microseconds. When ObLSTableOperator obtains log stream information from the __all_ls_meta_table table, the total time of the entire process is recorded. In the ObLSTableOperator::get method, the time difference from the start to the end of the method is accumulated. This includes all overhead of obtaining log stream information from memory, RPC, or persistent tables. |
| 160001 | oblogger log bytes | The cumulative total size of printed logs, in bytes. This refers to the output and printing process of system logs such as observer.log. |
| 160002 | election log bytes | Cumulative size of printed election logs, in bytes. This refers to the cumulative size of the election.log files that have been printed. |
| 160004 | oblogger total log count | Cumulative number of log prints. This refers to the output process of system logs such as observer.log. |
| 160019 | async error log dropped count | The number of times an ERROR log failed to be printed (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
| 160020 | async warn log dropped count | The number of WARN logs that failed to be printed. This refers to the output process of system logs such as observer.log. |
| 160021 | async info log dropped count | The number of INFO log print failures (due to throttling, etc.) refers to the output process of system logs such as observer.log. |
| 160022 | async trace log dropped count | The number of failed TRACE log prints. This refers to the output process of system logs such as observer.log. |
| 160023 | async debug log dropped count | The number of DEBUG log print failures (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
| 160024 | async log flush speed | Average number of logs written per second. This refers to the output and printing process of system logs such as observer.log. |
| 160025 | async generic log write count | The default number of successful log prints. This refers to the output process of system logs such as observer.log. |
| 160026 | async user request log write count | Deprecated and only retained in code. Refers to the output printing process of system logs such as observer.log. |
| 160027 | async data maintain log write count | The number of successful minor compaction log prints. This refers to the output process of system logs such as observer.log. |
| 160028 | async root service log write count | The number of successful RS log prints. This refers to the output process of system logs such as observer.log. |
| 160029 | async schema log write count | The number of times schema log printing failed (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
| 160030 | async force allow log write count | The number of successful forced log prints. This refers to the output process of system logs such as observer.log. |
| 160031 | async generic log dropped count | The default number of times that log printing fails (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
| 160032 | async user request log dropped count | Deprecated and only retained in code. Refers to the output printing process of system logs such as observer.log. |
| 160033 | async data maintain log dropped count | The number of times minor compaction logs failed to be printed (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
| 160034 | async root service log dropped count | The number of times RS log printing failed (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
| 160035 | async schema log dropped count | The number of times schema log printing failed (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
| 160036 | async force allow log dropped count | The number of forced log printing failures (due to throttling or other reasons). This refers to the output process of system logs such as observer.log. |
CLOG
You can query CLOG-related metrics using the following example.
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=256 and stat_id in (80001,80002,80057);
The result is as follows:
+-------+---------+--------------------------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+--------------------------------------+------------+
| 256 | 80001 | palf write io count to disk | ADD_VALUE |
| 256 | 80002 | palf write size to disk | ADD_VALUE |
| 256 | 80057 | clog trans log total size | ADD_VALUE |
+-------+---------+--------------------------------------+------------+
3 rows in set (0.07 sec)
Common CLOG monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 80001 | palf write io count to disk | The number of times a clog is written to disk. This statistic increments when a clog is successfully written to disk. |
| 80002 | palf write size to disk | The size of data written to disk by clogs, in bytes. When a clog is successfully written to disk, the size of the successfully written data is accumulated. |
| 80003 | palf write total time to disk | The time consumed in writing a clog to disk, in microseconds. When a clog is successfully written to disk, the time consumed for this write is accumulated. |
| 80004 | palf read count from hot cache | The number of hits for reading from the hot cache. When reading logs from PALF, it first attempts to read from the hot cache. If the requested log is found in the hot cache, this statistic increments by 1. |
| 80005 | palf read size from hot cache | The size of logs read from the hot cache, in bytes. When reading logs from PALF, it first attempts to retrieve them from the hot cache. If the requested log is found in the hot cache, the size of the log read from the hot cache is accumulated. |
| 80006 | palf read total time from hot cache | The time consumed in reading a clog from the hot cache, in microseconds. When reading logs from PALF, it first attempts to read from the hot cache. If the target log is found in the hot cache, the time spent on this read is accumulated. |
| 80007 | palf read io count from disk | The number of disk I/O operations for reading clogs. This statistic increments when a clog is successfully read from disk. |
| 80008 | palf read size from disk | The size of the clogs read from disk, in bytes. When a clog is successfully read from disk, the size of that clog is added to this value. |
| 80009 | palf read total time from disk | The time consumed by reading clogs from disk, in microseconds. When a clog is successfully read from disk, the time consumed for this read is accumulated. |
| 80010 | palf handle rpc request count | Number of RPCs processed by the PALF layer. PALF increments this statistic by 1 after processing each RPC. |
| 80011 | archive read log size | Total amount of clogs read from log archive, in bytes. This metric periodically measures the total amount of logs read by the log pull module per unit time and then accumulates the data. The statistical period is 10 seconds, and the statistical unit is bytes. |
| 80012 | archive write log size | Total amount of clogs written to the archive medium for log archiving, in bytes. This metric periodically measures the total volume of logs written to the archive medium by the log archiving module per unit time and then accumulates the data. The statistical period is 10 seconds, and the unit is bytes. |
| 80013 | restore read log size | Total amount of clogs read by the log restore module, in bytes. Each time the log restore module triggers reading remote logs, it accumulates the amount of logs read. The unit is bytes. |
| 80014 | restore write log size | Total number of clogs committed by the log restore module, in bytes. Each time the log restore module triggers log commit, it accumulates the volume of logs committed for statistics. The unit is bytes. |
| 80057 | clog trans log total size | The amount of data committed to PALF in bytes. Transactions commit logs to PALF, and this value increases by the size of the committed logs after the log commit is complete. |
| 80058 | log storage compress original size | The cumulative original size of logs before compression submitted by the upper layer to the PALF layer, in bytes. When log compression is enabled, the size of logs is compressed each time the upper layer calls the APPEND interface of the PALF layer to submit logs, and the compressed log size is accumulated. |
| 80059 | log storage compress compressed size | The cumulative size of compressed logs submitted by the upper layer to the PALF layer, in bytes. When log compression is enabled, the size of each log submitted by the upper layer to the PALF layer via the APPEND interface is compressed, and the cumulative size of the compressed logs is tracked. |
Root Service
Common Root Service monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 110015 | success rpc process | The number of RPC requests successfully processed by the Root Service. This metric increases by 1 each time the Root Service successfully processes an RPC request. It includes all RPC requests sent to the Root Service and executed successfully. |
| 110016 | failed rpc process | The number of RPC requests that the Root Service failed to process. This value increments each time the Root Service fails to process an RPC request. It includes all RPC requests sent to the Root Service but failed to execute. |
| 110017 | balancer succ execute count | The number of successful RS unit balancing executions. The counter increments by 1 each time the root service load balancer successfully performs a unit balancing operation. |
| 110018 | balancer failed execute count | The number of failed RS unit load balancing operations. This value increments each time the root service load balancer fails to perform a unit load balancing operation. |
ASH
You can query ASH-related metrics using the following example.
obclient> select * from oceanbase.V$STATNAME where class = 3072;
The result is as follows:
+--------+---------+------------+----------------------------------------+----------------------------------------+-------+
| CON_ID | STAT_ID | STATISTIC# | NAME | DISPLAY_NAME | CLASS |
+--------+---------+------------+----------------------------------------+----------------------------------------+-------+
| 1002 | 200001 | 368 | DB time | DB time | 3072 |
| 1002 | 200002 | 369 | DB CPU | DB CPU | 3072 |
| 1002 | 200005 | 370 | background elapsed time | background elapsed time | 3072 |
| 1002 | 200006 | 371 | background cpu time | background cpu time | 3072 |
| 1002 | 200010 | 372 | non idle wait time | non idle wait time | 3072 |
| 1002 | 200011 | 373 | idle wait time | idle wait time | 3072 |
| 1002 | 200012 | 374 | background database time | background database time | 3072 |
| 1002 | 200013 | 375 | background database non-idle wait time | background database non-idle wait time | 3072 |
| 1002 | 200014 | 376 | background database idle wait time | background database idle wait time | 3072 |
| 1002 | 220001 | 388 | concurrency wait total time | concurrency wait total time | 3072 |
| 1002 | 220002 | 389 | user io wait total time | user io wait total time | 3072 |
| 1002 | 220003 | 390 | application wait total time | application wait total time | 3072 |
+--------+---------+------------+----------------------------------------+----------------------------------------+-------+
12 rows in set (0.184 sec)
Note
Monitoring items without the background prefix do not include the background part. For example, idle wait time records only time related to foreground sessions and does not include content related to background database idle wait time. The statistics for these two monitoring items are mutually exclusive.
Common ASH monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 200001 | DB time | The total time spent by the foreground process on database operations, including the cumulative value of CPU time and non-idle wait time, in microseconds. OceanBase AI Database periodically updates Time Model-related statistics. When a thread is active, it must be in the ON_CPU state or waiting for an event. The Time Model-related statistics are accumulated based on the duration of each state. |
| 200002 | DB CPU | The CPU time consumed by the foreground process, in microseconds. OceanBase AI Database periodically updates time model-related statistics. When a thread is active, it is either in the ON_CPU state or waiting for an event. The time model-related statistics are accumulated based on the duration of each state. |
| 200005 | background elapsed time | The total execution time of the background process, in microseconds. OceanBase AI Database periodically updates time model statistics. When a thread is active, it must be in the ON_CPU state or waiting for an event. The time model statistics are accumulated based on the duration of each state. |
| 200006 | background cpu time | The CPU time consumed by the background process, in microseconds. OceanBase AI Database periodically updates the Time Model-related statistics. When a thread is active, it must be in the ON_CPU state or waiting for an event. The Time Model-related statistics are accumulated based on the duration of each state. |
| 200010 | non idle wait time | The total time that the frontend process is not in an idle wait state, meaning the total time the session is in a waiting state but not in an idle wait state, in microseconds. OceanBase AI Database periodically updates Time Model-related statistics. When a thread is active, it must be in the ON_CPU state or a waiting-for-event state. The Time Model-related statistics are accumulated based on the duration of each state it spends. |
| 200011 | idle wait time | The idle wait time of the foreground process, which is the total time the session spends in an idle-wait state, in microseconds. OceanBase AI Database periodically updates Time Model-related statistics internally. When a thread is active, it must be in the ON_CPU state or waiting for an event. The Time Model-related statistics are accumulated based on the duration of each state it occupies. |
| 200012 | background database time | The total time spent by background processes on database operations, in microseconds. OceanBase AI Database periodically updates Time Model-related statistics. When a thread is active, it is either in the ON_CPU state or waiting for an event. The Time Model-related statistics are accumulated based on the duration of each state. |
| 200013 | background database non-idle wait time | The time spent by a background process on database operations when it is not in the idle or waiting state, in microseconds. OceanBase AI Database periodically updates Time Model-related statistics. When a thread is active, it is either in the ON_CPU state or waiting for an event. The Time Model-related statistics are accumulated based on the duration of each state. |
| 200014 | background database idle wait time | The time spent by a background process in an idle wait state performing database operations, in microseconds. OceanBase AI Database periodically updates Time Model-related statistics. When a thread is active, it is either in the ON_CPU state or waiting for an event. The Time Model-related statistics are accumulated based on the duration of each state. |
| 200015 | diagnostic info object allocated count | Total number of diagnostic objects allocated. Diagnostic objects on OBServer nodes are used to store diagnostic statistics for database foreground and background tasks (including SQL, RPC, etc.). They are typically allocated at the start of a task and reclaimed at the end of the task. |
| 200016 | diagnostic info object allocate failure count | Total number of diagnostic object allocation failures, in quantities. The diagnostic object on an OBServer node is used to store diagnostic statistics for database foreground and background tasks (including SQL and RPC). It is typically allocated at the start of a task and reclaimed at the end of the task. |
| 200017 | diagnostic info object returned count | Total number of reclaimed diagnostic objects. An OBServer node diagnostic object is used to store diagnostic statistics for database foreground and background tasks (including SQL, RPC, etc.). It is typically allocated at the start of a task and reclaimed at the end of the task. |
| 200018 | diagnostic info object return failure count | Total number of failed reclaims of diagnostic objects. Diagnostic objects on OBServer nodes store diagnostic statistics for database foreground and background tasks (including SQL and RPC). They are typically allocated at the start of a task and reclaimed at the end of the task. |
| 220001 | concurrency wait total time | The total wait time caused by wait events of the Concurrency category, such as wait time due to resource locking. The unit is microseconds. It is obtained by accumulating and summarizing the corresponding analog wait events in OceanBase AI Database. |
| 220002 | user io wait total time | The total time spent by a user process waiting for I/O operations (such as reading or writing data to disk) to complete, in microseconds. This metric is derived from the cumulative summary of wait events analogously calculated in OceanBase AI Database. |
| 220003 | application wait total time | The total amount of wait time generated by user application code, such as lock waits caused by row-level locks or explicit lock commands, in microseconds. This value is derived from the cumulative summary of corresponding wait events in OceanBase AI Database. |
| 220004 | schedule wait total time | The total time consumed by wait events of the SCHEDULER category, in microseconds. This value is obtained by accumulating and summarizing the corresponding analog wait events in OceanBase AI Database. |
| 220005 | network wait total time | Total time consumed by network-related wait events, in microseconds. This value is derived from the cumulative summary of corresponding analog wait events in OceanBase AI Database. |
TableAPI
For detailed information about TableAPI monitoring items, see OBKV-Table monitoring metrics in the OBKV documentation.
WR
Common WR monitoring items:
Metric ID |
Metric name |
Description |
|---|---|---|
| 210001 | wr snapshot task elapse time | The total time consumed in collecting WR (Workload Repository) snapshots, in microseconds. WR periodically collects data from internal views of OceanBase AI Database to record the system status and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210002 | wr snapshot task cpu time | Collects the total CPU time consumed by WR (Workload Repository) snapshots, in microseconds. WR periodically collects data from internal OceanBase AI Database views to record system status and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210003 | wr purge task elapse time | WR (Workload Repository) The total time consumed by deleting a WR snapshot task, in microseconds. WR periodically collects data from internal views of OceanBase AI Database to record the system's status, and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210004 | wr purge task cpu time | WR (Workload Repository) The total CPU time consumed by the WR snapshot deletion task, in microseconds. WR periodically collects data from internal views of OceanBase AI Database to record system status, and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210005 | wr schedular elapse time | The total time consumed by WR (Workload Repository) tasks, in microseconds. WR periodically collects data from internal OceanBase AI Database views to record system status, and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210006 | wr schedular cpu time | The total CPU time consumed by a WR (Workload Repository) import task, in microseconds. WR periodically collects data from internal OceanBase AI Database views to record system status, and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210007 | wr user submit snapshot elapse time | The total time consumed in collecting WR (Workload Repository) snapshots, in microseconds. WR periodically collects internal views of OceanBase AI Database to record the system status and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210008 | wr user submit snapshot cpu time | The total CPU time consumed by the user-initiated collection of Workload Repository (WR) snapshots, in microseconds. WR periodically collects data from internal OceanBase AI Database views to record system status and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210009 | wr collected active session history row count | Total number of ASH (ob_active_session_history) rows collected by WR, in rows. WR periodically collects data from internal OceanBase AI Database views to record system status and provides related views for user display and management operations through the DBMS_WORKLOAD_REPOSITORY package. |
| 210010 | ash schedular elapse time | Total time spent by ASH sampling threads, in microseconds. ASH is a diagnostic mechanism based on sampling records from all active sessions within OceanBase AI Database. It uses background threads to sample the runtime status of active sessions every second. |
| 210011 | ash schedular cpu time | The total CPU time consumed by ASH sampling threads, in microseconds. ASH is a diagnostic mechanism based on sampling records from all active sessions within OceanBase AI Database. It uses background threads to sample the runtime status of active sessions every second. |
Other
In addition to the GV$SYSSTAT view, there are other rich views that describe metrics from various perspectives.
For example, GV$OB_PROCESSLIST describes the current sessions of the system, including their active status and the SQL statements being executed. It also includes important fields such as SID, SQL_ID, TRANS_ID, and TRACE_ID, making it a powerful tool for end-to-end tracing.
Query active sessions based on the
GV$OB_PROCESSLISTview:obclient> select TENANT,SVR_IP,SVR_PORT,USER,HOST,COMMAND,STATE,INFO,SQL_ID,TRACE_ID from GV$OB_PROCESSLIST where STATE='ACTIVE' or null limit 10;The result is as follows:
+--------+------------+----------+-----------+-------------------+---------+--------+------+----------------------------------+-----------------------------------+ | TENANT | SVR_IP | SVR_PORT | USER | HOST | COMMAND | STATE | INFO | SQL_ID | TRACE_ID | +--------+------------+----------+-----------+-------------------+---------+--------+------+----------------------------------+-----------------------------------+ | sys |xx.xx.xx.xx | 2882 | DBA_query | xx.xx.xx.xx:51588 | Query | ACTIVE | ... | D18F6AE855AD0D7478162DD8EF48C781 | YB4206008451-0005F55893CEA363-0-0 | +--------+------------+----------+-----------+-------------------+---------+--------+------+----------------------------------+-----------------------------------+ 1 row in set (0.04 sec)Query the number of active sessions at the unit level based on the
GV$OB_PROCESSLISTview:obclient> select tenant_id, tenant_name, svr_ip, case when cnt is null then 0 else cnt end as cnt from ( select DBA_OB_TENANTS.tenant_name, DBA_OB_TENANTS.tenant_id, svr_ip, cnt from DBA_OB_TENANTS left join ( select count(`state`='ACTIVE' OR NULL) as cnt, tenant as tenant_name, svr_ip from GV$OB_PROCESSLIST group by tenant,svr_ip ) t1 on DBA_OB_TENANTS.tenant_name = t1.tenant_name where DBA_OB_TENANTS.tenant_type!='META' ) t2;