DataWarehouse cluster Management

DWH Cluster Management feature in Amorphic allows the users to have a birds eye view of the backend DataWarehouse cluster.

Amorphic DWH Management presents the following details:

• Core cluster level details such as the cluster type, port, endpoint information and cluster maintenance details.

• A simplified view of some of the key cluster metrics Ex: CPU Utilization, DB Connection Counts, Disk Space etc.

• Facilitates cluster operations like creating snapshot, update maintenance and reboot etc.

• For redshift type DWH, this feature provides additional capabilities such as:

  • Run administrative operations on the tables Ex: Vacuuming
  • Enables user to run scheduled actions on the cluster Ex: Pause and resuming Cluster
  • Enable/disable DWH users to list schemas,tables inside an authorized tenant. For Example : If this option is disabled, users ability to perform catalog discovery will be disabled. i.e users won't be able to list all the tables or schemas under a tenant/database along with the ones the user owns. User capability of querying data will not be affected. In a non multi tenancy environment this option is enabled by default. In a multi tenancy environment all authorized users to use a specific tenant will have the catalog discovery enabled by default.

• Other Miscellaneous information such as the Cluster node types and the security configurations.

The following picture depicts the DWH Management Console in Amorphic:

Cluster Metrics

As understanding cluster metrics is a key for scaling and management of the system, Amorphic DWH management cluster provides an option to view a time line based cluster metrics.The selection console has option to filter through it for quicker selection. Once filtered, the user can quickly view all the cluster metrics. The selection criteria is a time range between 30 mins to a week. By default the management page shows the last 30 minute metrics.

Below are the available metrics:

• CPU Utilization
• Database Connection Count
• Health Status
• Network Receive Throughput
• Percentage Disk Space Used
• WLM Queue Wait Time
• WLM Running Queries

The following picture depicts the DWH Management Console in Amorphic:

Cluster Operations

Amorphic DWH management cluster console provides a handful of key operations such as:

• Creating a snapshot
• Update cluster maintenance window
• Reboot cluster
• Pause and Resume cluster ( only for Redshift type cluster )
• Running Vacuuming on tables ( only for Redshift type cluster )
• Running System Administrator queries ( only for Redshift type cluster )
• DWH Metadata Access ( only for Redshift type cluster )

The following picture depicts the DWH Management Console Actions in Amorphic:

Creating Snapshot

Management console allows users to create manual snapshots with customizable retention period of the cluster. Valid retention period is between 1-3653 days.

Update cluster maintenance window

Enables users to update the scheduled cluster maintenance window. Supported format : ddd:HH:MM-ddd:HH:MM

Rebooting Cluster

Enables users to reboot an active cluster.

Pause and Resume Cluster

Amorphic management console allows users to pause and resume their redshift cluster with customizable schedule. This option is only supported for redshift type DW clusters. The supported actions are:

• Pause Now : Pause the cluster immediately
• Pause Later : Pause the cluster at a give time stamp
• Pause and Resume Later: Schedule a pause and resume action at a defined period. Example : Pause on every friday 10:00 PM and resume on monday morning 9:00 AM
• Resume Now : Resume the cluster immediately
• Resume Later: Resume the cluster at a give time stamp
• Resume and Pause Later: Schedule a resume and pause action at a defined period. This action is similar to Pause and resume later defined above.

Vacuuming

Enables user to run vacuuming on the database tables. Currently Vacuuming is only supported on redshift cluster type backend. Amorphic does support all types of vacuuming such as FULL, SORT ONLY, DELETE ONLY AND REINDEX.

• FULL : Default option, Reclaims disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. VACUUM FULL skips the sort phase for any table that is already at least 95 percent sorted.
• SORT ONLY : Sorts the specified table without reclaiming space freed by deleted rows. - DELETE ONLY : Reclaims disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations, and compacts the table to free up the consumed space. A DELETE ONLY vacuum operation doesn't sort table data.
• REINDEX : Analyzes the distribution of the values in interleaved sort key columns, then performs a full VACUUM operation. VACUUM REINDEX isn't supported with TO threshold PERCENT.

Note

Threshold Percent is a clause that specifies the threshold above which VACUUM skips the sort phase and the target threshold for reclaiming space in the delete phase.

Administrator Queries

Enables users to run administrator level queries on the database cluster. Amorphic supports the following actions

• Query System Tables : Fetch table load, Work load management, S3 client errors and list table locks. Amorphic provides advanced querying options to enable conditional filtering of the results. Example : User can fetch work load errors based on the error code or message.
• Kill Sessions/Queries : Reclaims disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations, and compacts the table to free up the consumed space. A DELETE ONLY vacuum operation doesn't sort table data.

Query System tables

Amorphic allows querying the following system tables

• STL_LOAD_ERRORS : Contains a history of all Redshift load errors.

• STL_S3CLIENT_ERROR : Records errors encountered by a slice while loading a file from Amazon S3.

• STL_WLM_ERROR : Records all Work load management related errors as they occur.

• STL_TR_CONFLICT : Records all transaction conflicts with database tables.

• STV_SESSIONS : Lists all current active cluster connections.

• STV_RECENTS : Lists all current running queries.

• STV_LOCKS : Lists all existing table locks.

Kill Sessions/Queries

Amorphic allows user to kill the sessions and queries.

DWH Metadata Access

Amorphic allows user to Enable/disable DWH users to list schemas, tables inside an authorized tenant.

Non Multi tenancy environment

In Multi tenancy environment, user can list and choose the authorized tenant which it is a part of and update the user catalog access.