Metrics provide general diagnostic monitoring information for databases. They represent specific values collected during event tracking, such as the number of SQL statement executions, execution time, and I/O throughput. OceanBase Database supports more than 600 metrics, which are categorized into 14 classes to provide all the necessary information for key execution details such as network information, request queues, transactions, SQL statements, cache, storage, resource quotas, and logs. Some metrics, including QPS, TPS, QPS_RT, TPS_RT, and Active_sessions, provide real-time information and are collected every second in OceanBase Database. Metrics for other information are collected by minute.
The data source of the metrics is the GV$SYSSTAT view, which contains the following key fields:
CON_ID: the ID of the tenant.SVR_IP: the IP address of the node.CLASS: the class of the metric.STAT_ID: the ID of the metric.NAME: the name of the metric.VALUE: the value of the metric.VALUE_TYPE: the value type of the metric.
The CLASS field indicates the class of a metric. The following table describes the 14 classes supported by OceanBase Database V4.x.
| Class code | Class name |
|---|---|
| 1 | Network |
| 2 | Request queue |
| 4 | Transaction |
| 8 | SQL |
| 16 | Cache |
| 32 | Storage |
| 64 | Resource |
| 128 | Log |
| 256 | Clog |
| 512 | Election |
| 1024 | OBServer |
| 2048 | RootService |
| 3072 | Analyze Report |
| 4096 | TableAPI |
The STAT_ID and NAME fields describe the ID and name of a metric, respectively. For example, the following table displays the information about all metrics in the SQL class:
obclient> select distinct 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 | 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 |
+-------+---------+------------------------------------+------------+
42 rows in set (0.07 sec)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| sql select count | The number of times that the SELECT statement is executed. |
| sql select time | The total time spent in executing the SELECT statement. |
| sql insert count | The number of INSERT statement executions. |
| sql insert time | The total time spent in executing the INSERT statement. |
| sql replace count | The number of times that the REPLACE statement is executed. |
| sql replace time | The total time spent in executing the REPLACE statement. |
| sql update count | The number of UPDATE statement executions. |
| sql update time | The total time spent in executing the UPDATE statement. |
| sql delete count | The number of times that the DELETE statement is executed. |
| sql delete time | The total time spent in executing the DELETE statement. |
| sql other count | The number of times that other statements are executed. |
| sql other time | The total time spent on executing other statements. |
| ps prepare count | The number of PS preparations. |
| ps prepare time | The time spent on PS preparation. |
| ps execute count | The number of PS executions. |
| ps close count | The number of PS closes. |
| ps close time | The time spent on closing PS. |
| opened cursors current | The number of currently opened cursors, that is, the number of cursors that OceanBase Cloud Platform (OCP) uses as the session cache. |
| opened cursors cumulative | The cumulative number of opened cursors. |
| sql local count | The number of times that the LOCAL statement is executed. |
| sql remote count | The number of times that the REMOTE statement is executed. |
| sql distributed count | The number of times that the DISTRIBUTED statement is executed. |
| active sessions | The number of active connections. |
| single query count | The number of single queries. |
| multiple query count | The total number of statements sent over the Multiple Query protocol. |
| sql inner select count | The number of times that inner SQL SELECT statements are executed. |
| sql inner select time | The total time spent on executing inner SQL SELECT statements. |
| sql inner insert count | The number of times that inner SQL INSERT statements are executed. |
| sql inner insert time | The total time spent on executing inner SQL INSERT statements. |
| sql inner replace count | The number of times that inner SQL REPLACE statements are executed. |
| sql inner replace time | The total time spent on executing inner SQL REPLACE statements. |
| sql inner update count | The number of times that inner SQL UPDATE statements are executed. |
| sql inner update time | The total time spent on executing inner SQL UPDATE statements. |
| sql inner delete count | The number of times that inner SQL DELETE statements are executed. |
| sql inner delete time | The total time spent on executing inner SQL DELETE statements. |
| sql inner other count | The number of times that inner SQL OTHER statements are executed. |
| sql inner other time | The total time spent on executing inner SQL OTHER statements. |
| user logons cumulative | The cumulative number of user logons. |
| user logouts cumulative | The cumulative number of user logouts. |
| user logons failed cumulative | The cumulative number of user login failures. |
| user logons time cumulative | The total time spent on user logons. |
Some metrics, such as QPS, TPS, QPS_RT, and TPS_RT, provide real-time information and are collected by second.
The CON_ID and SVR_IP fields describe the monitored objects of a metric, so that metrics can be flexibly aggregated in different scenarios. For example, you can aggregate the QPS and TPS metrics based on the CON_ID field to observe the changes in the number of requests of a tenant, or the SVR_IP field to observe the changes in the number of requests of a node. When you perform load balancing on a cluster, you can aggregate the QPS and TPS metrics for resource units based on both the CON_ID and SVR_IP fields.
The VALUE_TYPE field describes the value type of the metric. Valid values include ADD_VALUE and SET_VALUE, which indicates the cumulative type and the status type, respectively. The values of cumulative metrics, such as SQL COUNT, SQL RT, and IO READ BYTES, keep increasing during event tracking in OceanBase Database. External monitoring systems regularly pull the cumulative metric values and calculate the differences to show the data change trend of these metrics. The latest values of status metrics, such as CACHE SIZE and CPU USAGE, are maintained during event tracking in OceanBase Database. External monitoring systems regularly pull and directly display the status metric values. In general, status metrics support aggregation only at the tenant level, while most cumulative metrics support aggregation both at the session and tenant levels.
Metrics of OceanBase Database are classified into 14 classes based on the components that they describe. The following sections describe the most frequently used metrics.
Network metrics
The following example shows how to query the network metrics:
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)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| rpc packet in | The cumulative number of RPC packets sent to the current OBServer node. |
| rpc packet in bytes | The cumulative bytes of RPC packets sent to the current OBServer node. |
| rpc packet out | The cumulative number of RPC packets sent by the current OBServer node. |
| rpc packet out bytes | The cumulative bytes of RPC packets sent by the current OBServer node. |
| rpc deliver fail | The cumulative number of RPC distribution failures. |
| rpc net delay | The delay of the network during RPC distribution, that is, the period between receiving time and the sending time. |
| rpc net frame delay | The latency of the RPC framework during RPC distribution. |
Request queue metrics
The following example shows how to query the request queue metrics:
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)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| request enqueue count | The cumulative number of requests that successfully enter the tenant waiting queue. |
| request dequeue count | The cumulative number of requests that successfully pop out of the tenant waiting queue. |
| request queue time | The cumulative time that requests take from being received to being processed and queued, that is, the time requests wait to end minus the time in the queue. |
Transaction metrics
The following example shows how to query the transaction metrics:
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)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| trans commit log sync time | The total duration for synchronizing the majority of commit logs (clogs). |
| trans commit log sync count | The total number of clogs. |
| trans commit log submit count | The number of transaction logs committed in the tenant. |
| trans start count | The number of transactions started in the tenant. |
| trans total used time | The total time spent on transactions in the tenant. |
| trans commit count | The number of times that the transaction was committed. |
| trans commit time | The time consumed in the transaction commit phase. |
| trans rollback count | The number of transaction rollbacks. |
| trans rollback time | The time consumed in the transaction rollback phase. |
| trans timeout count | The number of timeout transactions. |
| trans local trans count | The number of local transactions. |
| trans distribute trans count | The number of distributed transactions. |
| xa start total count | The total number of xa_start statements, including those that are successfully executed and those that failed to be executed. |
| xa start total used time | The total time spent on executing xa_start statements, including successful executions and failed executions. |
| xa start with rpc total count | The number of remote xa_start statements that are successfully executed. |
| failed xa start total count | The number of xa_start statements that failed to be executed. |
| xa end total count | The total number of xa_end statements, including those that are successfully executed and those that failed to be executed. |
| xa end total used count | The total time spent on executing xa_end statements, including successful executions and failed executions. |
| xa end with rpc total count | The number of remote xa_end statements that are successfully executed. |
| failed xa end total count | The number of xa_end statements that failed to be executed. |
| xa prepare total count | The total number of xa_prepare statements, including those that are successfully executed and those that failed to be executed. |
| xa prepare total used time | The total time spent on executing xa_prepare statements, including successful executions and failed executions. |
| xa prepare with rpc total count | The number of successful xa_prepare statements. |
| failed xa prepare total count | The number of xa_prepare statements that failed to be executed. |
| xa commit total count | The total number of xa_commit statements, including those that are successfully executed and those that failed to be executed. |
| xa commit total used time | The total time spent on executing xa_commit statements, including successful executions and failed executions. |
| xa commit with rpc total count | The number of remote xa_commit statements that are successfully executed. |
| failed xa commit total count | The number of xa_commit statements that failed to be executed. |
| xa rollback total count | The total number of xa_rollback statements, including those that are successfully executed and those that failed to be executed. |
| xa rollback total used time | The total time spent on executing xa_rollback statements, including successful executions and failed executions. |
| xa rollback with rpc total count | The number of remote xa_rollback statements that are successfully executed. |
| failed xa rollback total count | The number of xa_rollback statements that failed to be executed. |
SQL metrics
The following example shows how to query the SQL metrics:
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)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| sql select count | The number of times that the SELECT statement is executed. |
| sql select time | The total time spent on executing the SELECT statement. |
| sql insert count | The number of INSERT statement executions. |
| sql insert time | The total time spent on executing the INSERT statement. |
| sql replace count | The number of times that the REPLACE statement is executed. |
| sql replace time | The total time spent on executing the REPLACE statement. |
| sql update count | The number of UPDATE statement executions. |
| sql update time | The total time spent on executing the UPDATE statement. |
| sql delete count | The number of times that the DELETE statement is executed. |
| sql delete time | The total time spent on executing the DELETE statement. |
| sql other count | The number of times that other statements are executed. |
| sql other time | The total time spent on executing other statements. |
| sql local count | The number of times that the LOCAL statement is executed. |
| sql remote count | The number of times that the REMOTE statement is executed. |
| sql distributed count | The number of times that the DISTRIBUTED statement is executed. |
KVCache metrics
The following example shows how to query the KVCache metrics:
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,120000,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 | 120000 | location cache size | SET_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 following table describes the parameters.
| Parameter | Description |
|---|---|
| row cache hit | The number of row cache hits. |
| row cache miss | The number of row cache misses. |
| bloom filter cache hit | The number of Bloom filter cache hits. |
| bloom filter cache miss | The number of Bloom filter cache misses. |
| block cache hit | The number of block cache hits. |
| block cache miss | The number of block cache misses. |
| location cache hit | The number of location cache hits. |
| location cache miss | The number of location cache misses. |
| tablet ls cache hit | The cache hit. |
| tablet ls cache miss | The cache miss. |
| location cache size | The location cache size. |
| tablet ls cache size | The clog cache size. |
| user row cache size | The size of the user row cache. |
| bloom filter cache size | The size of the Bloom filter cache. |
The GV$SYSSTAT view describes the hit rate of KVCache. To learn about the actual memory usage of each module of KVCache, query the GV$OB_KVCACHE view. Here is an 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 metrics
The following example shows how to query the storage metrics:
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)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| io read count | The number of I/O reads. |
| io read delay | The I/O read latency. |
| io read bytes | The number of read bytes for I/O. |
| io write count | The number of I/O writes. |
| io write delay | The I/O write latency. |
| io write bytes | The number of I/O write bytes. |
| memstore read lock succ count | The number of successful snapshot read checks performed on rows. |
| memstore read lock fail count | The number of failed snapshot read checks performed on rows. |
| memstore write lock succ count | The number of row locks successfully added to the MemStore. |
| memstore write lock fail count | The number of row locks failed to be added to the MemStore. |
| memstore wait write lock time | The time spent on waiting for row locks. |
| memstore wait read lock time | The time spent on waiting for snapshot reads. |
| active memstore used | The active MemStore used, which may be larger than the actual value. |
| total memstore used | The total MemStore used. |
| major freeze trigger | The threshold for triggering a major freeze. |
| memstore limit | The maximum memory that the MemStore can use. |
Resource metrics
The following example shows how to query the resource metrics:
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)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| max memory size | The maximum memory for a tenant. |
| memory usage | The memory currently used by the tenant. |
| min cpus | min_cpu *100 in UNIT_CONFIG for the tenant. |
| max cpus | max_cpu *100 in UNIT_CONFIG for the tenant. |
| cpu usage | The logical CPU usage. For example, the value 148 indicates 148%, indicating that the tenant uses multiple logical CPU cores. |
The metrics described in the preceding sample code can be used to calculate the CPU utilization and memory usage of the tenant based on the following formulas:
Tenant CPU utilization = cpu usage (stat_id = 140006)/max cpus (stat_id = 140005)
Tenant memory usage = memory usage (stat_id = 140003)/max memory size (stat_id = 140002)
Clog metrics
The following example shows how to query the clog metrics:
obclient> select distinct class,stat_id,name,VALUE_TYPE from gv$sysstat where class=256 and stat_id in (80040,80041,80057);
The result is as follows:
+-------+---------+---------------------------+------------+
| class | stat_id | name | VALUE_TYPE |
+-------+---------+---------------------------+------------+
| 256 | 80040 | clog write count | ADD_VALUE |
| 256 | 80041 | clog write time | ADD_VALUE |
| 256 | 80057 | clog trans log total size | ADD_VALUE |
+-------+---------+---------------------------+------------+
3 rows in set (0.07 sec)
The following table describes the parameters.
| Parameter | Description |
|---|---|
| clog write count | The number of disk writes of clogs. |
| clog write time | The time spent on disk writes of clogs. |
| clog trans log total size | The total size of committed transaction logs. |
Time model metrics
The following example shows how to query the time model metrics:
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)
The following table describes the parameters.
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 amount of active time of the database, which is an accumulative value of the CPU time and non-idle wait time. |
| DB CPU | The CPU time consumed by the database in the active state. |
| background elapsed time | The total amount of time spent on background process execution. |
| background cpu time | The CPU time consumed by background processes. |
| non idle wait time | The amount of non-idle wait time, namely, the total amount of time elapsed when the session is in the wait but not idle state. |
| idle wait time | The amount of idle wait time, namely, the total amount of time elapsed when the session is in the idle wait state. |
| background database time | The total amount of time spent by background processes in executing database operations. |
| background database non-idle wait time | The amount of time spent by background processes in executing database operations in the non-idle wait state. |
| background database idle wait time | The amount of time spent by background processes in executing database operations in the idle wait state. |
| concurrency wait total time | The total amount of wait time caused by concurrency issues, such as the wait time caused by resource locking. |
| user io wait total time | The total amount of time spent by user processes in waiting for the I/O operations, such as data reads from or data writes to the disk, to complete. |
| application wait total time | The total amount of wait time caused by the application code, such as lock waits caused by row-level locking or explicit lock commands. |
Other metrics
In addition to the GV$SYSSTAT view, OceanBase Database provides many other views to describe the metrics from various aspects.
For example, the GV$OB_PROCESSLIST view describes the status of the current sessions of the system and the IDs of the executed SQL statements. This view also provides important information in fields such as SID, SQL_ID, TRANS_ID, and TRACE_ID. It is a useful tool for end-to-end tracing.
The following example shows how to query the
GV$OB_PROCESSLISTview for information about active sessions: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)The following example shows how to query the
GV$OB_PROCESSLISTview for the number of active sessions on each OBServer node: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)