WL#5342: PERFORMANCE SCHEMA SETUP_OBJECTS, ENABLED column
Affects: Server-5.6 — Status: Complete — Priority: Medium
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.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.