1 of 1

Configuration Parameters for HeavyDB

Following are the parameters for runtime settings on HeavyDB. The parameter syntax provides both the implied value and the default value as appropriate. Optional arguments are in square brackets, while implied and default values are in parentheses.

For example, consider allow-loop-joins [=arg(=1)] (=0).

If you do not use this flag, loop joins are not allowed by default.
If you provide no arguments, the implied value is 1 (true) (allow-loop-joins).
If you provide the argument 0, that is the same as the default (allow-loop-joins=0).
If you provide the argument 1, that is the same as the implied value (allow-loop-joins=1).

Additional Enterprise Edition Parameters

Following are additional parameters for runtime settings for the Enterprise Edition of HeavyDB. The parameter syntax provides both the implied value and the default value as appropriate. Optional arguments are in square brackets, while implied and default values are in parentheses.

Configuration Parameters for HeavyDB

For example, consider allow-loop-joins [=arg(=1)] (=0).

If you do not use this flag, loop joins are not allowed by default.
If you provide no arguments, the implied value is 1 (true) (allow-loop-joins).
If you provide the argument 0, that is the same as the default (allow-loop-joins=0).
If you provide the argument 1, that is the same as the implied value (allow-loop-joins=1).

Additional Enterprise Edition Parameters

Allow the queries that failed on GPU to retry on CPU, even when watchdog is enabled. When watchdog is enabled, most queries that run on GPU and throw a watchdog exception fail. Turn this on to allow queries that fail the watchdog on GPU to retry on CPU. The default behavior is for queries that run out of memory on GPU to throw an error if watchdog is enabled. Watchdog is enabled by default.

Allow for multiple queries to run execution kernels concurrently on CPU.

Example: In a system with a number of executor of 4 (controlled by the parameter number-executors) 3+1 (the +1 is depending by the allow-cpu-gpu-kernel-concurrency) can run concurrently in the CPU.

Allow multiple queries to run execution kernels concurrently on CPU while a GPU query is executing.

Example: In a system with a number of executor of 4 (controlled by the parameter number-executors), on of the 4 slot can be used to run a GPU query, along with other 3 running on CPU.

If SAML or LDAP logins are enabled, and the logins fail, this setting enables authentication based on internally stored login credentials. Command-line tools or other tools that do not support SAML might reject those users from logging in unless this feature is enabled. This allows a user to log in using credentials on the local database.

Enables all join queries to fall back to the loop join implementation. During a loop join, queries loop over all rows from all tables involved in the join, and evaluate the join condition. By default, loop joins are only allowed if the number of rows in the inner table is fewer than the trivial-loop-join-threshold, since loop joins are computationally expensive and run for an extended period. Modifying the trivial-loop-join-threshold is a safer alternative to globally enabling loop joins. You might choose to globally enable loop joins when you have many small tables for which loop join performance has been determined to be acceptable but modifying the trivial join loop threshold would be tedious.

Specify a list of allowed root paths that can be used in export operations, such as the COPY TO command. Helps prevent exploitation of security vulnerabilities and prevent server crashes, data breaches, and full remote control of the host machine. For example:

allowed-export-paths = ["/heavyai-storage/data/heavyai_export", "/home/centos"] The list of paths must be on the same line as the configuration parameter.

Allowed file paths are enforced by default. The default export path (<data directory>/heavyai_export) is allowed by default, and all child paths of that path are allowed.

When using commands with other paths, the provided paths must be under an allowed root path. If you try to use a nonallowed path in a COPY TO command, an error response is returned.

Specify a list of allowed root paths that can be used in import operations, such as the COPY FROM command. Helps prevent exploitation of security vulnerabilities and prevent server crashes, data breaches, and full remote control of the host machine.

For example:

allowed-import-paths = ["/heavyai-storage/data/heavyai_import", "/home/centos"] The list of paths must be on the same line as the configuration parameter.

Allowed file paths are enforced by default. The default import path (<data directory>/heavyai_import) is allowed by default, and all child paths of that allowed path are allowed.

When using commands with other paths, the provided paths must be under an allowed root path. If you try to use a nonallowed path in a COPY FROM command, an error response is returned.

Enable/disable clear render gpu memory on out-of-memory errors during rendering. If an out-of-gpu-memory exception is thrown while rendering, many users respond by running \clear_gpu via the heavysql command-line interface to refresh/defrag the memory heap. This process can be automated with this flag enabled. At present, only GPU memory in the renderer is cleared automatically.

Allows HEAVY.AI Core to directly materialize intermediate projections and the final ResultSet in Columnar format where appropriate. Columnar output is an internal performance enhancement that projects the results of an intermediate processing step in columnar format. Consider disabling this feature if you see unexpected performance regressions in your queries.

Set to TRUE to enable the data recycler. Enabling the recycler enables the following:

Hashtable recycler, which is the cache storage.
Hashing scheme recycler, which preserves a hashtable layout (such as perfect hashing and keyed hashing).
Overlaps hashtable tuning parameter recycler. Each overlap hashtable has its own parameters used during hashtable building.

Allow geospatial operations ST_Contains and ST_Intersects to process uncompressed coordinates where possible to increase execution speed. Provides control over the selection of ST_Contains and ST_Intersects implementations. By default, for certain combinations of compressed geospatial arguments, such as ST_Contains(POLYGON, POINT), the implementation can process uncompressed coordinate values. This can result in much faster execution but could decrease precision. Disabling this option enables full decompression, which is slower but more precise.

Enable runtime user defined function registration. Enables runtime registration of user defined functions. This functionality is turned off unless you specifically request it, to prevent unintentional inclusion of nonstandard code. This setting is a precursor to more advanced object permissions planned in future releases.

Set executor resource manager reserved memory for query result sets as a ratio greater than 0, representing the fraction of the system memory not allocatable for the CPU buffer pool. Values of 1.0 are permitted to allow over-subscription when warranted, but too high a value can cause out-of-memory errors.

Example: In a system with 256GB of Ram, the default of the cpu-buffer-size is 204.8GB so this ratio will be calculated on 51.2GB, limiting the maximum result set memory for a single query to 41GB

Set max fraction of executor resource manager total CPU slots/threads that can be allocated for a single query.

Note we allow executor-per-query-max-cpu-threads-ratio to have values > 1 to allow over-subscription of threads when warranted, given we may be overly pessimistic about kernel core occupation for some classes of queries. Care should be taken however with setting this value too high as thrashing and thread starvation can result. Example: on a physical server with 24 logical CPUs or in a VM with 24 vCPU the executor thread will be doubled to 48, so a value of 0.9 will use up 43 threads for a single query. This value can be lowered to lower memory requirements of single queries.

Set max fraction of executor resource manager total CPU result memory reservation that can be allocated for a single query.

Note we allow executor-per-query-max-cpu-result-mem-ratio to have values > 0 to allow over-subscription of memory when warranted, but user should be careful with this as too high a value can cause out-of-memory errors.

Enable automatic table reordering in FROM clause. Reorders the sequence of a join to place large tables on the inside of the join clause and smaller tables on the outside. HEAVY.AI also reorders tables between join clauses to prefer hash joins over loop joins. Change this value only in consultation with an HEAVY.AI engineer.

Force query to CPU when input data memory usage exceeds this percentage of available GPU memory. OmniSciDB loads data to GPU incrementally until data exceeds GPU memory, at which point the system retries on CPU. Loading data to GPU evicts any resident data already loaded or any query results that are cached. Use this limit to avoid attempting to load datasets to GPU when they obviously will not fit, preserving cached data on GPU and increasing query performance. If watchdog is enabled and allow-cpu-retry is not enabled, the query fails instead of re-running on CPU.

Path to the log directory. Can be either a relative path to the $HEAVYAI_STORAGE/data directory or an absolute path. Use this flag to control the location of your HEAVY.AI log files. If the directory does not exist, HEAVY.AI creates the top level directory. For example, a/b/c/logdir is created only if the directory path a/b/c already exists.

Log to file severity levels:

DEBUG4

DEBUG3

DEBUG2

DEBUG1

INFO

WARNING

ERROR

FATAL

All levels after your chosen base severity level are listed. For example, if you set the severity level to WARNING, HEAVY.AI only logs WARNING, ERROR, and FATAL messages.

Look up the origin of inbound connections by IP address and DNS name and print this information as part of stdlog. Some systems throttle DNS requests or have other network constraints that preclude timely return of user origin information. Set to FALSE to improve performance on those networks or when large numbers of users from different locations make rapid connect/disconnect requests to the server.

Frequency with which to check the interrupt status of pending queries, in milliseconds. Values larger than 0 are valid. If you set pending-query-interrupt-freq=100, each session's interrupt status is checked every 100 ms.

For example, assume you have three sessions (S1, S2, and S3) in your queue, and assume S1 contains a running query, and S2 and S3 hold pending queries. If you setpending-query-interrupt-freq=1000 both S2 and S3 are interrupted every 1000 ms (1 sec). See running-query-interrupt-freq for information about interrupting running queries. Decreasing the value increases the speed with which pending queries are removed, but also increases resource usage.

pki-db-client-auth [=arg]

Specifies the size of a per-GPU buffer that render query results are written to; allocated at the first rendering call. Persists while the server is running unless you run \clear_gpu_memory. Increase if rendering a large number of points or symbols and you get the following out-of-memory exception: Not enough OpenGL memory to render the query results.

Default is 500 MB.

A render execution time limit in milliseconds to retry a render request if an out-of-gpu-memory error is thrown. Requires enable-auto-clear-render-mem = true. If enable-auto-clear-render-mem = true, a retry of the render request can be performed after an out-of-gpu-memory exception. A retry only occurs if the first run took less than the threshold set here (in milliseconds). The retry is attempted after the render gpu memory is automatically cleared. If an OOM exception occurs, clearing the memory might get the request to succeed. Providing a reasonable threshold might give more stability to memory-constrained servers w/ rendering enabled. Only a single retry is attempted. A value of 0 disables retries.

Reserved memory for GPU. Reserves extra memory for your system (for example, if the GPU is also driving your display, such as on a laptop or single-card desktop). HEAVY.AI uses all the memory on the GPU except for render-mem-bytes + res-gpu-mem. Also useful if other processes, such as a machine-learning pipeline, share the GPU with HEAVY.AI. In advanced rendering scenarios or distributed setups, increase to free up additional memory for the renderer, or for aggregating results for the renderer from multiple leaf nodes. HEAVY.AI recommends always setting res-gpu-mem when using backend rendering.

Controls the frequency of interruption status checking for running queries. Range: 0.0 (less frequently) to 1.0 (more frequently).

For example, if you have 10 threads that evaluate a query of a table that has 1000 rows, then each thread advances its thread index up to 10 times. In this case, if you set the flag close to 1.0, you check a session's interrupt status for every increment of the thread index.

If we set the flag value as close to 0.0, you only check the session's interrupt status when the index increment is close to 10. The default value of running interrupt checking is close to half of the maximum increment of the thread index.

Frequent interrupt status checking reduces latency for the interrupt but also can decrease query performance.

Set the offset of the last Kafka message to be committed from a Kafka data stream. Set the offset of the last Kafka message to be committed from a Kafka data stream. This way, Kafka does not resend those messages. After the Kafka server commits messages through the number N, it resends messages starting at message N+1. This is particularly useful when you want to create a replica of the HEAVY.AI server from an existing data directory.

Enable use of CA-signed certificates presented by Calcite. Defines the file that contains trusted CA certificates. This information enables the server to validate the TCP/IP Thrift connections it makes as a client to the Calcite server. The certificate presented by the Calcite server is the same as the certificate used to identify the database server to its clients.

Path to the file containing trusted CA certificates; for PKI authentication. Used to validate certificates submitted by clients. If the certificate provided by the client (in the password field of the connect command) was not signed by one of the certificates in the trusted file, then the connection fails. PKI authentication works only if the server is configured to encrypt connections via TLS. The common name extracted from the client certificate is used as the name of the user to connect. If this name does not already exist, the connection fails. If LDAP or SAML are also enabled, the servers fall back to these authentication methods if PKI authentication fails. Currently works only with JDBC clients. To allow connection from other clients, set allow-local-auth-fallback or add LDAP/SAML authentication.

Specify the percentage (with a value of 0 implying 0% and a value of 1 implying 100%) of deleted rows in a fragment at which to perform automatic vacuuming.

Automatic vacuuming occurs when deletes or updates on variable-length columns result in a percentage of deleted rows in a fragment exceeding the specified threshold. The default threshold is 10% of deleted rows in a fragment.

When changing this value, consider the most common types of queries run on the system. In general, if you have infrequent updates and deletes, set vacuum-min-selectivity to a low value. Set it higher if you have frequent updates and deletes, because vacuuming adds overhead to affected UPDATE and DELETE queries.

Path to identity provider metadata file.

Required for running SAML. An identity provider (like Okta) supplies a metadata file. From this file, HEAVY.AI uses:

Public key of the identity provider to verify that the SAML response comes from it and not from somewhere else.
URL of the SSO login page used to obtain a SAML token.