Implementing a new performance_schema table

To implement a new performance schema table, two independent problems need to be resolved:

• HOW to expose the data as a SQL table, usable to user queries.
• WHAT data to expose, and where is this data coming from.

The storage engine interface is used to implement the former. Various design patterns can be used to resolve the later.

Storage engine interface

The performance schema engine, ha_perfschema, exposes performance schema tables to the MySQL server. Tables are implemented by sub classing PFS_engine_table.

The access pattern is always the same for all performance schema tables, and does not depend on what data is exposed, only on how it is exposed.

Application query

An application query in general just executes code in the server. Data may of may not be collected in this code path, see next for possible patterns.

Table exposing static data

Static data does not need to be collected, because it is already known. In this simple case, the table implementation just exposes some internal structure.

An example of table using this pattern is table_setup_consumers.

Table exposing collected data

When a table implementation exposes collected data:

• a memory buffer is used to represent the data
• the server code path is modified, calls instrumentation points, that feed the memory buffer,
• the performance schema table implementation reads from the memory buffer.

Note that access to the internal buffer is lock-free.

This pattern:

• creates memory overhead, for the memory buffer
• creates CPU overhead, with the instrumentation points.

This pattern should be used only when data is not available by other means.

An example of table using this pattern is table_mutex_instances.

Table exposing server internal data

When a table implementation exposes internal state:

• some structure in the server already exists and contains the data,
• the server code path is not modified, as it already maintains the data structure during the normal server operation,
• access to the structure is most likely protected by locks, to maintain integrity,
• the performance schema table implementation reads directly from the server internal memory, by inspecting it, using locks if necessary.

This pattern:

• creates no memory overhead,
• creates no CPU overhead,
• may cause a performance schema query to stall a client application query

To prevent stalls, by locking the server structure for a long time, the data is typically copied once (the table is 'materialized') before iterating on it.

If this pattern is possible (aka, the data already exists and can be inspected), is it the preferred implementation, which results in the least overhead.

An example of table using this pattern is table_host_cache.