Connection Pool Parameters
When a connection pool is used to access the database, database connections are established and then stored in the memory as objects during system running. When you need to access the database, no new connection is established. Instead, an existing idle connection is selected from the connection pool. After you finish accessing the database, the database does not disable the connection but puts it back into the connection pool. The connection can be used for the next access request.
pooler_port
Parameter description: Specifies the O&M management port of internal tools, such as cm_agent and cm_ctl. This port is used by the initial user or system administrator to connect to the database through the client.
Parameter type: integer.
Unit: none
Value range: 1 to 65535.
Default Value: 6667, if the installation does not depend on the OM component and the GUC parameter is not specified. If the OM component is installed and port is specified, the value of this parameter is the value of port plus 1. If port is not specified, the default value of this parameter is 8001 for CNs and 40001 for DNs.
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 2.
Setting suggestion: Generally, the value is set in the cluster installation configuration. You are advised not to change the value.
Risks and impacts of improper settings: If this parameter is modified, port numbers may conflict or be intercepted, causing database communications failures.
pooler_maximum_idle_time
Parameter description: Specifies the maximum amount of time that the connections can remain idle in a pool before being removed. After that, the automatic connection clearing mechanism is triggered to reduce the number of connections on each node to the value of minimum_pool_size.
Parameter type: integer.
Unit: second
Value range: 0 to 2147483647
Default value: 600 (that is, 10 minutes)
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 2. For example, if this parameter is set to 100 without a unit, it indicates 100s. If this parameter is set to 20min, it indicates 20 minutes. If the unit is required, the value must be s, min, h, or d.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the value is too large, the frequency of clearing idle connections in the pooler connection pool decreases and the recycling rate decreases. If the value is too small, the frequency of clearing idle connections in the pooler connection pool increases. As a result, services frequently establish connections, affecting performance.
minimum_pool_size
Parameter description: Specifies the minimum number of remaining connections on the node corresponding to each pooler connection pool after automatic pooler connection cleanup. This parameter is used by the automatic pooler connection cleanup function.
Parameter type: integer.
Unit: none
Value range: 1 to 65535
Default value: 50
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 2.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the value is too large, the number of clearing idle connections in the pooler connection pool decreases and the recycling rate decreases. If the value is too small, the number of clearing idle connections in the pooler connection pool increases. As a result, services frequently establish connections, affecting performance.
max_pool_size
Parameter description: Specifies the maximum number of connections between a CN and another CN/DN in a connection pool. This parameter needs to be modified when the cluster scale changes, for example, nodes are added or deleted.
Parameter type: integer
Unit: none
Value range: 1 to 65535
Default value:
-
32768 (60-core CPU/480 GB memory); 16384 (32-core CPU/256 GB memory); 8192 (16-core CPU/128 GB memory); 4096 (8-core CPU/64 GB memory); 2048 (4-core CPU/32 GB memory); 1000 (4-core CPU/16 GB memory)
Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 2.
Setting suggestion: Set this parameter based on the default value of different instance specifications. The value of this parameter must be greater than the value of max_connections. During the adjustment, the connections consumed by internal threads must be reserved.
Risks and impacts of improper settings: When the number of concurrent services is large, the connections from the CN to other CNs/DNs in the connection pool are consumed. If this parameter is set to a small value, an error is reported when the number of connections reaches the upper limit. As a result, services fail. When a CN is started, it allocates memory in advance based on the parameter value. Therefore, if the parameter value increases, the system consumes more memory resources. However, in general, the impact on the CN memory is small.
persistent_datanode_connections
Parameter description: Specifies whether to release the connection for the current session.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that the connection for the current session will not be released.
- off indicates that the connection for the current session will be released.
Default value: off
Setting method: This is a BACKEND parameter. Set it based on instructions provided in Table 2.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this parameter is enabled, a session may hold a connection but does not run a query. As a result, other queries cannot apply for a connection and an error is reported.
max_coordinators
Parameter description: Specifies the maximum number of CNs in a cluster. This parameter may need to be modified when the cluster scale changes, for example, nodes are added or deleted. During scale-out, ensure that the value of this parameter is greater than the number of CNs in the target cluster. Otherwise, the scale-out will fail. If this parameter is set to a large value during scale-in, more memory resources are consumed when the CN is started. However, in general, the impact on the CN memory is small.
Parameter type: integer.
Unit: none
Value range: 2 to 1024
Default value: 128
Setting method: This is a POSTMASTER parameter. You are advised not to modify it. If you need to modify it, set it based on instructions provided in Table 2.
Setting suggestion: Set this parameter based on the actual cluster specifications.
Risks and impacts of improper settings: If this parameter is set to a value less than the number of CNs in the cluster, node creation will fail. When a CN is started, it allocates memory in advance based on the parameter value. Therefore, if the parameter value increases, the system consumes more memory resources. However, in general, the impact on the CN memory is small.
max_datanodes
Parameter description: Specifies the maximum number of DNs in a cluster. This parameter may need to be modified when the cluster scale changes, for example, nodes are added or deleted. During scale-out, ensure that the value of this parameter is greater than the total number of DN shards in the target cluster. Otherwise, the scale-out will fail. If this parameter is set to a large value during scale-in, more memory resources are consumed when the CN is started. However, in general, the impact on the CN memory is small.
Parameter type: integer.
Unit: none
Value range: 2 to 65535
Default value: 256
Setting method: This is a POSTMASTER parameter. You are advised not to modify it. If you need to modify it, set it based on instructions provided in Table 2.
Setting suggestion: Set this parameter based on the actual cluster specifications.
Risks and impacts of improper settings: If this parameter is set to a value less than the number of DNs in the cluster, node creation will fail. When a CN is started, it allocates memory in advance based on the parameter value. Therefore, if the parameter value increases, the system consumes more memory resources. However, in general, the impact on the CN memory is small.
cache_connection
Parameter description: Specifies whether to recycle the connections of a connection pool.
Parameter type: Boolean.
Unit: none
Value range:
- on: The connections of a connection pool will be recycled.
- off: The connections of a connection pool will not be recycled.
Default value: on
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 2.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If this parameter is disabled, the connections held by the session are cleared after the session execution statement ends. As a result, the service frequently establishes connections, affecting the performance. After this parameter is enabled in the multi-database scenario, if the memory size is small and the value of max_connections is large, the memory may be insufficient and DNs cannot be started.
enable_force_reuse_connections
Parameter description: Specifies whether a session forcibly reuses a new connection.
Parameter type: Boolean.
Unit: none
Value range:
- on indicates that the new connection is forcibly used.
- off indicates that the current connection is used.
Default value: off
Setting method: This is a BACKEND parameter. Set it based on instructions provided in Table 2.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: After this parameter is enabled, the reset parameter is forcibly sent to existing connections each time before a statement is executed, which affects the performance.
pooler_connect_max_loops
Parameter description: Specifies whether to enable the connection retries to enhance stability of setting up connections in switchover scenarios. If a service fails to connect to the primary node, it will retry by attempting to connect to the standby node. If the standby node is successfully promoted to primary, the retry attempt will succeed. This parameter is used by the pooler connection retry function. This parameter specifies the total number of retry attempts. If this parameter is set to 0, retries are disabled. The service only establishes a connection to the primary node.
Parameter type: integer.
Unit: none
Value range: 0 to 20
Default value: 1
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 2.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: This parameter is an HA parameter. If this parameter is set to 0, the retry function is disabled, reducing the HA capability. If this parameter is set to a value greater than 1, the number of retries increases.
pooler_connect_interval_time
Parameter description: Specifies the interval between retries when pooler_connect_max_loops is set to a value greater than 1. This parameter is used by the pooler connection retry function. You are advised to set this parameter to a value slightly greater than the time required for primary/standby switchover in the current cluster.
Parameter type: integer.
Unit: second
Value range: 0 to 7200
Default value: 15
Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 2. For example, if this parameter is set to 100 without a unit, it indicates 100s. If this parameter is set to 20min, it indicates 20 minutes. If the unit is required, the value must be s, min, h, or d.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: This parameter is an HA parameter. If this parameter is set to a large value, the retry period is prolonged and the HA capability is reduced.
pooler_timeout
Parameter description: Specifies the timeout interval of communication between each connection in a CN's connection pool and another CN/DN.
Parameter type: integer.
Unit: second
Value range: 0 to 7200
Default value: 600 (that is, 10 minutes)
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 2. For example, if this parameter is set to 100 without a unit, it indicates 100s. If this parameter is set to 20min, it indicates 20 minutes. If the unit is required, the value must be s, min, h, or d.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the value is too small, an error is reported during data transmission and reception when the network fluctuates. If the value is too large, an error is reported during data transmission and reception when the network is disconnected. As a result, the HA capability is reduced.
pooler_connect_timeout
Parameter description: Specifies the timeout interval of connecting a CN's connection pool to another CN/DN in the same cluster.
Parameter type: integer.
Unit: second
Value range: 0 to 7200
Default value: 60 (1 minute)
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 2. For example, if this parameter is set to 100 without a unit, it indicates 100s. If this parameter is set to 20min, it indicates 20 minutes. If the unit is required, the value must be s, min, h, or d.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the value is too small, an error is reported during connection setup when the network fluctuates. If the value is too large, an error is reported during connection setup when the network is disconnected. As a result, the HA capability is reduced.
pooler_cancel_timeout
Parameter description: Specifies the timeout interval of canceling a connection by a CN's connection pool during error processing. If similar timeout occurs when an exception of the subtransaction or stored procedure is captured, the transaction containing the subtransaction or the stored procedure rolls back. If the source data from the COPY FROM operation is not consistent with that of the table structure in the target table, and the parameter value is not 0, an error is reported.
Parameter type: integer.
Unit: second
Value range: 0 to 7200. 0 (not recommended) indicates that this function is disabled and no timeout limit is set.
Default value: 15
Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 2. For example, if this parameter is set to 100 without a unit, it indicates 100s. If this parameter is set to 20min, it indicates 20 minutes. If the unit is required, the value must be s, min, h, or d.
Setting suggestion: Retain the default value.
Risks and impacts of improper settings: If the value is too small, an error is reported for cancel actions when the network fluctuates. If the value is too large, an error is reported for cancel actions when the network is disconnected. As a result, the HA capability is reduced.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
