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. Overview ======== 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.
Definitions: 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 Dictionary. 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 */ "setup_consumers", /* Definition */ " NAME VARCHAR(64) not null,\n" " ENABLED ENUM ('YES', 'NO') not null,\n" " PRIMARY KEY (NAME) USING HASH\n", /* Options */ " ENGINE=PERFORMANCE_SCHEMA" ); 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.
Copyright (c) 2000, 2020, Oracle Corporation and/or its affiliates. All rights reserved.