Feature description
Queries the SQL performance statistics of a tenant within a specified time interval. The performance statistics include the following information: cluster, tenant, server, database, user, SQL_ID, response time, CPU time, execution count, and error count. You can query the performance metrics of a specific SQL statement based on the following conditions: whether it is an internal SQL statement, keywords in the SQL text, and advanced search.
Note
This API is time-consuming. If the response is slow, reduce the query frequency, time range, or number of concurrent requests. You can also check the resource usage of the OCP server and Monitor DB, and adjust the specifications as needed.
Call description
Prerequisites
The caller must have the read permission on the specified tenant.
Request path
GET /api/v2/ob/clusters/{clusterId}/tenants/{tenantId}/topSql
Request parameters
| Parameter | Type | Required | Example Value | Description |
|---|---|---|---|---|
| clusterId | Integer | Yes | 1 | The ID of the cluster. |
| tenantId | Integer | Yes | 1001 | The ID of the tenant. |
| startTime | Datetime | Yes | 2020-02-16T05:32:16+08:00 | The start time. |
| endTime | Datetime | Yes | 2020-02-16T07:32:16+08:00 | The end time. |
| serverId | Integer | No | 300 | The ID of the OceanBase server. If not specified, the SQL is queried on all OceanBase servers. |
| inner | Boolean | No | false | Specifies whether to include internal SQL. Internal SQL is initiated by OceanBase. * The default value is false, which means that internal SQL is not included. |
| sqlText | String | No | hello | The keyword of the SQL statement. * The keyword is case-insensitive. |
| searchAttr | String | No | executions | The name of the metric for advanced search. This parameter takes effect only when you also specify searchOp and searchVal. |
| searchOp | String | No | GT | The operator for advanced search. This parameter takes effect only when you also specify searchAttr and searchVal. Valid values: * EQ: equal to * NE: not equal to * GT: greater than * GE: greater than or equal to * LT: less than * LE: less than or equal to |
| searchVal | String | No | 1543 | The value for advanced search. This parameter takes effect only when you also specify searchAttr and searchOp. |
Response parameters
Basic data structure
Parameter Type Description data Object The business data requested. ├─ contents Array A list of SQL performance metrics data. See the following table. successful Boolean Specifies whether the request is successful. timestamp Datetime The timestamp when the server finishes the request. duration Integer The time taken by the server to process the request in milliseconds. status Integer An HTTP status code that conforms to the HTTP status code standard. traceId String The trace ID of the request, which is used to troubleshoot problems. server String The IP address of the server that responds to the request.
Data structure of SQL performance metrics
Parameter Type Description sqlId String The ID of the SQL statement. server String The IP address of the OceanBase server. dbName String The database in which the SQL statement is executed. userName String The user that executes the SQL statement. sqlType String The type of the SQL statement. Valid values: - SELECT: a query statement
- INSERT: an insert statement
- UPDATE: an update statement
- DELETE: a delete statement
- REPLACE: a replace statement
- EXPLAIN: a plan parsing statement
- UNKNOWN: other statements
sqlTextShort String The first 100 characters of the SQL statement. inner Boolean Specifies whether the SQL statement is internal. waitEvent String The most time-consuming internal event of the SQL statement in the specified time interval. Valid values: - system internal wait
- mysql response wait client
- sync rpc
- db file data read
executions Integer The total number of executions of the SQL statement in the period. execPs Float The average number of executions of the SQL statement per second in the period, with two decimal places retained. avgAffectedRows Float The average number of rows updated by the SQL statement in the period, with two decimal places retained. avgReturnRows Float The average number of rows returned by the SQL statement in the period, with two decimal places retained. avgPartitionCount Float The average number of partitions accessed by the SQL statement in the period. failCount Integer The total number of errors of the SQL statement in the period. failPercentage Float The error percentage of the SQL statement in the period. The return value is in the range of [0, 1], with four decimal places retained. retCode4012Count Integer The total number of timeout errors (error code 4012) of the SQL statement in the period. retCode4013Count Integer The total number of memory errors (error code 4013) of the SQL statement in the period. retCode5001Count Integer The total number of syntax parsing errors (error code 5001) of the SQL statement in the period. retCode5024Count Integer The total number of key-value conflict errors (error code 5024) of the SQL statement in the period. retCode5167Count Integer The total number of data length errors (error code 5167) of the SQL statement in the period. retCode5217Count Integer The total number of unknown column errors (error code 5217) of the SQL statement in the period. retCode6002Count Integer The total number of transaction rollback errors (error code 6002) of the SQL statement in the period. avgWaitTime Float The average wait time of the SQL statement in the period. avgWaitCount Float The average number of wait times of the SQL statement in the period. avgRpcCount Float The average number of RPC requests sent by the SQL statement in the period. localPlanPercentage Float The percentage of local plans of the SQL statement in the period. remotePlanPercentage Float The percentage of remote plans of the SQL statement in the period. disPlanPercentage Float The percentage of distributed plans of the SQL statement in the period. avgElapsedTime Float The average response time of the SQL statement in the period in milliseconds. maxElapsedTime Float The maximum response time of the SQL statement in the period in milliseconds. avgCpuTime Float The average CPU time of the SQL statement in the period in milliseconds. maxCpuTime Float The maximum CPU time of the SQL statement in the period in milliseconds. avgNetTime Float The average network transfer time of the SQL statement in the period in milliseconds. avgNetWaitTime Float The average network wait time of the SQL statement in the period in milliseconds. avgQueueTime Float The average queue time of the SQL statement in the period in milliseconds. avgDecodeTime Float The average syntax parsing time of the SQL statement in the period in milliseconds. avgGetPlanTime Float The average plan generation time of the SQL statement in the period in milliseconds. avgExecuteTime Float The average plan execution time of the SQL statement in the period in milliseconds. avgExecutorRpcCount Float The average number of RPC requests executed by the SQL statement in the period. missPlanPercentage Float The plan miss rate of the SQL statement in the period. The return value is in the range of [0, 1], with four decimal places retained. avgApplicationWaitTime Float The average application event wait time of the SQL statement in the period in milliseconds. avgConcurrencyWaitTime Float The average concurrency event wait time of the SQL statement in the period in milliseconds. avgUserIoWaitTime Float The average user I/O event wait time of the SQL statement in the period in milliseconds. avgScheduleTime Float The average schedule event wait time of the SQL statement in the period in milliseconds. avgRowCacheHit Float The average row cache hit count of the SQL statement in the period. avgBloomFilterCacheHit Float The average bloom filter cache hit count of the SQL statement in the period. avgBlockCacheHit Float The average block cache hit count of the SQL statement in the period. avgBlockIndexCacheHit Float The average block index cache hit count of the SQL statement in the period. avgDiskReads Float The average disk read count of the SQL statement in the period. retryCount Integer The total number of retries of the SQL statement in the period. tableScanPercentage Float The table scan percentage of the SQL statement in the period. strongConsistencyPercentage Float The percentage of strong-consistency transactions of the SQL statement in the period. The return value is in the range of [0, 1], with four decimal places retained. weakConsistencyPercentage Float The percentage of weak-consistency transactions of the SQL statement in the period. The return value is in the range of [0, 1], with four decimal places retained. avgMemstoreReadRows Float The average number of rows read by MemStore of the SQL statement in the period. avgSsstoreReadRows Float The average number of rows read by SsStore of the SQL statement in the period. avgLogicalReads Double The average logical read count of the SQL statement in the period. cpuPercentage Double The percentage of CPU time of the SQL statement in the TOPSQL list. dynamicSql Boolean Specifies whether the SQL statement is dynamic. lastFailCode Integer The error code of the last error of the SQL statement in the period. lastFailTimestamp Integer The timestamp of the last error of the SQL statement in the period. obDbId Integer The ID of the OceanBase database. obTenantId Integer The ID of the OceanBase tenant. serverIp String The IP address of the OBServer. sumCpuTimeMs Double The total CPU time of the SQL statement in the period. sumElapsedTime Double The total response time of the SQL statement in the period in milliseconds. sumLogicalReads Integer The total logical read count of the SQL statement in the period. sumWaitTime Double The total wait time of the SQL statement in the period in milliseconds. tenantId Integer The ID of the tenant. tenantName String The name of the tenant. Note
The returned SQL statement contains only the first 100 characters. To view the full text of the SQL statement, call the query SQL text API.
Examples
Request example
Query the SQL performance of the cluster with Id 1 and tenant with Id 1001. The time range is from 2020-11-03T19:00:00+08:00 to 2020-11-03T20:00. Specify the advanced search condition as: the number of executions is greater than or equal to 500.
GET /api/v2/ob/clusters/1/tenants/1001/topSql?searchAttr=executions&searchOp=GE&searchVal=500&startTime=2020-11-03T19%3A00%3A00%2B08%3A00&endTime=2020-11-03T20%3A00%3A00%2B08%3A00
Response example
The actual number of returned performance data varies with the database load and query time range. Here are only two performance data samples.
{
"duration": 1435,
"server": "a83ad33525",
"status": 200,
"successful": true,
"timestamp": "2020-11-04T11:49:03.802+08:00",
"traceId": "ead2abb032cc464b",
"data": {
"contents": [
{
"avgAffectedRows": 0.00,
"avgApplicationWaitTime": 0.00,
"avgBlockCacheHit": 0.00,
"avgBlockIndexCacheHit": 0.00,
"avgBloomFilterCacheHit": 0.00,
"avgConcurrencyWaitTime": 0.00,
"avgCpuTime": 0.09,
"avgDecodeTime": 0.00,
"avgDiskReads": 0.00,
"avgElapsedTime": 0.09,
"avgExecuteTime": 0.04,
"avgExecutorRpcCount": 0.00,
"avgGetPlanTime": 0.05,
"avgMemstoreReadRows": 0.00,
"avgNetTime": 0.00,
"avgNetWaitTime": 0.00,
"avgPartitionCount": 1.00,
"avgQueueTime": 0.00,
"avgReturnRows": 0.00,
"avgRowCacheHit": 0.00,
"avgRpcCount": 0.00,
"avgScheduleTime": 0.00,
"avgSsstoreReadRows": 0.00,
"avgUserIoWaitTime": 0.00,
"avgWaitCount": 0.00,
"avgWaitTime": 0.00,
"dbName": "bar",
"distPlanPercentage": 0.00,
"execPs": 2.11,
"executions": 7522,
"failCount": 0,
"failPercentage": 0.00,
"inner": true,
"localPlanPercentage": 1.00,
"maxCpuTime": 0.00,
"maxElapsedTime": 0.00,
"missPlanPercentage": 0.00,
"remotePlanPercentage": 0.00,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"server": "xxx.xxx.xxx.1:2882",
"sqlId": "89FF9AC900FA242F1DEF8B2211377F60",
"sqlTextShort": "DELETE FROM X WHERE ID = 3",
"sqlType": "DELETE",
"strongConsistencyPercentage": 1.00,
"tableScanPercentage": 0.00,
"userName": "test",
"waitEvent": "none",
"weakConsistencyPercentage": 0.00
},
{
"avgAffectedRows": 0.00,
"avgApplicationWaitTime": 0.00,
"avgBlockCacheHit": 1.00,
"avgBlockIndexCacheHit": 1.00,
"avgBloomFilterCacheHit": 0.00,
"avgConcurrencyWaitTime": 0.00,
"avgCpuTime": 0.39,
"avgDecodeTime": 0.00,
"avgDiskReads": 0.00,
"avgElapsedTime": 0.42,
"avgExecuteTime": 0.28,
"avgExecutorRpcCount": 0.00,
"avgGetPlanTime": 0.11,
"avgMemstoreReadRows": 1.00,
"avgNetTime": 0.00,
"avgNetWaitTime": 0.00,
"avgPartitionCount": 2.00,
"avgQueueTime": 0.02,
"avgReturnRows": 2.00,
"avgRowCacheHit": 0.00,
"avgRpcCount": 0.00,
"avgScheduleTime": 0.00,
"avgSsstoreReadRows": 1.00,
"avgUserIoWaitTime": 0.00,
"avgWaitCount": 0.00,
"avgWaitTime": 0.00,
"dbName": "oceanbase",
"distPlanPercentage": 0.00,
"execPs": 1.00,
"executions": 3568,
"failCount": 0,
"failPercentage": 0.00,
"inner": false,
"localPlanPercentage": 1.00,
"maxCpuTime": 0.00,
"maxElapsedTime": 0.00,
"missPlanPercentage": 0.00,
"remotePlanPercentage": 0.00,
"retCode4012Count": 0,
"retCode4013Count": 0,
"retCode5001Count": 0,
"retCode5024Count": 0,
"retCode5167Count": 0,
"retCode5217Count": 0,
"retCode6002Count": 0,
"retryCount": 0,
"server": "xxx.xxx.xxx.2:2882",
"sqlId": "9CFB17EA7B3F88B92C6BD433D845963A",
"sqlTextShort": "select 1",
"sqlType": "SELECT",
"strongConsistencyPercentage": 0.00,
"tableScanPercentage": 0.00,
"userName": "foo",
"waitEvent": "none",
"weakConsistencyPercentage": 1.00
}
]
}
}