How OceanBase CPU Quotas and Large Queries Shape Performance

1. Background

When using OceanBase, there are questions about the meaning and relationships of parameters like

workers_per_cpu_quota

,

px_workers_per_cpu_quota

,

parallel_servers_target

and

cpu_quota_concurrency

. This article, targeting OceanBase v4, investigates these questions through theory and experiments.

2. Tenant Thread Concepts

2.1 Tenant Maximum Thread Count

Tenants need to request threads from a multi‑tenant thread pool to keep active thread numbers stable. The maximum thread count limits a tenant’s memory usage and is determined by configuration items and Unit specifications.

Tenant maximum thread count = unit_max_cpu × workers_per_cpu_quota

2.2 Tenant Active Thread Count

Active threads are those that can process requests normally; they differ from suspended threads. The active thread limit controls a tenant’s CPU usage.

Tenant active thread limit = unit_min_cpu × cpu_quota_concurrency

3. Large Queries and CPU Relationship

Using the parameter

large_query_threshold

, queries whose execution time exceeds a threshold are classified as large queries.

If large and small queries run simultaneously, OceanBase allocates a portion of CPU resources to large queries and limits the number of active worker threads for large queries via

large_query_worker_percentage

(default 30%).

By restricting the active worker threads for large queries, OceanBase caps the CPU resources large queries can consume, preserving CPU for OLTP workloads.

The

large_query_threshold

should not be set too high, otherwise large queries may monopolize CPU and cause OLTP slowdown or queue buildup.

4. Parameter Overview

5. Parameter Explanation

5.1 Relationship between workers_per_cpu_quota and cpu_quota_concurrency

The number of threads a tenant can process concurrently is controlled by

workers_per_cpu_quota

and

cpu_quota_concurrency

:

workers_per_cpu_quota

indicates the maximum number of worker threads a tenant can allocate per CPU.

cpu_quota_concurrency

indicates the maximum number of active threads allowed per CPU quota (i.e., concurrency).

When configuring, ensure

workers_per_cpu_quota > cpu_quota_concurrency

.

5.2 parallel_servers_target

parallel_servers_target = MIN CPU × px_workers_per_cpu_quota

Purpose : Controls when parallel execution (PX) SQL threads exceed the parallel query queue threshold, causing subsequent PX SQL to wait.

Parallel query queuing : The variable

PARALLEL_SERVERS_TARGET

specifies the maximum number of parallel execution work threads a tenant can provide per node.

When a parallel query starts, all relevant observers reserve work thread resources; if any observer cannot provide enough, the query is not executed.

After the query finishes, the reserved resources are released immediately.

Parameter recommendation : Generally set

parallel_servers_target

to

MIN CPU * 10

. For I/O‑intensive scenarios,

MIN CPU * 20

may be used.

Tip The default value of px_workers_per_cpu_quota only affects the tenant at creation time; modifying px_workers_per_cpu_quota later does not change the value of parallel_servers_target .

5.3 px_workers_per_cpu_quota

Defines the number of parallel execution threads that can be allocated per CPU.

When the tenant’s MIN CPU is N, the theoretical per‑node thread allocation is

N * px_workers_per_cpu_quota

. In practice, uneven data distribution may cause temporary overallocation, which is reclaimed after the load ends.

Usually the default value does not need to be changed.

6. Experimental Validation

6.1 Environment Preparation

Create a 1‑1‑1 architecture with an 8C6G MySQL tenant.

<code>obclient [oceanbase]> SELECT t1.tenant_name, CONCAT(t2.svr_ip, ':', t2.svr_port) AS "unit_server", CONCAT(t4.unit_count, ' unit: ', t3.min_cpu, 'C/', ROUND(t3.memory_size/1024/1024/1024,0), 'G') AS unit_info, t3.min_cpu, t3.max_cpu FROM OCEANBASE.DBA_OB_TENANTS t1 INNER JOIN OCEANBASE.DBA_OB_RESOURCE_POOLS t4 ON t1.tenant_id = t4.tenant_id INNER JOIN OCEANBASE.DBA_OB_UNITS t2 ON t4.resource_pool_id = t2.resource_pool_id INNER JOIN OCEANBASE.DBA_OB_UNIT_CONFIGS t3 ON t4.unit_config_id = t3.unit_config_id WHERE t1.tenant_id = 1008 ORDER BY unit_server;</code>

Result shows MIN_CPU = 8, MAX_CPU = 8.

6.2 Viewing Tenant Parameters

<code>show parameters like 'workers_per_cpu_quota';</code>

<code>show parameters like 'px_workers_per_cpu_quota';</code>

<code>show variables like 'parallel_servers_target';</code>

Values obtained:

workers_per_cpu_quota = 10

,

px_workers_per_cpu_quota = 10

,

cpu_quota_concurrency = 4

,

parallel_servers_target = 80

.

6.3 Data Preparation

Create a table with 12 partitions and insert 1.5 million rows per partition in database

zengquan_zheng

, table

test_table

.

6.4 VM CPU and Disk Performance

CPU stress test on three VMs (VM1, VM2, VM3) using:

<code>sysbench cpu --cpu-max-prime=20000 --threads=$(nproc) run</code>

VM1 > VM2 > VM3

Disk performance sampling shows VM1 > VM3 > VM2.

VM1 > VM3 > VM2

6.5 Stress Test Methodology

Observe tenant behavior under different primary_zone priorities and concurrency levels. Key points:

High concurrency may cause SQL retries, making actual execution count larger than the concurrency level.

Results are a single sample; actual outcomes may vary with physical host load.

Zone priority syntax:

zone3;zone2,zone1

(semicolon = higher priority, comma = equal priority).

Monitor tenant queue backlog via

grep "req_queue:total_size=" observer.log

.

Flush plan cache for the target SQL before each test:

ALTER SYSTEM FLUSH PLAN CACHE sql_id='$sql_id' databases='zengquan_zheng' tenant='mysql_ob' GLOBAL;

6.5.3 Test Scenarios

Scenario 1 : 75/78/80/85 concurrent executions of a fast query (≈ 700 ms) with large‑query threshold 5 s.

Scenario 2 : Same concurrency levels but with a slow query (≈ 10 s).

Scenario 3 : 100 concurrent executions of the slow query with threshold 5 s.

Scenario 4 : 100 concurrent executions of the slow query with threshold 50 s.

Scenario 5 : Two groups of 50 concurrent executions, one with fast query (≈ 700 ms) and one with slow query (≈ 10 s).

7. Conclusions

Scenario 1 & 2 :

parallel_servers_target

equals 80, but reaching 80 concurrent threads does not always cause queueing; queueing may occur below 80 depending on query mix.

Scenario 3 & 4 : An excessively large large‑query threshold can cause severe queue buildup and dramatically increase SQL execution time when primary zones have equal priority.

Scenario 1 & 5 : Small queries run quickly alone, but when mixed with large queries, the large queries can significantly increase the latency of small queries.

The observed results are related to VM CPU and disk performance, OB CPU/large‑query settings, and memory size.

Parameter values should be adjusted after performance testing in real business scenarios; otherwise, the defaults are sufficient.

8. References

cpu_quota_concurrency: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002015547

Parallel query parameter tuning: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002013758

Setting parallel execution parameters: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002016195

Concurrency control and queuing: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002016196

PX parallelism and speed relationship: https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000466049?back=kb

Worker threads: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002014039

Multi‑tenant thread FAQ: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000218492

Background threads: https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002014037