Managing Active Queries and Connections

Understanding the Active Queries and Connection Feature

For deployments that use Greenplum Data Computing Appliance (DCA) as a co-located data provider, this feature enables an administrator to monitor long running queries and terminate database connections.
In order to monitor queries and terminate connections, a database user account is needed to connect to the database with administrative privileges. In order to list the active queries for all connections and to be able to terminate connections, the database user account should be granted the superuser role.

Configuring the Greenplum Database Connection

When you first access the Active Queries and Connections module, the console cannot connect to the Greenplum database because it is not configured with the connection parameters. The following display shows an example of the console before it is configured.
Unconfigured Active Queries and Connections
Unconfigured Active Queries and Connections
To configure the module:
  1. Click HPC Management from the toolbar.
  2. Click Active Queries and Connections.
  3. Select Module Config. (Shown in the preceding display.)
  4. Specify the following parameters:
    Active Queries and Connections Configuration
    Field
    Description
    Account Name for DB Connections
    Enter the name of a database user account that has been granted the superuser role. For information about securing this user account, see Restrict the Database User Account.
    Account Password
    This field is used for storing a password. This field is also used to avoid changing a stored password:
    Don’t change
    Select this option to avoid changing a stored password.
    Set to
    Select this option and specify the password for the database user account. The password is stored in /opt/webmin/etc/actpsgqrys/config.
    Database Name
    Specifies the database name to use.
    MDW Hostname
    Specifies the host name for the Greenplum Database Master Host.
  5. Click Save.

Restrict the Database User Account

The database user account that is used for the Greenplum Database connection must have superuser privileges in order to monitor queries and terminate connections. You can increase the security for the account by configuring Greenplum Database to accept connections for the account from the machine that hosts the console.
To restrict the database user account:
  1. Edit the $MASTER_DATA_DIRECTORY/pg_hba.conf file.
  2. Add a line to the file that is similar to the following example: 
    # permit the 'sashpcadm' role access to any database from
# IP address 192.168.1.1 only and use md5 encrypted password
host   all   sashpcadm   192.168.1.1/32   md5
  3. Save and close the file.
  4. Run gpstop -u so that Greenplum Database rereads the file and applies the change.
The same database user account can be used for the active queries and connections module and the temporary table maintenance module. For more information about editing the pg_hba.conf file, see Greenplum Database Administrator Guide.

Viewing Active Queries and Connections

Once the database connection information has been configured, the active queries and connections module shows the active query and connection information. Each row provides information about a database server process.
Active Queries and Connections
Active Queries and Connections
Select the check box for a server process and click Kill Selected to terminate the queries, connection, and server process.
Copyright © SAS Institute Inc. All rights reserved.