Hologres:Manage connections

Overview

Query the pg_stat_activity view

select * from pg_stat_activity ;

Usage notes

Only a Superuser can view all connection information, whereas a standard user can view only their own.

Query the default maximum number of connections for an instance

Manage connections in HoloWeb

Query connection information using SQL

If you prefer to use SQL to query connection information, you can use the following methods.

1. Query the number of connections to the current database.

   You can run the following command to view the number of connections to the current database. For more information about the parameters, see Metric description.

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

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

2. View the state of each connection.

   You can view the connection state for each instance in the Hologres console, or you can query the pg_stat_activity view to get the state of all JDBC or PSQL connections by executing the following statement.

   select * from pg_stat_activity where backend_type = 'client backend' and state = '<statename>';

   In this command, statename is the state you want to query. Valid states include:

   • idle: The connection is idle, and the process is waiting for a new client command.

   • active: The connection is active, and the process is executing a query.

   • idle in transaction: The process is within a transaction but is not currently executing a query.

   • idle in transaction (aborted): The process is in a transaction that has encountered an error, and the process is not currently executing a query.

   • fastpath function call: The process is executing a fast-path function.

   • disabled: Tracking is disabled for this process because the track_activities parameter is disabled.

   For example, you can run the following command to query the idle connections of the current instance.

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

   Hologres-related components such as HoloWeb use a certain number of JDBC connections. If your total connection count is within the expected range, you can ignore these connections. However, if the number of SQL connections consistently approaches or reaches the max_connections limit, you should check your application for connection leaks. To avoid hitting the connection limit, configure a reasonable connection pool size in your application or terminate idle connections. For more information, see Terminate a connection.

3. View the number of connections for each access node.

   Query the current number of connections for each FE node of the instance. This is supported in Hologres v1.3.23 and later. FE nodes with no active connections do not appear in the query results.

   select * from hologres.hg_connections;

   The fields in the query result are described as follows:

   • fe_id: The ID of the FE node.

   • used_connections: The number of connections currently used on the FE node.

   • max_connections: The maximum number of connections for the FE node, which is the same as the value returned by the show max_connections command.

Terminate a connection

The following situations indicate that the connection limit for the system or a specific access node has been reached:

• The number of connections reaches or exceeds the value of max_connections. You can view the number of connections on the Monitoring and Alerts page in the Hologres Console.

• An error message is returned: FATAL: sorry, too many clients already connection limit exceeded for superusers.

• An error message is returned: FATAL: remaining connection slots are reserved for non-replication superuser connections.

When you encounter the preceding situations, you can connect to the instance with a Superuser account and execute the following statement to check for an excessive number of idle connections.

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

If the query returns too many idle processes that are no longer needed, find their pid and execute the following statements to terminate them.

select pg_cancel_backend(<pid>);     -- cancel the query on the connection
select pg_terminate_backend(<pid>);  -- kill the corresponding backend connection process

-- Batch terminate backend IDLE connection processes to release connections
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%';

Reserved connections for superusers

Hologres reserves a number of connections for Superusers. The number of reserved connections varies by instance type. For details, see Instance management. These reserved connections are intended for management operations, such as terminating idle connections, especially when the maximum number of connections has been reached. The maximum number of connections for a standard user is max_connections minus the number of reserved connections. We recommend that you do not use a Superuser account for regular database operations, as this could occupy all available connections, making it impossible to perform administrative tasks, such as releasing connections.

Limit the number of connections for a single user

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

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

Automatically terminate idle connections

If the number of connections to an instance is consistently near the upper limit, your application might have a connection leak. You can enable the automatic termination of idle connections to release resources. When a connection remains idle (no queries are executed) for longer than the specified time, it is automatically disconnected.

• Prerequisites

  The automatic termination of idle connections is supported only in Hologres v0.10.25 and later. If your instance runs an earlier version, upgrade it.

• Syntax

  • Session Level

    -- Automatically disconnect a connection if it is idle for 10 minutes. The unit is milliseconds. 
    SET idle_session_timeout = 600000;

  • Database level

    -- Automatically disconnect connections to this database if they are idle for 10 minutes. The unit is milliseconds. 
    ALTER DATABASE  <db_name> SET idle_session_timeout = 600000;

    Replace db_name with the name of the database for which you want to enable automatic termination of idle connections.

Best practices for using connections

Common errors

• Why do I get the following error when my SQL connection is idle? terminating connection due to idle state timeout.

• Cause: This error occurs because the connection was idle for a period longer than the configured idle_session_timeout. To conserve resources, the server automatically terminates connections that exceed this limit.

• Solution: To resolve this, you can either reconnect to the instance or increase the idle_session_timeout value. For instructions on how to modify this setting, refer to Automatically terminate idle connections.