10.1.5 HEATWAVE_ADVISOR

mysql> CALL sys.HEATWAVE_ADVISOR (options);
  
options: NULL 
       | JSON_OBJECT(keyvaluepair[, keyvaluepair] ...)
keyvaluepair: 'output', {'normal'|'silent'|'help'}
            | 'target_schema', JSON_ARRAY('db_name'[, 'db_name']...)
            | 'exclude_query', JSON_ARRAY('query_id'[, 'query_id']...)
            | 'query_session_id', JSON_ARRAY('query_session_id'[, 'query_session_id']...)
            | 'query_insights', {true|false}
            | 'auto_enc', JSON_OBJECT(auto_enc_option)
            | 'auto_dp', JSON_OBJECT(auto_dp_option[, auto_dp_option])
            | 'auto_unload', JSON_OBJECT(auto_unload_option[, auto_unload_option]...)

auto_enc_option: 'mode', {'off'|'recommend'} [, 'fixed_enc', JSON_OBJECT(col_enc[, col_enc]...)]
col_enc: 'db_name.tbl_name.col_name', {'varlen'|'dictionary'}

auto_dp_option: 'benefit_threshold',N
              | 'max_combinations',N
    
auto_unload_option: 'mode', {'off'|'recommend'}
                  | 'exclude_list', JSON_ARRAY('db_obj'[, 'db_obj']...)
                  | 'last_queried_hours', N
                  | 'memory_gain_ascending', {true|false}
                  | 'limit_tables', N
db_obj: db_name
       db_name.tbl_name

Advisor options are specified as key-value pairs in JSON format. Options include:

• output: Defines how Advisor produces output. Permitted values are:

  • normal: The default. Produces summarized output and sends it to stdout and to the heatwave_autopilot_report table. See Section 5.8.8, “Autopilot Report Table”.

  • silent: Sends output to the heatwave_autopilot_report table only. See Section 5.8.8, “Autopilot Report Table”. The silent output type is useful if human-readable output is not required; when the output is consumed by a script, for example.

  • help: Displays Advisor command-line help.

• target_schema: Defines one or more schemas for Advisor to analyze. The list is specified as a JSON array. If a target schema is not specified, Advisor analyzes all schemas in the MySQL HeatWave Cluster. When a target schema is specified, Advisor generates recommendations for tables belonging to the target schema. For the most accurate recommendations, specify one schema at a time. Only run Advisor on multiple schemas if the queries access tables in multiple schemas.

• exclude_query: Defines the IDs of queries to exclude when Advisor analyzes query statistics. To identify query IDs, query the performance_schema.rpd_query_stats table. For a query example, see Auto Data Placement Examples.

• query_session_id: Defines session IDs for filtering queries by session ID. To identify session IDs, query the performance_schema.rpd_query_stats table. For a query example, see Auto Query Time Estimation Examples.

• query_insights: Provides runtime information for successfully executed queries and runtime estimates for EXPLAIN queries, queries cancelled using Ctrl+C, and queries that fail due to an out-of-memory error. See: Section 5.8.6, “Auto Query Time Estimation”. The default setting is false.

auto_enc: Defines settings for Auto Encoding, see Section 5.8.4, “Auto Encoding”. Options include:

• mode: Defines the operational mode. Permitted values are:

  • off: The default. Disables Auto Encoding.

  • recommend: Enables Auto Encoding.

• fixed_enc: Defines an encoding type for specified columns. Use this option if you know the encoding you want for a specific column and you are not interested in an encoding recommendation for that column. Only applicable in recommend mode. Columns with a fixed encoding type are excluded from encoding recommendations. The fixed_enc key is a fully qualified column name without backticks in the following format: schema_name.tbl_name.col_name. The value is the encoding type; either varlen or dictionary. Multiple key-value pairs can be specified in a comma-separated list.

auto_dp: Defines settings for Data Placement, which recommends data placement keys. See: Section 5.8.5, “Auto Data Placement”. Options include:

• benefit_threshold: The minimum query performance improvement expressed as a percentage value. Advisor only suggests data placement keys estimated to meet or exceed the benefit_threshold. The default value is 0.01 (1%). Query performance is a combined measure of all analyzed queries.

• max_combinations: The maximum number of data placement key combinations Advisor considers before making recommendations. The default is 10000. The supported range is 1 to 100000. Specifying fewer combinations generates recommendations more quickly but recommendations may not be optimal.

auto_unload: Defines settings for Unload Advisor, which recommends tables to unload. See: Section 5.8.7, “Unload Advisor”. Options include:

• mode: Defines the operational mode. Permitted values are:

  • off: The default. Disables Unload Advisor.

  • recommend: Enables Unload Advisor.

• exclude_list: Defines a list of schemas and tables to exclude from Unload Advisor. Names must be fully qualified without backticks.

• last_queried_hours: Recommend unloading tables that were not queried in the past last_queried_hours hours. Minimum: 1, maximum 744, default: 24.

• memory_gain_ascending: Whether to rank the unload table suggestions in ascending or descending order based on the table size. Default: false.

• limit_tables: A limit to the number of unload table suggestions, based on the order imposed by memory_gain_ascending. The default is 10.

Advisor command-line help

To access the Advisor command-line help:

mysql> CALL sys.HEATWAVE_ADVISOR(JSON_OBJECT('output','help'));