Best Practices for Operating HAWQ

This topic provides best practices for operating HAWQ, including recommendations for stopping, starting and monitoring HAWQ.

Best Practices Using the Command Line to Start/Stop HAWQ Cluster Members

The following best practices are recommended when using hawq start and hawq stop to manage your HAWQ cluster.

• Always use HAWQ management commands to start and stop HAWQ, instead of Postgres equivalents.
• Use hawq start cluster or hawq restart cluster to start the entire cluster, rather than starting the master and individual segments.
• If you do decide to use hawq start standby|master|segment to start nodes individually, always start the standby before the active master. Otherwise, the standby can become unsynchronized with the active master.
• When stopping a cluster, issue the CHECKPOINT command to update and flush all data files to disk and update the log file before stopping the cluster. A checkpoint ensures that, in the event of a crash, files can be restored from the checkpoint snapshot.

• Stop the entire HAWQ system by stopping the cluster on the master host: shell $ hawq stop cluster

• To stop segments and kill any running queries without causing data loss or inconsistency issues, use fast or immediate mode on the cluster:

  $ hawq stop cluster -M fast

  $ hawq stop cluster -M immediate

• Use hawq stop master to stop the master only. If you cannot stop the master due to running transactions, try using fast shutdown. If fast shutdown does not work, use immediate shutdown. Use immediate shutdown with caution, as it will result in a crash-recovery run when the system is restarted.

  $ hawq stop master -M fast

  $ hawq stop master -M immediate

• When stopping a segment or all segments, you can use the default mode of smart mode. Using fast or immediate mode on segments will have no effect since segments are stateless.

  $ hawq stop segment

  $ hawq stop allsegments

Guidelines for Cluster Expansion

This topic provides some guidelines around expanding your HAWQ cluster.

There are several recommendations to keep in mind when modifying the size of your running HAWQ cluster:

• When you add a new node, install both a DataNode and a physical segment on the new node.
• After adding a new node, you should always rebalance HDFS data to maintain cluster performance.
• Adding or removing a node also necessitates an update to the HDFS metadata cache. This update will happen eventually, but can take some time. To speed the update of the metadata cache, execute select gp_metadata_cache_clear();.
• Note that for hash distributed tables, expanding the cluster will not immediately improve performance since hash distributed tables use a fixed number of virtual segments. In order to obtain better performance with hash distributed tables, you must redistribute the table to the updated cluster by either the ALTER TABLE or CREATE TABLE AS command.
• If you are using hash tables, consider updating the default_hash_table_bucket_number server configuration parameter to a larger value after expanding the cluster but before redistributing the hash tables.

Best Practices for Monitoring a HAWQ System

The sections below provide recommendations for common monitoring tasks.

Note: If your HAWQ cluster is managed through the Ambari interface, use Ambari to perform the suggested monitoring tasks.

Database State Monitoring Activities

SELECT * FROM gp_segment_configuration
WHERE status <> ‘u’;

Hardware and Operating System Monitoring

Data Maintenance

SELECT * FROM hawq_toolkit.hawq_stats_missing;

Database Maintenance

VACUUM <table>;

ANALYZEDB -d <database> -a

REINDEXDB -s

Patching and Upgrading