• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. User Guide
  4. Connecting to a Cluster
  5. Managing Database Connections

Managing Database Connections

Scenario

This section describes how to manage database connections to optimize database performance when the number of users and applications that can connect to the database is limited.

Viewing the Maximum Number of Connections

  1. Use the SQL client tool to connect to the database in a cluster.
  2. Run the following command:

    SHOW max_connections;

    The following information shows that the maximum number of database connections is 200 by default.

    max_connections
    ----------------- 
    200
    (1 row)

Viewing the Number of Used Connections

  1. Use the SQL client tool to connect to the database in a cluster.
  2. View the number of connections in scenarios described in Table 1.

    Table 1 Viewing the number of connections

    Description

    Command

    View the maximum number of sessions connected to a specific user.

    Run the following command to view the maximum number of sessions connected to user dbadmin. -1 indicates that the number of sessions connected to user dbadmin is not limited.

    postgres=# SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='dbadmin';
     rolname  | rolconnlimit
    ----------+--------------
     dwsadmin |           -1
    (1 row)

    View the number of session connections that have been used by a user.

    Run the following command to view the number of session connections that have been used by dbadmin. 1 indicates the number of session connections that have been used by dbadmin.

    postgres=# SELECT COUNT(*) FROM V$SESSION WHERE USERNAME='dbadmin';
    
     count
    -------
         1
    (1 row)

    View the maximum number of sessions connected to a specific database.

    Run the following command to view the maximum number of sessions that are allowed to connect to database postgres. -1 indicates that the number of sessions connected to database postgres is not limited.

    postgres=# SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='postgres';
    
     datname  | datconnlimit
    ----------+--------------
     postgres |           -1
    (1 row)

    View the number of session connections that have been used by a database.

    Run the following command to view the number of session connections that have been used by postgres. 1 indicates the number of connections that have been used by postgres.

    postgres=# SELECT COUNT(*) FROM PG_STAT_ACTIVITY WHERE DATNAME='postgres';
     count 
    -------
         1
    (1 row)

    View the number of session connections that have been used by all users.

    Run the following command to view the number of session connections that have been used by all users:

    postgres=# SELECT COUNT(*) FROM V$SESSION;
     count
    -------
         10
    (1 row)