WL#7316: Optimizer cost model: Command for online updating of cost model constants

Status: Complete   —   Priority: Medium

The optimizer cost model will store its "cost constants" in database tables. The
optimizer cost model can then be adjusted by updating the
"cost constants" in these tables. After the cost constant tables have been
updated, the new "cost constants" must be re-loaded into the cost constant
cache. This worklog implements support for doing the reloading
of constants on a running server. The initial idea for how to do this is to add
a FLUSH command that will read the cost constant tables and insert the new
values into the cost constant cache.

This worklog is part of making the hard-coded "cost constants"
configurable. It should be noted that adding the support for allowing online
changing of cost constants might not be included in the initial
version.

User Documentation
==================

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html
http://dev.mysql.com/doc/refman/5.7/en/cost-model.html
Functional requirements:

F-1: A new option to the FLUSH command shall be implemented:

       FLUSH OPTIMIZER_COSTS

F-2: To run FLUSH with the new option shall require the same privileges as
     common for the other FLUSH command options.

F-3: Running FLUSH with this new option shall not return any warnings or errors.

F-4: Running FLUSH OPTIMIZER_COSTS shall read the cost constant tables and make
     the server start using the updated cost constants.

F-5: After running this command, new session shall use the updates cost constants
     from the cost tables.

F-6: Existing sessions shall not be influenced and shall continue to use the cost
     constants that were current when the session started.

F-7: The reading of the cost constant tables and updating of the in-memory cost
     constant cache shall be as described in the functional specification for
     WL#7315.

F-8: Handling of invalid entries in the cost constant tables shall give warnings
     in the server error log as described in the functional specification for
     WL#7315.
This worklog will implement a new option to the FLUSH command that will reload
the "cost constants" from the cost constant tables and make the
optimizer start using the new version of the cost constants. The syntax for the
new command is:

  FLUSH OPTIMIZER_COSTS;

All functionality for reading the cost constant tables and updating the cost
constant cache with a new version of the cost constants was implemented before.
This worklog will add the functionality needed to do online updates of
the cost constants after having done updates to the cost constant tables.
When the server administrator runs the FLUSH OPTIMIZER_COSTS command, it will
call the Cost_constant_cache::reload() function that will do the needed work.

The new FLUSH command will not return any warnings or errors. If there are
problems reading the cost constant tables or the cost constant tables contain
invalid entries, this will be reported by writing warnings in the server error
log.

This new option to the FLUSH command will require the same privileges as most of
the other FLUSH commands.

ALTERNATIVES:
=============

The following alternatives were considered:

1. Use TRIGGERs on the cost constant tables. These triggers could either do the
   complete job of reloading the in-memory cost constant configuration from the
   table or just set a "flag" that triggered reloading at some later time.

   Status: It is not possible to create triggers for system tables in the
   "mysql" database. So if this should be implemented, the cost constant tables
   must be stored somewhere else than in the "mysql" database.

2. Extend FLUSH TABLE to reload the cost constants if the flushed tables are
   one of the cost constants tables. So if the user runs either of these:

    FLUSH TABLE mysql.server_cost;
    FLUSH TABLE mysql.engine_cost;

   the cost constant tables will be read and the in-memory cost constant
   configuration will be updated with the content of the tables.

   This seems like a easy way to refresh the cost constants into memory without
   having to extend the FLUSH command with a new option but might be a bit like
   a hack.
The following is implemented for adding the new FLUSH OPTIMIZER_COSTS command:

1. A new refresh sub-command is defined in include/mysql_com.h:

   #define REFRESH_OPTIMIZER_COSTS      0x200000L /* FLUSH OPTIMIZER_COSTS */

2. A new symbol is added to the lexer (in sql/lex.h):

   { "OPTIMIZER_COSTS",SYM(OPTIMIZER_COSTS)},

3. A new token is added to the grammar (in sql_yacc.yy):

   %token OPTIMIZER_COSTS_SYM

   and the flush_options rule is extended with:

     | OPTIMIZER_COSTS_SYM
     { Lex->type|= REFRESH_OPTIMIZER_COSTS; }

4. The actual call to reload the cost constants is added to
   reload_acl_and_cache() in sql_reload.cc:

     if (options & REFRESH_OPTIMIZER_COSTS)
       reload_optimizer_cost_constants();

   The function reload_optimizer_cost_constants() is implemented in WL#7315.