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

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:

3. Resource Pool Parameters That Matter Most

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

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.