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

2.8.1 Advisor Syntax

mysql> CALL sys.heatwave_advisor ([options]);
 options: {
     JSON_OBJECT('key','value'[,'key','value'] ...)
        ['exclude_query',JSON_ARRAY('query_id'[,'query_id'] ...)]
        ['query_session_id',JSON_ARRAY('query_session_id'[,'query_session_id'] ...)]

    auto_enc_option: {
                    [,'schema.tbl.col',{'varlen'|'dictionary'}] ...]

     auto_dp_option: {

 auto_unload_option: {
                    ['exclude_list',JSON_ARRAY(schema_name_1, schema_name_2.table_name_1, ...)]

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

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

  • 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, “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, “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.