Checking the Number of Application Connections

If the number of connections between applications and the database exceeds the maximum value, new connections cannot be established. You are advised to daily check the number of connections, release idle connections in time, or increase the allowed maximum number of connections.

Procedure

Log in as the OS user omm to the primary node of the database.
Run the following command to connect to the database:
```
gsql -d mogdb -p 8000
```
mogdb is the name of the database to be connected, and 8000 is the port number of the database primary node.

If information similar to the following is displayed, the connection succeeds:
```
gsql ((MogDB x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

mogdb=#
```
Run the following SQL statement to check the number of connections:
```
mogdb=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
```
Information similar to the following is displayed. 2 indicates that two applications are connected to the database.
```
count
-------
     2
(1 row)
```
View the allowed maximum connections.
```
mogdb=# SHOW max_connections;
```
Information similar to the following is displayed. 200 indicates the currently allowed maximum number of connections.
```
 max_connections
-----------------
 200
(1 row)
```

Exception Handling

If the number of connections in the command output is close to the value of max_connections of the database, delete existing connections or change the upper limit based on site requirements.

Run the following SQL statement to view information about connections whose state is set to idle, and state_change column is not updated for a long time.

mogdb=# SELECT * FROM pg_stat_activity where state='idle' order by state_change;

Information similar to the following is displayed:

 datid | datname  |       pid       | usesysid | usename  | application_name |  client_addr
 | client_hostname | client_port |         backend_start         | xact_start |          quer
y_start          |         state_change          | waiting | enqueue | state | resource_pool
|                    query
-------+----------+-----------------+----------+----------+------------------+---------------
-+-----------------+-------------+-------------------------------+------------+--------------
-----------------+-------------------------------+---------+---------+-------+---------------
+----------------------------------------------
 13626 | mogdb | 140390162233104 |       10 | gaussdba |                  |
 |                 |          -1 | 2016-07-15 14:08:59.474118+08 |            | 2016-07-15 14
:09:04.496769+08 | 2016-07-15 14:09:04.496975+08 | f       |         | idle  | default_pool
| select count(group_name) from pgxc_group;
 13626 | mogdb | 140390132872976 |       10 | gaussdba | cn_5002          | 10.180.123.163
 |                 |       48614 | 2016-07-15 14:11:16.014871+08 |            | 2016-07-15 14
:21:17.346045+08 | 2016-07-15 14:21:17.346095+08 | f       |         | idle  | default_pool
| SET SESSION AUTHORIZATION DEFAULT;RESET ALL;
(2 rows)

Release idle connections.

Check each connection and release them after obtaining approval from the users of the connections. Run the following SQL command to release a connection using pid obtained in the previous step:
```
mogdb=# SELECT pg_terminate_backend(140390132872976);
```
Information similar to the following is displayed:
```
mogdb=# SELECT pg_terminate_backend(140390132872976);
 pg_terminate_backend
----------------------
 t
(1 row)
```
If no connections can be released, go to the next step.
Increase the maximum number of connections.
```
gs_guc set -D /mogdb/data/dbnode -c "max_connections= 800"
```
800 is the new maximum value.
Restart database services to make the new settings take effect.

NOTE: The restart results in operation interruption. Properly plan the restart to avoid affecting users.
```
gs_om -t stop && gs_om -t start
```

Issue