Monitoring a HAWQ System | Apache HAWQ (Incubating) Docs

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.
Error Code
Meaning
Constant
Class 00— Successful Completion
02001
NO ADDITIONAL DYNAMIC RESULT SETS RETURNED
no_additional_dynamic_result_sets_returned
Class 03 — SQL Statement Not Yet Complete
08001
SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION
sqlclient_unable_to_establish_sqlconnection
08004
SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION
sqlserver_rejected_establishment_of_sqlconnection
08007
TRANSACTION RESOLUTION UNKNOWN
transaction_resolution_unknown
08P01
PROTOCOL VIOLATION
protocol_violation
Class 09 — Triggered Action Exception
2201G
INVALID ARGUMENT FOR WIDTH BUCKET FUNCTION
invalid_argument_for_width_bucket_function
22018
INVALID CHARACTER VALUE FOR CAST
invalid_character_value_for_cast
22007
INVALID DATETIME FORMAT
invalid_datetime_format
22019
INVALID ESCAPE CHARACTER
invalid_escape_character
2200D
INVALID ESCAPE OCTET
invalid_escape_octet
22025
INVALID ESCAPE SEQUENCE
invalid_escape_sequence
22P06
NONSTANDARD USE OF ESCAPE CHARACTER
nonstandard_use_of_escape_character
22010
INVALID INDICATOR PARAMETER VALUE
invalid_indicator_parameter_value
22020
INVALID LIMIT VALUE
invalid_limit_value
22023
INVALID PARAMETER VALUE
invalid_parameter_value
2201B
INVALID REGULAR EXPRESSION
invalid_regular_expression
22009
INVALID TIME ZONE DISPLACEMENT VALUE
invalid_time_zone_displacement_value
2200C
INVALID USE OF ESCAPE CHARACTER
invalid_use_of_escape_character
2200G
MOST SPECIFIC TYPE MISMATCH
most_specific_type_mismatch
22004
NULL VALUE NOT ALLOWED
null_value_not_allowed
22002
NULL VALUE NO INDICATOR PARAMETER
null_value_no_indicator_parameter
22003
NUMERIC VALUE OUT OF RANGE
numeric_value_out_of_range
22026
STRING DATA LENGTH MISMATCH
string_data_length_mismatch
22001
STRING DATA RIGHT TRUNCATION
string_data_right_truncation
22011
SUBSTRING ERROR
substring_error
22027
TRIM ERROR
trim_error
22024
UNTERMINATED C STRING
unterminated_c_string
2200F
ZERO LENGTH CHARACTER STRING
zero_length_character_string
22P01
FLOATING POINT EXCEPTION
floating_point_exception
22P02
INVALID TEXT REPRESENTATION
invalid_text_representation
22P03
INVALID BINARY REPRESENTATION
invalid_binary_representation
22P04
BAD COPY FILE FORMAT
bad_copy_file_format
22P05
UNTRANSLATABLE CHARACTER
untranslatable_character
Class 23 — Integrity Constraint Violation
25003
INAPPROPRIATE ACCESS MODE FOR BRANCH TRANSACTION
inappropriate_access_mode_for_branch_transaction
25004
INAPPROPRIATE ISOLATION LEVEL FOR BRANCH TRANSACTION
inappropriate_isolation_level_for_branch_transaction
25005
NO ACTIVE SQL TRANSACTION FOR BRANCH TRANSACTION
no_active_sql_transaction_for_branch_transaction
25006
READ ONLY SQL TRANSACTION
read_only_sql_transaction
25007
SCHEMA AND DATA STATEMENT MIXING NOT SUPPORTED
schema_and_data_statement_mixing_not_supported
25P01
NO ACTIVE SQL TRANSACTION
no_active_sql_transaction
25P02
IN FAILED SQL TRANSACTION
in_failed_sql_transaction
Class 26 — Invalid SQL Statement Name
2B000
DEPENDENT PRIVILEGE DESCRIPTORS STILL EXIST
dependent_privilege_descriptors_still_exist
2BP01
DEPENDENT OBJECTS STILL EXIST
dependent_objects_still_exist
Class 2D — Invalid Transaction Termination
40002
TRANSACTION INTEGRITY CONSTRAINT VIOLATION
transaction_integrity_constraint_violation
40001
SERIALIZATION FAILURE
serialization_failure
40003
STATEMENT COMPLETION UNKNOWN
statement_completion_unknown
40P01
DEADLOCK DETECTED
deadlock_detected
Class 42 — Syntax Error or Access Rule Violation