Resource Queues

Prior to version 5, Greenplum used the notion of resource queues. Each user or role is associated with a resource queue. Each queue defines the limits on resources that its associated roles can use.

For example, the following two statements will create a resource queue named adhoc and associate the role group2 with the queue:

CREATE RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=3);

ALTER ROLE group2 RESOURCE QUEUE adhoc;

The ACTIVE_STATEMENTS parameter specifies that at most three statements submitted to the server from logins associated with this role can be executed at any one time. If a fourth request is submitted while the previous three are still running, the new request will be held in a queue until a slot is available for it to be run.

In addition to the active statement limit, the queue definition can contain limits on memory utilization and/or maximum estimated query cost.

CREATE RESOURCE QUEUE etl
  WITH (ACTIVE_STATEMENTS=3, MEMORY_LIMIT = '500MB');

Estimated cost is a bit of an amorphous metric and can vary from the use of the legacy query optimizer in comparison to the Pivotal Query Optimizer (aka GPORCA). Given this background, we suggest that you not use the estimated cost of a plan.

In addition to these queue controls, several GUCs (or system configuration parameters) control memory utilization for the system.

CPU usage for queues is managed by the priority setting. It can be LOW, MEDIUM, HIGH, or MAX. Compared to active statements or memory, the priority setting is less well defined. As a general rule, the higher the setting, the more CPU:

CREATE RESOURCE QUEUE poweruser
  WITH (ACTIVE_STATEMENTS=3, MEMORY_LIMIT = '1500MB',
  PRIORITY = HIGH);

The system sets roles that have no assigned resource queue to the pg_default resource queue. As with many defaults, it’s best practice to not use it; rather, you should create resource queues and assign roles to them as appropriate. A typical data warehouse implementation might have a resource queue for ETL jobs, with another for ad hoc queries, another for standard reports, and yet another for short, high-priority queries. Only certain statements are actually affected by the resource queue mechanism. By default, these include SELECT, SELECT INTO, and CREATE TABLE AS SELECT. In an analytic data warehouse, these are the statements that are most likely to consume large amounts of resources.

The following query reveals the status of the queues:

select * from gp_toolkit.gp_resqueue_status;

-[ RECORD 1 ]--+------------
queueid        | 228978
rsqname        | power
rsqcountlimit  | 3
rsqcountvalue  | 0
rsqcostlimit   | −1
rsqcostvalue   | 0
rsqmemorylimit | 1.57286e+09
rsqmemoryvalue | 0
rsqwaiters     | 0
rsqholders     | 0
-[ RECORD 2 ]--+------------
queueid        | 6055
rsqname        | pg_default
rsqcountlimit  | 20
rsqcountvalue  | 0
rsqcostlimit   | −1
rsqcostvalue   | 0
rsqmemorylimit | −1
rsqmemoryvalue | 0
rsqwaiters     | 0
rsqholders     | 0

A fuller description of the resource queues is available in the Pivotal documentation.

Resource queues do an important job in proactively managing workload, but they do not respond to issues with dynamic resource consumption. For example, after a query leaves a queue and begins executing, the queue mechanism does nothing to further constrain its behavior. Poorly written SQL can easily overwhelm a system.

Resource Groups

Resource groups allow fine-grained control over resource usage such as concurrency, CPU, and memory. When resource groups are enabled, the system sets roles that have no assigned resource group to the default_group. Any admin and superuser roles are assigned to a special admin_group group with concurrency and resources set aside to ensure admin queries run smoothly.

CPU is set as a floor for each resource group, meaning that if default_group is configured for 20% of system CPU, any queries executed within default_group will share at least 20% of all CPU available to the system. If the total system CPU usage is at less than 100%, any idle CPU can be utilized as needed by any running queries on the system. Similarly, you also can guarantee memory for each resource group by defining a memory percentage. If memory for the default_group is set to 20%, running queries within default_group will share the 20% memory specified for it. If the total memory specified across all resource groups is less than 100%, unused memory can be used as needed by running queries.

CPU and memory percentages are also elastic, meaning that each group can use more or less depending on the need and overall system utilization.

Table 7-1 shows an example of resource groups created to manage workload in the cluster.

Based on Table 7-1, at least 20% of system CPU is made available to the default_group. Suppose that the default_group is running under a heavy workload and using all of its assigned CPU. However, adhoc_group and ETL_group are relatively idle, so the total system CPU usage is only 30%. The CPU allowance for default_group can elastically increase to utilize some or all of the 70% of idle system CPU. When the system workload becomes busy again, default_group will release the extra CPU back to the other resource groups. Memory sharing has similarities to CPU sharing, with unassigned memory available for sharing across all resource groups. For the just-described scenario, only 70% of total system memory is specifically assigned, so the 30% unassigned memory is opportunistically utilized across the three resource groups.

To use resource groups, set the gp_resource_manager parameter to groups with the following command:

gpconfig -v gp_resource_manager -c groups

Then restart the database. After the database is restarted, you can view and modify resource groups through the command line or through the Workload Management tab on the GPCC user interface, as seen in Figure 7-7.

Figure 7-7. Viewing resource groups in Workload Management view

To make things easier for users who want to transition from resource queues to groups, GPCC can convert existing queues into matching resource groups. Figure 7-8 shows the GPCC convert workflow. The converted resource group carries over any users and concurrency settings. CPU and memory settings are automatically set based on queue priority and memory spill ratio. You can modify these settings at will after the conversion step is complete.

Figure 7-8. Creating resource groups in Workload Management view

Basic Command and Control

Following are the utilities you will first encounter as they are used to establish and start and stop the database:

Initializing a new cluster: gpinitsystem

Along with configuration files, this utility is used to create the Greenplum cluster. Configuration parameters include the use of a standby master, the number of data segments, whether to enable mirroring (recommended), the location of the data directories, port ranges, and the cluster name.

Starting the cluster: gpstart

Orchestrates the parallel startup of all the individual PostgreSQL instances across the master, primary, and mirror segments in the Greenplum cluster.

Stopping the cluster: gpstop

A flexible utility allowing DBAs to stop, restart, or, most often, reload runtime settings found in the pg_hba.conf and postgresql.conf files without the need to take the database offline.

System Health

After the database is up and running, the following utilities are used to determine the overall health of a running cluster and address any issues that are reported:

Monitoring the state of the cluster: gpstate

Quickly provides information regarding the state of your Greenplum cluster, including the validity of the master, standby master, primary, and mirror segments.

Recovering from a primary segment failure: gprecoverseg

One of Greenplum’s features is high availability. This is partly accomplished by mirroring the primary data segments in case of hardware failures (disk, power, etc.). When a primary segment fails-over to the mirror, the cluster continues functioning. There comes a time when the original primary segment needs to be reintroduced to the cluster. This utility synchronizes the primary with the mirror based on the transactions that have occurred since the primary failed. Recovery is performed online, while the system is available to end users.

Performing system catalog maintenance: gpcheckcat

Checks the Greenplum catalog for inconsistencies across a variety of scenarios including access control lists (ACLs), duplicate entries, foreign keys, mirroring, ownership, partition integrity, and distribution policy.

Performing ANALYZE selectively in parallel: analyzedb

Maintaining a database at top performance involves generating good query plans. Plans are determined by the statistics that exist on a table, which are generated via the ANALYZE command. This utility can target a database or group of tables and perform the ANALYZE in parallel as well as skip analysis of tables that have not changed for append-optimized tables.

Cleaning up and reclaiming space: vacuumdb

As you use the Greenplum database and rows are deleted or updated, the previous version is replaced using the model-view-controller-context (MVCC) framework. These rows that no longer have use take up space, which is reclaimed by running the VACUUM command. vacuumdb will perform this action across an entire database. It is not recommended to run the vacuum full flag, because running this type of vacuum in Greenplum should be done only to address specific issues.

Disaster Recovery and Data Replication

Greenplum provides utilities to make copies of the data and metadata to ensure business continuity in case of technical failures and to duplicate data for test and development purposes.

Backing up the database: gpbackup

This backup utility provides options for full or partial backups, which are filtered at the database, schema, or table levels. Greenplum also supports incremental backups at the partition level for AO tables. This utility is replacing gpcrondump, which was formerly the default tool for this type of operation.

Restoring the database: gprestore

You can restore Greenplum objects and data on the original cluster or a different one from which the backup was taken. Similar to gpbackup, gpdbrestore allows for selective filtering at the database, schema, and table levels and supports. This is replacing the tool gpdbrestore, which previously had been used in conjunction with gpcrondump.

Transferring data across clusters: gpcopy

Moving data and objects from one cluster to another is a common task. This utility provides users with a flexible, high-speed option that uses gpfdist and external tables. This is commonly used to move full databases to new clusters or populate a subset of data into development/sandbox environments.

Operations and System Management

Managing a clustered set of server can present challenges. Greenplum has utilities that DBAs use to help streamline this job:

Interacting with the segment servers: gpssh and gpscp

Keeping configuration files consistent across large multinode systems can be difficult. These two tools allow administrators to run commands and copy files to all nodes via the master server, without the need to directly log in to each individual segment host.

Baselining system performance: gpcheckperf

Knowing the performance of the underlying hardware can provide help to diagnose whether issues are at a hardware level or in the database software. This tool can check the performance of disk I/O, memory bandwidth, and network. Ideally, DBAs should run this before installing the Greenplum software to get a baseline of performance for the system. Running it later in the life cycle of the cluster (when the database is not running), the output can be used to determine hardware or system problems in the cluster and whether the systems are all performing equally.

Setting database configuration parameters: gpconfig

Keeping configuration files synchronized for hundreds of underlying Postgres instances is a challenge. This tool reports and can change configuration settings in the postgresql.conf file across the entire cluster.

Other Tools

Although not strictly separate utilities, Greenplum also has techniques for managing database connections and disk space utilization.

Maintaining database connections efficiently: pgbouncer

PgBouncer is the name of a Greenplum and PostgreSQL connection pooler. Any target application can be connected using pgbouncer as if it were the Greenplum master server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections. Opening and closing connections has a performance impact that using pgbouncer mitigates.

Managing disk space: diskquota

diskquota is a tool that controls disk usage by roles and schemas in both PostgreSQL and Greenplum. Originally written by Pivotal’s Heikki Linnakangas as a PostgreSQL extension, it has been incorporated into Greenplum 6. For a clear description of diskquota’s design, visit this Github repo.

After you install it, diskquotas are set by using a UDF. The first statement sets a limit of 1 GB of disk space in the gpuser schema of the current database, the second a limit of 2 GB of disk space to the role user27 in the current database. There are also monitoring functions:

select diskquota.set_schema_quota('gpuser', '1 GB');
select diskquota.set_role_quota('user27', '2 GB');