MySQL :: HeatWave User Guide :: 2.9.7 Running Queries

HeatWave User Guide / ... / Running Queries

2.9.7 Running Queries

The following practices are recommended when running queries:

If a query fails to offload and you cannot identify the reason, enable tracing and query the INFORMATION_SCHEMA.OPTIMIZER_TRACE table to debug the query. See Section 2.3.6, “Debugging Queries”.

If the optimizer trace does not return all of the trace information, increase the optimizer trace buffer size. The MISSING_BYTES_BEYOND_MAX_MEM_SIZE column of the INFORMATION_SCHEMA.OPTIMIZER_TRACE table shows how many bytes are missing from a trace. If the column shows a non-zero value, increase the optimizer_trace_max_mem_size setting accordingly. For example:
```
SET optimizer_trace_max_mem_size=1000000;
```

If an INFORMATION_SCHEMA.OPTIMIZER_TRACE query trace indicates that a subquery is not yet supported, try unnesting the subquery. For example, the following query contains a subquery and is not offloaded as indicated by the EXPLAIN output, which does not show “Using secondary engine”.

mysql> EXPLAIN SELECT COUNT(*) 
          FROM orders o 
          WHERE o_totalprice> (SELECT AVG(o_totalprice) 
          FROM orders 
          WHERE o_custkey=o.o_custkey);
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: o
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 10.00
        Extra: Using where
2 rows in set, 2 warnings (0.00 sec)

This query can be rewritten as follows to unnest the subquery so that it can be offloaded.

mysql> EXPLAIN SELECT COUNT(*) 
          FROM orders o, (SELECT o_custkey, AVG(o_totalprice) a_totalprice 
          FROM orders 
          GROUP BY o_custkey)a 
          WHERE o.o_custkey=a.o_custkey AND o.o_totalprice>a.a_totalprice;

By default, SELECT queries are offloaded to HeatWave for execution and fall back to the MySQL DB System if that is not possible. To force a query to execute on HeatWave or fail if that is not possible, set the use_secondary_engine variable to FORCED. In this mode, a SELECT statement returns an error if it cannot be offloaded. The use_secondary_engine variable can be set as shown:
- Using a SET statement before running queries:
```
mysql> SET SESSION use_secondary_engine = FORCED;
```
- Using a SET_VAR optimizer hint when issuing a query:
```
mysql> SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ ... FROM ...
```

If you encounter out-of-memory errors when running queries:

Avoid or rewrite queries that produce a Cartesian product. In the following query, a JOIN predicated is not defined between the supplier and nation tables, which causes the query to select all rows from both tables:

mysql> SELECT s_nationkey, s_suppkey, l_comment FROM lineitem, supplier, nation
          WHERE s_suppkey = l_suppkey LIMIT 10;
ERROR 3015 (HY000): Out of memory in storage engine 'Failure detected in RAPID; query 
execution cannot proceed'.

To avoid the Cartesian product, add a relevant predicate between the supplier and nation tables to filter out rows:

mysql> SELECT s_nationkey, s_suppkey, l_comment 
          FROM lineitem, supplier, nation 
          WHERE s_nationkey = n_nationkey AND s_suppkey = l_suppkey LIMIT 10;

Avoid or rewrite queries that produce a Cartesian product introduced by the MySQL optimizer. Due to lack of quality statistics or non-optimal cost decisions, MySQL optimizer may introduce one or more Cartesian products in a query even if a query has predicates defined among all participating tables. For example:

mysql> SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment
          FROM lineitem, supplier, nation, customer, orders 
          WHERE c_custkey = o_custkey AND o_orderkey = l_orderkey
          AND c_nationkey = s_nationkey AND c_nationkey = n_nationkey AND c_custkey < 3000000
          LIMIT 10;
ERROR 3015 (HY000): Out of memory in storage engine 'Failure detected in RAPID; 
query execution cannot proceed'.

The EXPLAIN plan output shows that there is no common predicate between the first two table entries (NATION and SUPPLIER).

mysql> EXPLAIN SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment 
          FROM lineitem, supplier, nation, customer, orders
          WHERE c_custkey = o_custkey AND o_orderkey = l_orderkey AND c_nationkey = s_nationkey
          AND c_nationkey = n_nationkey AND c_custkey < 3000000 LIMIT 10;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: supplier
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99626
     filtered: 100.00
        Extra: Using secondary engine RAPID
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: nation
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1382274
     filtered: 5.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 56834662
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID

To force a join order so that there are predicates associated with each pair of tables, add a STRAIGHT_JOIN hint. For example:

mysql> EXPLAIN SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment
          FROM SUPPLIER STRAIGHT_JOIN CUSTOMER STRAIGHT_JOIN NATION STRAIGHT_JOIN ORDERS
          STRAIGHT_JOIN LINEITEM WHERE c_custkey = o_custkey and o_orderkey = l_orderkey
          AND c_nationkey = s_nationkey AND c_nationkey = n_nationkey AND c_custkey < 3000000
          LIMIT 10;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: supplier
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99626
     filtered: 100.00
        Extra: Using secondary engine RAPID
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1382274
     filtered: 5.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: nation
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 56834662
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID

Avoid or rewrite queries that produce a very large result set. This is a common cause of out of memory errors during query processing. Use aggregation functions, a GROUP BY clause, or a LIMIT clause to reduce the result set size.
Avoid or rewrite queries that produce a very large intermediate result set. In certain cases, large result sets can be avoided by adding a STRAIGHT_JOIN hint, which enforces a join order in a decreasing of selectiveness.
Check the size of your data by performing a cluster size estimate. If your data has grown substantially, the HeatWave Cluster may require additional nodes.
- For HeatWave on OCI, see Generating a Node Count Estimate in the HeatWave on OCI Service Guide.
- For HeatWave on AWS, see Estimating Cluster Size with HeatWave Autopilot in the HeatWave on AWS Service Guide.
- For HeatWave for Azure, see Provisioning HeatWave Nodes in the HeatWave for Azure Service Guide.
HeatWave optimizes for network usage by default. Try running the query with the MIN_MEM_CONSUMPTION strategy by setting by setting rapid_execution_strategy to MIN_MEM_CONSUMPTION. The rapid_execution_strategy variable can be set as shown:
- Using a SET statement before running queries:
```
mysql> SET SESSION rapid_execution_strategy = MIN_MEM_CONSUMPTION;
```
- Using a SET_VAR optimizer hint when issuing a query:
```
mysql> SELECT /*+ SET_VAR(rapid_execution_strategy = MIN_MEM_CONSUMPTION) */ ... FROM ...
```
- Unloading tables that are not used. These tables consume memory on HeatWave nodes unnecessarily. See Section 2.5.1, “Unloading Tables”.
- Excluding table columns that are not accessed by your queries. These columns consume memory on HeatWave nodes unnecessarily. This strategy requires reloading data. See Section 2.2.2.1, “Excluding Table Columns”.
After running queries, consider using HeatWave Autopilot Advisor for encoding and data placement recommendations. See Section 2.7, “Workload Optimization for OLAP”.

PREV HOME UP NEXT