[system] Settings
System settings in vectorwise.conf affect the X100 system.
full_rewrite_on_combine
Specifies whether all blocks are to be rewritten by the COMBINE command. Setting this option to true can be used to avoid or undo block fragmentation introduced by partial update propagation.
Default: false
full_rewrite_on_update_propagation
Specifies whether all blocks are to be rewritten by the system-triggered propagation process. Setting this option to true can be used to avoid block fragmentation introduced by partial update propagation.
Default: false
max_old_log_size
Specifies the maximum size for the main_wal_backups directory, which holds old main.wal files.
Default: 2 GB
Note: The system will exceed the default value if it is necessary to keep a single old main.wal file that exceeds 2 GB.
num_cores
Specifies the number of processing units in the system. This value is used to calculate the amount of memory available for each transaction.
Num_cores is used to calculate the maximum parallelism level for a newly issued query if there are many queries already being executed. The goal is not to deteriorate the total throughput of the system. Generally, the higher the num_cores value, the higher parallelism levels are granted for queries running concurrently (where each parallelism level is not higher than max_parallelism_level).
Num_cores is used to derive a database-wide target on the number of concurrent threads used for parallel execution. Queries will run at a lower degree of parallelism if the default or requested degree would lead to an uneven allocation of resources to the new query based on the number of concurrently executing queries. On a system running many concurrent queries, new queries may get only one core and will more likely run out of memory since the num_cores value is used to reserve memory for possible other threads.
Default: Number of processors visible to the operating system.
resolve_concurrent_updates
Tells X100 whether to attempt to merge concurrent updates on the same table. If set to false, the transaction that tries to commit last will fail and will have to abort. Disabling merging of concurrent updates can reduce memory consumption, especially if there are any long-running transactions in the system.
Default: true
use_sse42
Uses SSE4.2 for accelerated string processing on CPU architectures that support it.
Default: YES
PDT Parameters
The Positional Delta Tree (PDT) is a data structure used for batch updates residing in memory.
ioup_blocks_filled_trigger
Specifies an upper limit on a table's PDT memory consumption and is calculated as follows:
block_size * ioup_blocks_filled_trigger * number of columns of the table
An insert-only update propagation triggers when the memory consumption of PDT tuple inserts exceeds this threshold.
Default: 4
Notes:
• It does not override the min_propagate_table_count setting.
• The update propagation is not solely dependent on this setting and can be triggered even if only the max_table_update_ratio is met.
• The update propagation is triggered only when the update_propagation setting is True.
ioup_exclusive_lock_timeout_ms
Specifies the lock mode and timeout duration for an insert-only update propagation (IOUP) transaction. It can either be an exclusive read-write (ERW) lock or a shared lock (typically used for read-write transactions).
An ERW lock allows IOUP to run exclusively as the sole RW transaction, apart from the concurrent read-only transactions. On acquiring an ERW lock, no concurrent read-write transactions are allowed to start. In case of ongoing RW transactions, IOUP has to wait for them to commit or abort. If an IOUP transaction does not acquire an ERW lock within a specific duration (in milliseconds), the transaction fails.
You can specify either of the following values for this parameter:
• 0 - Do not try to acquire an ERW lock, but acquire a shared lock. It is the default value and indicates that the ERW lock feature is disabled for the IOUP transaction.
• > 0 - Try to acquire an exclusive lock, but timeout after the specified milliseconds
• < 0 - Try to acquire an exclusive lock indefinitely. Avoid this option.
Default: 0
Notes:
• The update propagation is triggered only when the update_propagation setting is True.
• In case of a shared lock, you may encounter concurrency issues. IOUP running in the background in shared mode can occasionally cause commit failure for a non-concurrent workload.
max_global_update_memory
Specifies an upper limit on total update memory consumption as a percentage of query memory limit (max_memory_size). When this limit is reached, the system propagates in-memory updates to disk.
Update memory is a component of query memory.
Limits: 0.01 to 1.0
Default: 0.25 (one quarter of max_memory_size)
max_number_propagate_retries
Specifies the maximum number of retries for failed automatic update propagation. When propagation for a certain table fails, X100 will retry propagating this table if no alternative tables are possible.
Default: 6
max_table_update_memory
Specifies an upper limit on the total update memory consumption per table as a percentage of global in-memory update limit (In case of a shared lock, you may encounter concurrency issues. IOUP running in the background in shared mode can occasionally cause commit failure for a non-concurrent workload).
When the amount of memory used by a table's PDT updates reaches the upper limit calculated as follows:
max_table_update_memory * max_global_update_memory * max_memory_size
the system propagates in-memory updates to disk. The propagation happens regardless of min_propagate_table_count, max_table_update_ratio or ioup_blocks_filled_trigger parameters.
Default: 0.1
max_table_update_ratio
Specifies a per-table maximum percentage of in-memory updates (that is, the number of updates divided by the number of stable tuples). When this limit is reached, the system propagates to disk in-memory updates for this table.
Limits: 0.01 to 1.0
Default: 0.05
max_update_memory_per_transaction
Specifies an upper limit on the total update memory in the snapshot layer of a transaction, as a percentage of query memory limit (max_memory_size). When this limit is reached, the system will not allow further update operations in this transaction.
Limits: 0.01 to 1.0
Default: 0.25 (one quarter of max_memory_size)
min_propagate_table_count
Specifies the minimum number of tuples in a table above which max_table_update_ratio is checked. This is to avoid frequent update propagation to disk on relatively small tables.
Default: 500K
shutdown_max_maintenance_window_ms
Specifies the time duration (milliseconds) to execute update propagation during system shutdown. On reaching the timeout, all ongoing update-propagation tasks are terminated, and the shutdown procedure starts.
You can set the value of this parameter to 0 to disable shutdown maintenance. For negative values such as -1, the system does not apply a timeout. It should be avoided as it leads to all tables with PDT updates getting propagated, which can be time-consuming.
Default: 15000
update_propagation
Enables or disables system-triggered propagation of in-memory updates. Valid values are true and false.
Note: Disabling automatic propagation overrides the max_global_update_memory setting and can lead to unwanted consequences if query memory becomes full with PDTs.
Default: true
update_propagation_retry_delay_base
Specifies a per-table multiplier for delays of subsequent failed update propagation retries calculated as follows:
update_propagation_retry_delay_sec * (update_propagation_retry_delay_base ^ num_failures)
Default: 2
update_propagation_retry_delay_sec
Specifies a per-table initial delay (that is, the delay after the first update propagation failure), in seconds, before retrying a failed update propagation. For each subsequent retry, the delay timer increases and is calculated as follows:
current_delay * system/update_propagation_retry_delay_base
When an update propagation against the failed table succeeds, the retry delay timer is reset to the value of the parameter set by the user. You can also set the value to 0 to disable the retry delay mechanism, in which case the system keeps retrying immediately after each update propagation failure.
Default: 60