How Vertica Allots Cache for Sequencing
Vertica caching is similar for all types of sequences, including named sequences, identity sequences, and auto-increment sequences. To allot cache among the nodes in a cluster using named sequences, Vertica uses the following process.
- By default, when a session begins, an initiator node requests cache for itself and all other nodes in a cluster.
- The initiator distributes the cache to other nodes when it distributes the execution plan.
- Because the initiator requests a block of cache for all nodes, only the initiator locks the global catalog for the cache request.
This approach is optimal for large INSERT-SELECT and COPY operations. The following figure shows the initiator request for cache (with 250-K cache default).
Nodes run out of cache at different times. While executing the same query, nodes individually request additional cache as needed.
For new queries in the same session, the initiator might have an empty cache if it used all of its cache to execute the previous query execution. In this case, the initiator requests cache for all nodes.
You can change how Vertica initially allots cache by setting the configuration parameter ClusterSequenceCacheMode
to 0 (disabled). When this parameter is set to 0, all nodes in the cluster request their own cache and catalog lock. However, for initial large INSERT-SELECT and COPY operations, when the cache is empty for all nodes, each node requests cache at the same time. These multiple requests result in multiple simultaneous locks on the global catalog, which can adversely affect performance. For this reason, ClusterSequenceCacheMode
should remain set to its default value of 1 (enabled).
The following example compares how different settings of ClusterSequenceCacheMode
affect cache allotment for sequence processing. The example assumes a three-node cluster, the default 250 K cache allotment for each node, and sequence ID values that are sequential (incrementing by 1).
Workflow step |
ClusterSequenceCacheMode = 1 | ClusterSequenceCacheMode = 0 |
---|---|---|
1 |
Cache is empty for all nodes. Initiator node requests block of 250 K cache equal to for each node. |
Cache is empty for all nodes. Each node, including initiator, requests its own 250 K cache. |
2 |
Each node has cache:
Each node begins to use its cache allotment as it does its work. |
|
3 |
Initiator node and node 3 run out of cache. Node 2 only uses 250 K +1 to 400 K, 100 K of cache remains from 400 K +1 to 500 K. |
|
4 |
Executing same statement:
Executing new statement in same session, if initiator node cache is empty:
|
Executing same or new statement:
|