Configuring and Using Vertica Management Console

2 Likes

over 2 years ago

1 Overview

Introduced in the ZENworks 2020 release, the Vertica database enables you to improve the performance of the Dashboard feature in ZENworks. Management Console (MC) is an in-browser tool meant to manage and monitor the Vertica database and the servers in which Vertica is installed (collectively known as a Vertica cluster). This GUI based tool makes maintaining the Vertica database easier and more effective. The main objective of this article is to provide a structured approach to configure and use Management Console.

Management Console allows you to perform the following activities:

Monitor the health of the database and view messages related to the health of the cluster.
View completed and failed queries and view the status of the servers (nodes).
Execute queries on the Vertica database.

2 Getting Started

Using zman commands, you can install the Vertica database in the ZENworks 2020 Appliance server. For more information on configuring the Vertica database in the zone, see https://www.novell.com/documentation/zenworks-2020/zen_vertica/data/zen_vertica.html.

The Appliance Server on which Vertica is installed can then be imported to Management Console (MC) and monitored through the user interface of the console.

2.1 System Requirements

2.1.1 Supported Platforms

SUSE Linux Enterprise Server
Version 12 SP2
Version 12 SP3
Version 12 SP4 (Vertica 9.2.1-2 and later) are supported for MC
CentOS, Ubuntu and RHEL also supported.

For more information on the supported platforms, see https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SupportedPlatforms/MCandServer.htm?tocpath=Vertica 9.3.x Supported Platforms|_____1.

2.1.2 Supported Vertica Server

Though MC can be installed before or after installing Vertica, since MC is being used to manage the existing cluster in ZENworks, it is recommended that you install MC after configuring the Vertica cluster. The steps to configure MC provided in this document are for the 9.3.x version that is compatible with the 7.2.3 or later versions of the Vertica server.

2.1.3 Supported Browsers

Internet Explorer 11: Version 11.0.9600.17843
Firefox: Version 53.0.3 (64 bit)
Chrome: Version 63.0.3239.84 (64 bit)

2.1.4 Port Requirements

22: For SSH
5450: For server (node)-to-MC communication
5444: For MC-to-node and node-to-node communication

Ensure that a firewall or iptables are not blocking communication among the cluster's database, MC, and MC agents on each cluster node.

2.1.5 RAM and Disk Space Requirements

Minimum Requirement: 4-core CPU, 4G RAM, 2G Disk Space
Recommended Requirement: 8-core CPU, 8G RAM, 2G Disk Space

Since MC requires sufficient RAM and Disk Space, it is recommended that you install MC on a different server and not on the one in which Vertica is installed.

3 Install and Configuration

3.1 Downloading MC

Download the Management Console from the Additional Downloads section at https://www.vertica.com. Click SUSE Linux Enterprise Server 11 and follow the prompts to complete downloading the package.

Figure 1: MC Download Window

3.2 Installing MC

The steps provided to install and configure MC are tested on a SLES 12 SP4 appliance server.

Run the following: rpm -ivh /<path_of_mc_rpm>/ vertica-console-9.3.0-0.x86_64.SUSE.rpm

Figure 2: Installing MC RPM

3.3 Configuring MC

Open a browser and enter the following URL:

https://<hostip / hostname>:5450

where hostip= the IP of the machine where MC installed.

where hostname= the hostname of the machine where MC installed.

For example: https://10.71.xx.xx:5450 or https://demo-mc.microfocus.com:5450

Accept the license agreement.
Configure MC by entering the username, password, unix group name, mc port, home directory, and license directory.
Configure the storage locations.
Configure the authentication. Choose from one of the following options:
- Use MC for Authentication
- Use LDAP for Authentication

3.4 Connecting to MC

Open an HTML-5 compliant browser.
Specify the IP or hostname of the device where MC was installed and the port which was specified during the configuration.

For example: https://<hostname/hostip>:port

Login with the username and password provided during the configuration.
Figure 3: MC Console

3.5 Importing a Vertica Database Cluster
1. After connecting to MC, click Import a Vertica Database Cluster.
  Figure 4: Home page before importing Vertica cluster
2. Enter the IP of the Vertica Database cluster and click Next.
Figure 5: Import Wizard of Cluster IP
3. Obtain the API key from the /opt/vertica/config/apikeys.dat file available on the server in which Vertica is installed and specify the key in the API Key field in the Import Vertica screen of the console.
Figure 6: Importing cluster wizard for API Key
Load Your Data into the Vertica Database: The Data Load Activity page allows you to monitor and perform data loading jobs. Navigate to the Instance tab and click New S3 Data Load to load the data from S3 bucket to your preexisting tables.
Go to database: Displays a dynamic dashboard of your database's health and activity. Explore the tabs within this dashboard that provide more ways to monitor the database. For more information, see Monitoring the Existing Infrastructure Using MC.
Load Your Data into the Vertica Database: The Data Load Activity page allows you to monitor and perform data loading jobs. Navigate to the Instance tab and click New S3 Data Load to load the data from S3 bucket to your preexisting tables.
Go to database: Displays a dynamic dashboard of your database's health and activity. Explore the tabs within this dashboard that provide more ways to monitor the database. For more information, see Monitoring the Existing Infrastructure Using MC.
Load Your Data into the Vertica Database: The Data Load Activity page allows you to monitor and perform data loading jobs. Navigate to the Instance tab and click New S3 Data Load to load the data from S3 bucket to your preexisting tables.
Go to database: Displays a dynamic dashboard of your database's health and activity. Explore the tabs within this dashboard that provide more ways to monitor the database. For more information, see Monitoring the Existing Infrastructure Using MC.
Load Your Data into the Vertica Database: The Data Load Activity page allows you to monitor and perform data loading jobs. Navigate to the Instance tab and click New S3 Data Load to load the data from S3 bucket to your preexisting tables.
Go to database: Displays a dynamic dashboard of your database's health and activity. Explore the tabs within this dashboard that provide more ways to monitor the database. For more information, see Monitoring the Existing Infrastructure Using MC.
Specify the zenvertica database username and password. To obtain the credentials, run the zman server-role-vertica-get-credentials (srvgc) command from the Vertica cluster or you can obtain the credentials from the coreadmin service. For more information, see https://www.novell.com/documentation/zenworks-2020/zen_vertica/data/zen_vertica.html#t4aag4s3tyhk.
                            Figure 7: Importing cluster wizard for Vertica admin credentials

4     Viewing the Home Page

The following home page is displayed after importing an existing cluster.
                 Figure 8::MC Home page after importing an existing cluster

You can perform the following tasks on this page:

Provision: This section enables you to create or import a Vertica database cluster.

Manage: This section enables you to view the details of all the clusters and databases monitored by MC, and stop and remove databases.

View your Infrastructure: For a summary of all the databases and clusters currently monitored by MC, click View Your Infrastructure on the MC Home page. For more information, see Managing Existing Cluster Using MC.

MC Tools: Contains the following settings:
- MC Settings: Enables you to change MC and agent default port assignments, MC monitor settings, MC Security and Authentication settings, and so on.
- Message Center: Enables you to view messages and alerts about the state of the cluster generated based on the periodic checks performed by MC on the system’s health and performance.
- MC Diagnostics: Helps in resolving issues related to MC.
- Recent Databases: Lists all the databases that are created or imported using MC, and also lists the name of the database, IP and number of nodes. It contains the following sub-sections:
- See Fast Tasks: Helps you perform the following tasks:
  - Manage and View Your Vertica Database: Displays the overview page that enables you to manage the cluster. For more information, see Go to database.
  - Connect to Your Vertica Database Using SQL: Use MC to run SQL queries on your database from within your browser. It also contains the history of already run queries as well.
                     For example: select * nodes; (which lists the nodes of the cluster)

                      Select * from zvbundles where name=’bundle1’; (displays the bundle1 information)

                    Select * from zvbundlestatus where OBJECTUID=’****’ ; (displays the bundle status                      as per the given bundle objectuid).

                           Figure 9: Connect to SQL to run queries on connected cluster
- Load Your Data into the Vertica Database: The Data Load Activity page allows you to monitor and perform data loading jobs. Navigate to the Instance tab and click New S3 Data Load to load the data from S3 bucket to your preexisting tables.
Go to database: Displays a dynamic dashboard of your database's health and activity. Explore the tabs within this dashboard that provide more ways to monitor the database. For more information, see Monitoring the Existing Infrastructure Using MC
5 Monitoring the Existing Infrastructure Using MC

On the Databases and Clusters page or the Home page, click the database that you want to monitor, to view its Overview page. The overview page contains the following sections:
- Quick stats
- Status Summary
- System Health
- Query Synopsis
5.1 Quick Stats

The Quick Stats sidebar displayed on the right pane, provides instant alerts and information about your cluster's status.

Figure 10: Quick stats from Overview page

Quick Stats displays the following information:

Database Nodes Health: Enables you to monitor the status of the clusters. You can also view the critical nodes and version of Vertica being used. The various statuses that can be displayed are:
- Up: The nodes appear in green
  Figure 11: Nodes when the DB is UP
- Down: Nodes will appear in red with down symbol.
  
  Figure 12: Nodes when the DB is down
- Critical: Nodes will appear in yellow and a warning icon is displayed at each node. This will occur, if k-safe 0 for a 3 node cluster.
                                            Figure 13: Critical nodes
  
  Running and Queued Queries: Displays current queries in the database. Click the query values to open the Query Monitoring charts.
  
  Projections: Displays the number of total projections, unsegmented projections, and unsafe projections for the database schema with the most projections. Click any value to open the Table Tree Map chart.
  
  Disk Space Usage: Alerts the MC Admin of how many nodes have low disk space. To check the details of the device, click the number to navigate to the Manage page.
  
  Workload Analyzer: Analyzes system information retained in the SQL system tables and provides tuning recommendations, along with the cost (low, medium, or high) of running the command.
  
  I/O Wait Notices: Displays the number of nodes that in the last hour, have recorded Disk I/O waits and Network I/O waits exceeding the wait threshold (1 second for Disk and 0 seconds for Network).
  
  License Consumption: Displays the number of licenses your database uses, and the percentage of your Vertica Community Edition or Premium Edition licenses being used.
  
  Unread Messages: Display the number of unread messages and alerts for the database. This count differs from the number of total messages across all your databases. Click the value to open the Message Center.
  
  5.2 Status Summary
  Provides a general overview of the status of your cluster.
                                 Figure 14: Overview Page (with Status summary)
  
  CPU/Memory/Disk I/O: Displays the graph for average CPU usage of each node and cluster, average usage of memory of the cluster and peak disk usage and disk space usage of each node. By clicking the calendar symbol on the top, you can generate the graph for a particular time period.
  
  Database General Pool Usage: Displays the information of max queue time, average free memory and resource rejections. By clicking the calendar symbol on the top, you can generate the graph for particular time period.
  
  Threshold Notifications: Enables you to customize the threshold limit of each node and cluster for disk space, memory usage, pool usage and so on.
  
  Queries: Displays the number of queries completed and failed and number of queries running and queued. By clicking the number, we can query the list of each node.
  
  5.3 System Health
  
  Analyzes system health using a comprehensive summary of your system resource usage and node information, with configurable statistics that allow you to specify acceptable ranges of resource usage.
  
                                                           Figure 15: System Health
  
  Memory Usage: Displays the number of nodes with high and low memory usage. Adjust the slider to set the memory usage limit. For example: If you set the memory usage to 25-75, it will list the nodes that are using less than 25% memory and the nodes using more than 75% memory.
  
  CPU Usage: Displays the number of nodes with high and low CPU usage. Move the sliders to adjust the CPU usage range filter.
  
  Disk Space Usage: Displays the number of nodes with high disk space usage. Move the slider to adjust the disk usage filter. Hover the cursor on the number to see the list of nodes that have crossed disk space usage.
  
  Spread Retransmission Rate: Displays the number of nodes with high spread retransmission rates. When a retransmission rate of a node is too high, it will not communicate properly with other nodes.
  
  Reboot Rate: Displays the number of times the nodes have restarted in a specified time frame. Click the number to obtain the node restart time. Using this you can obtain information of the node, if it has stopped responding in that period of time. Adjust the slider to set the reboot rate for a particular time frame.
  
  Cluster clock skew Rate: Displays the number of nodes that exceed a clock skew threshold.
  
  5.4 Query synopsis
  
  Enables you to monitor the system query activity and resource pool usage. This page provides two modules that report system query activity and resource pool usage.
  
                                               Figure 16: Query synopsis
  
  Query statics: Displays four bar charts that provide an overview of running, queued queries, failed, and completed queries in the past 24 hours. You can group the queries based one of the following options: Resource Pools, Users and Nodes. Click a bar on the chart to view details about those queries.
  
  User Query Distribution: Displays user and system query activity. Use the dropdown in the title bar to display the type of operations that ran on the nodes in a specific sub-cluster, or on the nodes not assigned to a sub-cluster. For more details, hover over the bar chart. Adjust the slider at the bottom, to see the queries that are run in a particular time frame. Select a particular type of operation (QUERY, SET and UTILITY) to remove or add to the bar chart.
  
  6        Managing Existing Cluster Using MC
  
  6.1   Viewing Cluster Infrastructure
  
  Connect to MC (https://<hostip>:5450>) and login. Click View Your Infrastructure under the Manage section, to manage and monitor the existing cluster. The following two tabs are displayed:
  - Database and Cluster View
  - Storage View
  6.1.1 Database and Cluster View
  
  Database and Cluster View contains the following three sections:
  - Infrastructure
  - Clusters
  - Databases
  Figure 17:Database and Cluster View
  
  6.1.1.1 Infrastructure
  
  It lists the number of Vertica clusters and Vertica databases.
  
  6.1.1.2 Clusters
  By licking the cluster, the following prompt will be displayed that contains information about the cluster name, type, cluster size, database name, CPU type and the Vertica version.
-                           Figure 18: Prompt opened for Clusters
  
  The prompt also contains the following buttons:
  
  API Key: Enables you to update the API Key for secure communication with the agent for 7.1 and later versions. The API key is available at “/opt/vertica/config/apikeys.dat” in the Vertica installed machine.
  
  View: Displays the cluster summary, node status (up/down) and node list.
  
                  Figure 19: Cluster View
  Remove: Enables you to remove the cluster from MC.
  
  Create Database: As the existing cluster is already imported, the button will be in a disabled state.
  
  6.1.1.3       Databases
  
  This section contains information about the database name, type, database status, cluster name and cluster size. On clicking Databases, the following prompt will be displayed.
  
                 Figure 20: Prompt for Databases
  
  This prompt contains the following buttons:
  
  View: Displays the Overview page that has all the information about the cluster like database node health, running and queued queries, projections, disk space usage and so on. For more information, see Monitoring the Existing Infrastructure Using MC.
  
  Stop: Enables the user to stop the database. A prompt is displayed for the user to confirm the stop database action. Subsequently, another prompt is displayed informing the user that ZENworks does not allow shutdown and provides the option to force shutdown. After a force shout down, the options to remove, drop and start will be enabled.
  
                   Figure 21: Prompt showed for Databases, when Vertica cluster is down
  
  Start: This button will be enabled if the database is down and is used to start the database.
  
  Remove: Removes the database from MC without MC Admin confirmation.
  
  6.2 Storage View:
  
  The Storage view, enables the admin to obtain the database name and IP, the database size, the database mode, storage type and the database storage details.
  
                                      Figure 22: Storage View
  
  For more information, see
- https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ManagementConsole/UsingManagementConsole.htm?tocpath=Using Management Console|_____0