Problem: Queries are not offloaded.
Solution A: Your query contains an unsupported predicate, function, operator, or has encountered some other limitation. See Query Prerequisites.
Solution B: Query execution time is less than the query cost threshold.
HeatWave is designed for fast execution of large analytic queries. Smaller, simpler queries, such as those that use indexes for quick lookups, often execute faster on the MySQL DB System. To avoid offloading inexpensive queries to HeatWave, the optimizer uses a query cost estimate threshold value. Only queries that exceed the threshold value on the MySQL DB System are considered for offload.
The query cost threshold unit value is the same unit value used by the MySQL optimizer for query cost estimates. The threshold is 100000.00000. The ratio between a query cost estimate value and the actual time required to execute a query depends on the type of query, the type of hardware, and MySQL DB System configuration.
To determine the cost of a query on the MySQL DB System:
use_secondary_engineto force MySQL DB System execution.
SET SESSION use_secondary_engine=OFF;
Run the query using
Last_query_coststatus variable. If the value is less than 100000.00000, the query cannot be offloaded.
SHOW STATUS LIKE 'Last_query_cost';
Solution C: The table you are querying is not loaded. You can check the load status of a table in HeatWave by querying
LOAD_STATUSdata from HeatWave Performance Schema tables. For example:
mysql> USE performance_schema; mysql> SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID; +------------------------------+---------------------+ | NAME | LOAD_STATUS | +------------------------------+---------------------+ | tpch.supplier | AVAIL_RPDGSTABSTATE | | tpch.partsupp | AVAIL_RPDGSTABSTATE | | tpch.orders | AVAIL_RPDGSTABSTATE | | tpch.lineitem | AVAIL_RPDGSTABSTATE | | tpch.customer | AVAIL_RPDGSTABSTATE | | tpch.nation | AVAIL_RPDGSTABSTATE | | tpch.region | AVAIL_RPDGSTABSTATE | | tpch.part | AVAIL_RPDGSTABSTATE | +------------------------------+---------------------+
For information about load statuses, see Section 5.4.8, “The rpd_tables Table”.
Alternatively, run the following statement:
mysql> ALTER TABLE tbl_name SECONDARY_LOAD;
The following error is reported if the table is already loaded:
ERROR 13331 (HY000): Table is already loaded.
Solution D: The HeatWave Cluster has failed. To determine the status of the HeatWave Cluster, run the following statement:
mysql> SHOW GLOBAL STATUS LIKE 'rapid_plugin_bootstrapped'; +---------------------------------+------------+ | Variable_name | Value | +---------------------------------+------------+ | rapid_plugin_bootstrapped | YES | +---------------------------------+------------+
See for Chapter 4, System and Status Variables for
If the HeatWave Cluster has failed, restart it in the Console and reload the data if necessary. The HeatWave recovery mechanism should reload the data automatically.
Problem: You cannot alter the table definition to exclude a column, define a string column encoding, or define data placement keys.
Solution: Before release 8.0.31, DDL operations are not permitted on tables defined with a secondary engine. In those releases, column attributes must be defined before or at the same that you define a secondary engine for a table. If you need to perform a DDL operation on a table that is defined with a secondary engine in those releases, remove the
mysql> ALTER TABLE orders SECONDARY_ENGINE = NULL;
For more information, see Section 2.4, “Modifying Tables”.
Problem: You have encountered an out-of-memory error when executing a query.
Solution: 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_CONSUMPTIONstrategy by setting
rapid_execution_strategyprior to executing the query:
SET SESSION rapid_execution_strategy = MIN_MEM_CONSUMPTION;
Also consider checking the size of your data by performing a cluster size estimate. If your data has grown substantially, you may require additional HeatWave nodes.
For MySQL HeatWave on OCI, refer to Generating a Node Count Estimate, in the MySQL Database Service Guide.
For MySQL HeatWave on AWS, see Estimating Cluster Size with MySQL Autopilot, in the MySQL HeatWave on AWS Service Guide.
For HeatWave in Oracle Database Service for Azure (ODSA), see Provisioning HeatWave Nodes.
Avoid or rewrite queries that produce a Cartesian product. For more information, see Running Queries.
Problem: A table load operation fails with “ERROR HY000: Error while running parallel scan.”
TEXT-type column value larger than 65532 bytes is rejected during
SECONDARY_LOADoperations. Reduce the size of the
TEXT-type column value to less than 65532 bytes or exclude the column before loading the table. See Excluding Table Columns.
Problem: Change propagation fails with the following error: “Blob/text value of
nbytes was encountered during change propagation but RAPID supports text values only up to 65532 bytes.”
TEXT-type values larger than 65532 bytes are rejected during change propagation. Reduce the size of
TEXT-type values to less than 65532 bytes. Should you encounter this error, check the change propagation status for the affected table. If change propagation is disabled, reload the table. See Section 2.2.7, “Change Propagation”.
Problem: A warning was encountered when running Auto Parallel Load.
Solution: When Auto Parallel Load encounters an issue that produces a warning, it automatically switches to
dryrunmode to prevent further problems. In this case, the load statements generated by the Auto Parallel Load utility can still be obtained using the SQL statement provided in utility's output, but those load statements should be avoided or used with caution, as they may be problematic.
If a warning message indicates that the HeatWave Cluster or service is not active or online, this means that the load cannot start because a HeatWave Cluster is not attached to the MySQL DB System or is not active. In this case, provision and enable a HeatWave Cluster and run Auto Parallel Load again.
If a warning message indicates that MySQL host memory is insufficient to load all of the tables, the estimated dictionary size for dictionary-encoded columns may be too large for MySQL host memory. Try changing column encodings to
VARLENto free space in MySQL host memory.
If a warning message indicates that HeatWave Cluster memory is insufficient to load all of the tables, the estimated table size is too large for HeatWave Cluster memory. Try excluding certain schemas or tables from the load operation or increase the size of the cluster.
If a warning message indicates that a concurrent table load is in progress, this means that another client session is currently loading tables into HeatWave. While the concurrent load operation is in progress, the accuracy of Auto Parallel Load estimates cannot be guaranteed. Wait until the concurrent load operation finishes before running Auto Parallel Load.
Problem: When attempting to retrieve generated Auto Parallel Load or Advisor DDL statements, an error message indicates that the
heatwave_load_reporttable does not exist. For example:
mysql> SELECT log->>"$.sql" AS "SQL Script" FROM sys.heatwave_advisor_report WHERE type = "sql" ORDER BY id; ERROR 1146 (42S02): Table 'sys.heatwave_advisor_report' doesn't exist mysql> SELECT log->>"$.sql" AS "SQL Script" FROM sys.heatwave_load_report WHERE type = "sql" ORDER BY id; ERROR 1146 (42S02): Table 'sys.heatwave_load_report' doesn't exist
Solution: This error can occur when querying a report table from a different session. Query the report table using the same session that issued the Auto Parallel Load or Advisor
CALLstatement. This error also occurs if the session used to call Auto Parallel Load or Advisor has timed out or was terminated. In this case, run Auto Load or Advisor again before querying the report table.