1. Checking System State: The gpstate utility can be used to identify failed segments. A Greenplum Database system will incur performance degradation when it has segment instances down because it requires some hosts to pick up the processing responsibilities of the downed segments. Failed segments can be an indicator of some type of hardware failure, such as a failed disk drive or network card. Greenplum Database provides the hardware verification tool gpcheckperf to help identify segment hosts that have hardware issues. 2. 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. 3. 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 pg_resqueue_status - Deprecated system catalog view. For example: SELECT * FROM gp_tookit.gp_resqueue_status; 4. 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 Performance Monitor also collects query and system utilization metrics. See the Greenplum Database Performance Monitor Guide for information on enabling Greenplum Performance Monitor. |
|
|