WL#5863: PERFORMANCE SCHEMA, NESTED-SET data model

Affects: Server-Prototype Only   —   Status: Complete

Implement columns to support the NESTED-SET model,
for tables:
- events_waits_current / history / history_long
- events_stages_current / history / history_long
- events_statements_current / history / history_long

Currently, relationships are using:
- EVENT_ID (PK)
- NESTING_EVENT_ID / NESTING_EVENT_TYPE (FK)
so only the ADJACENT-LIST model is supported.

See
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Edit, 2012-11-26: the previous link is not valid anymore.
For an alternate description, see:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

In particular, add a END_EVENT_ID column to event tables, so that:

"Event B is included in event A"
can be computed as

A.EVENT_ID <= B.EVENT_ID and B.END_EVENT_ID <= A.END_EVENT_ID

without having to follow each parent / child relation using NESTING_EVENT_ID.




TABLE performance_schema.events_waits_current
=============================================

Add a new column END_EVENT_ID after column EVENT_ID

  `THREAD_ID` int(11) NOT NULL,
  `EVENT_ID` bigint(20) unsigned NOT NULL,
  `END_EVENT_ID` bigint(20) unsigned DEFAULT NULL,
  ...

TABLE performance_schema.events_waits_history
=============================================

Likewise

TABLE performance_schema.events_waits_history_long
==================================================

Likewise

TABLE performance_schema.events_stages_current
==============================================

Likewise

TABLE performance_schema.events_stages_history
==============================================

Likewise

TABLE performance_schema.events_stages_history_long
===================================================

Likewise

TABLE performance_schema.events_statements_current
==================================================

Likewise

TABLE performance_schema.events_statements_history
==================================================

Likewise

TABLE performance_schema.events_statements_history_long
=======================================================

Likewise


Semantic, for all tables
------------------------

When an event starts, the column EVENT_ID is populated with the thread current
event counter. This is existing functionality, unchanged.
The column END_EVENT_ID is NULL for incomplete events.

When an event ends, the column END_EVENT_ID is populated with the thread current
event counter. This is new functionality.

Requirements:
=============

For all the tables affected:
- performance_schema.events_waits_current
- performance_schema.events_waits_history
- performance_schema.events_waits_history_long
- performance_schema.events_stages_current
- performance_schema.events_stages_history
- performance_schema.events_stages_history_long
- performance_schema.events_statements_current
- performance_schema.events_statements_history
- performance_schema.events_statements_history_long

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

Func-Req (1.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
a new column END_EVENT_ID column in the existing table.