workload级别索引推荐

对于workload级别的索引推荐,用户可通过运行数据库外的脚本使用此功能,本功能将包含有多条DML语句的workload作为输入,最终生成一批可对整体workload的执行表现进行优化的索引。同时,本功能提供从日志中抽取业务数据SQL流水的功能。

前提条件

  • 数据库状态正常、客户端能够正常连接。
  • 当前执行用户下安装有gsql工具,该工具路径已被加入到PATH环境变量中。
  • 具备Python3.7的环境。
  • 若使用本功能提供的业务数据抽取功能,需提前将要收集的节点的GUC参数按如下设置:
    • log_min_duration_statement = 0

    • log_statement = 'all'

      说明: 业务数据抽取完毕后,建议将上述GUC参数复原,否则容易导致日志文件膨胀。

业务数据抽取脚本使用步骤

  1. 按前提条件中要求设置相关GUC参数。

  2. 运行命令如下:

    gs_dbmind component extract_log [-h] [-d DATABASE] [-U DB_USER] [--start-time START_TIME] [--sql-amount SQL_AMOUNT] [--statement] [--max-reserved-period MAX_RESERVED_PERIOD] [--max-template-num MAX_TEMPLATE_NUM] [--json] log_dir file line_prefix
    

    其中的输入参数依次为:

    • DATABASE:(可选)数据库名称,不指定时默认抽取所有数据库的数据。
    • DB_USER:(可选)用户名称,不指定时默认抽取所有用户的数据。
    • START_TIME:(可选)日志收集的开始时间,不指定时默认收集所有日志文件。
    • SQL_AMOUNT:(可选)收集日志中SQL数量的最大值,不指定时默认收集所有SQL。
    • statement:(可选)表示收集gs_log日志中statement标识开头的SQL,不指定时默认不收集。
    • MAX_RESERVED_PERIOD:(可选)指定json模式下,增量收集日志中保留的模板的最大的更新时长,不指定时默认都保留,单位:天。
    • MAX_TEMPLATE_NUM:(可选)指定json模式下保留的最大模板数量,不指定时默认都保留。
    • json:(可选)指定收集日志的文件存储格式为SQL归一化后的json,不指定时默认为每条SQL占一行。
    • log_dir:gs_log的存放目录。
    • file:输出SQL流水文件的保存路径,即抽取出的业务数据存放的文件路径。
    • line_prefix:指定每条日志信息的前缀格式,可通过show log_line_prefix查询。

    使用示例:

    gs_dbmind component extract_log $GAUSSLOG/gs_log/dn_6001 sql_log.txt '%m %c %d %p %a %x %n %e' -d testdb -U omm --start_time '2021-07-06 00:00:00' --statement
    

    说明: 若指定-d/-U参数,日志打印每条日志信息的前缀格式需包含%d、%u,若需要抽取事务,必须指定%p,详见log_line_prefix参数。max_template_num参数设置建议不超5000条,避免workload索引推荐执行时间过长。

  3. 1中设置的GUC参数还原为设置前的值。

索引推荐脚本使用步骤

  1. 准备好包含有多条DML语句的文件作为输入的workload,文件中每条语句占据一行。用户可从数据库的离线日志中获得历史的业务语句。

  2. 运行命令如下:

    gs_dbmind component index_advisor [-h] [--db-host DB_HOST] [-U DB_USER, --db-user DB_USER] [--schema SCHEMA] [--max-index-num MAX_INDEX_NUM] [--max-index-storage MAX_INDEX_STORAGE] [--multi-iter-mode] [--max-n-distinct MAX_N_DISTINCT] [--min-improved-rate MIN_IMPROVED_RATE] [--max-index-columns MAX_INDEX_COLUMNS] [--min-reltuples MIN_RELTUPLES] [--multi-node] [--json] [--driver] [--show-detail] [--show-benefits] [--advise_gsi] [--multi_thread_num MULTI_THREAD_NUM] db_port database file
    

    密码通过管道输入或交互式输入,对于免密用户,任意输入都可通过检验。命令行参数如表1所示:

    表 1 命令行参数

    参数

    参数说明

    取值范围

    -h

    (可选)返回帮助信息。

    -

    --db-host DB_HOST

    (可选)连接数据库的主机号。

    -

    -U DB_USER

    (可选)连接数据库的用户名。

    -

    --schema SCHEMA

    (必选)模式名称。

    -

    --max-index-num MAX_INDEX_NUM

    (可选)最大的索引推荐数目。

    >=1

    --max-index-storage MAX_INDEX_STORAGE

    (可选)最大的索引集合空间大小。

    >=1

    --multi-iter-mode

    (可选)算法模式,可通过是否设置该参数来切换算法。

    -

    --max-n-distinct MAX_N_DISTINCT

    (可选)(1/distinct数)的最大值,默认为0.01。

    0-1

    --min-improved-rate MIN_IMPROVED_RATE

    (可选)索引的最大提升幅度,默认为0.1,即提升10%。

    0-1

    --max-index-columns MAX_INDEX_COLUMNS

    (可选)联合索引的最大列数(默认为4)。

    >=1

    --min-reltuples MIN_RELTUPLES

    (可选)表的最小行数,默认为10000。

    >0

    --json

    (可选)指定workload语句的文件路径格式为SQL归一化后的json。

    -

    --driver

    (可选)指定是否使用python驱动器连接数据库,默认gsql连接。

    -

    --show-detail

    (可选)是否显示当前推荐索引集合的详细优化信息。

    -

    --show-benefits

    (可选)是否显示收益信息。

    -

    --advise_gsi

    (可选)不支持。

    -

    --multi_thread_num

    (可选)以多线程运行脚本,指定线程数。

    [1,64]

    db_port

    (必选)连接数据库的端口号。

    -

    database

    (必选)连接数据库的名字。

    -

    file

    (必选)包含workload语句的文件路径。

    -

    例如:

    gs_dbmind component index_advisor 6001 testdb tpcds_log.txt --schema public --max_index_num 10
    

    结果在屏幕输出,包含候选索引、推荐索引、已创建索引、无用索引(该给定的workload里面没有用到系统中的索引列表)、冗余索引(当前系统中重复创建的索引)以及历史有效索引,如下:

    ########################## Generate candidate indexes(FQS GSI) ###########################
    No candidate indexes generated!
    ############################### Generate candidate indexes ###############################
    table:  public.catalog_returns columns:  cr_return_amount
    table:  public.catalog_sales columns:  cs_item_sk
    table:  public.catalog_sales columns:  cs_sold_date_sk
    table:  public.customer_address columns:  ca_city type:  global
    table:  public.customer_address columns:  ca_state, ca_county type:  global
    table:  public.customer_demographics columns:  cd_demo_sk type:  local
    table:  public.date_dim columns:  d_month_seq type:  global
    table:  public.date_dim columns:  d_year type:  global
    table:  public.date_dim columns:  d_date_sk type:  local
    table:  public.date_dim columns:  d_month_seq type:  local
    table:  public.date_dim columns:  d_year type:  local
    table:  public.item columns:  i_class type:  global
    table:  public.item columns:  i_manager_id, i_brand_id type:  global
    table:  public.item columns:  i_manager_id, i_category_id type:  global
    table:  public.item columns:  i_manufact_id type:  global
    table:  public.item columns:  i_product_name type:  global
    table:  public.store_returns columns:  sr_cdemo_sk
    table:  public.store_returns columns:  sr_reason_sk
    table:  public.store_returns columns:  sr_return_amt
    table:  public.store_sales columns:  ss_item_sk, ss_sold_date_sk
    table:  public.store_sales columns:  ss_store_sk
    table:  public.time_dim columns:  t_time_sk type:  local
    table:  public.web_returns columns:  wr_return_amt
    table:  public.web_sales columns:  ws_item_sk
    table:  public.web_sales columns:  ws_web_page_sk, ws_ship_hdemo_sk, ws_sold_time_sk
    ############################### Determine optimal indexes ###############################
    CREATE INDEX idx_catalog_sales_cs_item_sk ON public.catalog_sales(cs_item_sk);
    CREATE INDEX idx_catalog_sales_cs_sold_date_sk ON public.catalog_sales(cs_sold_date_sk);
    CREATE INDEX idx_customer_demographics_local_cd_demo_sk ON public.customer_demographics(cd_demo_sk) local;
    CREATE INDEX idx_item_global_i_manufact_id ON public.item(i_manufact_id) global;
    CREATE INDEX idx_store_returns_sr_cdemo_sk ON public.store_returns(sr_cdemo_sk);
    CREATE INDEX idx_store_sales_ss_item_sk_ss_sold_date_sk ON public.store_sales(ss_item_sk, ss_sold_date_sk);
    CREATE INDEX idx_store_sales_ss_store_sk ON public.store_sales(ss_store_sk);
    CREATE INDEX idx_web_sales_ws_web_page_sk_ws_ship_hdemo_sk_ws_sold_time_sk ON public.web_sales(ws_web_page_sk, ws_ship_hdemo_sk, ws_sold_time_sk);
    ################################# Created indexes ###############################
    public: CREATE UNIQUE INDEX ship_mode_pkey ON ship_mode USING btree (sm_ship_mode_sk) LOCAL(PARTITION p_list_15_sm_ship_mode_sk_idx, PARTITION p_list_14_sm_ship_mode_sk_idx, PARTITION p_list_13_sm_ship_mode_sk_idx, PARTITION p_list_12_sm_ship_mode_sk_idx, PARTITION p_list_11_sm_ship_mode_sk_idx, PARTITION p_list_10_sm_ship_mode_sk_idx, PARTITION p_list_9_sm_ship_mode_sk_idx, PARTITION p_list_8_sm_ship_mode_sk_idx, PARTITION p_list_7_sm_ship_mode_sk_idx, PARTITION p_list_6_sm_ship_mode_sk_idx, PARTITION p_list_5_sm_ship_mode_sk_idx, PARTITION p_list_4_sm_ship_mode_sk_idx, PARTITION p_list_3_sm_ship_mode_sk_idx, PARTITION p_list_2_sm_ship_mode_sk_idx, PARTITION p_list_1_sm_ship_mode_sk_idx)  TABLESPACE pg_default;
    public: CREATE INDEX temptable_int2_int3_int4_idx ON temptable USING btree (int2, int3, int4) TABLESPACE pg_default;
    public: CREATE INDEX temptable_int2_int3_idx ON temptable USING btree (int2, int3) TABLESPACE pg_default;
    public: CREATE INDEX temptable_int1_int2_int3_idx ON temptable USING btree (int1, int2, int3) TABLESPACE pg_default;
    public: CREATE INDEX temptable_int1_int2_idx ON temptable USING btree (int1, int2) TABLESPACE pg_default;
    public: CREATE INDEX temptable_int1_idx ON temptable USING btree (int1) TABLESPACE pg_default;
    ############################### Current workload useless indexes ###############################
    DROP INDEX temptable_int2_int3_int4_idx;
    DROP INDEX temptable_int2_int3_idx;
    DROP INDEX temptable_int1_int2_int3_idx;
    DROP INDEX temptable_int1_int2_idx;
    DROP INDEX temptable_int1_idx;
    ############################### Redundant indexes ###############################
    DROP INDEX public.test1_age_idx;(CREATE INDEX test1_age_idx ON test1 USING btree (age) TABLESPACE pg_default)
    Related indexes:
            CREATE INDEX test1_age_id_idx ON test1 USING btree (age, id) TABLESPACE pg_default
    
    DROP INDEX public.test1_id_idx;(CREATE INDEX test1_id_idx ON test1 USING btree (id) TABLESPACE pg_default)
    Related indexes:
            CREATE INDEX test1_id_age_idx ON test1 USING btree (id, age) TABLESPACE pg_default
    ############################### Historical effective indexes ###############################
    CREATE INDEX idx_temptable_int2 ON ztt_test.temptable(int2);
    CREATE INDEX idx_item_i_manufact_id ON public.item(i_manufact_id);
    CREATE INDEX idx_item_i_color ON public.item(i_color);
    

说明:

  • 如前文所述,“Current workload useless indexes”、“Redundant indexes”分别表示无用索引和冗余索引,判断依据是给定的workload。由于workload中的SQL语句可能出现缺失(例如由日志报错、没有捕获到等原因导致的),故该结论仅供提示,用户需要根据自己的业务逻辑进行排查,防止错误删除。
  • 与单query索引推荐相同,本功能暂不支持段页式表、普通视图、物化视图、全局临时表、二级分区表以及密态数据库。
意见反馈
编组 3备份
    openGauss 2025-06-07 22:42:34
    取消