如何使用PolarDB PostgreSQL版的Global Plan Cache功能 - PolarDB

This topic describes the global plan cache (GPC) feature of PolarDB for PostgreSQL.

Background information

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

Plan cache cannot be shared across connections.
Each connection has its specific plan cache, which causes memory usage overheads.

To resolve the preceding issues, PolarDB for PostgreSQL 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 object path.
The user ID.

Prerequisites

The GPC feature is enabled for your PolarDB for PostgreSQL cluster. By default, the GPC feature is enabled.
Your PolarDB for PostgreSQL cluster runs the following engine:
- PostgreSQL 11 (revision version 1.1.28 or later)
- PostgreSQL 14 (revision version 14.9.15.0 or later)
Note
You can run one of the following statements to query the minor version of your PolarDB for PostgreSQL cluster:
- PostgreSQL 11
```
show polar_version;
```
- PostgreSQL 14
```
select version();
```

Limits

GPC supports only prepared statements and cannot be used in PL/SQL scenarios.
GPC supports only the SELECT, INSERT, UPDATE, and DELETE statements.
GPC does not support temporary tables.

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 shared buffers. Note After you change the parameter value, you must restart your cluster for the change to take effect. If the value of this parameter is smaller than or equal to 0, GPC is disabled. If the value of this parameter is larger than 0, the amount of memory specified for this parameter is reserved for GPC. If shared memory is depleted, new plan cache is temporarily stored on local disks. After GPC that is infrequently used or invalid is cleared, shared memory space is released and then the system tries to move the plan cache stored on local disks to GPC.
polar_enable_gpc_level	The types of nodes on which you want to enable GPC. The value can be changed. Valid values: 0: disables GPC. This is the default value. 1: enables GPC only on read-only nodes. 2: enables GPC on read-write nodes and read-only nodes. Note You must configure this parameter together with the `polar_gpc_mem` parameter. GPC can work as expected only if you set the `polar_gpc_mem` parameter to a value larger than 1 and this parameter to 1 or 2. If you set the `polar_gpc_mem` parameter to a value larger than 0 and this parameter to 0, only queries that used GPC can use it. New queries cannot use GPC.
polar_gpc_clean_timeout	The time interval at which infrequently used GPC is cleared. The value can be changed. Unit: seconds. Default value: 1800. Valid values: 0 to 86400.
polar_worker.gpc_clear_interval	The time interval at which invalid GPC is cleared. The value can be changed. Unit: seconds. Default value: 60. Valid values: 0 to 4294967.
polar_gpc_clean_max	The maximum amount of GPC that can be cleared at a time. The value can be changed. 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 notes

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;

Metrics:

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 of this metric frequently increases, 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 is 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;

Metrics:

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 is 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;

Metrics:

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;

Metrics:

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 GPC. Sample statement:

SELECT * FROM polar_prepared_statement;

Metrics:

is_saved: indicates whether the query plan is 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 clear invalid GPC. Sample statement:

SELECT polar_gpc_evict_invalid_gpc();

If you do not manually run the statement, the system periodically clears invalid GPC based on the interval that is specified by $polar_worker.gpc_clear_interval.

polar_gpc_evict_live_gpc

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

SELECT polar_gpc_evict_live_gpc();

If you do not manually run the statement, the system periodically clears the infrequently used GPC based on the interval that is specified by $polar_worker.gpc_clear_interval.