Hologres:Manage connections

Overview

Query the pg_stat_activity view

select * from pg_stat_activity ;

Usage notes

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

• In SQL query results, holo_admin is the username of a system account. The account is used to manage backend processes. The connections created by using the holo_admin account are released on a regular basis. You can ignore the connections.

Query the default maximum number of connections to a Hologres instance

Manage connections in the HoloWeb console

Query connection information by executing SQL statements

You can execute SQL statements to query connection information.

1. 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'
     AND     usename != 'holo_admin'
     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'
     AND     usename != 'holo_admin'
     GROUP BY datname;

2. 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.

3. 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     usename != 'holo_admin'
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.

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.

1. Configure the maximum number of connections to a single FE node for a single user.

   • 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.

   • 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; 

2. Query the maximum number of connections to a single FE node for an instance 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

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.