WL#8879: PERFORMANCE_SCHEMA, TABLE PLUGIN

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.

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.

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.

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:

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:

::rnd_next()
-::rnd_next()
--::rnd_next()
----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 : 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 --=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.