WL#5342: PERFORMANCE SCHEMA SETUP_OBJECTS, ENABLED column

Affects: Server-5.6   —   Status: Complete

This is a usability feature, which also has implications for the performance
schema overhead.

Currently, the performance_schema.setup_objects table can only be used to
include patterns for objects to instrument, but there is no simple way to
configure it to instrument "every table except ...".

This feature improves the performance_schema.setup_objects table usability,
by also allowing exclusion patterns, by adding a new ENABLED column.

Default values are also provided, which disables instrumentation for all the
tables in the mysql schema.

This reduces the performance overhead of the performance schema by default, 
since every table io and table lock on tables mysql.* is not instrumented.

This default setting is chosen assuming that what end users want to instrument
are the applications objects, not the internal server tables.
TABLE performance_schema.SETUP_OBJECTS
======================================

Add the following column before the TIMED column:

  `ENABLED` enum('YES','NO') NOT NULL

Operations
----------

The ENABLED column can be updated.

A user must have the proper privileges to perform these operations.

Semantic
========

See the existing documentation for 5.6:
http://dev.mysql.com/doc/refman/5.6/en/setup-objects-table.html

"
When the Performance Schema checks for a match in setup_objects, it tries to
find more specific matches first. For example, with a table db1.t1, it looks for
a match for 'db1' and 't1', then for 'db1' and '%', then for '%' and '%'.
"

With the added ENABLED column:
- when the first match returns a row with ENABLED=YES, the table is instrumented
(no change compared to existing functionality)
- when the first match returns a row with ENABLED=NO, the table is not
instrumented (new feature)
- when no match is found, the table is not instrumented (no change compared to
existing functionality)

Default setup
=============

After the server completes the performance schema initialization,
the table SETUP_OBJECTS is populated with the following default rules:

TABLE mysql.% ENABLED=NO, TIMED=NO
TABLE performance_schema.% ENABLED=NO, TIMED=NO
TABLE information_schema.% ENABLED=NO, TIMED=NO
TABLE %.% ENABLED=YES, TIMED=YES

In other words, by default:
- mysql, performance schema, and information schema tables are not instrumented
- every other table is instrumented

Requirements
============

(1) Table performance_schema.setup_objects.
-------------------------------------------

Func-Req (1.1): A fresh MySQL installation of CURRENT-VERSION must create the
table performance_schema.setup_objects with the new ENABLED column.

Func-Req (1.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
the table performance_schema.setup_objects with the new ENABLED column.

Func-Req (1.3): Security privileges for column setup_objects.ENABLED
are enforced. This column can be updated, by users with the proper grants.

Func-Req (1.4): Table performance_schema.setup_objects contains, by default, the
following values:
- OBJECT_TYPE="TABLE", OBJECT_SCHEMA="mysql", OBJECT_NAME="%", ENABLED="NO",
TIMED="NO"
- OBJECT_TYPE="TABLE", OBJECT_SCHEMA="performance_schema", OBJECT_NAME="%",
ENABLED="NO", TIMED="NO"
- OBJECT_TYPE="TABLE", OBJECT_SCHEMA="information_schema", OBJECT_NAME="%",
ENABLED="NO", TIMED="NO"
- OBJECT_TYPE="TABLE", OBJECT_SCHEMA="%", OBJECT_NAME="%", ENABLED="YES",
TIMED="YES"
The detailed design is documented in doxygen format.
See instructions in the file Doxyfile-perfschema, in the project branch.