Check application connections
If the number of connections between the application and the database exceeds the maximum value, new connections cannot be established. It is recommended to check the number of connections every day, release idle connections in time or increase the maximum number of connections.
Steps
Log in to the active database node as the operating system user omm.
Use the following command to connect to the database.
gsql -d postgres -p 8000
postgres is the name of the database to be connected, and 8000 is the port number of the master node of the database.
After the connection is successful, the system displays information similar to the following:
gsql ((openGauss 1.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=#
Execute the following SQL statement to view the number of connections.
openGauss=# SELECT count(*) FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
Information similar to the following is displayed, where 2 indicates that there are currently two applications connected to the database.
count
2
(1 row)
View the maximum number of existing connections.
openGauss=# SHOW max_connections;
The displayed information is as follows, where 200 is the current maximum number of connections.
max_connections
200
(1 row)
exception handling
If the number of connections displayed is close to the maximum number of connections max_connections of the database, you need to consider cleaning up the number of existing connections or adding new connections.
Execute the following SQL statement to check the connection information whose state field is equal to idle and whose state_change field has not been updated for a long time.
openGauss=# 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 | postgres | 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 | postgres | 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 confirm with the user of this connection whether the connection can be disconnected, or execute the following SQL statement to release the connection. Among them, pid is the value of the pid field corresponding to the idle connection in the previous query.
openGauss=# SELECT pg_terminate_backend(140390132872976);
Information similar to the following is displayed:
openGauss=# SELECT pg_terminate_backend(140390132872976);
pg_terminate_backend
t
(1 row)
If there are no releasable connections, proceed to the next step.
Increase the maximum number of connections.
gs_guc set -D /gaussdb/data/dbnode -c "max_connections= 800"
Among them, 800 is the number of newly modified connections.
Restart the database service for the new settings to take effect.
Note: Restarting the openGauss operation will cause the user to interrupt the operation. Please plan a suitable execution window before the operation.
gs_om -t stop && gs_om -t start
Top comments (0)