Provide a mechanism to allow dynamic plugins to provide their own performance schema table.

Currently, all performance schema tables are hard coded and there is no way to add a new table in performance schema except doing code changes internally. With this feature implemented, dynamic plugins would be able to have their own tables in Performance Schema which could be queried using SQL interface.

This feature is needed in particular by replication plugins, firewall plugins, etc.

Note: This task provides the infrastructure needed to define a performance schema table. Migrating existing code which expose INFORMATION_SCHEMA plugin tables to use this new infrastructure is outside of the scope for this task.

User Documentation

• https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html
• https://dev.mysql.com/doc/refman/8.0/en/component-plugin-services.html

Functional requirements:

For performance schema tables provided by a plugin, the following applies:

F-1: Tables can be added dynamically.

F-2: Tables can be removed dynamically.

F-3: Tables added are visible with SHOW TABLES.

F-4: Tables added are visible in the INFORMATION_SCHEMA.

F-5: Tables can be indexed.

F-6: Tables statistics are visible in the performance_schema, if enabled.

F-7: Index statistics are visible in the performance_schema, if enabled.

F-8: Tables privileges are enforced. The following privileges sets are implemented: F-8.1 : SELECT F-8.2 : SELECT + TRUNCATE F-8.3 : INSERT + DELETE F-8.4 : UPDATE

Non Functional Requirements:

NF-1: Example code is provided, that details in particular how to implement: NF-1.1 A read only table NF-1.2 A truncatable table NF-1.3 An editable (INSERT, DELETE) table NF-1.4 An updatable (UPDATE) table NF-1.5 An index on a single column NF-1.6 An index on multiple columns NF-1.7 An iterator using a simple loop NF-1.8 An iterator using a nested loop NF-1.9 Multiple indexes on the same table

NF-2: Developer documentation is provided, in doxygen, that details the steps needed to implement a table from a plugin.

NF-3: There should be no impact on performance.

Definitions

Consumer: A Plugin or A Component which adds a table in performance schema.

High Level Specification

The high level design specification can be divided into two parts:

• Creation/Deletion of the new table.
• DML operation on the new table.

Creation/Deletion

Creating and registering a new table in performance schema by a consumer could be achived by add_tables method of pfs_plugin_table service (explained later).

Similarly, deleting and unregistering a table from performance schema by a consumer could be achived by delete_tables() methods of pfs_plugin_table service.

A new component

A new component named 'performance_schema' is added which works as container for 'pfs_plugin_table' service.

A new service

A new service is introduced in performance_schema component named 'pfs_plugin_table'.

This service provide following methods :

- add_tables    : To add tables in performance schema
- delete_tables : To delete tables from performance_schema

Apart from above two methods, pfs_plugin_table service also provides get_field_/set_field_ methods for following datatypes:

  TINYINT/SMALLINT/MEDIUMINT/INTEGER/BIGINT
  DECIMAL/FLOAT/DOUBLE
  ENUM
  DATE/TIME/DATETIME/TIMESTAMP/YEAR
  CHAR/VARCHAR
  BLOB

For ex:

 get_field_integer/set_field_integer
 get_field_tinyint/set_field_tinyint

NOTE : As of now, index support is available only for INTEGER and CHAR columns.

Flow diagram

Following diagram explains this service in action:

 -------------     ----------------     -----------------------
|Consumer code|-> |service(API foo)|-> |PFS(implementation foo)|
 -------------     ----------------     -----------------------

Where foo is the service method (say add_tables(), delete_tables()). When these methode are invoked from consumer code, they internally redirects calls to the actual implementation in pfs to perform the required operation.

DML Operations

Performance schema Storage Engine works as a medium only between user and consumer tables i.e. all queries to consumer tables goes through Performance Schema Storage Engine.

Proxy table interface

A Proxy interface (PFS_engine_table_proxy) is exposed to consumer, which consumer is supposed to implement. This interface would contain APIs which are required to do DML operations on consumer table. For eg.

Proxy_pfs_engine_table
{
  open_table,
  close_table,
  rnd_next,
  rnd_init,
  rnd_pos,
  index_init,
  index_next,
  ...
}

Consumer has full flexibility to implement the interface the way they want.

Proxy table share

Consumer also needs to provide an instance of PFS_engine_table_share_proxy, which would contain required information to instantiate a PFS_engine_table_share for the table being exposed by consumer. For example:

PFS_engine_table_share_proxy
{
  table_name,
  table_name_length,
  table_definition,  
  cursor_ref_length,
  privileges_on_table,
  ...
}

So, in nutshell, while adding/deleting tables to/from performance schema using pfs_table_service:add/delete_tables() a consumer is supposed to provide

- list of Proxy_pfs_engine_table implementations (one for each table)
- list of Proxy_pfs_engine_table_share instances (one for each table)

Consumer has responsibility to allocate/keep/deallocate/traverse the buffer where consumer table records will be kept. And when a request for table records comes, consumer has to provide this data (by implementing Proxy_pfs_engine_table interface).

Call Flow

This flow can be understood from following function call diagram:

 <HANDLER API>::rnd_next()
 -<PFS SE API>::rnd_next()
 --<PFS PLUGIN TABLE IMP API>::rnd_next()
 ----<consumer implementation of>rnd_next()

Therefore, for all SQL operations (select/insert/delete/update/truncate etc.) consumer is to provide the implementation by implementing Proxy_pfs_engine_table interface.

Relevant Questions

Q : When the server stops, does the data remain in the table?
A : It is up to the consumer code.
    The consumer provides the table data so if it wants to make the data 
    persistent by storing it on persistent storage, it can do that.

Q : Is there any limit on number of rows in the table?
A : It depends on consumer. PFS would display whatever rows comes from consumer.

Q : Is there any specific naming convention on table name?
A : Yes. Table name is to be prefixed by consumer name and (_). For ex: if
    consumer P wants to add table T, then table name in performance schema 
    should be P_T. Though, this compulsion is not imposed by code.

Q : When will the table be dropped?
A : When delete_tables() call is executed, table is unregistered from PFS and dropped.

Q : What are the operations allowed on consumer table?
A : This has to be provided by consumer while registering table (add_tables()).
    But alter table is not supported. Refer example plugin/component.

Q : Can a consumer provide multiple tables?
A : Yes.

Q : Will there be an example consumer implementation available?
A : Yes. 
    An example plugin    : pfs_example_plugin_employee implementation.
    An example component : pfs_example_component_population implementation.

Q : Are the operation on consumer table instrumented as any other table?
A : Yes.

Q : If the server is restarted, would the consumers tables be available?
A : Addition and deletion of consumer tables is driven by add_tables() and delete_tables().
    So if consumers, in its deinit() function, calls delete_tables(), tables would be dropped during server shutdown.
    And if consumer, in its init() function, calls create_tables(), tables will be created again.

Q : If a table is added by a plugin and server stopped without uninstalling 
    plugin. And now server is started with --<plugin_name>=OFF, will the 
    table still be available?
A : Yes. Table metadata will still be in there in Data Dictionary. So it could be seen in 'show tables in performance_schema'.
    But, they could not be operated upon as they are not registered with Performance Schema any more.

Q : Can the plugins who adds tables in Performance Schema in their init()
    function, can be loaded with --early_plugin_load option during server  
    startup?
A : No. Its a limitation.

See bug#22241274