Extended monitoring

• 1: Managing the storage database
• 2: Managing extended monitoring on a database
• 3: Managing streaming services for extended monitoring

1 - Managing the storage database

Extended Monitoring stores your Vertica database's monitoring data in a dedicated MC storage database.

To use Extended Monitoring, you must first set up the storage database and configure it for Kafka streaming. Then, turn on Extended Monitoring for any or all monitored databases.

MC automatically configures a schema for the storage database, named dcschema, which is synced with DC tables on your monitored databases.

MC preparation

First verify that MC is not installed on the same host as a Vertica database. When Extended Monitoring is enabled, MC sharing a host with a production database can affect performance.

You must also increase the allocation of memory for the MC application server, as described in the next section. Tune the memory allocation options based on:

• The demands of your database.

• The amount of monitoring data you plan to view in MC charts at the same time.

For example, MC requires more memory to display a week of data in a chart.

Modify memory allocation

To modify memory allocation:

1. In Management Console, select the Configuration tab on the MC Settings page.

2. Modify the following fields under Application Server JVM Settings to increase the allocation of memory for the JVM:

   • Initial Heap Size: For Extended Monitoring, a minimum value of 2 GB is recommended. (The default value is 1 GB.)

   • Maximum Heap Size: For Extended Monitoring, a minimum value of 4 GB is recommended. (The default value is 2 GB.)

3. Click Apply at the top right of the page. A prompt appears to restart MC.

4. Click OK to restart MC and save your changes.

Storage database requirements

To set up storage for Extended Monitoring, your system must meet the following prerequisites:

• An available host, or available database whose Vertica version is the same version or a higher version of the database you plan to monitor.

• Configured MC for Extended Monitoring (See Prepare MC to Use Extended Monitoring.)

• Access to a deployed Kafka server (For details on installing Kafka, see the Apache Kafka site.)

Set up the storage database

To configure the storage database for Extended Monitoring, on the MC Settings page, select the MC Storage DB Setup tab. Modify the settings in each of the three areas:

1) Kafka Broker>

Enter the host name or IP addresses and ports for one or more of your deployed Kafka servers.

2) MC external storage database

Designate the storage database. You can create a new database or use an existing database.

• Create a new database: To create a new single node cluster on an available host using a Community Edition license of Vertica, choose this option. Doing so does not affect your normal Vertica license usage.

• Use an existing database known to MC: To designate a database you have already imported to MC, choose this option. If the schema 'dcschema' exists in the database, a dialog appears. Depending on your system needs, do one of the following:

  • To keep the existing schema's data, click Append. For example, if you have already used this database for Extended Monitoring storage and are reimporting it, you can use this option to retain its historical data for continued use.
  • To clear the existing schema from the database and create a fresh version of dcschema configured for Extended Monitoring storage, click Remove.

At the Database name prompt:

1. Select the database you want to use from the drop-down list.

2. To use that database for Extended Monitoring, click Prepare MC Storage database.

Advanced Streaming Options:

To change the value of the Scheduler Frame Duration, click Advanced Streaming Options. Management Console displays the Streaming Options window, which allows you to modify the Scheduler Frame Duration default that Management Console uses for Extended Monitoring..

The Scheduler Frame Duration is the amount of time given to the Kafka scheduler for each individual frame to process and run the COPY statements, after which KafkaSource terminates the COPY statements. Vertica must have enough time to complete COPY tasks within this duration.

If the frame duration is too small, you would see data loss, as the scheduler does not have sufficient time to process all the data. You may see errors or messages on Management Console’s Load page for microbatches that are not able to process the data.

On the contrary, if the frame duration is too large, the scheduler will have too much time to process the incoming data and after it has finished processing the data, it might wait for the frame duration to expire. In this case, you may see some latency in the data getting processed. In addition, the charts in Management Console may not show the data in real time and may show some latency.

You can approximate the average available time per COPY using the following equation:

TimePerCopy=(FrameDurationParallelism)/Microbatches*

This equation provides only a rough estimate. There are many factors that impact the amount of time that each COPY statement needs to run.

Vertica requires at least 100 milliseconds per COPY to function.

3) enable extended monitoring

Click Select database(s) for extended monitoring.

Restart the storage database

If you stop the storage database while streaming is enabled, streaming to the storage database stops automatically. You must re-enable streaming on the MC Storage DB Setup tab after you restart the storage database.

If streaming to the MC storage database is disabled while Extended Monitoring on your database is on, the Kafka retention policy determines how long streaming can remain disabled without data loss. See Managing streaming services for extended monitoring.

Discontinue the storage database

1. Select the Extended Monitoring tab in MC Settings.

2. Set Extended Monitoring for all databases to OFF.

3. Select the MC Storage DB Setup tab in MC Settings.

4. Click Disable Streaming in the MC External Storage Database section to de-activate your storage database.

5. Click Remove in the MC External Storage Database section to remove the MC Storage Database from MC.

6. Choose whether to keep or remove the data your storage database has collected:

   • Keep Data: Existing data will not removed. If you re-use this database for Extended Monitoring storage, you can choose to append new collected monitoring data to this existing data.

   • Remove Data: MC deletes its customized storage schema from the database.

Configure storage database memory usage

On the Resource Pools tab of the storage database, you can optionally increase the memory size of SYSQUERY and KAFKA_DEFAULT_POOL. For setting resource pool parameters in MC, see Configuring resource pools in Management Console.

• SYSQUERY: Reserved for temporary storage of intermediate results of queries against system monitoring and catalog tables. Default setting is 1G. For best performance for MC, set to 2G or higher.

• KAFKA_DEFAULT_POOL: Reserved for all queries executed by the Kafka scheduler. Default setting is 30%, which is the recommended setting. By default, queries spill to the general pool when they exceed the 30% memory size.

Manage disk space

The storage database uses a customized schema, named dcschema. You can monitor these tables on MC, using the Table Utilization chart on the storage database's Activity tab. The Table Utilization chart lists all the tables in dcschema and their details, such as row counts and column properties. You can sort by row count to determine if certain tables use more disk space on your storage database. SeeMonitoring table utilization and projections.

You should regularly drop partitions from dcschema if you have limited disk space for the MC storage database. MC does not automatically drop partitions from the storage database. For more information on dropping partitions, seeDropping partitions.

The table dc_execution_engine_profiles is partitioned by day. Because this table typically contains the most rows, as a best practice you should drop partitions from this table more often. The following example shows how you can specify partition key 2016-08-22 to drop a partition from dc_execution_engine_profiles.

=> SELECT DROP_PARTITIONS
  ('dcschema.dc_execution_engine_profiles', 2016-08-2, 2016-08-22);

Other than dc_execution_engine_profiles, all other tables in dcschema are partitioned by week. The next example shows you how you can drop a partition from the table dc_cpu_aggregate_by_minute, specifying the thirty-fourth week of 2016.

=> SELECT DROP_PARTITION
  ('dcschema.dc_cpu_aggregate_by_minute', 201634, 201634);

Manage client sessions

By default Vertica allows 50 client sessions and an additional five administrator sessions per node. If you reach the limit on the storage database, MC switches back to default monitoring, and does not use Extended Monitoring data from the storage database.

You can optionally configure the maximum number of client sessions that can run on a single database cluster node on your MC storage database's Settings page:

1. On the storage database dashboard, click the Settings page.

2. Choose the General tab.

3. Enter a value in the Maximum client sessions field. Valid values are 0–1000.

For more details about managing client connections in MC, see Configuring Management Console.

See also

• Extended monitoring
• Managing extended monitoring on a database
• Viewing load history
• Create a private key file

2 - Managing extended monitoring on a database

When you enable extended monitoring on your Vertica database, monitoring data from your database streams through Kafka servers to the MC storage database.

You can enable streaming for any or all databases that MC monitors.

Extended monitoring prerequisites

Before you can enable extended monitoring, your system must meet these prerequisites:

• The versions of MC and Vertica must match.

• Deployed Kafka server(s)

• Configured MC for extended monitoring (See Managing the storage database)

• Deployed MC storage database (See Managing the storage database)

Enable extended monitoring

1. Select the Extended Monitoring tab on MC Settings.
   
   The Extended Monitoring page displays all databases monitored by MC.

2. In the Memory Limit field for the database of your choice, set the maximum amount of memory the database can use for streaming monitoring data. For more about the memory limit, see Managing streaming services for extended monitoring.

3. In the Extended Monitoring column, select ON to enable streaming for the database of your choice.
   
   The database begins streaming its monitoring data to the Kafka server.

User access

When you change user permissions for a database using extended monitoring, the user access policy on the storage database does not automatically update. On the Extended Monitoring page, in the user access column for your database, click Refresh to sync the policy.

If you rename a Vertica user, you must re-map the user in MC Settings before refreshing the user access policy.

See also

• Extended monitoring
• Managing the storage database
• Viewing load history
• Apache Kafka integration

3 - Managing streaming services for extended monitoring

When extended monitoring is enabled, Vertica streams data from your database through Kafka servers to the storage database.

For additional parameters that optimize the performance of Kafka with Vertica, see Kafka and Vertica configuration settings.

View streaming details in MC

Click the Load tab on your database's MC dashboard to see the Data Load Activity page. On this page, the Continuous tab displays details about all continuous loading jobs for extended monitoring. You can use this page to monitor whether your extended monitoring data is streaming successfully to the MC storage database.

See Viewing load history for more about the Data Load Activity page.

Prevent data loss

The Memory Limit buffer allows you to restart the Kafka server without data loss. Vertica queues the streamed data until you restart the Kafka server. When the Kafka server remains down for an extended period of time, data loss occurs when the queue of streamed data exceeds the buffer. You set the buffer size on the Extended Monitoring tab when you enable extended monitoring for a database. See Managing extended monitoring on a database.

The Kafka retention policy determines when data loss occurs during the following scenarios:

• Restarting the MC storage database (see Managing the storage database)

• Disabling streaming on the MC storage database (see Managing the storage database)

• Restart a micro-batch (see Viewing load history)

The Kafka retention policy can allow you to restart these extended monitoring components without data loss. The Kafka server retains the data while the listed components are disabled. Data loss occurs when the streamed data exceeds the Kafka retention policy's log size or retention time limits. See the Apache Kafka documentation for how to configure the retention policy.

Changing the Kafka server

Be aware that when you change Kafka servers for extended monitoring on the MC Storage DB Setup page, you must disable all extended monitoring processes and re-configure the MC storage database. For storage database setup instructions, see Managing the storage database.

See also

• Managing extended monitoring on a database
• Viewing load history
• Kafka and Vertica configuration settings
• Apache Kafka integration