This topic describes the OceanBase Cloud Platform (OCP) parameters that are related to SQL performance diagnostics.
SQL performance module
The following table describes the parameters for SQL performance diagnostics.
| Parameter | Default value | Description |
|---|---|---|
| ocp.perf.sql-diag.awful-performance-index-used-config | {"enabled":true, "cpuTimeLimitUs":100000, "execPsLimit":5.0, "fullLogicalReadsLimit":10000} | The diagnostic parameter for poor performance despite the use of index. Parameters:
|
| ocp.perf.sql-diag.cpu-time-proportion-high-config | {"enabled":true, "cpuTimeLimitUs":10000, "execPsLimit":10.0, "maxCpuTimeLimitUs":30000, "affectedRowsLimit":100, "execLimit":30, "sqlCountLimit":20, "cpuTimePercentLimit":20} | The diagnostic parameter for a high CPU time percentage for execution. Parameters:
1) The percentage of the CPU time of the diagnosed SQL query to the CPU time of the tenant exceeds $cpuTimePercentLimit. 2) The total number of SQL executions by the tenant exceeds $execLimit. 3) The number of unique SQL queries executed by the tenant is greater than $sqlCountLimit. |
| ocp.perf.sql-diag.database-white-list | oceanbase,information_schema,mysql,__recyclebin,sys,__public | The names of the databases to be skipped in SQL diagnostics. |
| ocp.perf.sql-diag.execution-spike-config | {"enabled":true, "cpuTimeLimitUs":3000, "execPsLimit":5.0, "pointsLimit":5, "compareMin":20, "avgExecutionMultiply":{"0":6, "1":5, "5":4, "10":3, "50": 2.5, "100": 2, "500": 1.6}, "stdExecutionMultiply":{"0":80, "1":60 , "3": 30,"5":20, "10":15, "20":8, "30":6, "50":5, "80":4}} | The diagnostic parameter for execution spikes. Parameters:
1. Find the SQL queries that meet the diagnostic conditions within the last $compareMin before the diagnosis. Calculate the average execution frequency per minute (avg_exec_ps) of the SQL queries, the standard deviation of the execution frequency (std_exec_ps) in the last $compareMin, and the ratio of the standard deviation to the average (range_percent). In other words, the following statistics are calculated:
The value of multiple_value_1 parameter varies with that of the avg_exec_ps parameter, depending on the value of the avgExecutionMultiply parameter. When avg_exec_ps > 500, multiple_value_1 = 1.6. Likewise, 100 -> 2, 50 -> 2.5, 10 -> 3, 5 -> 4, and 1 -> 5. The value of multiple_value_2 parameter varies with that of the range_percent parameter, depending on the value of the stdExecutionMultiply parameter. When range_percent > 80, multiple_value_ 2 = 4. Likewise, 50 -> 5, 30 -> 6, 20 -> 8, 10 -> 15, 5 -> 20, 3 -> 30, 1 -> 60, and 0 -> 80. |
| ocp.perf.sql-diag.index-diagnoser-config | {"enabled":true,"schedulePeriodMin":5, "coreThreadSize":10, "maxThreadSize":50,"maxQueueSize":10000,"diagPeriodSec":300,"diagOffsetSec":60,"maxDiagPeriodSec":1800} | The parameter for SQL index diagnostics. It takes effect after a restart. We recommend that you leave this parameter unchanged. |
| ocp.perf.sql-diag.ineffective-hint-config | {"enabled":true, "execPsLimit":5.0, "cpuTimeLimitUs":20000} | The diagnostic parameter for inactive hints. Parameters:
|
| ocp.perf.sql-diag.perf-diagnoser-config | {"enabled":true,"schedulePeriodMin":5, "coreThreadSize":10, "maxThreadSize":50,"maxQueueSize":10000,"diagPeriodSec":300,"diagOffsetSec":60,"maxDiagPeriodSec":1800} | The parameter for SQL performance diagnostics. It takes effect after a restart. We recommend that you leave this parameter unchanged. |
| ocp.perf.sql-diag.performance-degradation-after-plan-changed-config | {"enabled":true, "compareMin":5, "cpuTimeMultiply":{"0":50, "1":30, "10":10, "100":8, "1000":6}} | The diagnostic parameter for changes in the execution plan and performance degradation. Parameters
|
| ocp.perf.sql-diag.performance-degradation-config | {"enabled":true, "cpuTimeLimitUs":10000, "execPsLimit":5.0, "maxCpuTimeLimitUs":30000, "affectedRowsLimit":100, "execLimit":5, "compareMin":20, "cpuTimeMultiply":{"0":50, "1":30, "10":10, "100":8, "1000":6}} | The diagnostic parameter for performance degradation.
|
| ocp.perf.sql-diag.query-timeout | 30000000 | Specifies the timeout threshold of SQL diagnostic queries (μs). |
| ocp.perf.sql-diag.row-lock-contention-high-config | {"enabled":true, "execPsLimit":0, "cpuTimeLimitUs":1000, "elapsedTimeLimitUs":0} | The diagnostic parameter for high row lock contention.
Note: The elapsedTimeLimitUs parameter is used only when it is configured. Diagnostic criteria: A SQL query is considered to have caused high row lock contention if its type is %select%for%update%. |
| ocp.perf.sql-diag.table-scan-index-not-exists-config | {"enabled":true, "execPsLimit":1.0, "cpuTimeLimitUs":20000} | The diagnostic parameter for the full-table scan without any indexes available.
|
| ocp.perf.sql-diag.table-scan-index-not-used-config | {"enabled":true, "execPsLimit":1.0, "cpuTimeLimitUs":2000} | The diagnostic parameter for a full-table scan without using the index.
|
| ocp.perf.sql.diag-plan-change-config | {"enabled":true,"schedulePeriodMin":5, "coreThreadSize":10, "maxThreadSize":50,"maxQueueSize":10000,"diagPeriodSec":300,"diagOffsetSec":300,"maxDiagPeriodSec":1800} | The parameter for scheduling of diagnostic tasks. We recommend that you leave this parameter unchanged. |
| ocp.perf.sql.max-query-range | 24h | Specifies the maximum length of time range for querying SQL performance data. Default value: 24h. If the time range of a query you specified on the TopSQL or SlowSQL tab exceeds the value of this parameter, an error is returned when you submit the query. |
| ocp.perf.sql.plan-hist-level0-granularity | 30s | Specifies the time interval for OCP to aggregate the performance data of the Level 0 SQL execution plans, or plans for short. By default, OCP collects and aggregates the performance data of plans once every 30s. The performance data is stored in specific tables for use in TopSQL diagnostics. You can increase this value to relieve the storage pressure for the MetaDB and MonitorDB of OCP. |
| ocp.perf.sql.plan-hist-level0-query-interval | 2h | Specifies the maximum time range for querying the Level 0 plan performance data. If the specified query time range exceeds this value, OCP queries data tables with larger aggregation time intervals. |
| ocp.perf.sql.plan-hist-level0-retention | 5d | Specifies the period for retaining performance data partitions of Level 0 plans. Performance data partitions are created by day. By default, the data is retained for only five days. Partitions that have been retained for more than five days are automatically deleted. |
| ocp.perf.sql.prepare-partition-ahead | 8 | Specifies the number of days for creating performance data partitions before the diagnosis. You need to specify the period in days. |
| ocp.perf.sql.query-timeout | 30000000 | Specifies the timeout threshold for SQL performance data queries (μs). |
| ocp.perf.sql.sql-hist-level0-granularity | 30s | Specifies the time interval for OCP to aggregate the performance data of the Level 0 SQL queries. Note: To improve the performance of monitoring metric collection, OCP collects and aggregates SQL performance data at different time intervals for TopSQL diagnostics. The data is divided into three levels and is collected and stored in table partitions of the corresponding levels by day. The retention period for performance data partitions is specified by a fixed parameter. For the same type of performance data, a larger time interval requires less storage space. |
| ocp.perf.sql.sql-hist-level0-query-interval | 2h | Specifies the maximum time range for querying the Level 0 SQL performance data. If the time range you specified on the TopSQL tab exceeds this value, OCP queries the Level 1 SQL performance data tables. |
| ocp.perf.sql.sql-hist-level0-retention | 2d | Specifies the retention period for partitions of Level 0 SQL performance data. Partitions for Level 0 SQL performance data are created by day. By default, the MetaDB and MonitorDB of OCP only retains partitions created in the last two days. |
| ocp.perf.sql.sql-hist-level1-granularity | 2m | Specifies the time interval for OCP to aggregate the performance data of the Level 1 SQL queries. By default, OCP aggregates SQL performance data once every 2 minutes and writes the data into Level 2 SQL performance data tables. |
| ocp.perf.sql.sql-hist-level1-query-interval | 12h | Specifies the maximum time interval for querying the Level 1 SQL performance data. If the time range you specified on the TopSQL tab exceeds this value, OCP queries the Level 2 SQL performance data tables. |
| ocp.perf.sql.sql-hist-level1-retention | 8d | Specifies the retention period for partitions of Level 1 SQL performance data. |
| ocp.perf.sql.sql-hist-level2-granularity | 10m | Specifies the time interval for OCP to aggregate the performance data of the Level 2 SQL queries. By default, OCP aggregates SQL performance data once every 10 minutes and writes the data into Level 2 SQL performance data tables. |
| ocp.perf.sql.sql-hist-level2-query-interval | 48h | Specifies the maximum time interval for querying the Level 2 SQL performance data. Regardless of whether the time range you specified exceeds this value, OCP queries the Level 2 SQL performance data tables. |
| ocp.perf.sql.sql-hist-level2-retention | 15d | Specifies the retention period for partitions of Level 2 SQL performance data. |