Greenplum Error: "Failed to Acquire Resources on One or More Segments"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Your query fails and is instead returning the following error message: "failed to acquire resources on one or more segments".

 

Environment

Cause

The error message is a generic message and can be because of various resource constraints.

In order to find the root cause, check the segment log for the segment which fails to acquire resources.

Below are some possible causes:

• GPHOME is owned by root on some segment servers.
• Database parameters "max_connections" and "max_prepared_transactions" are set improperly. This is accompanied by the following error in primary segment logs: "FATAL","53300","sorry, too many clients already." 
• The disk is 100% full on one or more segments.
• Any other resource constraint indicated by the segment log.

Resolution

Check $GPHOME on every segment server to make sure it is owned by gpadmin.

If not, run below command to change it:

chown -R gpadmin:gpadmin <$GPHOME>

According to the Greenplum Admin Guide:

max_connections:

The maximum number of concurrent connections to the database server. In a Greenplum system, user client connections go through the Greenplum master instance only. Segment instances should allow 5-10 times the amount as the master. When you increase this parameter, max_prepared_transactions must be increased as well. For more information about limiting concurrent connections, refer to the Greenplum Database Administrator Guide. Increasing this parameter may cause Greenplum to request more shared memory.

max_prepared_transactions:

Sets the maximum number of transactions that can be in the prepared state simultaneously. Greenplum uses prepared transactions internally to ensure data integrity across the segments. This value must be at least as large as the value of max_connections on the master. Segment instances should be set to the same value as the master.

For example (if max_connections are set to 100 on master):

gpconfig -c max_prepared_transactions -v 100
gpconfig -c max_connections -v 500 -m 100

And then, restart Greenplum.

Also, clean the disk space on segments and check any other resource constraint on segments, e.g., memory, CPU, directory permission, etc.