Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Usage Guide

Configuring Automatic Query Termination Based on Memory Usage

YMatrix supports Runaway detection. For queries managed by resource groups, YMatrix can automatically terminate queries based on their memory consumption.

Relevant configuration parameters include:

  • gp_vmem_protect_limit: Sets the total amount of memory that all postgres processes in an active segment instance can consume. If a query causes this limit to be exceeded, no additional memory is allocated, and the query fails.
  • runaway_detector_activation_percent: When resource groups are enabled, if memory usage exceeds gp_vmem_protect_limit * runaway_detector_activation_percent, YMatrix begins terminating queries managed by resource groups (excluding those in system_group), starting with the query consuming the most memory. Termination continues until memory usage drops below the specified threshold.

Assigning Resource Groups to Roles

  • Use the RESOURCE GROUP clause in CREATE ROLE or ALTER ROLE to assign a resource group to a database role:
ALTER ROLE bill RESOURCE GROUP rg_light;
CREATE ROLE mary RESOURCE GROUP exec;

A resource group can be assigned to one or more roles. If a role hierarchy exists, the resource group assigned to a parent role does not propagate to its member roles.

  • To remove a resource group assignment from a role and revert it to the default group, set the group name to NONE:
ALTER ROLE mary RESOURCE GROUP NONE;

Monitoring Resource Group Status

  • View resource group limits:

    SELECT * FROM gp_toolkit.gp_resgroup_config;

  • View current resource group query status:

    SELECT * FROM gp_toolkit.gp_resgroup_status;

  • View memory usage per host for each resource group:

    SELECT * FROM gp_toolkit.gp_resgroup_status_per_host;

  • View which resource groups are assigned to roles:

    SELECT rolname, rsgname 
FROM pg_roles, pg_resgroup
WHERE pg_roles.rolresgroup = pg_resgroup.oid;

  • View running and pending queries in resource groups:

    SELECT query, rsgname, wait_event_type, wait_event 
FROM pg_stat_activity;

  • Cancel running or queued transactions in a resource group:
    To manually cancel a transaction, first identify its process ID (pid). Then call pg_cancel_backend() to terminate it.

    Steps:

    1. Run the following query to list all active or idle statements across all resource groups. If no results are returned, there are no running or queued transactions.
      SELECT rolname, g.rsgname, pid, waiting, state, query, datname 
FROM pg_roles, gp_toolkit.gp_resgroup_status g, pg_stat_activity 
WHERE pg_roles.rolresgroup = g.groupid
  AND pg_stat_activity.usename = pg_roles.rolname;
    2. Example output:
      rolname | rsgname  |  pid  | waiting | state  |          query           | datname 
---------+----------+-------+---------+--------+--------------------------+---------
  sammy  | rg_light | 31861 |    f    | idle   | SELECT * FROM mytesttbl; | testdb
  billy  | rg_light | 31905 |    t    | active | SELECT * FROM topten;    | testdb
    3. Terminate the transaction process:
      SELECT pg_cancel_backend(31905);

Note!
Do not use the operating system KILL command to terminate any YMatrix database process.

Moving Queries Between Resource Groups

Superusers can use the gp_toolkit.pg_resgroup_move_query() function to move a running query from one resource group to another without stopping it. This is useful for accelerating long-running queries by moving them to a resource group with higher allocation or availability.

pg_resgroup_move_query() affects only the specified query; subsequent queries from the same session remain assigned to the original resource group.

Note!
Only active (running) queries can be moved. Idle, queued, or pending queries—those blocked due to concurrency or memory limits—cannot be moved.

The function requires the query’s process ID (pid) and the target resource group name:

pg_resgroup_move_query(pid int4, group_name text);

As described in the section on canceling transactions, you can use the gp_toolkit.gp_resgroup_status view to list resource group names, IDs, and statuses.

When pg_resgroup_move_query() is called, the running query becomes subject to the target resource group’s configuration, including concurrency and memory limits:

  • If the target group has reached its concurrency limit, the query is queued until a slot becomes available—or until gp_resource_group_queuing_timeout (if set) expires.
  • If a slot is available, pg_resgroup_move_query() attempts to transfer slot ownership to the target process for up to gp_resource_group_move_timeout milliseconds. If the transfer isn’t completed within this time, an error is returned.
  • If the move operation is canceled after the target process has already acquired slots but before all segments complete the move, the segments remain in the original group while the target process holds the slots. This inconsistency is resolved at transaction end or during the next command scheduled by the target process in the same transaction.
  • If the target resource group lacks sufficient memory to meet the query’s current demand, an error is returned. To resolve this, either increase the target group’s memory quota or wait for other queries to finish before retrying.

After a query is moved, there is no guarantee that the total memory usage of running queries in the target group will stay within its MEMORY_QUOTA. In such cases, one or more queries—including the moved one—may fail. To minimize this risk, ensure adequate global shared memory is reserved for the resource group.

← Previous
Enabling
Next →
Row and column Permissions