Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3334

parameter optimizer_switch can not be changed

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 1.1.6, 1.1.7, 1.2.3
    • 5.4.3
    • MariaDB Server
    • CentOS Linux, Debian Linux, Docker

    Description

      Parameter optimizer_switch can not be changed and it is not possible to enable/disable specific optimizations of query optimizer.
      Testcase:
      1) SET optimizer_switch='index_merge=on';
      2) SHOW VARIABLES WHERE variable_name LIKE 'optimizer_switch';
      Result: index_merge=off......
      Outcome: optimization index_merge was not enabled.

      It is also not possible to enable these optimizations as session variable. Testcase:
      1) SET SESSION optimizer_switch='index_merge=on';
      2) SHOW SESSION VARIABLES WHERE variable_name LIKE 'optimizer_switch';
      Result: index_merge=off......
      Outcome: again, optimization index_merge was not enabled.

      I tried to configure optimizer_switch parameter also in my.cnf, but this modification was not reflected and all optimizations are always disabled:
      _index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=on,orderby_uses_equalities=off,condition_pushdown_for_derived=on,split_materialized=off
      _

      This bug is affecting me because I need to run some queries on InnoDB tables in MariaDB ColumnStore server. Without query optimizations I always have very silly query plan and complex queries with subselects do not use most of database indexes. When I executed the same query on standard MariaDB with query optimizations enabled in parameter optimizer_switch, all my queries were fast and indexes were used. My test query took 18 minutes on standard MariaDB and 50 hours on MariaDB ColumnStore on the same dataset.

      This behavior affects only MariaDB ColumnStore. Standard MariaDB without ColumnStore is not affected and optimizer_switch works according to documentation.

      Attachments

        Activity

          People

            Unassigned Unassigned
            m.liptak Maroš Lipták
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.