Monitoring Database Size for License Compliance
Your Vertica license can include a data storage allowance. The allowance can consist of data in columnar tables, flex tables, or both types of data. The
AUDIT() function estimates the columnar table data size and any flex table materialized columns. The
AUDIT_FLEX() function estimates the amount of
__raw__ column data in flex or columnar tables. In regards to license data limits, data in
__raw__ columns is calculated at 1/10th the size of structured data. Monitoring data sizes for columnar and flex tables lets you plan either to schedule deleting old data to keep your database in compliance with your license, or to consider a license upgrade for additional data storage.
An audit of columnar data includes flex table real and materialized columns, but not
__raw__ column data.
Viewing Your License Compliance Status
Vertica periodically runs an audit of the columnar data size to verify that your database is compliant with your license terms. You can view the results of the most recent audit by calling the GET_COMPLIANCE_STATUS function.
=> select GET_COMPLIANCE_STATUS(); GET_COMPLIANCE_STATUS --------------------------------------------------------------------------------- Raw Data Size: 2.00GB +/- 0.003GB License Size : 4.000GB Utilization : 50% Audit Time : 2011-03-09 09:54:09.538704+00 Compliance Status : The database is in compliance with respect to raw data size. License End Date: 04/06/2011 Days Remaining: 28.59 (1 row)
Periodically running GET_COMPLIANCE_STATUS to monitor your database's license status is usually enough to ensure that your database remains compliant with your license. If your database begins to near its columnar data allowance, you can use the other auditing functions described below to determine where your database is growing and how recent deletes affect the database size.
Manually Auditing Columnar Data Usage
You can manually check license compliance for all columnar data in your database using the AUDIT_LICENSE_SIZE function. This function performs the same audit that Vertica periodically performs automatically. The AUDIT_LICENSE_SIZE check runs in the background, so the function returns immediately. You can then query the results using GET_COMPLIANCE_STATUS.
When you audit columnar data, the results include any flex table real and materialized columns, but not data in the
__raw__ column. Materialized columns are virtual columns that you have promoted to real columns. Columns that you define when creating a flex table, or which you add with
ALTER TABLE…ADD COLUMN statements are real columns. All
__raw__ columns are real columns. However, since they consist of unstructured or semi-structured data, they are audited separately.
An alternative to AUDIT_LICENSE_SIZE is to use the AUDIT function to audit the size of the columnar tables in your entire database by passing an empty string to the function. This function operates synchronously, returning when it has estimated the size of the database.
=> SELECT AUDIT(''); AUDIT ---------- 76376696 (1 row)
The size of the database is reported in bytes. The AUDIT function also allows you to control the accuracy of the estimated database size using additional parameters. See the entry for the AUDIT function in the SQL Reference Manual for full details. Vertica does not count the AUDIT function results as an official audit. It takes no license compliance actions based on the results.
The results of the AUDIT function do not include flex table data in
__raw__ columns. Use the AUDIT_FLEX function to monitor data usage flex tables.
Manually Auditing __raw__ Column Data
You can use the AUDIT_FLEX function to manually audit data usage for flex or columnar tables with a
__raw__ column. The function calculates the encoded, compressed data stored in ROS containers for any
__raw__ columns. Materialized columns in flex tables are calculated by the
AUDIT function. The AUDIT_FLEX results do not include data in the
__raw__ columns of temporary flex tables.
If audits determine that the columnar table estimates are unexpectedly large, consider schemas, tables, or partitions that are using the most storage. You can use the AUDIT function to perform targeted audits of schemas, tables, or partitions by supplying the name of the entity whose size you want to find. For example, to find the size of the online_sales schema in the VMart example database, run the following command:
=> SELECT AUDIT('online_sales'); AUDIT ---------- 35716504 (1 row)
You can also change the granularity of an audit to report the size of each object in a larger entity (for example, each table in a schema) by using the granularity argument of the AUDIT function. See the AUDIT function in the SQL Reference Manual.
Using Management Console to Monitor License Compliance
You can also get information about data storage of columnar data (for columnar tables and for materialized columns in flex tables) through the Management Console. This information is available in the database Overview page, which displays a grid view of the database's overall health.
- The needle in the license meter adjusts to reflect the amount used in megabytes.
- The grace period represents the term portion of the license.
- The Audit button returns the same information as the AUDIT() function in a graphical representation.
- The Details link within the License grid (next to the Audit button) provides historical information about license usage. This page also shows a progress meter of percent used toward your license limit.