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. See Modify Memory Allocation. Tune these options based on:
- The demands of your database
- The amount of monitoring data you plan to view in MC charts at once.
For example, MC requires more memory to display a week of data in a chart.
Modify Memory Allocation
To modify memory allocation:
- In Management Console, select the Configuration tab on the MC Settings page.
- 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 2GB is recommended. (The default value is 1GB.)
- Maximum Heap Size: For Extended Monitoring, a minimum value of 4GB is recommended. (The default value is 2GB.)
- Click Apply at the top right of the page. A prompt appears to restart MC.
- 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:
- Vertica 8.x
- An available host, or available database whose Vertica version corresponds to the version of the database you plan to monitor. (eg. To use Extended Monitoring for a Vertica 8.1.0 database, its storage database should also be on Vertica 8.1.0.)
- Configured MC for Extended Monitoring (See MC Preparation)
- Access to a deployed Kafka server (For details on installing Kafka, see the Apache Kafka site)
Set Up the Storage Database
To set up the storage database for Extended Monitoring:
- On the MC Settings page, select the MC Storage DB Setup tab.
- In the Server field in the Kafka Broker section, enter the host name or IP addresses and ports for one or more of your deployed Kafka servers.
- Designate the storage database in the MC External Storage Database section. You can create a new database, or use an existing database.
- Create a new database: Choose this option to create a new single node cluster on an available host using a Community Edition license of Vertica. This choice does not affect your normal Vertica license usage.
- Use an existing database known to MC: Choose this option to designate a database you have already imported to MC. If the schema 'dcschema' exists in the database, a dialog appears:
- Click Append to keep the existing schema's data. For example, if you have already used this database for Extended Monitoring storage and are re-importing it, you can use this option to retain its historical data for continued use.
- Click Remove to clear the existing schema from the database, and creates a fresh version of dcschema configured for Extended Monitoring storage.
- Click Enable Streaming to enable data streaming from the Kafka server to the MC storage database.
- Turn on Extended Monitoring for your databases on the Extended Monitoring tab. SeeManaging Extended Monitoring on a Databasefor more information.
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
- Select the Extended Monitoring tab in MC Settings.
- Set Extended Monitoring for all databases to OFF.
- Select the MC Storage DB Setup tab in MC Settings.
- Click Disable Streaming in the MC External Storage Database section to de-activate your storage database.
- Click Remove in the MC External Storage Database section to remove the MC Storage Database from MC.
- 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 64M. 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 the partition key 2016-08-22 to drop a partition from dc_execution_engine_profiles.
SELECT DROP_PARTITION('dcschema.dc_execution_engine_profiles', 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);
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:
- On the storage database dashboard, click the Settings page.
- Choose the General tab.
- Input a value in the Maximum client sessions field. Valid values for the parameter are 0 to 1000.
For more details about managing client connections in MC, see Managing Client Connections.