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	(0.125) * #memory GB
  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”.

• bulk_loader.concurrency

  System Variable	bulk_loader.concurrency
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	(1/2) * #vcpus
  Minimum Value	1
  Maximum Value	1024

  The maximum number of concurrent threads to use by one LOAD statement with ALGORITHM=BULK. See: Section 4.1.2, “Bulk Ingest Data”.

• bulk_loader.chunk_size

  System Variable	bulk_loader.chunk_size
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	4194304
  Minimum Value	1048576
  Maximum Value	16777216

  The queue chunk size in MiB used for reading data from CSV files.

  • Each thread uses one queue with four elements. Another element is used for data conversion. Total memory requirement by the component is (4 + 1) x bulk_loader.chunk_size x bulk_loader.concurrency. The default value is 5 x 16 x 4 MiB = 320 MiB.

  • The above calculation is for a single LOAD operation running with ALGORITHM = BULK. For concurrent execution, you need to multiply the value with the number of LOAD operations running with ALGORITHM=BULK. 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.12, “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.

• rapid_hnsw_ef_search

  System Variable	rapid_hnsw_ef_search
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	40
  Minimum Value	0
  Maximum Value	50000

  As of MySQL 9.5.0, controls the accuracy of query results using the vector index. To disable the usage of HNSW indexes, you can set this session variable to 0.

• rapid_hnsw_use_adaptive_accuracy_estimation

  System Variable	rapid_hnsw_use_adaptive_accuracy_estimation
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Enumeration
  Default Value	ON
  Valid Values	ON OFF

  As of MySQL 9.5.0, controls adaptive accuracy estimation for similarity search queries that use the vector indexes.

• rapid_auto_vector_index_enabled

  System Variable	rapid_auto_vector_index_enabled
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Enumeration
  Default Value	ON
  Valid Values	ON OFF

  As of MySQL 9.5.0, controls automatic vector index creation for frequently queried vector store columns. If disabled, all automatically created indexes are removed and no more automatic index creation happens until this variable is enabled.

• rapid_auto_vector_index_interval_seconds

  System Variable	rapid_auto_vector_index_interval_seconds
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	86400
  Minimum Value	30
  Maximum Value	604800

  As of MySQL 9.5.0, specifies the time interval, in seconds, for automatically creating the vector indexes for frequently queried vector store columns.

• rapid_auto_vector_index_server_memory_quota_pct

  System Variable	rapid_auto_vector_index_server_memory_quota_pct
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	20
  Minimum Value	0
  Maximum Value	100

  As of MySQL 9.5.0, specifies the maximum memory percentage that can be used by created indexes. Less beneficial indexes are removed if memory limits are exceeded, making space for more beneficial indexes.

• rapid_auto_vector_index_skip_quiet_check

  System Variable	rapid_auto_vector_index_skip_quiet_check
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Enumeration
  Default Value	OFF
  Valid Values	ON OFF

  As of MySQL 9.5.0, controls whether MySQL HeatWave checks if the system is quiet before automatic vector index creation is started.

  A quit system is:

  • When mo tables have been queried in MySQL HeatWave within the past 5 minutes.

  • When no ongoing table is loading into MySQL HeatWave.

  • There is no lag in change propagation.

  • If the system is not found to be quiet, MySQL HeatWave holds automatic index creation for 300 seconds and checks up to 10 times before skipping until the next interval.

  This variable cannot be modified when RAPID is not bootstrapped.

• rapid_auto_vector_index_build_timeout_seconds

  System Variable	rapid_auto_vector_index_build_timeout_seconds
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	3600
  Minimum Value	1
  Maximum Value	43200

  As of MySQL 9.5.0, specifies the timeout duration, in seconds, for automatic vector index creation.

  This variable cannot be modified when RAPID is not bootstrapped.

• 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=#
  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

• rapid_ace_auto_train_enabled

  System Variable	rapid_ace_auto_train_enabled
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Boolean
  Default Value	ON

  As of MySQL 9.4.2, enables or disables Advanced Cardinality Estimation (ACE) auto-training, which periodically runs in the background and manages rebuilding stale ACE models for loaded tables. See Analyze Tables.

• rapid_ace_auto_train_interval_seconds

  System Variable	rapid_ace_auto_train_interval_seconds
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	3600
  Minimum Value	60
  Maximum Value	86400

  As of MySQL 9.4.2, determines the time (in seconds) between runs of ACE auto-training. See Analyze Tables.

• rapid_ace_auto_train_max_execution_seconds

  System Variable	rapid_ace_auto_train_max_execution_seconds
  Scope	Global
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Integer
  Default Value	180
  Minimum Value	30
  Maximum Value	1800

  As of MySQL 9.4.2, determines the maximum amount of time in seconds ACE auto-training runs before stopping and completing early. See Analyze Tables.