Global Plan Cache - PolarDB - Alibaba Cloud Documentation Center

This topic describes the Global Plan Cache (GPC) feature of PolarDB for PostgreSQL(Compatible with Oracle).

Background information

In previous editions of PolarDB for PostgreSQL, plan cache can only be used on prepared statements. This causes the following issues:

Plan cache cannot be shared across connections.
Each connection has their specific plan cache, causing memory usage overheads.

To address these issues, PolarDB for PostgreSQL(Compatible with Oracle) provides the GPC feature that allows plan cache to be shared across prepared statements and connections.

GPC can significantly reduce memory usage and lower the risks of Out-of-Memory (OOM) issues for applications that send large numbers of SQL statements. GPC can also improve performance by reducing the overheads for generating execution plans.

Queries with the same query key can share the plan cache. A query key contains the following components:

The query statement.
The database ID.
The path of the object.
The ID of the user.

Usage note

GPC is supported for PolarDB for PostgreSQL(Compatible with Oracle) clusters that run PostgreSQL 11 (revision version 1.1.30 or later) and is enabled by default.

Note

You can execute the following statement to view the minor version of a PolarDB for PostgreSQL(Compatible with Oracle) cluster:

show polar_version;

Limitations

GPC supports only prepared statements and cannot be used in PL/SQL scenarios.
Only SELECT, INSERT, UPDATE, and DELETE statements are supported.
Temporary tables are not supported.

Parameters

Parameter	Description
polar_gpc_mem	The size of memory that is used to store GPC. Unit: MB. Default value: 30. The value cannot be larger than the capacity of the shared buffers. Note After you change the parameter value, you must restart your cluster for the change to take effect. When `polar_gpc_mem` is smaller than or equal to 0, GPC is disabled. If the value is larger than 0, the amount of memory specified in this parameter is reserved for GPC. When shared memory is depleted, new plan cache will be stored to local disks. It may be moved to shared memory after the shared memory is cleaned when GPC that is infrequently used or invalid is deleted.
polar_enable_gpc_level	The types of nodes on which to enable GPC. The value can be modified. Valid values: 0 (default): GPC is not enabled. 1: GPC is enabled only on read-only nodes. 2: GPC is enabled on both read-write nodes and read-only nodes. Note GPC is enabled only when `polar_gpc_mem` is larger than 0 and `polar_enable_gpc_level` is set to 1 or 2. If `polar_gpc_mem` is larger than 0 but `polar_enable_gpc_level` is set to 0, the queries that have used GPC can continue using GPC, but new queries cannot use GPC.
polar_gpc_clean_timeout	The time interval at which infrequently used GPC is deleted. The value can be modified. Unit: seconds. Default value: 1800. Valid values: 0 to 86400.
polar_worker.gpc_clear_interval	The time interval at which invalid GPC is deleted. The value can be modified. Unit: seconds. Default value: 60. Valid values: 0 to 4294967.
polar_gpc_clean_max	The maximum amount of GPC that can be deleted in batch. The value can be modified. Default value: 100. Valid values: 10 to 10000.
polar_gpc_partitions	The number of hash tables that are used to store GPC. Default value: 32. Valid values: 1 to 1024. Note After you change the parameter value, you must restart your cluster for the change to take effect.
polar_gpc_entries	The maximum number of entries that can be stored in a hash table. Default value: 1024. Valid values: 1 to 10000. Note After you change the parameter value, you must restart your cluster for the change to take effect.

Usage

polar_stat_gpc

You can query the polar_stat_gpc view to obtain the overall statistics of GPC. Sample statement:

SELECT * FROM polar_stat_gpc;

Fields:

get: the number of times that GPC matches are attempted.
hit: the number of times that GPC is matched.
store: the number of times that query plans are cached.
store_failed: the number of times that query plans failed to be cached due to insufficient memory. If the value is large, the polar_gpc_mem parameter may have been set to a value that cannot meet your business requirements.
store_exists: the number of times that local plan cache failed to be added to GPC because the plan had been written in another session.

polar_gpc_plan

You can query the polar_gpc_plan view to obtain the memory usage information about each GPC. Sample statement:

SELECT * FROM polar_gpc_plan;

Fields:

plan_id: the query plan ID.
stmt_name: the name of the prepared statement.
query: the SQL statement.
used_cnt: the number of times that the plan has been used.
last_use_time: the most recent time when the GPC is used.
is_valid: indicates whether the plan is valid.

polar_gpc_plan_mcxt

You can query the polar_gpc_plan_mcxt view to obtain the MemoryContext information about each GPC. Sample statement:

SELECT * FROM polar_gpc_plan_mcxt;

Fields:

plan_id: the query plan ID.
mcxt_name: the name of the MemoryContext.
totalspace: the total memory space.
freespace: the available memory space.
used: the used memory space.
nblocks: the number of blocks.

polar_gpc_plan_key

You can query the polar_gpc_plan_key view to obtain the query key of each GPC. Sample statement:

SELECT * FROM polar_gpc_plan_key;

Fields:

plan_id: the plan ID.
query: the SQL statement.
dbid: the database ID.
pid: the process ID.
num_params: the number of parameters of the query statement.
search_path: the path of the object.
role_id: the user ID.

polar_prepared_statement

You can query the polar_prepared_statement view to obtain information about all prepared statements of the GPC. Sample statement:

SELECT * FROM polar_prepared_statement;

Fields:

is_saved: indicates whether the query plan has been saved to GPC.
is_valid: indicates whether the plan is valid.
cacheable: indicates whether the plan can be cached.

polar_gpc_evict_invalid_gpc

You can use the polar_gpc_evict_invalid_gpc function to delete invalid GPC. Sample statement:

SELECT polar_gpc_evict_invalid_gpc();

Even if you do not manually execute the statement, the system periodically deletes invalid GPC based on the $polar_worker.gpc_clear_interval interval.

polar_gpc_evict_live_gpc

You can use the polar_gpc_evict_live_gpc function to delete GPC that is infrequently used. Sample statement:

SELECT polar_gpc_evict_live_gpc();

Even if you do not manually execute the statement, the system periodically deletes the infrequently used GPC based on the $polar_worker.gpc_clear_interval interval.