2.8.1 Advisor Syntax

mysql> CALL sys.heatwave_advisor ([options]);
  
 options: {
     JSON_OBJECT('key','value'[,'key','value'] ...)
        'key','value': 
        ['output',{'normal'|'silent'|'help'}]
        ['target_schema',JSON_ARRAY({'schema_name'[,'schema_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_unload',JSON_OBJECT(auto_unload_option)]
}

    auto_enc_option: {
                    ['mode',{'off'|'recommend'}]
                    ['fixed_enc',JSON_OBJECT('schema.tbl.col',{'varlen'|'dictionary'}
                    [,'schema.tbl.col',{'varlen'|'dictionary'}] ...]
}

     auto_dp_option: {
                    ['benefit_threshold',N]
                    ['max_combinations',N]
}

 auto_unload_option: {
                    ['mode',{'off'|'recommend'}]
                    ['exclude_list',JSON_ARRAY(schema_name_1, schema_name_2.table_name_1, ...)]
                    ['last_queried_hours',N]
                    ['memory_gain_ascending',{true|false}]
                    ['limit_tables',N]
}

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 6.1, “HeatWave Autopilot Report Table”. Before MySQL 8.0.32, it sends it to the heatwave_advisor_report table. See Section 2.8.7, “Advisor Report Table”.

  • silent: Sends output to the heatwave_autopilot_report table only. See Section 6.1, “HeatWave Autopilot Report Table”. Before MySQL 8.0.32, it sends it to the heatwave_advisor_report table. See Section 2.8.7, “Advisor 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. See Section 2.8.6, “Advisor Command-line Help”.

• target_schema: Defines one or more schemas for Advisor to analyze. The list is specified in JSON-array format. If a target schema is not specified, all schemas in the HeatWave Cluster are analyzed. 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 your 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 Section 2.8.3.2, “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 Section 2.8.4.3, “Query Insights 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 2.8.4, “Query Insights”. The default setting is false.

auto_enc: Defines settings for Auto Encoding, see Section 2.8.2, “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 2.8.3, “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 2.8.5, “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.