Query Accelerator

What is Query Accelerator

Query Accelerator is a feature that allows MariaDB to use ColumnStore to execute queries that are otherwise executed by InnoDB. Under the hood Columnstore:

• receives a query

• searches for applicable Engine Independent statistics for InnoDB table index column

• applies RBO rule to transform its InnoDB tables into a number of UNIONs over non-overlapping ranges of a suitable InnoDB table index

• retrieves the data in parallel from MariaDB and runs it using Columnstore runtime

How to Enable Query Accelerator

• One has to set columnstore_innodb_queries_use_mcs = on in MariaDB configuration file and restart MariaDB server(my.cnf).

• Set a number of parameters in a client session:

set columnstore_unstable_optimizer=on;
set optimizer_switch="index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,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=off,orderby_uses_equalities=off,condition_pushdown_for_derived=on,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off,cset_narrowing=off,sargable_casefold=off";

Enable ColumnStore Processing for InnoDB Tables

There must be Engine Independent statistics for InnoDB table index column to be used for Query Accelerator.

analyze table <table_name> persistent for columns (<column_name>) indexes();

Control Client Session Variables and Parameters

• columnstore_unstable_optimizer: enables unstable optimizer that is required for Query Accelerator RBO rule

• columnstore_select_handler: enables/disables ColumnStore processing for InnoDB tables

• columnstore_query_accel_parallel_factor: controls the number of parallel ranges to be used for Query Accelerator

Watch out max_connections. If you set columnstore_query_accel_parallel_factor to a high value, you may need to increase max_connections to avoid connection pool exhaustion.

How to Verify Query Accelerator is Being Used

There are two ways to verify Query Accelerator is being used:

1. Use select mcs_get_plan('rules') to get a list of the rules that were applied to the query.

2. Look for patterns like derived table - $added_sub_#db_name_#table_name_X in the optimized plan using select mcs_get_plan('optimized').