Monitoring a HAWQ System

You can monitor a HAWQ system using a variety of tools included with the system or available as add-ons.

Observing the HAWQ system day-to-day performance helps administrators understand the system behavior, plan workflow, and troubleshoot problems. This chapter discusses tools for monitoring database performance and activity.

Also, be sure to review Recommended Monitoring and Maintenance Tasks for monitoring activities you can script to quickly detect problems in the system.

Using hawq_toolkit

Use HAWQ’s administrative schema hawq_toolkit to query the system catalogs, log files, and operating environment for system status information. The hawq_toolkit schema contains several views you can access using SQL commands. The hawq_toolkit schema is accessible to all database users. Some objects require superuser permissions. Use a command similar to the following to add the hawq_toolkit schema to your schema search path:

=> SET ROLE 'gpadmin' ;
=# SET search_path TO myschema, hawq_toolkit ;

Monitoring System State

As a HAWQ administrator, you must monitor the system for problem events such as a segment going down or running out of disk space on a segment host. The following topics describe how to monitor the health of a HAWQ system and examine certain state information for a HAWQ system.

• Checking System State
• Checking Disk Space Usage
• Viewing Metadata Information about Database Objects
• Viewing Query Workfile Usage Information

Checking System State

A HAWQ system is comprised of multiple PostgreSQL instances (the master and segments) spanning multiple machines. To monitor a HAWQ system, you need to know information about the system as a whole, as well as status information of the individual instances. The hawq state utility provides status information about a HAWQ system.

Viewing Master and Segment Status and Configuration

The default hawq state action is to check segment instances and show a brief status of the valid and failed segments. For example, to see a quick status of your HAWQ system:

$ hawq state -b

You can also display information about the HAWQ master data directory by invoking hawq state with the -d option:

$ hawq state -d <master_data_dir>

Checking Disk Space Usage

Checking Sizing of Distributed Databases and Tables

The hawq_toolkit administrative schema contains several views that you can use to determine the disk space usage for a distributed HAWQ database, schema, table, or index.

Viewing Disk Space Usage for a Database

To see the total size of a database (in bytes), use the hawq_size_of_database view in the hawq_toolkit administrative schema. For example:

=> SELECT * FROM hawq_toolkit.hawq_size_of_database
     ORDER BY sodddatname;

Viewing Disk Space Usage for a Table

The hawq_toolkit administrative schema contains several views for checking the size of a table. The table sizing views list the table by object ID (not by name). To check the size of a table by name, you must look up the relation name (relname) in the pg_class table. For example:

=> SELECT relname AS name, sotdsize AS size, sotdtoastsize
     AS toast, sotdadditionalsize AS other
     FROM hawq_toolkit.hawq_size_of_table_disk AS sotd, pg_class
   WHERE sotd.sotdoid=pg_class.oid ORDER BY relname;

Viewing Disk Space Usage for Indexes

The hawq_toolkit administrative schema contains a number of views for checking index sizes. To see the total size of all index(es) on a table, use the hawq_size_of_all_table_indexes view. To see the size of a particular index, use the hawq_size_of_index view. The index sizing views list tables and indexes by object ID (not by name). To check the size of an index by name, you must look up the relation name (relname) in the pg_class table. For example:

=> SELECT soisize, relname AS indexname
     FROM pg_class, hawq_size_of_index
   WHERE pg_class.oid=hawq_size_of_index.soioid
     AND pg_class.relkind='i';

Viewing Metadata Information about Database Objects

HAWQ uses its system catalogs to track various metadata information about the objects stored in a database (tables, views, indexes and so on), as well as global objects including roles and tablespaces.

Viewing the Last Operation Performed

You can use the system views pg_stat_operations and pg_stat_partition_operations to look up actions performed on a database object. For example, to view when the cust table was created and when it was last analyzed:

=> SELECT schemaname AS schema, objname AS table,
     usename AS role, actionname AS action,
     subtype AS type, statime AS time
   FROM pg_stat_operations
   WHERE objname='cust';

 schema | table | role | action  | type  | time
--------+-------+------+---------+-------+--------------------------
  sales | cust  | main | CREATE  | TABLE | 2010-02-09 18:10:07.867977-08
  sales | cust  | main | VACUUM  |       | 2010-02-10 13:32:39.068219-08
  sales | cust  | main | ANALYZE |       | 2010-02-25 16:07:01.157168-08
(3 rows)

Viewing the Definition of an Object

You can use the psql \d meta-command to display the definition of an object, such as a table or view. For example, to see the definition of a table named sales:

=> \d sales

Append-Only Table "public.sales"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 year   | integer | 
 qtr    | integer | 
 day    | integer | 
 region | text    | 
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Distributed by: (id)

Viewing Query Workfile Usage Information

The HAWQ administrative schema hawq_toolkit contains views that display information about HAWQ workfiles. HAWQ creates workfiles on disk if it does not have sufficient memory to execute the query in memory. This information can be used for troubleshooting and tuning queries. The information in the views can also be used to specify the values for the HAWQ configuration parameters hawq_workfile_limit_per_query and hawq_workfile_limit_per_segment.

Views in the hawq_toolkit schema include:

• hawq_workfile_entries - one row for each operator currently using disk space for workfiles on a segment
• hawq_workfile_usage_per_query - one row for each running query currently using disk space for workfiles on a segment
• hawq_workfile_usage_per_segment - one row for each segment where each row displays the total amount of disk space currently in use for workfiles on the segment

HAWQ Error Codes

The following section describes SQL error codes for certain database events.

SQL Standard Error Codes

The following table lists all the defined error codes. Some are not used, but are defined by the SQL standard. The error classes are also shown. For each error class there is a standard error code having the last three characters 000. This code is used only for error conditions that fall within the class but do not have any more-specific code assigned.

The PL/pgSQL condition name for each error code is the same as the phrase shown in the table, with underscores substituted for spaces. For example, code 22012, DIVISION BY ZERO, has condition name DIVISION_BY_ZERO. Condition names can be written in either upper or lower case.

Note: PL/pgSQL does not recognize warning, as opposed to error, condition names; those are classes 00, 01, and 02.