PolarDB:Transaction-level connection pools

Prerequisites

PolarProxy V2.3.46 or later is installed.

Background information

The connection model of PolarDB for PostgreSQL(Compatible with Oracle) and requires the creation of a process for each connection. Therefore, if the client maintains a large number of long connections at the same time or frequently creates new connections, the pressure on the database increases and the performance degrades.

To improve performance in these scenarios, PolarDB for PostgreSQL(Compatible with Oracle) and provide the transaction-level connection pool feature. The transaction-level connection pool feature allows multiple frontend connections to share one backend connection. This way, the number of backend connections that are created or maintained is decreased, and the processing capability of databases is improved.

Principle

Transaction-level connection pools help reduce the number of direct connections to a database and the loads that are caused by frequent short connections.

After the transaction-level connection pool feature is enabled, the client that sends a request first connects to PolarDB PolarProxy. PolarProxy does not immediately establish a connection between the client and the backend database. PolarProxy checks whether an idle connection in the transaction-level connection pool matches the conditions specified by the request parameters such as user, dbname, and the system variable. If no idle connection matches the conditions, PolarProxy creates a new connection to the database. If an idle connection matches the conditions, PolarProxy uses the connection. After the transaction is committed, the connection is retained in the connection pool for other requests. This reduces the number of connections between PolarProxy and databases.

After the transaction-level connection pool feature is enabled, you can establish thousands of connections between clients and PolarDB PolarProxy. However, only dozens or hundreds of connections are established between PolarProxy and backend databases.

PolarDB PolarProxy does not limit the number of connections. The maximum number of connections to a PolarDB cluster endpoint varies based on the specifications of the compute nodes in backend databases. If the transaction-level connection pool feature is disabled, the system must create a connection on the primary node and each read-only node each time a client sends a request.

Procedures

Enable the transaction-level connection pool feature

1. Log on to the PolarDB console.

2. Click Clusters in the left-side navigation pane. In the upper-left corner of the Clusters page, select the region where the cluster to manage resides.

3. Find the cluster for which you want to enable the transaction-level connection pool feature and click the cluster ID.

4. In the URL section, click Configure.

5. In the Connection Pool Settings section, select Transaction-level as Connection Pool.

6. Then, click OK.

Disable the transaction-level connection pool feature

1. Log on to the PolarDB console.

2. Click Clusters in the left-side navigation pane. In the upper-left corner of the Clusters page, select the region where the cluster to manage resides.

3. Find the cluster for which you want to disable the transaction-level connection pool feature and click the cluster ID.

4. In the URL section, click Configure.

5. In the Connection Pool Settings section, select Off as Connection Pool.

6. Then, click OK.

Limits

• If you perform one of the following operations, the connection is locked until the connection is closed. The locked connection is no longer retained in the connection pool and becomes unavailable to other requests.

  • Execute a PREPARE statement.

  • Process large packets, such as the packets whose sizes exceed 16 MB.

  • copy mode

  • flush mode

  • Create a temporary table, a sequence, or a view.

  • Use a transaction.

  • Declare a cursor.

• A connection can be used for other requests. Therefore, the pid that you query by using select pg_backend_pid() may be different.

• A connection can be used for other requests. Therefore, the IP address and port number that are displayed in pg_stat_activity or SQL Explorer may be different from your actual IP address and port number.

How to select a connection pool

You can determine whether to enable the connection pool feature and select a type of connection pool based on the following recommendations:

• Your service requires a small number of connections and most of the required connections are long connections, or a well-established connection pool is available for your service. In this case, you do not need to enable the connection pool feature provided by PolarDB.

• Your service requires a large number of connections such as tens of thousands of connections, or your service is a serverless service whose connections linearly increase based on the scale-up or scale-out of business servers. In this case, you can enable the transaction-level connection pool feature if your service does not run in scenarios that are described in the "Limits" section of this topic.