WL#5692: Enable Disk-Sweep Multi-Range Read optimizer feature
Affects: Server-5.6 — Status: Complete
The optimizer feature named Disk-Sweep Multi-Range Read (DS-MRR or just MRR) has been implemented as part of WL#2474/WL#2475. The feature is currently in our source code but disabled when doing a normal build. In order to enable it you have to include a special pre-processor define either by including it on the command line or by editing the source. The purpose of this worklog is to: 1. enable Disk-Sweep Multi-Range Read in the server so that it can be used by the MySQL optimizer 2. enable the mrr switch to be configurable via the optimzer_switch configuration variable. The default value for mrr will be on. 3. enable the mrr_cost_based switch to be configurable via the optimizer_switch configuration variable. The default value for mrr_cost_based will be on. After this change the DS-MRR optimzier feature will be available for the optimizer to use for data stored in MyISAM and InnoDB. The use of it will be configurable by the user.
Multi-Range Read (MRR) and Disk-Sweep Multi-Range Read (DS-MRR) have been implemented in WL#2474/WL#247 (both normally referred to as just Multi-Range Read or MRR). The implementation is included in our source code but is not enabled for use. The goal of this worklog is to enable this optimizer feature so that it will be used by the MySQL optimizer and allow users to control whether it should be used or not. The following changes to MySQL will be implemented: 1. Enable use of DS-MRR/MRR: With this feature enabled it will be possible to retrieve data for range scans on indexes using the Disk-Sweep MRR implementation. This should improve accesses to data residing on disk by reducing the amount of random disk access to do more sequential disk access for records stored in the base table. 2. Enable the user to control the usage of MRR: The code for controlling whether MRR should be used or not is already in the server code (see WL#5116) but also currently disabled. We will enable the user to control MRR by using two new optimizer switch flags that can be controlled by the optimizer_switch variable (see http://dev.mysql.com/doc/refman/5.5/en/switchable-optimizations.html). The new optimizer switch flags are named: A. mrr - this controls whether or not MRR is available for use or not. It can take the following values "on", "off", "default". If its value is "on" then MRR is available for use (see more details further down). If it is "off" then MRR will never be used. The default value will be "on". B. mrr_cost_based - this controls whether whether the server should base it choise on using MRR based on cost-estimation or not. Note for this flag to have any effect the mrr optimizer_switch must be "on". If both mrr and mrr_cost_based are "on" the server will choose between using MRR or not based on cost estimates. If mrr is on and mrr_cost_based is off then the server will use MRR for all data accesses where MRR can be used. The default value for mrr_cost_based will be "on".
The main changes that will be done to code for implementing this worklog are: 1. Make MRR cost based become default: a. In sql/sql_priv.h: Include the OPTIMIZER_SWITCH_MRR_COST_BASED flag in the OPTIMIZER_SWITCH_DEFAULT macro for the case where OPTIMIZER_SWITCH_ALL is defined. b. Adjust test files. The change to enable MRR to be cost based by default will result that in most (all?) of our tests MRR will no longer be used when the test is run with default values. So to ensure that we still test the MRR implementation we will add mrr_cost_based=off to the tests that are supposed to be run with MRR (test files with _mrr in the name) and tests that are supposed to be run with all optimizer features (test files with _all in the name). 2. Enable MRR in default builds: a. In sql/sql_priv.h: Include the OPTIMIZER_SWITCH_MRR and OPTIMIZER_SWITCH_MRR_COST_BASED flags in the OPTIMIZER_SWITCH_DEFAULT macro also for the case where OPTIMIZER_SWITCH_ALL is undefined. b. In sql/sys_vars.cc: - Change *optimizer_switch_names variable so that "mrr" and "mrr_cost_based" are included also when OPTIMIZER_SWITCH_ALL is not defined. - Adjust text written by --help c. Adjust test result files.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.