The INFORMATION_SCHEMA is schema/database within MySQL that contains a series of tables which report rows of data describing various types of internal information. Most of these tables can also be displayed using one of the various SHOW commands. It is implemented within the server in many places but mostly in a file called sql_show.cc. There are two general types of schema tables; 1) Server tables in which the column and index metadata is hard coded (as defined in http://en.wikipedia.org/wiki/Hard_coding) so table structure is always in memory and is fixed for a given MySQL version. 2) Storage Engine tables are declared as new information_schema plugins by storage engines. Since these may be compiled separately, the metadata for these tables are not hard-coded within the server, but discovered when the plugin is loaded. The INFORMATION_SCHEMA implementation uses internally "SQL derived tables" (internal temporary tables of the optimizer) as a temporary, intermediate storage for produced contents. This creates extra overhead during execution and is not the right tool for the job because: * In MySQL, there is no generic mechanism implemented for optimization of WHERE clauses against an internal temporary table - prior to introduction of this usage there were no use for this. * The current mechanism is not suited for producing contents on-demand, that is, producing data only when a particular row or column is accessed. * This mechanism creates unnecessary overhead and implementation complexity when providing data that is otherwise fully in-memory. Some examples of INFORMATION SCHEMA tables that are derived from in-memory sources are; CHARACTER_SETS, COLUMN_PRIVILEGES, COLLATIONS, ENGINES, GLOBAL_STATUS, GLOBAL_VARIABLES, PLUGINS, PROCESSLIST, SESSION_STATUS, SESSION_VARIABLES & STATISTICS. Whereas, long term, we would like to use base physical tables of the data dictionary as the underlying storage for strict *INFORMATION_SCHEMA* views (see WL#4039), such as TABLES, COLUMNS, KEY_COLUMN_USAGE, we still need a more efficient and easily programmable way to support views of volatile data (that is; data that is modified by a source external from the declaring object). The proposal of this worklog is to use our storage engine API to implement a special "information schema" storage engine, that will be used to produce the necessary data. This underlying mechanism is more suited for the task because the storage engine API; * Supports condition pushdown * Supports operation costs * Has support for index declaration and can automatically let the optimizer use the needed index. Indexes can be used for; 1) The natural index provided by the internal layout of data, e.g. if this data is stored on disk. (Konstantin explains the meaning of "natural index" and "hash index" here: [mysql intranet archive] /secure/mailarchive/mail.php?folder=4&mail=35344. "Supporting a "natural" index on information_schema.tables would be telling the optimizer that there is a unique key on the table name + db name. With that metadata the optimizer can faster and without hacks employed today resolved WHERE table_name= type queries. This "index" is maintained by the filesystem, and even though it's not utf8_general_ci, which is collation of the column, it's in some known collation.Why does the collation of the index have to match the collation of the column?.") Since currently we do not have an internal table-based data dictionary, it is suggested that this underlying mechanism is used for *all* INFORMATION_SCHEMA tables currently available in the system. In the future, for data that is located in base tables, we may use a different, separate mechanism to access it. The main purpose of this worklog is to improve the performance of INFORMATION_SCHEMA table access. This will add to the performance gain implemented by WL#3732, which was a less intrusive way of limiting table related data materialized to only those databases and tables matching the selection criteria. A good example of a performance problem that should be targetted by this worklog is BUG#43818
A new Storage engine will be created in /storage called infoschema. Written in C++ in a similar manner as performance_schema, it will make use of inheritance and well defined interfaces including the Handler interface. The infoschema storage engine will NOT ever need to be compiled independently of the server it runs against. The data it collects is intrinsically tied to the server that provides it. So it may be necessary for this engine to 'know too much' about its host since it gathers information directly from server memory objects. However, wherever possible, it is to be a goal of this project to separate the transfer of data back and forth from this storage engine to the server into well defined interfaces. It is also a goal to keep the number of new interfaces to a minimum as well. This can be accomplished by allowing the server to do as much of its own data collection as possible, and report that through a minimum number of new function calls from infoschema back to the server. Then infoschema will use the Handler interface to report rows back to the server. The isolation level of data reported will be a sort of 'rolling snapshot'. In other words, a list will initially be collected of the primary keys for rows to report with as little blocking as possible. Then those rows will be reported if they still exist at the time the row is requested. So this is by no means a serializable or repeatable-read isolation. And it is not exactly a read-committed isolation either because new rows that appear while data is being reported will not be found or returned. For example; infoschema.engines will report engines that are in the ready state (PLUGIN_IS_READY). If a new storage engine enters in this state immediately after the call to Handler::rnd_init(), it will not be reported because it was not collected into the list of engines to report by this Handler object. However, if an engine leaves this state immediately after the call to Handler::rnd_init(), it will again not be reported because the call to rnd_next() does not see the PLUGIN_IS_READY flag. So new rows, it is like repeatable read. For updated and deleted rows, it is like read-committed. Schema tables that report information about other tables, views, and triggers will need to use existing DDL locking when it collects data. Some information_schema tables are provided by third-party storage engine plugins as a plugin of type MYSQL_INFORMATION_SCHEMA_PLUGIN. These plugins use an existing limited interface which includes only one function that can be called by the server to collect rows for these tables.It is called fill_table(). With this current API, the INFOSCHEMA storage engine must call this function once, save the rows reported by the third party plugin, and then report them one by one as needed by the optimizer. So for the case of third party plugin schema tables, all data must be materialized once, but only once, per query. A future worklog task could involve expanding the plugin API for these types of plugins.
This low level design is broken up into multiple milestones. The technical issues involved will be added to these milestones once they are understood. Milestone 1: Merge to mysql-next-mr 100% Get the code basically compiling with mysql-next-mr. Create a repository on bk-internal supported by pushbuild. Milestone 2: Initial Bug fixing. 80% Get the merged infoschema storage engine to work for some simple queries. Start identifying problems and fix them one by one. The old code is not to be replaced while the INFOSCHEMA storage engine is being built. The two should work side by side so that changes to information_schema code can be merged in during the development process and the output of the two parallel sources can be compared. Milestone 3: Improve Discovery 90% The existing discovery mechanism was written for NDB cluster so that it could move FRM files around and let a clustered engine 'discover' a table from a newly written FRM file. This process should change so that an FRM file is not needed. Much of this work will feed into WL#5308. The storage engine should provide metadata about the table to code maintained in the server so that a TABLE_SHARE can be created without the necessity of an FRM file. Milestone 4: Variables and Status 100% done Improve and fix the 6 tables associated with collecting status variables from in memory arrays. These tables at VARIABLES, GLOBAL_VARIABLES, SESSION_VARIABLES, STATUS, GLOBAL_STATUS, & SESSION_STATUS. Make the global and session tables inherit the VARIABLE and STATUS classes to reduce and simplify code. Milestone 5: Colations and Character sets 80% Clean up these three simple tables and make sure they work the same as the old code. CHARACTER_SETS, COLLATIONS & COLLATION_CHARACTER_SET_APPLICABILITY Milestone 6: Plugins and Engines 100% Clean up these two similar tables. ENGINES is a subset of PLUGINS and should inherit the plugin class to reduce code. Milestone 7: Tables, etc 50% There are 10 information_schema tables that collect data through sql_show.cc; get_all_tables(). Three of them were previously implemented. This process is the most complex in information_schema and needs to be simplified. Create an infoschema_tables_base class that the others will inherit and reimplement the work in this class. Make sure TABLES, TABLE_NAMES & PARTITIONS work with this refactoring. Milestone 8: Third-Party tables Third party storage engines may register their own information_schema tables through the plugin interface. This API has an init() and fill_table() function to provide the table. Create a generic class in INFOSCHEMA that can be set up to collect and display this information. 100% Milestone 9: Columns Create the table COLUMNS inheriting infochema_tables_base Milestone 10: Key Column Usage Create the table KEY_COLUMN_USAGE inheriting infochema_tables_base Milestone 11: Referential Contrainsts Create the table REFERENTIAL_CONSTRAINTS inheriting infochema_tables_base Milestone 12: Statistics Create the table STATISTICS inheriting infochema_tables_base Milestone 13: Triggers Create the table TRIGGERS inheriting infochema_tables_base Milestone 14: Views Create the table VIEWS inheriting infochema_tables_base Milestone 15: Events Create the table EVENTS Milestone 16: Files Create the table FILES Milestone 17: Parameters Create the table PARAMETERS Milestone 18: Profiling Create the table PROFILING Milestone 19: Routines Create the table ROUTINES Milestone 20: Schema Privileges Create the table SCHEMA_PRIVILEGES Milestone 21: Schemata Create the table SCHEMATA Milestone 22: Table Constraints Create the table TABLE_CONSTRAINTS Milestone 23: Table Privileges Create the table TABLE_PRIVILEGES Milestone 24: Tablespaces Create the table TABLESPACES Milestone 25: User Privileges Create the table USER_PRIVILEGES Milestone 26: Index use by the Optimizer Determine how the Optimizer uses hash indexes and debug the current implementation in INFOSCHEMA. Clean this up and implement indexes in any table where it makes sense or delete all the existing index code. Milestone 27: Condition Pushdown The existing information schema implementation uses a limited condition pushdown for the fields db_name and table_name and only for table related schema tables. This is lost in the infoschema storage engine. So infoschema will collect all tables for all databases on each call. Since the main purpose of this worklog is performance, infoschema must be able to avoid collecting data from plugins and from in-disk directories that are not needed. Milestone 28: Compare Do a final round of testing to insure that all queries against both databases return the same thing. Some field contents are timing sensitive so they cannot be the same. But all other data should be. The order of rows may be different if the query is not sorted. Milestone 29: Redirect SHOW commands Switch all the SHOW commands over to INFOSCHEMA Milestone 30: Redirect INFORMATION_SCHEMA Switch all INFORMATION_SCHEMA references to INFOSCHEMA. In this way, these two names become synonyms for each other. The shorter name is much easier to deal with, but the older name is in use everywhere. Milestone 31: Discover Performance Schema This may be a separate task, or part of WL#5308. Make changes to Performance Schema code to allow it to avoid FRM files by implementing a handler::find_files() function and a handler::discover() Milestone 32: Consolidate Look for commonalities between INFOSCHEMA and PERFORMANCE_SCHEMA and reduce code wherever possible. The attached chart titled Show2Infoschema shows all SHOW commands and all INFORMATION_SCHEMA commands and connects the SHOW commands that map to INFORMATION_SCHEMA tables. It also shows the original function that collects the data. It is attached both as a tabl-delimited txt file and an Excel spreadsheet.