WL#5692: Enable Disk-Sweep Multi-Range Read optimizer feature

Affects: Server-5.6   —   Status: Complete   —   Priority: Medium

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.