Documentation Home
MySQL HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.6Mb
PDF (A4) - 1.6Mb


MySQL 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.5, “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:

    1. 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;
    2. 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
    3. 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.

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

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

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

    7. After running queries, consider using HeatWave Advisor for encoding and data placement recommendations. See Section 2.8, “Workload Optimization using Advisor”.