WL#7900: New DD: Support flexible creation and versioning of virtual P_S tables

Affects: Server-8.0   —   Status: Complete

The scope of this worklog is limited only by server performance schema tables.
Plugin specific performance schema tables is out of scope for this worklog and
covered by separate worklog.


Supporting the creation and versioning of server P_S tables in the new DD
has two related aspects:

1. Create the P_S tables on behalf of the server P_S implementation to make
   the virtual tables visible in I_S queries. The table definitions may be
   retrieved from the P_S implementation in terms of SQL DDL statements.
   This mechanism may replace the currently hard coded table definitions
   executed when the server is initialized.

2. Provide support for versioning of the P_S table definitions to allow the
   table definitions to change independently of the server- or dictionary
   versioning scheme. This requires the versioning number to be stored in 
   mysql.dd_properties in the same way as for the I_S views.

Use cases

1. Currently, the P_S table definitions are retrieved from SQL scripts
   executed when the server is bootstrapped. Instead, the table definitions
   can be retrieved from a P_S function as SQL DDL statements, which can
   then be executed by the DD initialization code. A mechanism similar to
   what the server and the DDSE uses for allowing the server to create
   tables on behalf the DDSE, may be used. The benefit of this would be to
   make the P_S more self contained. This worklog doesn't cover support
   of plugin versioning that will be implemented by a separate worklog.

   Since the I_S virtual tables are implemented as views on top of the 
   dictionary tables, it would simplify the implementation to have also the P_S 
   table meta data stored persistently. Otherwise, the I_S implementation would 
   need to fetch the P_S table meta data from the P_S implementation on demand, 
   thus complicating the implementation somewhat by requiring special handling 
   for P_S virtual tables.

2. For the P_S virtual tables, there is no persistent data, so upgrading to new
   P_S table definitions will simply mean to drop the current definitions and
   replace them by the new ones. The procedure will involve consulting, on
   server restart, the mysql.dd_properties table as part of the server
   performance schema initialization. If performance schema version stored
   as a property in this table differs from a version of performance schema
   supported by server the new P_S tables will be created basing on the set of
   table definitions returned by server performance schema.
Server P_S tables are performance tables supported by mysql server.

The following requirements are relevant for allowing the server to create and
maintain server P_S tables:

FR1. DDL SQL-statements for creation server P_S tables must be removed from
     the file mysql_system_tables.sql

FR2. DDL for creation of server P_S tables must be embedded in the server
     source code.

FR3. Server P_S tables must be created during the server initialization.

FR4. Version of server performance schema must be stored in the Data

FR5. On server restart performance schema tables that are no longer supported
     must be dropped, new ones must be created.
The goal of this worklog is to implement creating server P_S tables based on
table definitions provided by performance schema engine and taking into
account a version of performance schema supported by the engine.

For server P_S tables it is required to store a version of performance schema
supported by server. This information will be stored in the table
mysql.dd_propertiesas as a property with the name "PS_version".

For every created server P_S table the new boolean property with the name
"server_p_s_table" and the value "true" is stored in the column "options" of
the table mysql.tables. This property allows to find all server P_S tables
supported by the mysql server.

Creating server P_S tables.
During server initialization the server P_S tables are created and a version of
performance_schema is stored as key-value pair "PS_version=actual_ver_num" in
the table mysql.dd_properties. For every created server P_S table the option
with the name "server_p_s_table" is added to the table's metadata stored in the
Data Dictionary.

Note: P_S tables are not written to binlog since performance schema
initialization is happened before binlog is opened.

To define a set of server P_S tables embedded into the server the class 
Plugin_table is used. Every server P_S table is defined in terms of instance of
class Plugin_table. All definitions of server P_S tables are placed in the file
ha_perschema.cc. For example, a definition of P_S table "setup_consumers" can be
expressed in the following way:

  static Plugin_table pfst_setup_consumers(
    /* Name */
    /* Definition */
    "  NAME VARCHAR(64) not null,\n"
    "  ENABLED ENUM ('YES', 'NO') not null,\n"
    /* Options */

The new function pfs_dict_init is introduced in the file ha_perfschema.cc.
This function has the following signature:
  static bool pfs_dict_init(dict_init_mode_t               dict_init_mode,
                            uint version,
                            List*      tables,
                            List* tablespaces)
and is used to initialize the member dict_init of performance schema handlerton.
Later the function pfs_dict_init is called on server start up to get definitions
of P_S tables supported by the current version of the server. When this function
is called every instance of Plugin_table is added to the list of P_S tables
(specified by third arguments) that must be created as part of
performance_schema engine initialization. This list is used later for producing
SQL DDL statements (represented as strings) for creating server P_S tables.
After SQL DDL statements for creating server P_S tables are generated the
function dd::performance_table::create_pfs_tables() is called. This function
iterates along the list of SQL DDL statements and runs the function 
execute_query() to create server P_S tables.

Working with server performance schema tables on server restart.
On every server restart the value of property PS_version is extracted from the
table mysql.dd_properties and checked against the version of server performance
schema hardcoded in the mysql server. If version values are different all "old"
server P_S tables are dropped and new P_S tables supported by the current server
version are created. To find all old server P_S tables the Data Dictionary table 
mysql.tables is queried and for every table from the schema "performance_schema"
a value of column "options" is extracted. Those tables that contain a property
with the name "server_p_s_table" in the value of column "options" are added to
a set of old P_S tables to drop.