WL#5371: PERFORMANCE_SCHEMA Instrumenting Table Locks

Instrument table locks in the performance schema.

This WL provides the basic instrumentation required to support
WL#5420 PERFORMANCE SCHEMA TABLE LOCK WAIT SUMMARY.
See WL#5420 HLD for more details about the target audience of this instrumentation.

performance_schema.SETUP_INSTRUMENT
===================================

A new instrument is added in SETUP_INSTRUMENT:
"wait/lock/table/sql/handler".

This instrument is different from "wait/io/table/sql/handler",
which is dedicated to table io.

As a result, a user can instrument table io and table locks independently.

performance_schema.EVENTS_WAITS_CURRENT
=======================================

Lock events are collected in the server.

There are two types of locks.

external lock
-------------

An "external lock" corresponds to a lock in the storage engine layer.
This is currently implemented by a call to handler::external_lock().

Attributes for the event collected are set as follows:

THREAD_ID: same as WL#2360
EVENT_ID: same as WL#2360
EVENT_NAME: wait/lock/table/sql/handler
SOURCE: same as WL#2360
TIMER_START: same as WL#2360
TIMER_END: same as WL#2360
TIMER_WAIT: same as WL#2360
SPINS: NULL
OBJECT_SCHEMA: the table schema
OBJECT_NAME: the table name
OBJECT_TYPE: the table type ('TABLE')
OBJECT_INSTANCE_BEGIN: Implementation defined
NESTING_EVENT_ID: NULL
OPERATION: See below
NUMBER_OF_BYTES: NULL
FLAGS: NULL.

An external lock has a lock_type of either "READ" (F_RDLCK) or "WRITE" (F_WRLCK)
The OPERATION column will display:
- "read external" for F_RDLCK
- "write external" for F_WRLCK

internal lock
-------------

An "internal lock" corresponds to a lock in the sql layer itself.
This is currently implemented by a call to thr_lock().

Attributes for the event collected are set as follows:

THREAD_ID: same as WL#2360
EVENT_ID: same as WL#2360
EVENT_NAME: wait/lock/table/sql/handler
SOURCE: same as WL#2360
TIMER_START: same as WL#2360
TIMER_END: same as WL#2360
TIMER_WAIT: same as WL#2360
SPINS: NULL
OBJECT_SCHEMA: the table schema
OBJECT_NAME: the table name
OBJECT_TYPE: the table type ('TABLE')
OBJECT_INSTANCE_BEGIN: Implementation defined
NESTING_EVENT_ID: NULL
OPERATION: See below
NUMBER_OF_BYTES: NULL
FLAGS: NULL

An internal lock has a lock_type of "enum thr_lock_type"
The OPERATION column records the value of the internal lock type:
- "read normal"
- "read with shared locks"
- "read high priority"
- "read no insert"
- "write allow write"
- "write concurrent insert"
- "write delayed"
- "write low priority"
- "write normal"

OPERATION column in tables events_waits_current
-----------------------------------------------

The OPERATION column is currently a VARCHAR(16).
It is changed to VARCHAR(32)

The tables EVENTS_WAITS_HISTORY and EVENTS_WAITS_HISTORY_LONG
are changed accordingly.

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

(1) Table performance_schema.setup_instruments.
-----------------------------------------------

Func-Req (1.1): A new instrument, "wait/lock/table/sql/handler",
is available in table performance_schema.setup_instrument.
This instruments controls the table lock instumentation.

Func-Req (1.2): Table io and table lock instrumentation can be
enabled or disabled independently.

(2) Table performance_schema.setup_objects.
-------------------------------------------

Func-Req (2.1): Rows in performance_schema.setup_objects controls whether or not
TABLE lock instrumentation is enabled and/or timed.

(3) Table performance_schema.events_waits_current.
--------------------------------------------------

Func-Req (3.1): External locks are visible in events_waits_current,
under the instrument name "wait/lock/table/sql/handler".

Func-Req (3.2): Internal locks are visible in events_waits_current,
under the instrument name "wait/lock/table/sql/handler".

Func-Req (3.3): A fresh MySQL installation of CURRENT-VERSION must create
table events_waits_current with a varchar(32) type for the operation column.

Func-Req (3.4): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade
table events_waits_current with a varchar(32) type for the operation column.

(4) Table performance_schema.events_waits_history.
--------------------------------------------------

Func-Req (4.1): External locks are visible in events_waits_history,
under the instrument name "wait/lock/table/sql/handler".

Func-Req (4.2): Internal locks are visible in events_waits_history,
under the instrument name "wait/lock/table/sql/handler".

Func-Req (4.3): A fresh MySQL installation of CURRENT-VERSION must create
table events_waits_history with a varchar(32) type for the operation column.

Func-Req (4.4): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade
table events_waits_history with a varchar(32) type for the operation column.

(5) Table performance_schema.events_waits_history_long.
-------------------------------------------------------

Func-Req (5.1): External locks are visible in events_waits_history_long,
under the instrument name "wait/lock/table/sql/handler".

Func-Req (5.2): Internal locks are visible in events_waits_history_long,
under the instrument name "wait/lock/table/sql/handler".

Func-Req (5.3): A fresh MySQL installation of CURRENT-VERSION must create
table events_waits_history_long with a varchar(32) type for the operation column.

Func-Req (5.4): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade
table events_waits_history_long with a varchar(32) type for the operation column.

(6) Table performance_schema.events_waits_summary_by_thread_by_event_name.
--------------------------------------------------------------------------

Func-Req (6.1): Table locks by thread are aggregated
under the instrument name "wait/lock/table/sql/handler".

(7) Table performance_schema.events_waits_summary_global_by_event_name.
-----------------------------------------------------------------------

Func-Req (7.1): Table locks are aggregated globaly
under the instrument name "wait/lock/table/sql/handler".

(8) Table performance_schema.objects_summary_global_by_type.
------------------------------------------------------------

Func-Req (8.1): All the instrumentation for a given table,
that is, both table io and table lock, is aggregated by table,
in objects_summary_global_by_type.

The detailed design is documented in doxygen format.
See instructions in the file Doxyfile-perfschema, in the project branch.