MySQL HeatWave User Guide
MySQL HeatWave maintains several variables that configure its operation. Variables are set when the MySQL HeatWave Cluster is enabled. Most MySQL HeatWave variable settings are managed by OCI and cannot be modified directly.
| Command-Line Format | --bulk_loader.data_memory_size=# |
|---|---|
| System Variable | bulk_loader.data_memory_size |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Integer |
| Default Value | 1073741824 |
| Minimum Value | 67108864 |
| Maximum Value | 1099511627776 |
Specifies the amount of memory to use for
LOAD DATA with
ALGORITHM=BULK, in bytes. See:
Section 4.1.2, “Bulk Ingest Data”.
lakehouse_filter_warning_codes_list
| Command-Line Format | --lakehouse_filter_warning_codes_list=codes |
|---|---|
| System Variable | lakehouse_filter_warning_codes_list |
| Scope | Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | String |
| Default Value | {6042,6044} |
Specifies the warning codes for Lakehouse to filter.
lakehouse_filter_warning_modes_list
| Command-Line Format | --lakehouse_filter_warning_modes_list=modes |
|---|---|
| System Variable | lakehouse_filter_warning_modes_list |
| Scope | Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | String |
| Default Value | EMPTY_FILES,DUPLICATE_FILES,MISSING_FILES,NULL_COLUMNS,INFER_SKIPPED |
| Valid Values |
|
Specifies the warning modes for Lakehouse to filter.
| System Variable | rapid_compression |
|---|---|
| Scope | Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Enumeration |
| Default Value | AUTO |
| Valid Values |
|
Whether to enable or disable data compression before loading data into MySQL HeatWave. Data compression is enabled by default. The setting does not affect data that is already loaded. See Section 4.2.3, “Enable or Disable Data Compression”.
The default option is AUTO which
automatically chooses the best compression algorithm for each
column.
| Command-Line Format | --rapid-bootstrap[={OFF|ON|IDLE}] |
|---|---|
| System Variable | rapid_bootstrap |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Enumeration |
| Default Value | OFF |
| Valid Values |
|
The setting for this variable is managed by OCI and cannot be modified directly. Defines the MySQL HeatWave Cluster bootstrap state. States include:
OFF
The MySQL HeatWave Cluster is not bootstrapped (not initialized).
IDLE
The MySQL HeatWave Cluster is idle (stopped).
SUSPENDED
The MySQL HeatWave Cluster is suspended. The
SUSPENDED state is a transition state
between IDLE and ON
that facilitates planned restarts of the MySQL HeatWave Cluster.
ON
The MySQL HeatWave Cluster is bootstrapped (started).
| Command-Line Format | --rapid-dmem-size=# |
|---|---|
| System Variable | rapid_dmem_size |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Integer |
| Default Value | 2048 |
| Minimum Value | 512 |
| Maximum Value | 2097152 |
The setting for this variable is managed by OCI and cannot be modified directly. Specifies the amount of DMEM available on each core of each node, in bytes.
| Command-Line Format | --rapid-memory-heap-size=# |
|---|---|
| System Variable | rapid_memory_heap_size |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Integer |
| Default Value | unlimited |
| Minimum Value | 67108864 |
| Maximum Value | unlimited |
The setting for this variable is managed by OCI and cannot be modified directly. Defines the amount of memory available for the MySQL HeatWave plugin, in bytes. Ensures that MySQL HeatWave does not use more memory than is allocated to it.
| Command-Line Format | --rapid_execution_strategy[={MIN_RUNTIME|MIN_MEM_CONSUMPTION}] |
|---|---|
| System Variable | rapid_execution_strategy |
| Scope | Session |
| Dynamic | No |
SET_VAR Hint Applies |
No |
| Type | Enumeration |
| Default Value | MIN_RUNTIME |
| Valid Values |
|
Specifies the query execution strategy to use. Minimum runtime
(MIN_RUNTIME) or minimum memory consumption
(MIN_MEM_CONSUMPTION).
MySQL HeatWave optimizes for network usage rather than memory. If you
encounter out of memory errors when running a query, try
running the query with the
MIN_MEM_CONSUMPTION strategy by setting by
setting
rapid_execution_strategy
prior to executing the query:
SET SESSION rapid_execution_strategy = MIN_MEM_CONSUMPTION;
| Command-Line Format | --rapid-stats-cache-max-entries=# |
|---|---|
| System Variable | rapid_stats_cache_max_entries |
| Scope | Global |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | Integer |
| Default Value | 65536 |
| Minimum Value | 0 |
| Maximum Value | 1048576 |
The setting for this variable is managed by OCI and cannot be modified directly. Specifies the maximum number of entries in the statistics cache.
The number of entries permitted in the statistics cache by default is 65536, which is enough to store statistics for 4000 to 5000 unique queries of medium complexity.
For more information, see Section 5.4.11, “Auto Query Plan Improvement”.
| Command-Line Format | --rapid-ml-genai=# |
|---|---|
| System Variable | rapid_ml_genai |
| Scope | Session |
| Dynamic | No |
SET_VAR Hint Applies |
No |
| Type | String |
| Default Value | Compartment ID of the DB system currently in use |
The setting for this variable is managed by OCI and cannot be modified directly. To use the OCI Generative AI Service with MySQL HeatWave GenAI, MySQL HeatWave automatically sets this system variable for resource principal authentication. The default value of the variable is the compartment ID of the database system currently in use.
| Command-Line Format | --rapid-ml-genai-session=# |
|---|---|
| System Variable | rapid_ml_genai_session |
| Scope | Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | String |
| Default Value | Compartment ID of the DB system currently in use |
Specifies the ID of the compartment you want to use for accessing the OCI Generative AI service. If you do not want to use the compartment ID of the DB system currently in use for accessing the OCI Generative AI service, then you can set this system variable to specify the ID of the compartment you want to use.
secondary_engine_cost_threshold
| System Variable | secondary_engine_cost_threshold |
|---|---|
| Scope | Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
Yes |
| Type | Numeric |
| Default Value | 100000.000000 |
| Minimum Value | 0 |
| Maximum Value | DBL_MAX (maximum double value) |
Defines the cost estimate threshold for offloading queries to the secondary engine. A query with a cost estimate on the primary engine that is higher than the threshold value is considered for execution on the secondary engine.
show_create_table_skip_secondary_engine
| Command-Line Format | --show-create-table-skip-secondary-engine[={OFF|ON}] |
|---|---|
| System Variable | show_create_table_skip_secondary_engine |
| Scope | Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
Yes |
| Type | Boolean |
| Default Value | OFF |
Whether to exclude the SECONDARY ENGINE
clause from SHOW CREATE TABLE
output, and from CREATE TABLE
statements dumped by the mysqldump utility.
mysqldump provides the
--show-create-skip-secondary-engine
option. When specified, it enables the
show_create_table_skip_secondary_engine
system variable for the duration of the dump operation.
| System Variable | use_secondary_engine |
|---|---|
| Scope | Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
Yes |
| Type | Enumeration |
| Default Value | ON |
| Valid Values |
|
Whether to execute queries using the secondary engine. These values are permitted:
OFF: Queries execute using the primary
storage (InnoDB) on the MySQL DB System. Execution using the
secondary engine (RAPID) is disabled.
ON: Queries execute using the secondary
engine (RAPID) when conditions warrant, falling back to
the primary storage engine (InnoDB) otherwise. In the case
of fallback to the primary engine, whenever that occurs
during statement processing, the attempt to use the
secondary engine is abandoned and execution is attempted
using the primary engine.
FORCED: Queries always execute using
the secondary engine (RAPID) or fail if that is not
possible. Under this mode, a query returns an error if it
cannot be executed using the secondary engine, regardless
of whether the tables that are accessed have a secondary
engine defined.
| Command-Line Format | --external_table_storage_engine=text |
|---|---|
| System Variable | external_table_storage_engine |
| Scope | Global, Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | String |
| Default Value | LAKEHOUSE |
Determines which engines are used for external tables.
external_table_secondary_storage_engine
| Command-Line Format | --external_table_secondary_storage_engine=text |
|---|---|
| System Variable | external_table_secondary_storage_engine |
| Scope | Global, Session |
| Dynamic | Yes |
SET_VAR Hint Applies |
No |
| Type | String |
| Default Value | RAPID |
Determines which engines are used for external tables.