2.8.3.2 Auto Data Placement Examples

• Invoking Advisor without any options runs the Data Placement Advisor with the default option settings.

  mysql> CALL sys.heatwave_advisor(NULL);

• Running Advisor with only the target_schema option runs the Data Placement Advisor on the specified schemas with the default option settings.

  mysql> CALL sys.heatwave_advisor(JSON_OBJECT("target_schema",JSON_ARRAY("tpch","employees")));

• Running the Advisor with the data placement max_combinations and benefit_threshold parameters. For information about these options, see Section 2.8.1, “Advisor Syntax”.

  mysql> CALL sys.heatwave_advisor(JSON_OBJECT("auto_dp",
            JSON_OBJECT("max_combinations",100,"benefit_threshold",20)));

• The following example shows how to view the HeatWave query history by querying the performance_schema.rpd_query_stats table, and how to exclude specific queries from Data Placement Advisor analysis using the exclude_query option:

  mysql> SELECT query_id, LEFT(query_text,160) 
            FROM performance_schema.rpd_query_stats;
  
  mysql> CALL sys.heatwave_advisor(JSON_OBJECT("target_schema",
            JSON_ARRAY("tpch"),"exclude_query",JSON_ARRAY(1,11,12,14)));

• This example demonstrates how to invoke the Data Placement Advisor with options specified in a variable:

  mysql> SET @options = JSON_OBJECT(
              "target_schema", JSON_ARRAY("analytics45","sample_schema"),
              "exclude_query", JSON_ARRAY(12,24),
              "auto_dp", JSON_OBJECT(
                     "benefit_threshold", 12.5,
                     "max_combinations", 100
  ));
  
  mysql> CALL sys.heatwave_advisor( @options );

• This example demonstrates how to invoke Advisor in silent output mode, which is useful if the output is consumed by a script, for example. Auto Data Placement is run by default if no option such as auto_enc or query_insights is specified.

  mysql> CALL sys.heatwave_advisor(JSON_OBJECT("output","silent"));