Related Documentation Download this Manual
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb


HeatWave User Guide  /  Troubleshooting

Chapter 10 Troubleshooting

  • Problem: Queries are not offloaded.

    • Solution A: Your query contains an unsupported predicate, function, operator, or has encountered some other limitation. See Chapter 6, Running Queries.

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

      1. Disable use_secondary_engine to force MySQL DB System execution.

      2. Run the query using EXPLAIN.

      3. Query the Last_query_cost status variable. If the value is less than 100000.00000, the query cannot be offloaded.

    • Solution C: The table you are querying is not loaded. You can check the load status of a table in HeatWave by querying LOAD_STATUS data 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 11.10.4, “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 Section 11.7, “System Variables” for rapid_plugin_bootstrapped status values.

      If the HeatWave cluster has failed, restart it and reload your data. For restart instructions, refer to the MySQL Database Service User Guide.

  • Problem: You cannot alter the table definition to exclude a column, define a string column encoding, or define data placement keys.

    Solution: Column attributes must be defined before or at the same that you define a secondary engine for a table. Defining a column attribute is not possible after a table is defined with a secondary engine, as DDL operations are not permitted on tables defined with a secondary engine. If you need to perform a DDL operation on a table that is defined with a secondary engine, remove the SECONDARY_ENGINE option first:

    mysql> ALTER TABLE orders SECONDARY_ENGINE NULL;
  • 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_CONSUMPTION strategy by setting rapid_execution_strategy prior to executing the query:

    SET SESSION rapid_execution_strategy = MIN_MEM_CONSUMPTION;

    Also consider checking the size of your data by performing a node count estimate. If your data has grown substantially, you may require additional HeatWave nodes. For node count estimate instructions, refer to the MySQL Database Service User Guide.

    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.

    Solution: TEXT-type values larger than 8000 bytes are rejected during SECONDARY_LOAD operations. Reduce the size of TEXT-type values to less than 8000 bytes or exclude the column before loading the table. See Section 3.2, “Excluding Table Columns”.

  • Problem: Change propagation fails with the following error: Blob/text value of n bytes was encountered during change propagation but RAPID supports text values only up to 8000 bytes.

    Solution: TEXT-type values larger than 8000 bytes are rejected during change propagation. Reduce the size of TEXT-type values to less than 8000 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 4.2, “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 dryrun mode 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 VARLEN to 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_advisor_report or heatwave_load_report table 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 CALL statement. 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.