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 view 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. You can perform the following operations to manage connections:

Query the pg_stat_activity view

pg_stat_activity is a helpful system view. You can use pg_stat_activity to perform diagnostics on SQL queries that are being run and 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 parameters 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 that is logged on to the Hologres backend.
usename The username that is used to create the current connection.

holo_admin is the username of a built-in service account in Hologres. The connection that is automatically created by using this username is a PostgreSQL connection. This connection is required. If the number of connections to your Hologres instance does not reach the upper limit, you do not need to optimize this connection. For information about how to query the number of connections, see Query connection information by executing SQL statements.

application_name The type of the application on the client.
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 the first transaction of the process, the value of this parameter is the same as the value of the query_start parameter.
query_start The start time of the current active query. If the current connection is not active, the value of this parameter 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 not waiting for an event, the value of this parameter 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 parameter 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 not waiting for an event, the value of this parameter is NULL.
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.
  • \N: This value indicates that the process is not a user connection. 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 parameter is active, the value of this parameter is the query that is being run. If the connection is not active, the value of this parameter is the query that was last run.
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 PostgreSQL Query Engine (PQE) are also supported.

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 type. You can execute the following statement to query the default maximum number of connections to your Hologres instance. The return value is the default maximum number of connections to a single frontend (FE). The maximum number of total connections equals the maximum number of connections to a single FE multiplied by the number of FEs. For information about the maximum number of FEs for each instance type, see Instance types.
-- Query the maximum number of connections to a single FE. 
show max_connections;

Manage connections in the HoloWeb console

You can use HoloWeb to view and manage active connections in a visualized manner.
  1. Log on to the HoloWeb console. For more information, see HoloWeb quick start.
  2. In the top navigation bar, click Diagnostics and Optimization.
  3. In the left-side navigation pane, click Connectivity.
  4. On the Connectivity page, set the filters and view the information about connections.
    Note Only superusers can view information about all connections. Regular users can view only information about their own connections.
    Manage active connectionsThe 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 specify 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 connections you want to view.
    Note The connections to the database named postgres are background O&M connections. These connections can be ignored.
    Username The username 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, and may be 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. Common states include:
    • 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 terminate 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.

  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 parameters in the query results of the pg_stat_activity view.
    • 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;
    • 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 state of each connection.
    You can query the state 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. 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 that cannot be executed due to a syntax error.
    • fastpath function call: This state indicates that the process is executing a fast-path function call.
    • 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 is sufficient to meet your business requirements, you do not need to worry about these connections. If the number of SQL connections to your Hologres instance constantly approaches or reaches the upper limit, check whether a connection leak has occurred in your applications. If a connection leak has occurred, set 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.

  3. Query the number of connections to each FE.
    A Hologres instance consists of multiple FEs. Each FE independently maintains a set of connections. Loads are balanced among the FEs. Each connection belongs to an FE. If connections are unevenly distributed among FEs and the number of connections to an FE exceeds the upper limit, the system returns an exception for excessive connections. You can execute the following statement to query the number of current connections to an FE:
    -- pid specifies the process ID. The last two digits of the PID are the ID of the frontend node. 
    select mod(pid,100) as fe_id, count(*)
    from pg_stat_activity
    where backend_type='client backend'
    group by 1 order by 1;

Release a connection

In the following scenarios, the number of connections to a Hologres instance or a single frontend node of the Hologres instance has reached the upper limit:

  • The number of connections has reached or exceeds the value of the max_connections parameter. You can view the number of connections on the Alerts 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 log on to the Hologres instance as a superuser and execute the following statement to check whether an excessive number of idle connections exist:
select * from pg_stat_activity where backend_type = 'client backend' and state = 'idle';
If the query results show an excessive number of idle processes and you confirm that they are idle and useless connections, you can execute the following statements to release idle connections based on your business requirements. You can use the values of the pid parameter in the preceding query results to specify the connections that you want to release. For more information about the parameters in the statements, see the parameters in the query results of the pg_stat_activity view.
select pg_cancel_backend(<pid>); -- Cancel the query on the connection.
select pg_terminate_backend(<pid>);  --Terminate the backend.

-- Close and release the idle connections in a 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 the superusers of Hologres instances. The number of reserved connections to an instance varies based on the instance type. For more information, see Instance types. For a Hologres instance, reserved connections are used by the 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.

Set the maximum number of connections for a single user

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

Note The maximum number of connections for a single user takes effect only on regular users. This setting does not take effect on superusers. We recommend that you do not use a superuser account to connect to applications.
  1. Set the maximum number of connections to a single FE for a single account.
    • Syntax
      ALTER ROLE "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 Account overview.
      number The maximum number of connections.
    • Example
      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. To query the maximum number of connections to a single frontend node for all users, execute the following statement:
    SELECT rolname, rolconnlimit
    FROM pg_roles
    WHERE rolconnlimit <> -1;
    The following results are returned:
           rolname | rolconnlimit 
    ---------------+--------------
     p4_283813xxxx |      1
    (1 row)

Automatically release idle connections (Beta)

If the number of connections to your Hologres instance constantly approaches or reaches the upper limit, a connection leak may have occurred. 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 during which no SQL statement is executed, the connection is automatically closed.

  • Limits

    Only 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, submit a ticket or join the official DingTalk group of Hologres for technical support. For more information about how to join the DingTalk group, see Obtain online support for Hologres.

  • Syntax
    • Enable the feature for a session.
      -- If a connection remains idle for 10 minutes during which no SQL statement is executed, the connection is automatically closed. Unit: milliseconds. 
      SET idle_session_timeout = 600000;
    • Enable the feature for a database.
      -- If a connection remains idle for 10 minutes during which no SQL statement is executed, the connection is automatically closed. Unit: milliseconds. 
      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.

Read the 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 at the earliest opportunity.
    • We recommend that you release idle connections on a regular basis to prevent them from affecting your online business.
    • The connection named holo_admin is the O&M connection that is automatically generated and runs at the backend. The system releases this connection on a regular basis. You do not need to manually manage it.