General Parameters
You use these general parameters to configure Vertica.
Parameter | Description |
---|---|
Specifies how often Vertica checks the number of projection rows and whether the threshold set by For more information, see Collecting Database Statistics. Default Value: 86400 seconds (24 hours) |
|
ApplyEventsDuringSALCheck |
When enabled, Vertica uses catalog events to filter out dropped corrupt partitions during node startup. Dropping corrupt partitions can speed node recovery. When disabled, Vertica reports corrupt partitions, but takes no action. Leaving corrupt partitions in place can reset the current projection checkpoint epoch to the epoch before the corruption occurred. This parameter has no effect on unpartitioned tables. Default Value: 0 |
ApportionedFileMinimumPortionSizeKB |
Specifies the minimum portion size (in kilobytes) for use with apportioned file loads. Vertica apportions a file load across multiple nodes only if:
See also EnableApportionLoad and EnableApportionedFileLoad. Default Value: 1024 |
ARCCommitPercentage |
Sets the threshold percentage of WOS to ROS rows, which determines when to aggregate projection row counts and commit the result to the catalog. Vertica performs this action when the WOS to ROS percentage exceeds this setting. Default Value: 3 (percent) |
BlockedSocketGracePeriod |
Sets how long a session socket remains blocked while awaiting client input or output for a given query. Default Value: None (Socket blocking can continue indefinitely.) |
CatalogCheckpointPercent |
Specifies the threshold at which a checkpoint is created for the database catalog. By default, this parameter is set to 50 (percent), so when transaction logs reach 50% of the size of the last checkpoint, Vertica adds a checkpoint. Each checkpoint demarcates all changes to the catalog since the last checkpoint. Default Value: 50 (percent) |
ClusterSequenceCacheMode |
Indicates whether the initiator node requests cache for other nodes in a cluster, and then sends cache to other nodes along with the execution plan. Enabled by default. When disabled, all nodes request their own cache. Default Value: 1 (enabled) Valid Values:
|
CompressCatalogOnDisk |
Compresses the size of the catalog on disk when enabled (value set to 1 or 2). Default Value: 0 Valid Values:
Consider enabling this parameter if the catalog disk partition is small (<50 GB) and the metadata is large (hundreds of tables, partitions, or nodes). |
CompressNetworkData |
Compresses all data sent over the internal network when enabled (value set to 1). This compression speeds up network traffic at the expense of added CPU load. If the network is throttling database performance, enable compression to correct the issue. Default Value: 0 |
CopyFaultTolerantExpressions |
Indicates whether to report record rejections during transformations and proceed (true) or abort COPY operations if a transformation fails (false). Default Value: 0 (false) |
CopyFromVerticaWithIdentity |
Allows Default Value: 1 |
DatabaseHeartBeatInterval |
Determines the interval (in seconds) at which each node performs a health check and communicates a heartbeat. If a node does not receive a message within five times of the specified interval, the node is evicted from the cluster. Setting the interval to 0 disables the feature. Default Value: 120 |
DefaultTempTableLocal |
When set to 0, CREATE TEMP TABLE creates a global temporary table. Default Value: 0 When set to 1, CREATE TEMP TABLE creates a local temporary table. For more information, see Creating Temporary Tables. |
DivideZeroByZeroThrowsError |
If set to 1, returns an error if a division by zero operation is requested. Otherwise, returns 0 as the result of a division by zero operation. Default Value: 1 |
EnableApportionedChunkingInDefaultLoadParser |
Enables the built-in parser for delimited files to take advantage of both apportioned load and cooperative parse for potentially better performance. Default Value: 1 |
EnableApportionedFileLoad |
Enables automatic apportioning across nodes of file loads using COPY FROM. Vertica attempts to apportion the load if:
Setting this parameter does not guarantee that loads will be apportioned, but disabling it guarantees that they will not be. Default Value: 1 |
EnableApportionLoad |
Enables automatic apportioning across nodes of data loads using COPY WITH SOURCE. Vertica attempts to apportion the load if:
Setting this parameter does not guarantee that loads will be apportioned, but disabling it guarantees that they will not be. Default Value: 1 |
EnableBetterFlexTypeGuessing |
Enables more accurate type guessing when assigning data types to non-string keys in a flex table Default Value: 1 |
EnableCooperativeParse |
Implements multi-threaded parsing capabilities on a node. You can use this parameter for both delimited and fixed-width loads. Enabled by default. Default Value: 1 |
EnableDataTargetParallelism |
Enables multiple threads for sorting and writing data to ROS, improving data loading performance. Enabled by default. Default Value: 1 |
EnableForceOuter |
Determines whether Vertica uses a table's Default Value: 0 (forced join inputs disabled) |
EnableMetadataMemoryTracking |
Enables Vertica to track memory used by database metadata in the METADATA resource pool. Default Value: 1 |
EnableResourcePoolCPUAffinity |
Aligns queries to the resource pool of the processing CPU. When disabled (value is set to 0), queries run on any CPU, regardless of the Default Value: 1 |
EnableStorageBundling |
(Deprecated) Enables storing multiple ROS containers as a single file. Each ROS must be less than the size specified in MaxBundleableROSSizeKB. In environments with many small storage files, bundling improves the performance of any file-intensive operations, including backups, restores, mergeouts and moveouts. Default Value: 1 |
EnableUniquenessOptimization |
Enables query optimization that is based on guaranteed uniqueness of column values. Columns that can be guaranteed to include unique values include:
Default Value: 1 (enabled) |
EnableWithClauseMaterialization |
Superseded by WithClauseMaterialization. |
ExternalTablesExceptionsLimit |
Determines the maximum number of Default Value: 100 |
FailoverToStandbyAfter |
Specifies the length of time that an active standby node waits before taking the place of a failed node. This parameter is set to an interval literal. Default Value: None |
FencedUDxMemoryLimitMB |
Sets the maximum amount of memory, in megabytes (MB), that a fenced-mode UDF can use. If a UDF attempts to allocate more memory than this limit, that attempt triggers an exception. For more information, see Fenced Mode in Extending Vertica. Default Value: -1 (no limit) |
FlexTableDataTypeGuessMultiplier |
Specifies a multiplier that the Default Value: Any value that results in a column width neither less than 20 bytes nor greater than |
FlexTableRawSize |
Specifies the default column width for the Default Value: Value range: |
HiveMetadataCacheSizeMB |
Sets the maximum memory to be used when caching metadata during Parquet reads, either when querying external tables or when bulk-loading data. Caching is especially helpful when the data contains many small row groups. Increasing the size of this cache can improve performance but consumes more memory. Disabling the cache reduces memory consumption and makes more calls to the file system to read this data. Default Value: 0 (disabled) |
JavaBinaryForUDx |
Sets the full path to the Java executable that Vertica uses to run Java UDxs. See Installing Java on Vertica Hosts in Extending Vertica. |
JavaClassPathForUDx |
Sets the Java classpath for the JVM that executes Java UDxs. This parameter must list all directories containing JAR files that Java UDxs import. Default Value: ${vertica_home}/packages/hcat/lib/* See Handling Java UDx Dependencies in Extending Vertica. |
LockTimeout | Specifies in seconds how long a table can be locked, by default set to 300. You can set this parameter at all levels: session, node, and database. |
MaxAutoSegColumns |
Specifies the number of columns (0 –1024) to use in an auto-projection's hash segmentation clause. Set to Default Value: 8 |
MaxAutoSortColumns |
Specifies the number of columns (0 –1024) to use in an auto-projection's sort expression. Set to Default Value: 8 |
MaxBundleableROSSizeKB |
Specifies the minimum size, in kilobytes, of an independent ROS file. When EnableStorageBundling is true, Vertica bundles storage container ROS files below this size into a single file. Bundling improves the performance of any file-intensive operations, including backups, restores, mergeouts and moveouts. If you enable storage bundling and specify this parameter with a value of 0, Vertica bundles .fdb and .pidx files without bundling other storage container files. Default Value: 1024 |
MaxClientSessions |
Determines the maximum number of client sessions that can run on a single node of the database. The default value allows for five additional administrative logins. These logins prevent DBAs from being locked out of the system if non-dbadmin users reach the login limit. Setting this parameter to 0 prevents new client sessions from being opened while you are shutting down the database. Restore the parameter to its original setting after you restart the database. For details, see Managing Sessions. Default Value: 50 user logins and 5 additional administrative logins |
MaxTieredPoolScale |
Specifies the threshold for allocating chunks of memory from the system or dedicated pools for Vertica system components—for example, for WOS containers. Above this threshold, Vertica allocates memory from the system (malloc), and returns it when no longer needed. Below this threshold, Vertica allocates memory from dedicated pools (talloc), which manage smaller memory allocations more efficiently. Tiered memory management facilitates more efficient use of resources, and minimizes the risk of any one process (such as the database catalog) from preemptively allocating and retaining excessive amounts of memory. Set this parameter to the desired power of 2, any value ≥ 20. For example, for 128 MB, set the parameter to 27 ( This parameter cannot be set below 20 (1MB). Any allocation that is less than 20 always uses talloc, regardless of this parameter setting. This parameter must be set at the database level, and takes effect only on database restart. Default Value: 27 (128 MB) |
PatternMatchAllocator |
Overrides the heap memory allocator for the pattern-match library when set to 1. The Perl Compatible Regular Expressions (PCRE) pattern-match library evaluates regular expressions. Restart the database for this parameter to take effect. Default Value: 0 |
PatternMatchingUseJit |
Enables just-in-time compilation (to machine code) of regular expression pattern matching functions used in queries. Using this parameter can usually improve pattern matching performance on large tables. The Perl Compatible Regular Expressions (PCRE) pattern-match library evaluates regular expressions. Restart the database for this parameter to take effect. Default Value: 1 |
PcreJitStackMaxSizeScaleFactor |
Determines the maximum size of the Perl Compatible Regular Expressions (PCRE) just-in-time stack. The maximum stack size will be PcreJitStackMaxSizeScaleFactor * 1024 * 1024 bytes. Default Value: 32 |
PatternMatchStackAllocator |
Overrides the stack memory allocator for the pattern-match library when set to 1. The Perl Compatible Regular Expressions (PCRE) pattern-match library evaluates regular expressions. Restart the database for this parameter to take effect. Default Value: 1 |
SegmentAutoProjection |
Determines whether auto-projections are segmented by default. Set to 0 to disable. Default Value: 1 |
TerraceRoutingFactor |
Specifies a value large enough that it cannot be enabled by default, even for the largest clusters. Use the Terrace Routing equation to find the appropriate value for your cluster. Default Value: 1000.0 SeeTerrace Routing. |
Changes the isolation level for the database. After modification, Vertica uses the new transaction level for every new session. Existing sessions and their transactions continue to use the original isolation level. Default Value: READ COMMITTED |
|
TransactionMode |
Specifies whether transactions are in read/write or read-only modes. Read/write is the default. Existing sessions and their transactions continue to use the original isolation level. Default Value: READ WRITE |
UDxFencedBlockTimeout |
Specifies the number of seconds to wait for output before aborting a UDx running in Fenced Mode. If the server aborts a UDx for this reason, it produces an error message similar to "ERROR 3399: Failure in UDx RPC call: timed out in receiving a UDx message". If you see this error frequently, you can increase this limit. UDxs running in fenced mode do not run in the server process, so increasing this value does not impede server performance. Default Value: 60 |
UseLocalTzForParquetTimestampConversion |
Specifies whether to do timezone conversion when reading Parquet files. Hive version 1.2.1 introduced an option to localize timezones when writing Parquet files. Previously it wrote them in UTC and Vertica adjusted the value when reading the files. Set this parameter to 0 if Hive has already adjusted the timezones. Default Value: 1 (enabled) |
WithClauseMaterialization |
Boolean, specifies whether to enable materialization of WITH clause results. When materialization is enabled (1), Vertica evaluates each WITH clause once and stores results in a temporary table. Default: 0 (disable) |
WithClauseMaterialization |
Boolean, specifies whether to enable materialization of WITH clause results. When materialization is enabled (1), Vertica evaluates each WITH clause once and stores results in a temporary table. Default: 0 (disable) |