9.1 System Variables

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.

• bulk_loader.data_memory_size

  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	AVRO_FILE_BLOCK_HEADER DUPLICATE_FILES EMPTY_FILES EMPTY_MISSING_COLUMNS INFER_SKIPPED MISSING_FILES NULL_COLUMNS NUMERIC_PARSING NUMERIC_TRUNCATION PARQUET_SCHEMA STRING_PARSING STRING_TRUNCATION TEMPORAL_PARSING TEMPORAL_TRUNCATION

  Specifies the warning modes for Lakehouse to filter.

• rapid_compression

  System Variable	rapid_compression
  Scope	Session
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Enumeration
  Default Value	AUTO
  Valid Values	ON OFF

  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.

• rapid_bootstrap

  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	IDLE SUSPEND ON

  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).

• rapid_dmem_size

  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.

• rapid_memory_heap_size

  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.

• rapid_execution_strategy

  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	MIN_RUNTIME MIN_MEM_CONSUMPTION

  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;

  See Section 11.1.1, “MySQL HeatWave Issues”.

• rapid_stats_cache_max_entries

  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”.

• rapid_ml_genai

  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.

• rapid_ml_genai_session

  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.

• rapid_enable_my_sc

  Command-Line Format	--rapid-enable-my-sc=#
  System Variable	rapid_enable_my_sc
  Scope	Session
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Enumeration
  Default Value	ON
  Valid Values	ON OFF

  As of MySQL 9.4.1, controls whether statistics feedback from the InnoDB execution engine is used to improve query plans, particularly for queries with two or more joins and high MySQL costs. By injecting actual statistics from previous query executions into subsequent optimizations, MySQL HeatWave can produce more accurate and efficient plans, leading to significant performance improvements.

• rapid_ap_stats_cache_max_entries

  Command-Line Format	--rapid-ap-stats-cache-max-entries=#
  System Variable	rapid_ap_stats_cache_max_entries
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	65536
  Minimum Value	0
  Maximum Value	1048576

  As of MySQL 9.4.1, controls the size of the HashMap used to control statistics feedback. Setting to 0 disables statistics feedback. Lower values imply fewer statistics cached for feedback.

• 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.

• use_secondary_engine

  System Variable	use_secondary_engine
  Scope	Session
  Dynamic	Yes
  SET_VAR Hint Applies	Yes
  Type	Enumeration
  Default Value	ON
  Valid Values	OFF ON FORCED

  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.

• external_table_storage_engine

  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.

• rapid_ml_concurrency

  Command-Line Format	--rapid_ml_concurrency=#
  Introduced	9.4.1
  System Variable	rapid_ml_concurrency
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	8
  Minimum Value	1
  Maximum Value	8

  Allows concurrent query processing for MySQL HeatWave AutoML and MySQL HeatWave GenAI as of MySQL 9.4.1. It can have a value between 1 and 8. The default value is 8. A value over 1 allows for concurrent query processing.

  We recommend not enabling concurrent query processing with the following DB System shapes:

  • HeatWave.Free

  • HeatWave.32GB