How to Determine the Value to set for "gp_max_plan

Products

VMware Tanzu Greenplum Greenplum Pivotal Data Suite Non Production Edition VMware Tanzu Data Suite VMware Tanzu Data Suite

Issue/Introduction

There are some memory allocations that are not controlled by statement_mem or resource queues. Memory needed for query plans is one such thing and leaving this as unlimited may potentially cause an out of memory failure. Please refer to configuration parameter document , by default the value is "0" which means that the size of the plan is not monitored.

To restrict the growth of query plan, you need to set parameter "gp_max_plan_size" on the database. Note that the memory for the planned size is still counted against gp_vmem_protect_limit though stement_men and resource queues do not apply.

Determining the value for "gp_max_plan_size" that should work for any environment is difficult as every business has different logic and query, therefore the value for "gp_max_plan_size" should be assigned by the application & DBA of the organization based on the way the query is designed and performing under a normal business day.

In this article, we are discussing the procedure to determine the appropriate value for gp_max_plan_size specific to your database environment.

Environment

Greenplum (GPDB) all versions

Resolution

Turn on the parameter "gp_log_gang" to VERBOSE for a couple of days using:

gpconfig -c gp_log_gang -v VERBOSE --skipvalidation

Monitor the query plan size for few days to get a sample data around the size of query plans. We recommend monitoring for days where the system is heavily used. Below is an example for collecting query plan size information from the database log files:

[gpadmin@smdw pg_log]$ grep -i "Query plan size to dispatch" < master-logfile-under-pg_logs-folder > | cut -d',' -f 19 | sort |  uniq -c | sort -k1 -nr
     46 "Query plan size to dispatch: 9KB"
     32 "Query plan size to dispatch: 3KB"
     18 "Query plan size to dispatch: 14KB"
     11 "Query plan size to dispatch: 7KB"
      9 "Query plan size to dispatch: 8KB"
      3 "Query plan size to dispatch: 4KB"
[gpadmin@smdw pg_log]$

Above the most queries are using 9 KB (around 46 queries), but the maximum is 14 KB (follow the same for the rest of the days' logs).

The memory that is shown above is calculated as:

Query plan size to dispatch = (uncompressed_plan_size) * (number of slices in the query)

See that the entire plan is replicated for each slice, which means that the plan is directly proportional to the number of slices.

In this particular environment, the query plan does not exceed more than 14 KB so for example, "gp_max_plan_size" = 50 MB would be sufficient to ensure the database runs uninterruptedly.

Setting the values of "gp_max_plan_size" too high (e.g. 1 GB) when the RAM on the server is small ( for eg.s 64G ) is not recommended since when summing up with a number of primaries in the host (like 8), the total memory used by the plan is 1 * 8 = 8 GB on the host.

Note: We have not considered the role switch of the segments, that is the mirror taken the role of the primary. In this case, the memory used by the plan would increase since a number of primaries are running on the host has increased.