GBase 8a Resource Management in Practice: From Workload Grouping to Concurrency Control

Performance jitter in a gbase database MPP cluster often isn't caused by a single slow SQL statement. It's the result of mixed workloads fighting for the same resources — reports clashing with batch jobs, imports squeezing out queries, or uncontrolled concurrency piling up. The fix isn't to tune every query individually, but to set clear resource boundaries and execution priorities at the system level.

1. Four Common Signs of Resource Contention

Symptom	Likely Cause	First Action
Reports slow down entire batch pipeline	Workloads not separated, competing for same resources	Check if user access is split by workload
A few large queries block all later sessions	No concurrency cap	Look for queuing in SHOW PROCESSLIST
Node CPU / memory swings wildly	Resource pool boundaries not defined	Inspect resource pool and consumer group config
Temporary tablespace balloons quickly	Large queries and batch tasks colliding	Check max_temp_diskspace

2. The Four‑Layer Resource Management Model

GBase 8a binds a workload's identity to a resource policy through four layers:

• Consumer Group – Groups users or business roles.
• Resource Pool – Defines CPU, memory, I/O, and concurrency caps for each group.
• Resource Plan – Assembles multiple pools into a named strategy.
• Resource Directive – Wires consumer groups to pools and activates the plan.

A sensible grouping:

User / Task Type	Suggested Group	Reason
Core query APIs	High priority	Latency‑sensitive
Nightly batch jobs	Medium priority	Can consume resources, but shouldn't starve daytime traffic
Ad‑hoc analytical reports	Low priority	Allowed to queue, avoiding impact on core services
Data load / import tasks	Dedicated group	Heavy I/O and temp space usage
Operations / DBA checks	Default or separate	Should never compete with heavy reporting

3. Resource Pool Parameters That Matter Most

Beyond CPU percentage, these parameters have a bigger impact on stability:

Parameter	What It Controls
max_activetask	Maximum concurrent active tasks
max_memory	Memory cap for operators in a group
max_temp_diskspace	Temporary disk space ceiling
max_disk_writeio / max_disk_readio	Read/write I/O throughput
max_disk_space	Tablespace growth limit

Among these, max_activetask is often the most effective first lever — it caps how many heavy queries can run simultaneously, preventing a sudden surge from overwhelming the cluster.

4. Concurrency Control Configuration Example

The following assigns a report user to a consumer group and restricts the group's dynamic pool to at most 2 active tasks.

-- 1. Create consumer group and assign user
CREATE CONSUMER GROUP cg_report COMMENT 'report workload';
ALTER CONSUMER GROUP cg_report ADD USER report_user;

-- 2. Create a static resource pool (total budget)
CREATE RESOURCE POOL static_report(
  cpu_percent=100,
  max_memory=4096,
  max_disk_readio=100,
  max_disk_writeio=100,
  max_temp_diskspace=1024,
  max_disk_space=102400,
  max_activetask=10
) TYPE static;

-- 3. Create a dynamic resource pool with strict concurrency limit
CREATE RESOURCE POOL dy_report(
  priority=1,
  cpu_percent=60,
  max_memory=1024,
  max_temp_diskspace=256,
  max_disk_space=4096,
  max_disk_writeio=80,
  max_disk_readio=80,
  max_activetask=2
) TYPE dynamic BASE ON static_report;

-- 4. Build the resource plan and directives
CREATE RESOURCE PLAN rp_daytime;
CREATE RESOURCE DIRECTIVE rd_report(
  plan_name='rp_daytime',
  group_name='cg_report',
  pool_name='dy_report'
);
CREATE RESOURCE DIRECTIVE rd_default(
  plan_name='rp_daytime',
  group_name='default_consumer_group',
  pool_name='dy_report'
);

-- 5. Activate the plan
SET GLOBAL active_resource_plan='rp_daytime';

When more than 2 large queries are submitted by the same group, extra sessions show waiting in res pool in SHOW PROCESSLIST. This is a sign that resource governance is working — the system is queuing excess work rather than letting it swamp the cluster.

5. Add Disk and Temporary Space Guardrails

Beyond resource pools, GBase 8a supports user‑level disk quotas to cap long‑term consumption:

CREATE USER etl_user IDENTIFIED BY '***';
GRANT ALL ON ods.* TO etl_user;
GRANT USAGE ON *.* TO etl_user LIMIT_STORAGE_SIZE=500G;

Check current usage:

SELECT * FROM information_schema.gnodes_user_diskspace_usage;

Resource pools handle execution‑time control; disk quotas handle long‑term occupancy. They work together.

6. Common Pitfalls

• Building resource pools without separating user logins – reports and batch jobs sharing the same account can't be differentiated.
• Blurring the boundary between static and dynamic pools – dynamic pool parameters cannot exceed the static pool's total budget arbitrarily.
• Misusing the default consumer group – applying high‑resource policies to the default group is equivalent to no segregation at all.
• Seeing queues and thinking the system is broken – waiting in res pool means throttling is active. What you should worry about is whether critical workloads are also getting queued.
• Validating on a single coordinator – in multi‑coordinator environments, evaluate the global entry point, not just one node.

7. Recommended Governance Sequence

1. Split users and entry points by workload – never mix reports, ETL, and ad‑hoc analysis under one account.
2. Design consumer groups – decide who belongs where.
3. Apply concurrency caps first – max_activetask stabilizes the system quickly.
4. Add CPU, memory, I/O, and temp space limits – finer‑grained tuning.
5. Finally, add user‑level disk quotas – control long‑term space usage.

Getting the workload groups and resource boundaries right is far more effective at stabilizing a gbase database cluster than endlessly tuning individual SQL statements. When the order is set, many "performance problems" turn out to be resource governance problems that were waiting to be fixed.