Pivotal Knowledge Base

Follow

How to determine the value to set for "gp_max_plan_size" ?

Environment

Product Version
Pivotal Greenplum (GPDB) All versions

Purpose

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 the article for more information.

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 days.

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

Procedure

Turn on the parameter "gp_log_gang" to VERBOSE for 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.

Restrictions

Only superuser can set this parameter on session level, if the end users tends to change the parameter, he/she would receive the error message

ERROR:  permission denied to set parameter "gp_max_plan_size"

A simple workaround to resolve the issue is to create a function as "SECURITY DEFINER" on the super user account and grant the permission to the end-user. Below is an example.

  • Create the user
flightdata=# create user noaccess ;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
flightdata=# alter user noaccess with password 'noaccess';
ALTER ROLE
  • Created the function which modify the parameter as super user.
flightdata=# CREATE OR REPLACE FUNCTION plan_size_check()
flightdata-# RETURNS boolean AS $body$
flightdata$# BEGIN
flightdata$# execute 'set gp_max_plan_size to ''1kB''';
flightdata$# return 0;
flightdata$# END;
flightdata$# $body$
flightdata-#     LANGUAGE plpgsql
flightdata-#     SECURITY DEFINER
flightdata-#     VOLATILE
flightdata-#     RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
Grant permission
flightdata=# GRANT EXECUTE ON FUNCTION plan_size_check() TO noaccess;
GRANT
  • Connect as the user and set the parameter via the function
[gpadmin@gpdbsne gp4.3.8.2_13052016154112-1]$ psql -U noaccess
Password for user noaccess:  
psql (8.2.15)
Type "help" for help.

flightdata=> set gp_max_plan_size to '1kb';
ERROR:  permission denied to set parameter "gp_max_plan_size"
flightdata=>  
flightdata=> select plan_size_check();
 plan_size_check  
-----------------
 f
(1 row)

flightdata=> show gp_max_plan_Size;
 gp_max_plan_size  
------------------
 1kB
(1 row)

flightdata=> 

Comments

Powered by Zendesk