Managing depot pinning policies

Vertica evicts data from depots as needed to provide room for new data, and expedite request processing. You can pin database objects to reduce the risk of depot eviction. Three object types can be pinned: tables, projections, and table partitions.

The Depot Pinning tab lets you perform the following tasks:

• List and modify current pinning policies.

• Create and remove pinning policies.

• View tables that are frequently fetched from communal storage.

For details on pinning policies, see Managing depot caching.

Listing pinning policies

To list existing depot pinning policies:

1. Select Display Existing Pinning Policies.

2. Click Search. Vertica lists all tables that are currently pinned to the depot, under Existing Pinning Policies:

   

3. If desired, filter and sort the list of policies by setting the following fields:

Filter on:

• Schema: Full or partial name of the desired schema
• Table: Full or partial name of the desired table
• Policy Type: Table or Partition
• Policy Scope: Database Level or subcluster name
• Partitioned Table: Y (yes) or N (no)

Sort on (in ascending/descending order):

• Size in Depot: Absolute size (in MB) of cached data -% of Depot: Percentage of depot storage used by the cached data
• Total Access Count: Number of times that cached data from this table has been queried or otherwise accessed
• Last Access Time: Last time cached data of this table or partition was queried or otherwise accessed

Removing existing policies

You can also use the result set under Existing Pinning Policies to remove one or more policies.

To remove one or more table policies:

• From the policy list, select the check boxes of policies to remove.

• Click Bulk Remove Table Policies.

To remove a table's partition policies:

1. On the policy to remove, click Modify Policy.

2. In the Modify Pinning Policy dialog, perform one of the following actions:

   • Click Remove Policy on the desired policy.
   • Select the check boxes of one or more policies, then click Remove Selected Policies.

3. Click Close.

Creating pinning policies

You can create a policy that pins table data to a subcluster depot or to all database depots. You can specify the following policy types:

• Table: Pins all table data

• Partition: Pins the specified range of partition keys.

Find candidates for pinning

1. Select Create or Modify Pinning Policies.

2. Optionally filter the search by specifying a schema and the full or (for wildcard searches) partial name of a table.

3. Click Search.

You can use the filtered data to determine which tables or partitions are good candidates for depot pinning. For example, a high total access count relative to other tables (Total Access Count) might argue in favor of pinning. This can be evaluated against data storage requirements (% of Depot) and age of the cached data. For example, if pinned objects claim too much storage, a depot might be required to:

• Route large load operations directly to communal storage for processing.

• Increase frequency of evictions.

• Increase frequency of fetches from communal storage to handle queries on non-pinned objects.

All these cases can adversely affect overall database performance.

For details on how Vertica handles depot storage and turnover, see Managing depot caching.

Create a table or partition pinning policy

To create a pinning policy for a single table or table partition:

1. Under the Create or Modify Pinning Policies list , find the table to pin.

2. Click Create Policy. The Create a Pinning Policy dialog opens.

3. Select the desired policy scope, one of the following:

   • Database
   • An available subcluster

4. Select the desired policy type: Table Policy or Partition Policy

   Table Policy

   Click Create:
   

   Partition Policy (available only if the table is partitioned)

   • Enter the minimum and maximum partition keys.

     Note

     The MC shows a sample range of valid keys for this partition.

   For example:

   

   • Click Create.

     Vertica displays the new pinning policy:

   • Optionally, add more partition-level policies on the same table by setting new partition keys.

5. When finished, click Close.

If partition pinning policies on the same table specify overlapping key ranges, Vertica collates the partition ranges. For example, if you create two partition pinning policies with key ranges of 1-3 and 2-4, Vertica creates a single policy with a key range of 1-4.

Create pinning policies on multiple tables

To create a pinning policy on multiple tables:

1. On Create or Modify Pinning Policies, select the check boxes of the tables to pin.

   Note

   All checked tables must be unassigned to a pinning policy, as indicated by their Create Policy link.

2. Click Bulk Create Table Policies. The Bulk Create Table Policies dialog opens.

3. Select the desired policy scope, one of the following:

   • Database
   • subcluster (choose the desired subcluster)

4. Click Create, then click Close.

Removing a pinning policy

To remove an existing pinning policy:

1. On Create or Modify Pinning Policies, find the table with the policy to remove.

2. Click Modify Policy.

3. In the Modify Pinning Policy dialog, perform one of the following actions:

   • Click Remove Policy on the policy to remove.
   • Select the check boxes of one or more policies, then click Remove Selected Policies.

4. Click Close.

Remove pinning policies from multiple tables

To bulk-remove pinning policies from one or more tables:

1. On Create or Modify Pinning Policies, select the target table check boxes.

   Note

   All checked tables must comply with the following requirements:

   • They must be assigned to a pinning policy as indicated by their Modify Policy link.

   • Their pinning policy type must be set to Table.

2. Click Bulk Remove Table Policies. The Bulk Remove Table Policies dialog opens.

3. Click Remove, then click Close.

Viewing frequently fetched tables

You can query the depot for tables that are most frequently fetched from communal storage. This can help you quickly identify potential candidates for depot pinning:

1. Select Top num Refetched Tables(s) from Depot.

2. Specify the maximum number of results to return (by default 10), and the range of dates to query.

From the list, you can perform the following tasks:

• Create or remove pinning policies on individual tables and partitions by clicking on the desired action—Create Policy or Modify Policy.

• Select multiple tables and then remove their pinning policies. See Remove Pinning Policies from Multiple Tables.