|Pivotal Greenplum (GPDB)||All versions|
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.
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.
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 gp220.127.116.11_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=>