SYSSTAT metrics are one of the common diagnostic monitoring information items, which collect key performance values of system running, such as the number of SQL statement executions, execution time, and I/O count.
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 CLASS,STAT_ID,NAME,VALUE_TYPE FROM GV$SYSSTAT WHERE CLASS=8;
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 | 40020 | ps prepare count | ADD_VALUE |
| 8 | 40021 | ps prepare time | ADD_VALUE |
| 8 | 40022 | ps execute count | ADD_VALUE |
| 8 | 40023 | ps close count | ADD_VALUE |
| 8 | 40024 | ps close time | ADD_VALUE |
| 8 | 40025 | sql commit count | ADD_VALUE |
| 8 | 40026 | sql commit time | ADD_VALUE |
| 8 | 40027 | sql rollback count | ADD_VALUE |
| 8 | 40028 | sql rollback time | ADD_VALUE |
| 8 | 40030 | opened cursors current | ADD_VALUE |
| 8 | 40031 | opened cursors cumulative | ADD_VALUE |
| 8 | 40010 | sql local count | ADD_VALUE |
| 8 | 40011 | sql remote count | ADD_VALUE |
| 8 | 40012 | sql distributed count | ADD_VALUE |
| 8 | 40013 | active sessions | ADD_VALUE |
| 8 | 40014 | single query count | ADD_VALUE |
| 8 | 40015 | multiple query count | ADD_VALUE |
| 8 | 40016 | multiple query with one stmt count | ADD_VALUE |
| 8 | 40100 | sql inner select count | ADD_VALUE |
| 8 | 40101 | sql inner select time | ADD_VALUE |
| 8 | 40102 | sql inner insert count | ADD_VALUE |
| 8 | 40103 | sql inner insert time | ADD_VALUE |
| 8 | 40104 | sql inner replace count | ADD_VALUE |
| 8 | 40105 | sql inner replace time | ADD_VALUE |
| 8 | 40106 | sql inner update count | ADD_VALUE |
| 8 | 40107 | sql inner update time | ADD_VALUE |
| 8 | 40108 | sql inner delete count | ADD_VALUE |
| 8 | 40109 | sql inner delete time | ADD_VALUE |
| 8 | 40110 | sql inner other count | ADD_VALUE |
| 8 | 40111 | sql inner other time | ADD_VALUE |
| 8 | 40112 | user logons cumulative | ADD_VALUE |
| 8 | 40113 | user logouts cumulative | ADD_VALUE |
| 8 | 40114 | user logons failed cumulative | ADD_VALUE |
| 8 | 40115 | user logons time cumulative | ADD_VALUE |
| 8 | 40116 | sql local execute time | ADD_VALUE |
| 8 | 40117 | sql remote execute time | ADD_VALUE |
| 8 | 40118 | sql distributed execute time | ADD_VALUE |
| 8 | 40119 | sql fail count | ADD_VALUE |
| 8 | 40120 | inner sql local count | ADD_VALUE |
| 8 | 40121 | inner sql remote count | ADD_VALUE |
| 8 | 40122 | inner sql distributed count | ADD_VALUE |
+-------+---------+------------------------------------+------------+
42 rows in set (0.07 sec)
Metric categories
The metrics supported by OceanBase Database V4.x are divided into 15 categories, as shown below:
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. For the full list of metrics, see Metrics overview.
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)
Parameter description:
NAME |
Description |
|---|---|
| rpc packet in | The cumulative number of RPC packets received by the tenant. When a tenant receives an RPC request or receives a response after sending an RPC request, the count increases by 1. |
| rpc packet in bytes | The cumulative number of bytes of RPC packets received by the tenant, in bytes. When a tenant receives an RPC request or receives a response after sending an RPC request, the byte size of the RPC request or response is added. |
| rpc packet out | The cumulative number of RPC packets sent by the tenant. When a tenant sends an RPC request or sends a response after receiving an RPC request, the count increases by 1. |
| rpc packet out bytes | The cumulative number of bytes of RPC packets sent by the tenant, in bytes. When a tenant sends an RPC request or sends a response after receiving an RPC request, the byte size of the RPC request or response is added. |
| rpc deliver fail | The total number of failures to forward RPC requests to the tenant work queue. When RPC IO fails to forward an RPC request to the tenant queue, the count increases by 1. |
| rpc net delay | The cumulative network transmission delay of RPC requests received by the tenant, in microseconds. When the RPC IO thread parses an RPC request, it obtains the current timestamp and subtracts the sending timestamp from the packet header to get the difference, which is added to RPC_NET_DELAY. |
| rpc net frame delay | The cumulative network framework processing time of RPC requests received by the tenant, in microseconds. When RPC IO forwards an RPC request to the tenant queue, it obtains the current timestamp and subtracts the timestamp recorded when parsing the RPC packet to get the difference, which is added to RPC_NET_FRAME_DELAY. |
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)
Parameter description:
NAME |
Description |
|---|---|
| request enqueue count | The total number of times a tenant request enters the queue. Each time a request is placed in the worker thread queue, the count increases by 1. |
| request dequeue count | The total number of times a tenant request leaves the queue. Each time a request leaves the worker thread queue, the count increases by 1. |
| request queue time | The total time that all tenant requests wait in the queue, in microseconds. When a tenant worker thread gets a request, it subtracts the queue entry time from the current time to get the request queue wait time, which is added to 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 |
| 4 | 30200 | xa start total count | ADD_VALUE |
| 4 | 30201 | xa start total used time | ADD_VALUE |
| 4 | 30202 | xa start with rpc total count | ADD_VALUE |
| 4 | 30203 | failed xa start total count | ADD_VALUE |
| 4 | 30204 | xa end total count | ADD_VALUE |
| 4 | 30205 | xa end total used count | ADD_VALUE |
| 4 | 30206 | xa end with rpc total count | ADD_VALUE |
| 4 | 30207 | failed xa end total count | ADD_VALUE |
| 4 | 30208 | xa prepare total count | ADD_VALUE |
| 4 | 30209 | xa prepare total used time | ADD_VALUE |
| 4 | 30210 | xa prepare with rpc total count | ADD_VALUE |
| 4 | 30211 | failed xa prepare total count | ADD_VALUE |
| 4 | 30212 | xa commit total count | ADD_VALUE |
| 4 | 30213 | xa commit total used time | ADD_VALUE |
| 4 | 30214 | xa commit with rpc total count | ADD_VALUE |
| 4 | 30215 | failed xa commit total count | ADD_VALUE |
| 4 | 30216 | xa rollback total count | ADD_VALUE |
| 4 | 30217 | xa rollback total used time | ADD_VALUE |
| 4 | 30218 | xa rollback with rpc total count | ADD_VALUE |
| 4 | 30219 | failed xa rollback total count | ADD_VALUE |
+-------+---------+----------------------------------+------------+
32 rows in set (0.013 sec)
Parameter description:
Parameter |
Description |
|---|---|
| trans commit log sync time | The cumulative time consumed from the successful submission of the transaction clog to the majority success on the OBServer node of the tenant, in microseconds. When the clog majority is successful and the transaction layer is called back, the time consumed by the majority is calculated by subtracting the clog submission time from the current time, and added to this metric. |
| trans commit log sync count | The number of clogs that are successfully submitted and have achieved majority on the OBServer node of the tenant. Each time the clog majority is successful, the transaction layer is called back, and the count increases when the transaction layer receives the callback. |
| trans commit log submit count | The number of clogs submitted on the OBServer node of the tenant. Each time a clog is successfully submitted (regardless of majority success), the count increases by 1. |
| trans start count | The number of transactions started on the OBServer node of the tenant. Each time a transaction is successfully started on the OBServer node that creates the session, the count of this metric increases. |
| trans total used time | The cumulative time consumed by transactions on the OBServer node of the tenant, in microseconds. After each transaction ends on the OBServer node that creates the session, the transaction time consumed is added to this metric. |
| trans commit count | The number of successfully committed transactions on the OBServer node of the tenant. Each time a transaction is successfully committed on the OBServer node connected to the session, the count increases by 1. |
| trans commit time | The cumulative time consumed by transaction commits on the OBServer node of the tenant, in microseconds. After each successful commit on the OBServer node that creates the session, the commit time consumed is added to this metric. |
| trans rollback count | The number of successfully rolled back transactions on the OBServer node of the tenant. Each time a transaction is successfully rolled back on the OBServer node that creates the session, the count of this metric increases. |
| trans rollback time | The time consumed by transaction rollbacks on the OBServer node of the tenant, in microseconds. After each successful rollback on the OBServer node that creates the session, the rollback time consumed is added to this metric. |
| trans timeout count | The number of timed-out transactions on the OBServer node of the tenant. Each time a transaction times out on the OBServer node that creates the session, the count of this metric increases. |
| trans local trans count | The number of standalone transactions ended on the OBServer node of the tenant (including both Commit and Rollback ending methods). After each transaction ends on the OBServer node that creates the session, the number of participants is checked. If the number of participants is 1, the count increases. |
| trans distribute trans count | The number of distributed transactions ended on the OBServer node of the tenant (including both Commit and Rollback ending methods). After each transaction ends on the OBServer node that creates the session, the number of participants is checked. If the number of participants is greater than 1, the count increases. |
| xa start total count | For XA transactions, the total number of XA_START statements, including successful and failed ones. After the transaction side executes the XA_START logic, the count increases by 1. |
| xa start total used time | For XA transactions, the total time consumed by XA_START statement execution, including successful and failed ones, in microseconds. After the transaction side executes the XA_START logic, the local processing time consumed is added. |
| xa start with rpc total count | For XA transactions, the total number of remote executions of XA_START statements, only including successful ones. After the transaction side executes the XA_START logic, if the XA_START is a remote execution and succeeds, the count increases by 1. |
| failed xa start total count | For XA transactions, the total number of failed XA_START statement executions. After the transaction side executes the XA_START logic, if the execution fails, the count increases by 1. |
| xa end total count | For XA transactions, the total number of XA_END statement executions, including successful and failed ones. After the transaction side executes the XA_END logic, the count increases by 1. |
| xa end total used count | For XA transactions, the total time consumed by XA_END statement execution, in microseconds. After the transaction side executes the XA_END logic, the local processing time consumed is added. |
| xa end with rpc total count | For XA transactions, the total number of remote executions of XA_END statements, only including successful ones. After the transaction side executes the XA_END logic, if it is a remote execution and succeeds, the count increases by 1. |
| failed xa end total count | For XA transactions, the total number of failed XA_END statement executions. When the transaction side processes XA_END, if an error occurs during execution, the count increases by 1. |
| xa prepare total count | For XA transactions, the total number of XA_PREPARE statements, including successful and failed ones. After the transaction side executes the XA_PREPARE logic, the count increases by 1. |
| xa prepare total used time | For XA transactions, the total time consumed by XA_PREPARE statement execution, including successful and failed ones, in microseconds. After the transaction side executes the XA_PREPARE logic, the local processing time consumed is added. |
| xa prepare with rpc total count | For XA transactions, the total number of remote executions of XA_PREPARE statements, only including successful ones. After the transaction side executes the XA_START logic, if the XA_PREPARE is a remote execution and succeeds, the count increases by 1. |
| failed xa prepare total count | For XA transactions, the total number of failed XA_PREPARE statement executions. After the transaction side executes the XA_PREPARE logic, if the execution fails, the count increases by 1. |
| xa commit total count | For XA transactions, the total number of XA_COMMIT statement executions, including successful and failed ones. After the transaction side executes the XA_COMMIT logic, the count increases by 1. |
| xa commit total used time | For XA transactions, the total time consumed by XA_COMMIT statement execution, in microseconds. After the transaction side executes the XA_COMMIT logic, the local processing time consumed is added. |
| xa commit with rpc total count | For XA transactions, the total number of remote executions of XA_COMMIT statements, only including successful ones. After the transaction side executes the XA_COMMIT logic, if it is a remote execution and succeeds, the count increases by 1. |
| failed xa commit total count | For XA transactions, the total number of failed XA_COMMIT statement executions. When the transaction side processes XA_COMMIT, if an error occurs during execution, the count increases by 1. |
| xa rollback total count | For XA transactions, the total number of XA_ROLLBACK statements, including successful and failed ones. After the transaction side executes the XA_ROLLBACK logic, the count increases by 1. |
| xa rollback total used time | For XA transactions, the total time consumed by XA_ROLLBACK statement execution, including successful and failed ones, in microseconds. After the transaction side executes the XA_ROLLBACK logic, the local processing time consumed is added. |
| xa rollback with rpc total count | For XA transactions, the total number of remote executions of XA_ROLLBACK statements, only including successful ones. After the transaction side executes the XA_START logic, if the XA_ROLLBACK is a remote execution and succeeds, the count increases by 1. |
| failed xa rollback total count | For XA transactions, the total number of failed XA_ROLLBACK statement executions. After the transaction side executes the XA_ROLLBACK logic, if the execution fails, the count increases by 1. |
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)
Parameter description:
Parameter |
Description |
|---|---|
| sql select count | The number of SQL SELECT executions. |
| sql select time | The total time spent on SQL SELECT execution, in microseconds. |
| sql insert count | The total number of INSERT statements submitted by users. |
| sql insert time | The total time spent on INSERT statements submitted by users. |
| sql replace count | The total number of REPLACE statements submitted by users. |
| sql replace time | The total time spent on REPLACE statements submitted by users. |
| sql update count | The total number of UPDATE statements submitted by users. |
| sql update time | The total time spent on UPDATE statements submitted by users, in microseconds. |
| sql delete count | The total number of DELETE statements submitted by users. |
| sql delete time | The total time spent on DELETE statements submitted by users, in microseconds. |
| sql other count | The number of SQL executions other than SELECT/INSERT/REPLACE/UPDATE/DELETE/COMMIT/ROLLBACK. |
| sql other time | The total time spent on SQL executions other than SELECT/INSERT/REPLACE/UPDATE/DELETE/COMMIT/ROLLBACK, in microseconds. |
| sql local count | The number of SQL LOCAL executions. |
| sql remote count | The number of SQL REMOTE executions. |
| sql distributed count | The number of distributed SQL statement executions. |
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)
Parameter description:
Parameter |
Description |
|---|---|
| row cache hit | The number of times the cache is hit when the table get operator prefetches data rows. In the lookup_in_cache function, it determines whether the data row is in the cache. If the cache is hit, this item increases by 1. |
| row cache miss | The number of times the cache is not hit when the table get operator prefetches data rows. In the lookup_in_cache function, it determines whether the data row is in the cache. If the cache is not hit, this item increases by 1. |
| bloom filter cache hit | The number of macroblock Bloom filter cache hits, indicating that the SQL statement uses the already-built macroblock Bloom filter. |
| bloom filter cache miss | The number of macroblock Bloom filter cache misses, indicating that the corresponding macroblock Bloom filter has not been built. |
| block cache hit | The number of times the block cache is hit when fetching microblocks. When fetching microblocks, it determines whether the microblock is in the cache. If the cache is hit, this item increases by 1. |
| block cache miss | The number of times the block cache is not hit when fetching microblocks. When fetching microblocks, it determines whether the microblock is in the cache. If the cache is not hit, this item increases by 1. |
| location cache hit | The number of location cache hits. The number of hits for ls_location_cache and tablet_ls_cache. When querying the mapping relationship from tablet to logstream or logstream location information, if valid data is successfully obtained from the local cache, the statistical value increases by 1. Triggered in the get methods of ObTabletLSService and ObLSLocationService. |
| location cache miss | The number of location cache misses. The number of misses for ls_location_cache and tablet_ls_cache. When querying the mapping relationship from tablet to logstream or logstream location information, if valid data cannot be obtained from the local cache (cache does not exist, has expired, or is invalid), the statistical value increases by 1. In the get methods of ObTabletLSService and ObLSLocationService, when is_cache_hit is false, the metric increases by 1. |
| tablet ls cache hit | The number of times the cache is hit when reading a tablet. In the get_from_cache_ function, it determines whether the tablet is in the cache. If the cache is hit, this item increases by 1. |
| tablet ls cache miss | The number of times the cache is not hit when reading a tablet. In the get_from_cache function, it determines whether the tablet is in the cache. If the cache is not hit, this item increases by 1. |
| tablet ls cache size | The cache size of the tablet, in bytes. In the get_from_cache function, the tablet's cache is used. |
| user row cache size | The cache size used when the table get operator prefetches data rows, in bytes. In the lookup_in_cache function, this cache is used. |
| bloom filter cache size | The total size of the macroblock Bloom filter cache, in bytes. User INSERT statements need to check for primary key conflicts. When the data being checked is on disk, a common performance optimization method to avoid sending I/O is to build a Bloom filter for the relevant data range. The metrics here are related to the Bloom filter cache. |
The GV$SYSSTAT view describes the hit rate of KVCache. To further explore the actual memory usage of each KVCache module, 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;
The result is as follows:
+-----------+----------------+------------+----------+-----------------------+------------+
| tenant_id | tenant_name | svr_ip | svr_port | cache_name | cache_size |
+-----------+----------------+------------+----------+-----------------------+------------+
| 1 | sys |xx.xx.xx.xx | 2882 | schema_cache | 14564352 |
| 1 | sys |xx.xx.xx.xx | 2882 | tablet_table_cache | 6241280 |
| 1 | sys |xx.xx.xx.xx | 2882 | vtable_cache | 35372032 |
| 1 | sys |xx.xx.xx.xx | 2882 | opt_column_stat_cache | 6241280 |
| 1008 | mq_t1 |xx.xx.xx.xx | 2882 | index_block_cache | 6241280 |
| 1008 | mq_t1 |xx.xx.xx.xx | 2882 | user_block_cache | 10402816 |
| 1008 | mq_t1 |xx.xx.xx.xx | 2882 | user_row_cache | 6241280 |
| 1010 | oracle_tenant1 |xx.xx.xx.xx | 2882 | index_block_cache | 6241280 |
| 1008 | mq_t1 |xx.xx.xx.xx | 2882 | bf_cache | 4160512 |
| 1010 | oracle_tenant1 |xx.xx.xx.xx | 2882 | user_block_cache | 8322048 |
+-----------+----------------+------------+----------+-----------------------+------------+
10 rows in set (0.06 sec)
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)
Parameter description:
Parameter |
Description |
|---|---|
| io read count | The total IOPS of read I/O. After each read I/O is executed, the IO_READ_COUNT metric increases by 1. |
| io read delay | The total latency of read I/O, in microseconds. The IO_READ_DELAY metric accumulates the hardware service latency of each read I/O operation. The latency is timed from when the request is dispatched to the disk until the disk completes processing and returns. |
| io read bytes | The total bandwidth of read I/O. This counts the total amount of read I/O, in bytes. The average read I/O per second can be calculated based on the change over a period of time. After each read I/O is executed, its read bandwidth is counted and added to the IO_READ_BYTES metric. |
| io write count | The total IOPS of write I/O. After each write I/O is executed, IO_WRITE_COUNT increases by 1. |
| io write delay | The total latency of write I/O, in microseconds. The IO_READ_DELAY metric accumulates the hardware service latency of each write I/O operation. The latency is timed from when the request is dispatched to the disk until the disk completes processing and returns. |
| io write bytes | The total bandwidth of write I/O. This counts the total amount of write I/O, in bytes. The average can be calculated based on the change over a period of time. After each write I/O is executed, its write bandwidth is counted and added to the IO_WRITE_BYTES metric. |
| memstore read lock succ count | The number of rows successfully read from MemTable. After a row is successfully read from MemTable, this value increases by 1. |
| memstore read lock fail count | The number of rows that failed to be read from MemTable due to read lock conflict timeout. During MemTable reading, if the data being read is in the committing state but the commit status and version number are undetermined (for example, in the pending commit log or distributed transaction pending state, which is very short), it waits on the read lock. If this conflict causes a timeout and failure, this value increases by 1. |
| memstore write lock succ count | The number of rows successfully written to MemTable. During MemTable writing, lock conflicts, lost updates, and primary key conflicts are checked. If there are no such conflicts, this value increases by 1.
NoteSuccessful verification on MemTable may still fail on SSTable. |
| memstore write lock fail count | The number of rows that failed to be written to MemTable due to conflicts. During MemTable writing, lock conflicts, lost updates, and primary key conflicts are checked. If there are such conflicts, this value increases by 1.
NoteVerification on SSTable is not included. |
| memstore wait write lock time | The waiting time after being awakened from a MemTable write lock conflict, in microseconds. During MemTable writing, if a lock conflict occurs, the request is placed in the lock conflict queue. This value adds the time from being placed in the lock conflict queue to being awakened. |
| memstore wait read lock time | The waiting time for a MemTable read lock conflict, in microseconds. During MemTable reading, if the data being read is in the committing state but the commit status and version number are undetermined, it waits on the read lock. This value adds the waiting time. |
| active memstore used | The size of the active memstore on the node, in bytes. Node replay or writing uses memstore. When memstore usage reaches a certain level, freezing is triggered. All unfrozen memstores are called active memstores. |
| total memstore used | The total amount of memory used by the tenant memstore on the node, in bytes. This memory usage includes both active and frozen memstores, that is, all memstore memory usage of the tenant on the node. |
| major freeze trigger | The memstore size that triggers freezing, in bytes. In the get_tenant_memstore_cond function, it is obtained by adding memstore_freeze_trigger and max_cached_memstore_size. |
| memstore limit | The upper limit of memory that the tenant memstore can use on the node, in bytes. The user specifies the upper limit of the user tenant memory. The maximum memstore usage is configured through memstore_limit_percentage and _memstore_limit_percentage. Among them, memstore_limit_percentage is cluster-level, and _memstore_limit_percentage is tenant-level. _memstore_limit_percentage has higher priority than memstore_limit_percentage. When neither is set, the memstore available ratio is 40% for tenants smaller than 8 GB and 50% for tenants larger than 8 GB. |
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)
Parameter description:
Parameter |
Description |
|---|---|
| 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. |
| 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. |
| 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. |
| 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. |
| 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. |
Based on the above metrics, you can easily obtain the CPU usage and memory usage of the tenant:
Tenant CPU usage = 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)
Parameter description:
Parameter |
Description |
|---|---|
| oblogger log bytes | The cumulative total size of printed logs, in bytes. Refers to the output printing process of system logs such as observer.log. |
| election log bytes | The cumulative total size of printed election logs, in bytes. Refers to the cumulative printing size of election.log. |
| oblogger total log count | The cumulative number of times logs are printed. Refers to the output printing process of system logs such as observer.log. |
| async error log dropped count | The number of times ERROR log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async warn log dropped count | The number of times WARN log printing fails. Refers to the output printing process of system logs such as observer.log. |
| async info log dropped count | The number of times INFO log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async trace log dropped count | The number of times TRACE log printing fails. Refers to the output printing process of system logs such as observer.log. |
| async debug log dropped count | The number of times DEBUG log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async log flush speed | The average number of log entries written per second. Refers to the output printing process of system logs such as observer.log. |
| async generic log write count | The number of times default log printing succeeds. Refers to the output printing process of system logs such as observer.log. |
| async user request log write count | Deprecated, retained only in code. Refers to the output printing process of system logs such as observer.log. |
| async data maintain log write count | The number of times dump and merge log printing succeeds. Refers to the output printing process of system logs such as observer.log. |
| async root service log write count | The number of times RS log printing succeeds. Refers to the output printing process of system logs such as observer.log. |
| async schema log write count | The number of times Schema log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async force allow log write count | The number of times forced log printing succeeds. Refers to the output printing process of system logs such as observer.log. |
| async generic log dropped count | The number of times default log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async user request log dropped count | Deprecated, retained only in code. Refers to the output printing process of system logs such as observer.log. |
| async data maintain log dropped count | The number of times dump and merge log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async root service log dropped count | The number of times RS log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async schema log dropped count | The number of times Schema log printing fails (due to throttling or other reasons). Refers to the output printing process of system logs such as observer.log. |
| async force allow log dropped count | The number of times forced log printing fails (due to throttling or other reasons). Refers to the output printing 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)
Parameter description:
Parameter |
Description |
|---|---|
| palf write io count to disk | The number of clog disk writes. When a clog is successfully written to disk, the count of this metric increases by 1. |
| palf write size to disk | The size of clog disk writes, in bytes. When a clog is successfully written to disk, the size of this successful disk write is added. |
| clog trans log total size | The amount of data committed by transactions to PALF, in bytes. Transactions commit logs to PALF. After the log commit is complete, this value increases by the log size. |
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)
Parameter description:
Notice
The metrics that do not include the background item do not include the background components. For example, idle wait time only records the time related to foreground sessions and does not include the content related to background database idle wait time. The statistical values of these two metrics are mutually exclusive.
Parameter |
Description |
|---|---|
| DB time | The total time that foreground processes spend executing database operations, including the cumulative value of CPU and non-idle wait time, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| DB CPU | The CPU time consumed by foreground processes, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| background elapsed time | The total time spent on background process execution, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| background cpu time | The CPU time consumed by background processes, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| non idle wait time | The non-idle wait time of foreground processes, that is, the total time a session is in the waiting state but not idle wait, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| idle wait time | The idle wait time of foreground processes, that is, the total time a session is in the idle wait state, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| background database time | The total time spent by background processes executing database operations, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| background database non-idle wait time | The time spent by background processes executing database operations in the non-idle wait state, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| background database idle wait time | The time spent by background processes executing database operations in the idle wait state, in microseconds. OceanBase Database periodically updates Time Model-related metrics. When a thread is active, it must be in the ON_CPU state or waiting event state. The Time Model-related metrics are accumulated based on the duration of each state. |
| concurrency wait total time | The total waiting time caused by concurrency category wait events, such as the time spent waiting due to resource locking, in microseconds. It is accumulated and summarized by the corresponding wait events of OceanBase Database. |
| user io wait total time | The total time that user processes wait for I/O operations (such as reading data from or writing data to disk) to complete, in microseconds. It is accumulated and summarized by the corresponding wait events of OceanBase Database. |
| application wait total time | The total waiting time generated by user application code (for example, lock waits caused by row-level locking or explicit lock commands), in microseconds. It is accumulated and summarized by the corresponding wait events of OceanBase Database. |
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 | 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 | 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;The result is as follows:
+-----------+-----------------+-------------+------+ | tenant_id | tenant_name | svr_ip | cnt | +-----------+-----------------+-------------+------+ | 1 | sys | xx.xx.xx.xx | 0 | | 1 | sys | xx.xx.xx.xx | 0 | | 1 | sys | xx.xx.xx.xx | 0 | | 1002 | midas0_3790 | xx.xx.xx.xx | 0 | | 1002 | midas0_3790 | xx.xx.xx.xx | 0 | | 1002 | midas0_3790 | xx.xx.xx.xx | 0 | +-----------+-----------------+-------------+------+ 6 rows in set (0.05 sec)