Checking database activity in Greenplum

Checking database activity

• Checking for Active Sessions (Workload)

• Checking for Locks (Contention)

• Checking Query Status and System Utilization

Checking for Active Sessions (Workload)

The pg_stat_activity system catalog view shows one row per server process, showing database OID, database name, process ID, user OID, user name, current query, time at which the current query began execution, time at which the process was started, and client address and port number. Querying this view can provide more information about the current workload on the system. For example:

SELECT * FROM pg_stat_activity;

This view should be queried as the database superuser to obtain the most information possible. Also note that the information does not update instantaneously.

Checking for Locks (Contention)

If a transaction is holding a lock on an object, there may be other queries that are waiting for that lock to be released before they can continue. This may appear to the user as if their query is hanging. The pg_locks system catalog view allows you to view information about outstanding locks. Examining pg_locks for ungranted locks can help identify contention between database client sessions. pg_locks provides a global view of all locks in the database system, not only those relevant to the current database. Although its relation column can be joined against pg_class.oid to identify locked relations (such as tables), this will only work correctly for relations in the current database. The pid column can be joined to the pg_stat_activity.procpid to get more information on the session holding or waiting to hold a lock. 

For example:

SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query

FROM pg_locks l, pg_class c, pg_stat_activity a WHERE l.relation=c.oid AND l.pid=a.procpid

ORDER BY c.relname;

If you are using resource queues for workload management, queries that are waiting in a queue will also show in pg_locks. To see how many queries are waiting to run from a particular resource queue, use the gp_resqueue_status system catalog view. 

For example:

SELECT * FROM gp_toolkit.gp_resqueue_status;

Checking Query Status and System Utilization

System monitoring utilities such as ps, top, iostat, vmstat, netstat and so on can be used to monitor database activity on the hosts in your Greenplum Database array. These tools can be used to help identify Greenplum Database processes (postgres processes) currently running on the system and the most resource intensive tasks with regards to CPU, memory, disk I/O, or network activity. Looking at these system statistics can help identify queries that are overloading the system by consuming excessive resources and thereby degrading database performance. Greenplum Database comes with an management tool called gpssh, which allows you to run these system monitoring commands on several hosts at once.

The Greenplum Command Center also collects query and system utilization metrics.