Manage connections - Hologres - Alibaba Cloud Documentation Center

This topic describes how to manage and perform diagnostics on the connections to a Hologres instance.

Overview

Hologres is compatible with PostgreSQL and allows you to query the pg_stat_activity view to obtain the information about connections to a Hologres instance. This helps you analyze the status of the connections to the instance and perform diagnostics on SQL execution. For more information, see Query the pg_stat_activity view in this topic. This topic includes the following sections:

Query the default maximum number of connections to a Hologres instance: The default maximum number of connections to a Hologres instance varies based on the instance specifications. You can execute an SQL statement to query the default maximum number of connections to your Hologres instance.
Manage connections in the HoloWeb console: You can use HoloWeb to view and manage active connections to your Hologres instance. For example, you can close a connection.
Query connection information by executing SQL statements: You can query the number of connections to an instance or a database, query the status of each connection, and close idle connections. This helps you better manage your Hologres instance.
Release a connection: You can execute SQL statements to release specified connections.
Reserve connections for superusers: If the number of connections to an instance reaches the upper limit, you can use a reserved connection to connect to the instance and manage the connections to the instance as a superuser.
Maximum number of connections for a single user: You can configure the maximum number of connections for a single user. This prevents a user from occupying unnecessary resources due to excessive connections.
Enable the system to automatically release idle connections: You can enable the automatic release of idle connections to release connections that are not in use for a specific period of time.
Best practices: You can manage connections based on the best practices provided by Hologres.

Query the pg_stat_activity view

You can use the pg_stat_activity view to perform diagnostics on SQL queries that are being run and to troubleshoot issues. To query the information about connections to a Hologres instance and the execution information about SQL queries in the instance, execute the following statement:

select * from pg_stat_activity ;

The following table describes the fields in the query results of the pg_stat_activity view.

Field	Description
datid	The object identifier (OID) of the connected database at the Hologres backend.
datname	The name of the connected database at the Hologres backend.
pid	The ID of the process at the Hologres backend.
usesysid	The OID of the user who logs on to the Hologres backend.
usename	The username that is used to create the current connection.
application_name	The type of the application on the client. Common application types: Realtime Compute for Apache Flink (VVR version): `{client_version}_ververica-connector-hologres`. Apache Flink: `{client_version}_hologres-connector-flink`. DataWorks Data Integration that allows you to run batch synchronization tasks to read data from Hologres: `datax_{jobId}`. DataWorks Data Integration that allows you to run batch synchronization tasks to write data to Hologres: `{client_version}_datax_{jobId}`. DataWorks Data Integration that allows you to synchronize data from databases to Hologres in real time: `{client_version}_streamx_{jobId}`. HoloWeb: `holoweb`. MaxCompute that allows you to access Hologres by using external tables: `MaxCompute`. Process of reading Hologres binary logs initiated by the Holo client: `holo_client_replication`. The query content is not displayed for tasks of this type. For other applications, we recommend that you explicitly specify the `application_name` field in the connection string if an application is connected to a Hologres instance.
client_addr	The IP address of the client. The displayed IP address may have been resolved and may not be the actual IP address of the client.
client_hostname	The hostname of the client.
client_port	The port number of the client.
backend_start	The start time of the backend process.
xact_start	The start time of the current transaction of the process. If no transactions are active, an empty string is returned. If the current query is in the first transaction of the process, the value of this field is the same as that of the query_start field.
query_start	The start time of the current active query. If the current connection is not active, the value of this field is the start time of the last query.
state_change	The time when the state of the connection was last changed.
wait_event_type	The type of the event for which the backend is waiting. If the backend is waiting for no event, the value of this field is NULL. Valid values: LWLock: The backend is waiting for a lightweight lock. Lock: The backend is waiting for a heavyweight lock. The `wait_event` field indicates the type of the lock for which the backend is waiting. BufferPin: The server process is waiting to access a data buffer, and no other process is checking the data buffer. Activity: The server process is idle. This may be a system process that is waiting to be run in the main processing loop. Extension: The server process is in an extension module and is waiting to be run. Client: The server process is waiting for a query from a user application. In addition, the server is expecting an activity that is unrelated to its internal processing to happen. PC: The server process is waiting for an activity of another process on the server. Timeout: The server process is waiting for a timeout. IO: The server process is waiting for the completion of an I/O operation.
wait_event	The name of the event for which the backend is waiting. If the backend is waiting for no event, the value of this field is NULL.
state	The status of the connection. Valid values: active: The connection is active. idle: The connection is idle. idle in transaction: The connection is idle in a long-running transaction. idle in transaction (Aborted): The connection is idle in a failed transaction. \N: This value indicates that the process is not a user connection process. In most cases, this state is returned for a maintenance process at the system backend and can be ignored.
backend_xid	The identifier of the top-level transaction at the Hologres backend.
backend_xmin	The xmin scope at the backend.
query	The last query that was run at the backend. If the value of the state field is `active`, the query that is being run is displayed. If the connection is not active, the query that was last run is displayed.
backend_type	The type of the backend. Supported types include autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, startup, walreceiver, walsender, and walwriter. Backend execution components such as PQE are also supported. Note Take note of the `client backend` type. This type indicates the type of the application connection.

Usage notes

Only superusers can view information about all connections. Regular users can view only the information about their own connections.

Query the default maximum number of connections to a Hologres instance

The default maximum number of connections to a Hologres instance varies based on the instance specifications. You can use one of the following methods to query the maximum number of allowed connections to your Hologres instance. In the second method, the return value is the default maximum number of connections to a single frontend (FE) node. The maximum number of total connections equals the maximum number of connections to a single FE node multiplied by the number of FE nodes. For more information about the maximum numbers of FE nodes for different instance specifications, see Instance specifications.

Execute the following statement to query the maximum number of allowed connections to your Hologres instance. This statement is supported in Hologres V1.3.23 and later.
```
select instance_max_connections();
```
Execute the following statement to query the maximum number of allowed connections to a single FE node. You can obtain the maximum number of allowed connections to your Hologres instance by multiplying the maximum number of allowed connections to a single FE node with the number of FE nodes.
```
show max_connections;
```

Manage connections in the HoloWeb console

You can use HoloWeb to view and manage active connections in a visualized manner.

Log on to the HoloWeb console. For more information, see Connect to HoloWeb.
In the top navigation bar, click Diagnostics and Optimization.
In the left-side navigation pane, click Connectivity.

On the Connectivity page, select the desired instance and view the connection information.

Note

Only superusers can view information about connections of all instances. Regular users can view only the information about connections of their own instances.

The following table describes the parameters in the connection information.

Parameter	Description
Instance	The name of the Hologres instance.
Database	The name of the Hologres database. You can select the database whose connections you want to view. If you do not specify this parameter, the connections to all databases are displayed by default.
Database	The name of the database whose connection information is displayed. Note The connections to the database named Postgres are O&M connections that run at the backend. These connections can be ignored.
User Name	The user account that is used to create the connection.
Client Address	The IP address of the client. The displayed IP address may not be the actual IP address of the client but the outbound IP address of a router.
Application Name	The name of the application that is connected to the instance.
State	The state of the connection. Valid values: active: The connection is active. idle: The connection is idle. idle in transaction: The connection is idle in a long-running transaction. idle in transaction (Aborted): The connection is idle in a failed transaction.
Query Start	The start time of the query.
Query	The query that is executed. Note If the query statement is long, the statement may be truncated.
PID	The process ID (PID) of the query.
Operation	Kill: You can directly close one or more connections that do not meet your expectations. Details: You can click Details to view the detailed connection information.

Query connection information by executing SQL statements

You can execute SQL statements to query connection information.

Query the number of connections to the current database.
You can execute one of the following statements to query the number of connections to the current database. For more information, see the fields in the query results of the pg_stat_activity view in this topic.
- Syntax supported in Hologres V1.1 and later
```
SELECT  datname::TEXT
        ,COUNT(1) AS COUNT
FROM    pg_stat_activity
WHERE   backend_type = 'client backend'
AND     application_name != 'hologres'
GROUP BY datname::TEXT;
```
- Syntax supported in Hologres V0.10 and earlier
```
SELECT  datname
        ,COUNT(1) AS COUNT
FROM    pg_stat_activity
WHERE   backend_type = 'client backend'
AND     application_name != 'hologres'
GROUP BY datname;
```
Query the status of each connection.
You can query the status of each connection to an instance by using HoloWeb in the Hologres console. You can also execute the following statement to query all Java Database Connectivity (JDBC) or PostgreSQL connections in a specified state by querying the pg_stat_activity view:
```
select * from pg_stat_activity where backend_type = 'client backend' and state = '<statename>';
```
Replace <statename> in the statement with a state name. The parameter has the following valid values:
- idle: specifies idle connections. This state indicates that the process is waiting for a command from the client.
- active: specifies active connections. This state indicates that the process is executing a query statement.
- idle in transaction: This state indicates that the process is in a transaction but is not executing a query statement.
- idle in transaction (aborted): This state indicates that the process is in a transaction that contains a query statement. The query statement cannot be executed due to a syntax error.
- fastpath function call: This state indicates that the process is executing a fast-path function.
- disabled: This state indicates that the feature of tracking active SQL statements is disabled for the process.
For example, you can execute the following statement to query the idle connections to the current instance:
```
select * from pg_stat_activity where backend_type = 'client backend' and state = 'idle';
```
Hologres components such as HoloWeb use JDBC connections. If the maximum number of connections to your Hologres instance meets your business requirements, you do not need to worry about these connections. If the number of SQL connections to your Hologres instance comes close to or reaches the upper limit specified by max_connections, check whether a connection leak occurs in your applications. If a connection leak occurs, configure a reasonable limit on the connection pool of your applications or release idle connections. For more information about how to release idle connections, see Release a connection in this topic.
Query the number of connections to each FE node.
In Hologres V1.3.23 and later, you can execute the following statement to query the number of connections to each FE node of the Hologres instance. The statement does not return information about FE nodes that have no connections.
```
select * from hologres.hg_connections;
```
Description of fields in the returned result:
- fe_id: the ID of the FE node.
- used_connections: the number of connections that are used by the FE node.
- max_connections: the maximum number of connections allowed to an FE node. The value of this field is the same as the return value of the show max_connections command.

Release a connection

In the following scenarios, the number of connections to a Hologres instance or a single FE node of the Hologres instance reaches the upper limit:

The number of connections reaches or exceeds the value of the max_connections parameter. You can view the number of connections on the Monitoring Information tab of the instance details page in the Hologres console.
The following error message is returned: FATAL: sorry, too many clients already connection limit exceeded for superusers.
The following error message is returned: FATAL: remaining connection slots are reserved for non-replication superuser connections.

In these scenarios, you can connect to the Hologres instance as a superuser and execute the following statement to check whether excessive idle connections exist:

select * from pg_stat_activity where backend_type = 'client backend' and state = 'idle';

If the query result indicates that excessive idle processes exist and you confirm that they are useless connections, you can execute the following statements to release idle connections based on your business requirements. You can use the value of the pid field in the preceding query result to specify the connections that you want to release. For more information about the fields in the statement, see the fields in the query result of the pg_stat_activity view in this topic.

select pg_cancel_backend(<pid>); -- Cancel the query on the connection.
select pg_terminate_backend(<pid>);  -- Close connections in the backend process.

-- Close and release the idle connections in the backend process.
SELECT pg_terminate_backend(pid)
        ,query
        ,datname
        ,usename
        ,application_name
        ,client_addr
        ,client_port
        ,backend_start
        ,state
FROM    pg_stat_activity
WHERE   length(query) > 0
AND     pid != pg_backend_pid()
AND     backend_type = 'client backend'
AND     state = 'idle'
AND     application_name != 'hologres'
AND     query not like '%pg_cancel_backend%';

Reserve connections for superusers

Hologres automatically reserves connections for superusers of Hologres instances. The number of reserved connections to an instance varies based on the instance specifications. For more information, see Instance specifications. For a Hologres instance, reserved connections are used by superusers to manage connections when the number of connections to the instance reaches the upper limit. For example, superusers can close idle connections. The maximum number of connections that regular users can use equals the maximum number of connections minus the number of reserved connections. If you are a regular user, we recommend that you do not use a superuser account to manage databases. Otherwise, no connections are reserved, and connections cannot be released.

Maximum number of connections for a single user

Configure the Maximum number of connections for a single user

Hologres is compatible with PostgreSQL and allows you to configure the maximum number of connections for a single user. This prevents a single user from occupying excessive resources due to excessive connections.

You can execute the following statement to configure the maximum number of connections to a single FE node for a single user. If an instance contains multiple nodes, the maximum number of connections to the instance for a single user is calculated as follows: Maximum number of connections to a single FE node × Number of nodes.

Syntax

ALTER ROLE "Alibaba Cloud account ID" CONNECTION LIMIT <number>;

Parameters

Parameter

Description

Account ID

The user ID (UID) of the account that you want to manage. The account can be an Alibaba Cloud account or a RAM user. If the account is a RAM user, add p4_ before the UID.

For more information, see Overview.

number

The maximum number of connections.

Valid values: [-1 , N]. The value -1 indicates that no limit is imposed on the number of connections for a single user.

Examples
The following example shows how to set the maximum number of connections to 1 for the RAM user whose UID is 283813xxxx:
```
ALTER ROLE "p4_283813xxxx" CONNECTION LIMIT 1; 
```

Query the maximum number of connections to a single node for each user

You can execute the following statement to query the maximum number of connections to a single node that is configured for each user.

SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit <> -1;

The following result is returned:

       rolname | rolconnlimit 
---------------+--------------
 p4_283813xxxx |      1
(1 row)

Enable the system to automatically release idle connections

If the number of connections to your Hologres instance comes close to or reaches the upper limit, a connection leak may occur. You can enable the automatic release of idle connections to release the connections that are not in use for a specific period of time. If a connection remains idle for the specified period of time, the connection is automatically closed. A connection on which no SQL statement is being executed is considered to be idle.

Limits
Hologres V0.10.25 and later support the automatic release of idle connections. If the version of your Hologres instance is earlier than V0.10.25, manually upgrade your Hologres instance in the Hologres console or join the DingTalk group for technical support. For more information about how to manually upgrade your Hologres instance in the Hologres console, see Manual upgrade in the "Instance upgrades" topic. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.

Syntax

Enable the feature for a session.

-- If a connection is idle for 600,000 ms (10 minutes), the connection is automatically closed. 
SET idle_session_timeout = 600000;

Enable the feature for a database.
```
-- If a connection is idle for 600,000 ms (10 minutes), the connection is automatically closed. 
ALTER DATABASE  <db_name> SET idle_session_timeout = 600000;
```
The db_name parameter specifies the name of the database for which you want to enable the automatic release of idle connections.

Best practices

You can manage connections based on the best practices provided by Hologres.

Properly use superuser accounts.
- We recommend that you do not use a superuser account to manage your Hologres instance or connect to applications. Otherwise, if the number of connections to the instance reaches the upper limit, you cannot use the superuser account to connect to the instance.
- You can create a superuser account as the O&M account. This way, if the number of connections to the instance reaches the upper limit or if a query stops responding, you can use the O&M account to manage connections or the query in the HoloWeb console.
Configure a proper connection pool.
- For security reasons, Hologres does not automatically release connections at the backend. We recommend that you configure appropriate settings for the connection pool of your applications. This way, idle connections can be released in a timely manner.
- We recommend that you regularly release idle connections to prevent them from affecting your online business.

FAQ

What do I do if the error message terminating connection due to idle state timeout is returned when I execute an SQL statement?
Cause: A timeout period for automatically releasing idle connections is configured for the Hologres instance. After the specified timeout period expires, the connection is automatically closed. Then, this error message is returned.
Solution: Connect to the Hologres instance again, or increase the timeout period for automatically releasing idle connections. For more information about how to increase the timeout period, see Enable the system to automatically release idle connections in this topic.