An Error Indicating Too Many Client Connections Is Reported When a User Connects to a GaussDB(DWS) Database

Symptom

An error indicating too many client connections is reported when a user connects to a GaussDB(DWS) database.

• When a user uses an SQL client tool, such as gsql, to connect to a database, the following error information is displayed:

  1	FATAL: Already too many clients, active/non-active/reserved: 5/508/3.

• When the user uses multiple clients to concurrently connect to the database, the following error information is displayed:

  1 2	[2019/12/25 08:30:35] [ERROR] ERROR: pooler: failed to create connections in parallel mode for thread 140530192938752, Error Message: FATAL: dn_6001_6002: Too many clients already, active/non-active: 468/63. FATAL: dn_6001_6002: Too many clients already, active/non-active: 468/63.

Possible Causes

1. The number of current database connections exceeds the upper limit.

   In the error information, the value of non-active indicates the number of idle connections. For example, if the value of non-active is 508, there are 508 idle connections.

2. The upper limit of connections is set when the user is created.

   If the number of connections does not reach the upper limit, the possible cause is that the maximum number of connections is set when the user is created.

Handling Procedure

You can preferentially use the following methods to rectify the fault:

1. Release all non-active connections temporarily.

   1	SELECT PG_TERMINATE_BACKEND(pid) from pg_stat_activity WHERE state='idle';

2. On the GaussDB(DWS) management console, set parameter session_timeout, which controls the timeout period of idle sessions. After an idle session's timeout period exceeds the specified value, the server automatically closes the connection.

   The default value of parameter session_timeout is 600 seconds. The value 0 indicates that the timeout limit is disabled. You are not advised to set session_timeout to 0.

   The procedure for setting parameter session_timeout is as follows:

1. Log in to the GaussDB(DWS) management console.
2. In the navigation tree on the left, click Cluster Management.
3. In the cluster list, find the target cluster and click its name. The Basic Information page is displayed.
4. Click the Parameter Modifications tab and modify the value of parameter session_timeout. Then click Save.
5. In the Modification Preview dialog box, confirm the modification and click Save.

If the preceding methods cannot meet service requirements, perform the following operations:

If the number of database connections exceeds the maximum, perform the following operations:

1. Check where the connections on the CN come from, the total number of connections, and whether the number of connections exceeds the value of max_connections. The default value is 800 for CNs and 5000 for DNs.

   1	SELECT coorname, client_addr, count(1) FROM pgxc_stat_activity group by coorname, client_addr order by coorname;

2. Check whether the value of max_connections can be increased. The adjustment policies are as follows:
   • Increasing the value of max_connections for CNs will increase the number of concurrent queries connected to DNs. Therefore, you need to increase the values of max_connections and comm_max_stream for DNs,
   • Increase the max_connections value of CNs/DNs by two times. For clusters with small values, increase it by four times.
   • To avoid failures in the preparation step, the value of max_prepared_transactions cannot be smaller than that of max_connections. You are advised to set max_prepared_transactions to a value equal to that of max_connections. In this way, each session can have a prepared transaction in waiting state.
3. Change the value of max_connections on the management console.

   On the management console, choose Basic Information, click the Parameter Modification tab, change the value of max_connections, and click Save.

   

If the maximum number of connections of a user is set, perform the following operations:

The value is specified by the CONNECTION LIMIT connlimit parameter of the CREATE ROLE command used when a user is created. After the value is specified, you can also change it through the CONNECTION LIMIT connlimit parameter of the ALTER ROLE command.

1. Use PG_ROLES to check the maximum number of connections of a specified user.

   1 2 3 4 5

   SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='role1'; rolname | rolconnlimit ---------+-------------- role1 | 10 (1 row)

2. Change the maximum number of connections of a user.

   1	ALTER ROLE role1 connection limit 20;