WL#5864: PERFORMANCE SCHEMA, instrument TRANSACTIONS

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

Add transaction instrumentation to the Performance Schema. This instrumentation
will expose all aspects of transaction operation within the server and the
storage engines. The information collected will include quantitative and
qualitative data including transaction duration, transaction counts, frequency
of various transaction attributes such as isolation level and access modes. This
information will be aggregated across several dimensions, including user,
account, session. 

===============================================================================
Target Audience
===============================================================================

The information collected will be useful to developers and production engineers
alike.

* Server core developers

MySQL server and storage engine developers will be able to use the results of
this instrumentation to evaluate the overall performance impact of a server
change when doing benchmarks.

* Application developers

As with statement instrumentation, the primary target audience for this
instrumentation are application developers writing queries against the database
server.

Application developers can use the results of this instrumentation to change how
the application issues queries against the database, to minimize the application
footprint on the server, and improve the application performances / scalability.

In particular, this instrumentation can be used to see in detail the
transactions generated by the application, and to see how these transactions are
executed by the server.

* Production engineers

The secondary audience for this instrumentation are production engineers
monitoring the impact of applications against a database server.

Production engineers are expected to use the results of this instrumentation to
monitor and assess the impact of an application on the whole system during
deployment.

The aggregation of transaction and statement statistics per connection can be
used to monitor the general behavior of an application on the connection.

1) Diagnose problems with a connection;
2) Identify 'stuck' transactions that are blocking other resources or 
statements;
3) Monitor application activity by user and by account.

User Documentation
==================

http://dev.mysql.com/doc/refman/5.7/en/performance-schema-transaction-
tables.html
http://dev.mysql.com/doc/refman/5.7/en/transaction-summary-tables.html
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
Transaction Boundaries
----------------------

In the MySQL Server, transactions are started explicitly with the statements

  START TRANSACTION | BEGIN | XA START | XA BEGIN

Transactions are also started implicitly. For example, when AUTOCOMMIT is enabled,
the start of each statement also marks the start of a new transaction.

When AUTOCOMMIT is disabled, the first statement following a committed
transaction marks the start of a new transaction. Subsequent statements will
be part of the transaction until it is committed.

Transactions are explicitly ended with the statements

  COMMIT | ROLLBACK | XA COMMIT | XA ROLLBACK

Transactions are also implicitly ended by DDL statements, locking statements
and server administration commands.

The Performance Schema defines transaction boundaries similarly to that of the
server, such that the start and end of a transaction event closely matches the
corresponding state transitions in the server. Specifically:

* For explicitly started transactions, the timing of the transaction event
  starts during the processing of the START TRANSACTION statement,
  specifically when the server status transitions to SERVER_STATUS_IN_TRANS.
  Similarly, the end of the transaction event occurs during the processing of
  the COMMIT or ROLLBACK statements when the server transitions out of the
  active transaction state.

* For implicitly started transactions, the transaction event starts on the
  first statement that uses a transactional engine after the previous
  transaction has ended.

* For both explicitly and implicitly ended transactions, the transaction
  event ends when the server state transitions out of the active transaction
  state during the processing of COMMIT or ROLLBACK.

There are subtle implications to this approach:

* Transaction events in the Performance Schema do not fully include the
  statement events associated with the corresponding START TRANSACTION, COMMIT
  or ROLLBACK statements. There is a trivial amount of timing overlap of the
  transaction event and these statements.

* Statements that work with non-transactional engines have no effect on the
  transaction state of the connection. For implicit transactions,
  the transaction event begins with the first statement that uses a
  transactional engine. This means that statements operating exclusively on
  non-transactional tables will be ignored, even following START TRANSACTION.

  To illustrate, consider the following scenario:

  1. SET AUTOCOMMIT = OFF;
  2. CREATE TABLE t1 (a INT) ENGINE = INNODB;
  3. START TRANSACTION;                       // Transaction 1 START
  4. INSERT INTO t1 VALUES (1), (2), (3);
  5. CREATE TABLE t2 (a INT) ENGINE = MYISAM; // Transaction 1 COMMIT (implicit)
                                              // DDL forces commit
  6. INSERT INTO t2 VALUES (1), (2), (3);     // Update non-transactional table
  7. UPDATE t2 SET a = a + 1;                 // ...and again
  8. INSERT INTO t1 VALUES (4), (5), (6);     // Write to transactional table
                                              // Transaction 2 START (implicit)
  9. COMMIT;                                  // Transaction 2 COMMIT

  From the perspective of the server, Transaction 1 ends when the table t2 is
  created. Transaction 2 doesn't start until a transactional table is accessed,
  despite the intervening updates to non-transactional tables.

  Similarly, from the perspective of the Performance Schema, Transaction 2
  starts when the server transitions into an active transaction state.
  Statements 6 and 7 are not included within the boundaries of Transaction 2,
  which is consistent with how transactions are written to the binary log.  

Transaction Instrumentation
---------------------------

Transactions are defined by three attributes:
1) Access Mode (Read Only, Read-Write)
2) Isolation Level (SERIALIZABLE, REPEATABLE READ, etc)
3) Implicit / Explicit (AUTOCOMMIT = ON/OFF)

To reduce complexity of the transaction instrumentation, and to ensure that the
collected transaction data provides complete, meaningful results, all
transactions are instrumented independently of access mode, isolation level or
autocommit state.

Transaction history can be selectively examined using the attribute columns in
the EVENTS_TRANSACTION tables: ACCESS_MODE, ISO LEVEL, AUTOCOMMIT.

The cost of transaction instrumentation can be reduced through various methods,
such as enabling or disabling transaction instrumentation according to user,
account, host or client connection.

Transactions and Nested Events
------------------------------

The parent of a transaction event is the event that initiated the transaction.
For explicitly started transactions, this includes the START TRANSACTION and
COMMIT AND CHAIN statements. For implicitly started transactions, it is the
first statement that uses a transactional engine after the previous transaction
has ended.

In general, a transaction is the top-level parent to all events initiated
during the transaction, including statements that explicitly end the
transaction such as COMMIT and ROLLBACK. The exception to this are statements
that implicitly end a transaction, such as DDL statements, in which case the
current transaction must be committed before the new statement is executed.

Transactions and Stored Programs
--------------------------------

The relationship between transactions and stored program events is as follows:

1) Stored Procedures
  
   Stored procedures operate independently of transactions. A stored procedure
   may be started within a transaction, and a transaction may be started or
   ended from within a stored procedure. If called from within a transaction,
   a stored procedure can execute statements that force a commit of the parent
   transaction and then start a new transaction.

   If stored procedure is started within a transaction, then that transaction
   is the parent of the stored procedure event.

   If a transaction is started by a stored procedure, then the stored procedure
   is the parent of the transaction event.

2) Stored Functions

   Stored functions are restricted from causing an explicit or implicit commit
   or rollback. Stored function events can reside within a parent transaction
   event.

3) Triggers

   Triggers are activated as part of a statement that accesses the table with
   which it is associated, so the parent of a trigger event is always the
   statement that triggered it.

   Triggers cannot issue statements that cause an explicit or implicit commit
   or rollback of a transaction.

4) Scheduled Events

   The execution of the statements in the body of a scheduled event takes place
   in a new connection. The nesting of a scheduled event within a parent
   transaction is not applicable.


Transactions and Savepoints
---------------------------

Savepoint statements are recorded as separate statement events. Transaction
events include separate counters of the SAVEPOINT, ROLLBACK TO SAVEPOINT and
RELEASE SAVEPOINT statements issued during the transaction.


Transactions and Errors
-----------------------

Errors and warnings that occur within a transaction are recorded in statement
events, but are not recorded in the corresponding transaction event. This
includes transaction-specific errors and warnings such as a rollback on a
non-transactional table or GTID consistency errors.


Transaction Event Summaries and Aggregation
-------------------------------------------

Transaction events are collected regardless of isolation level, access mode or
autocommit state.

Read-write transactions are generally more resource-intensive than read-only
transactions, therefore transaction summary tables include aggregate columns
for the read-write and read-only access modes.

Resource requirements may also vary with transaction isolation level. However,
presuming that only one isolation level would be used per server, aggregation
by isolation level is not provided.


===============================================================================
NEW TABLES
===============================================================================

TABLE performance_ schema.events_transactions_current
====================================================

Columns
-------
'THREAD_ID' bigint unsigned not null
'EVENT_ID' bigint unsigned not null
'END_EVENT_ID' bigint unsigned
'EVENT_NAME' varchar(128) not null
'STATE' ENUM('ACTIVE', 'COMMITTED', 'ROLLED_BACK')
'TRX_ID' bigint unsigned
'GTID' varchar(64)
'XID' varchar (140)
'XA_STATE' ENUM('ACTIVE', 'IDLE', 'PREPARED', 'ROLLED_BACK', 'COMMITTED')
'SOURCE' varchar(64)
'TIMER_START' bigint unsigned
'TIMER_END' bigint unsigned
'TIMER_WAIT' bigint unsigned
'ACCESS_MODE' ENUM('READ_WRITE', 'READ_ONLY')
'ISOLATION_LEVEL' ENUM('REPEATABLE_READ', 'READ_COMMITTED', 'READ_UNCOMMITTED',
'SERIALIZABLE')
'AUTOCOMMIT' ENUM('YES','NO')
'NUMBER_OF_SAVEPOINTS' bigint unsigned
'NUMBER_OF_ROLLBACK_TO_SAVEPOINT' bigint unsigned
'NUMBER_OF_RELEASE_SAVEPOINT' bigint unsigned
'OBJECT_INSTANCE_BEGIN' bigint unsigned
'NESTING_EVENT_ID' bigint unsigned
'NESTING_EVENT_TYPE' ENUM('TRANSACTION', 'STATEMENT', 'STAGE', 'WAIT')

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to clear all rows.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
This table contains the most recent transaction per thread, one row per thread.

STATE is the current transaction state:
  ACTIVE       - after START TRANSACTION or BEGIN
  COMMITTED    - after COMMIT
  ROLLED_BACK  - after ROLLBACK

GTID is the global transaction identifier created for each transaction when it
is committed. The GTID is guaranteed, with very high probability, to be globally
unique.

TRX_ID is the transaction identifier assigned by InnoDB, and is the primary key
for the information_ schema.innodb_trx table.

XID is the XA transaction identifier. It is a binary string with the format:

  gtrid [, bqual [, formatID ]]

where gtrid is a global transaction identifier, bqual is a branch qualifier,
and formatID is a number that identifies the format used by gtrid and bqual.
Taken together, both gtrid and bqual must be globally unique.

The XID is not related to the GTID. Its format is described in the Open Group
document, "Distributed TP: The XA Specification":
https://www2.opengroup.org/ogsys/catalog/c193

XA_STATE is the state of the XA transaction:
  ACTIVE       - after XA START
  IDLE         - after XA END
  PREPARED     - after XA PREPARE
  ROLLED_BACK  - after XA ROLLBACK
  COMMITTED    - after XA COMMIT


TABLE performance_schema.events_transactions_history
====================================================

Columns
-------
'THREAD_ID' bigint unsigned not null
'EVENT_ID' bigint unsigned not null
'END_EVENT_ID' bigint unsigned
'EVENT_NAME' varchar(128) not null
'STATE' ENUM('ACTIVE', 'COMMITTED', 'ROLLED_BACK')
'TRX_ID' bigint unsigned
'GTID' varchar(64)
'XID' varchar (140)
'XA_STATE' ENUM('ACTIVE', 'IDLE', 'PREPARED', 'ROLLED_BACK', 'COMMITTED')
'SOURCE' varchar(64)
'TIMER_START' bigint unsigned
'TIMER_END' bigint unsigned
'TIMER_WAIT' bigint unsigned
'ACCESS_MODE' ENUM('READ_WRITE', 'READ_ONLY')
'ISOLATION_LEVEL' ENUM('REPEATABLE_READ', 'READ_COMMITTED', 'READ_UNCOMMITTED',
'SERIALIZABLE')
'AUTOCOMMIT' ENUM('YES','NO')
'NUMBER_OF_SAVEPOINTS' bigint unsigned
'NUMBER_OF_ROLLBACK_TO_SAVEPOINT' bigint unsigned
'NUMBER_OF_RELEASE_SAVEPOINT' bigint unsigned
'OBJECT_INSTANCE_BEGIN' bigint unsigned
'NESTING_EVENT_ID' bigint unsigned
'NESTING_EVENT_TYPE' ENUM('TRANSACTION', 'STATEMENT', 'STAGE', 'WAIT')

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to clear all rows in the table.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
The events_transactions_history table contains the most recent 10 transactions
per thread. The table size may be changed by modifying the
performance_schema_events_transactions_history_size system variable at server
startup. As new transaction events are added to the table, older transactions
are discarded if the table is full. Transactions are added to the table when
they have ended. 


TABLE performance_schema.events_transactions_history_long
=========================================================

Columns
-------
'THREAD_ID' bigint unsigned not null
'EVENT_ID' bigint unsigned not null
'END_EVENT_ID' bigint unsigned
'EVENT_NAME' varchar(128) not null
'STATE' ENUM('ACTIVE', 'COMMITTED', 'ROLLED_BACK')
'TRX_ID' bigint unsigned
'GTID' varchar(64)
'XID' varchar (140)
'XA_STATE' ENUM('ACTIVE', 'IDLE', 'PREPARED', 'ROLLED_BACK', 'COMMITTED')
'SOURCE' varchar(64)
'TIMER_START' bigint unsigned
'TIMER_END' bigint unsigned
'TIMER_WAIT' bigint unsigned
'ACCESS_MODE' ENUM('READ_WRITE', 'READ_ONLY')
'ISOLATION_LEVEL' ENUM('REPEATABLE_READ', 'READ_COMMITTED', 'READ_UNCOMMITTED',
'SERIALIZABLE')
'AUTOCOMMIT' ENUM('YES','NO')
'NUMBER_OF_SAVEPOINTS' bigint unsigned
'NUMBER_OF_ROLLBACK_TO_SAVEPOINT' bigint unsigned
'NUMBER_OF_RELEASE_SAVEPOINT' bigint unsigned
'OBJECT_INSTANCE_BEGIN' bigint unsigned
'NESTING_EVENT_ID' bigint unsigned
'NESTING_EVENT_TYPE' ENUM('TRANSACTION', 'STATEMENT', 'STAGE', 'WAIT')

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to clear all rows.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
The events_transactions_history_long table contains the most recent 10,000
transactions. The table size may be changed by modifying the
performance_schema_events_transactions_history_size system variable at server
startup. As new transaction events are added to the table, older transactions
are discarded if the table is full. Transactions are added to the table when
they have ended. 


TABLE performance_schema.events_transactions_summary_by_account_by_event_name
=============================================================================

Columns
-------
'USER' char(16) collate utf8_bin default null
'HOST' char(60) collate utf8_bin default null
'EVENT_NAME' varchar(128) not null
'COUNT_STAR' bigint unsigned not null
'SUM_TIMER_WAIT' bigint unsigned not null
'MIN_TIMER_WAIT' bigint unsigned not null
'AVG_TIMER_WAIT' bigint unsigned not null
'MAX_TIMER_WAIT' bigint unsigned not null
'COUNT_READ_WRITE' bigint unsigned not null
'SUM_TIMER_READ_WRITE' bigint unsigned not null
'MIN_TIMER_READ_WRITE' bigint unsigned not null
'AVG_TIMER_READ_WRITE' bigint unsigned not null
'MAX_TIMER_READ_WRITE' bigint unsigned not null
'COUNT_READ_ONLY' bigint unsigned not null
'SUM_TIMER_READ_ONLY' bigint unsigned not null
'MIN_TIMER_READ_ONLY' bigint unsigned not null
'AVG_TIMER_READ_ONLY' bigint unsigned not null
'MAX_TIMER_READ_ONLY' bigint unsigned not null

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to reset all summaries.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
This table aggregates the transaction count and transaction wait time statistics
per account and event name.


TABLE performance_schema.events_transactions_summary_by_host_by_event_name
==========================================================================

Columns
-------
'HOST' char(60) collate utf8_bin default null
'EVENT_NAME' varchar(128) not null
'COUNT_STAR' bigint unsigned not null
'SUM_TIMER_WAIT' bigint unsigned not null
'MIN_TIMER_WAIT' bigint unsigned not null
'AVG_TIMER_WAIT' bigint unsigned not null
'MAX_TIMER_WAIT' bigint unsigned not null
'COUNT_READ_WRITE' bigint unsigned not null
'SUM_TIMER_READ_WRITE' bigint unsigned not null
'MIN_TIMER_READ_WRITE' bigint unsigned not null
'AVG_TIMER_READ_WRITE' bigint unsigned not null
'MAX_TIMER_READ_WRITE' bigint unsigned not null
'COUNT_READ_ONLY' bigint unsigned not null
'SUM_TIMER_READ_ONLY' bigint unsigned not null
'MIN_TIMER_READ_ONLY' bigint unsigned not null
'AVG_TIMER_READ_ONLY' bigint unsigned not null
'MAX_TIMER_READ_ONLY' bigint unsigned not null

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to reset all summaries.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
This table aggregates the transaction count and transaction wait time statistics
by host and event name.


TABLE performance_schema.events_transactions_summary_by_thread_by_event_name
============================================================================

Columns
-------
'THREAD_ID' bigint unsigned not null
'EVENT_NAME' varchar(128) not null
'COUNT_STAR' bigint unsigned not null
'SUM_TIMER_WAIT' bigint unsigned not null
'MIN_TIMER_WAIT' bigint unsigned not null
'AVG_TIMER_WAIT' bigint unsigned not null
'MAX_TIMER_WAIT' bigint unsigned not null
'COUNT_READ_WRITE' bigint unsigned not null
'SUM_TIMER_READ_WRITE' bigint unsigned not null
'MIN_TIMER_READ_WRITE' bigint unsigned not null
'AVG_TIMER_READ_WRITE' bigint unsigned not null
'MAX_TIMER_READ_WRITE' bigint unsigned not null
'COUNT_READ_ONLY' bigint unsigned not null
'SUM_TIMER_READ_ONLY' bigint unsigned not null
'MIN_TIMER_READ_ONLY' bigint unsigned not null
'AVG_TIMER_READ_ONLY' bigint unsigned not null
'MAX_TIMER_READ_ONLY' bigint unsigned not null

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to reset all summaries.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
This table aggregates the transaction count and transaction wait time statistics
by thread and event name.


TABLE performance_schema.events_transactions_summary_by_user_by_event_name
==========================================================================

Columns
-------
'USER' CHAR(16) collate utf8_bin default null
'EVENT_NAME' varchar(128) not null
'COUNT_STAR' bigint unsigned not null
'SUM_TIMER_WAIT' bigint unsigned not null
'MIN_TIMER_WAIT' bigint unsigned not null
'AVG_TIMER_WAIT' bigint unsigned not null
'MAX_TIMER_WAIT' bigint unsigned not null
'COUNT_READ_WRITE' bigint unsigned not null
'SUM_TIMER_READ_WRITE' bigint unsigned not null
'MIN_TIMER_READ_WRITE' bigint unsigned not null
'AVG_TIMER_READ_WRITE' bigint unsigned not null
'MAX_TIMER_READ_WRITE' bigint unsigned not null
'COUNT_READ_ONLY' bigint unsigned not null
'SUM_TIMER_READ_ONLY' bigint unsigned not null
'MIN_TIMER_READ_ONLY' bigint unsigned not null
'AVG_TIMER_READ_ONLY' bigint unsigned not null
'MAX_TIMER_READ_ONLY' bigint unsigned not null

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to reset all summaries.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
This table aggregates the transaction count and transaction wait time statistics
by user and event name.


TABLE performance_schema.events_transactions_summary_global_by_event_name
=========================================================================

Columns
-------
'EVENT_NAME' varchar(128) not null
'COUNT_STAR' bigint unsigned not null
'SUM_TIMER_WAIT' bigint unsigned not null
'MIN_TIMER_WAIT' bigint unsigned not null
'AVG_TIMER_WAIT' bigint unsigned not null
'MAX_TIMER_WAIT' bigint unsigned not null
'COUNT_READ_WRITE' bigint unsigned not null
'SUM_TIMER_READ_WRITE' bigint unsigned not null
'MIN_TIMER_READ_WRITE' bigint unsigned not null
'AVG_TIMER_READ_WRITE' bigint unsigned not null
'MAX_TIMER_READ_WRITE' bigint unsigned not null
'COUNT_READ_ONLY' bigint unsigned not null
'SUM_TIMER_READ_ONLY' bigint unsigned not null
'MIN_TIMER_READ_ONLY' bigint unsigned not null
'AVG_TIMER_READ_ONLY' bigint unsigned not null
'MAX_TIMER_READ_ONLY' bigint unsigned not null

Operations
----------
SELECT is supported.
TRUNCATE is supported, and is used to reset all summaries.
Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------
This table aggregates the transaction count and transaction wait time statistics
by event name.


===============================================================================
MODIFIED TABLES
===============================================================================

TABLE performance_schema.setup_timers
=====================================
There will be a new timer, "transaction". The default timer_name for the
transaction timer is MICROSECOND.

+-------------+-------------+
| NAME        | TIMER_NAME  |
+-------------+-------------+
| idle        | MICROSECOND |
| wait        | CYCLE       |
| stage       | MICROSECOND |
| statement   | MICROSECOND |
| transaction | MICROSECOND |
+-------------+-------------+

Transactions are timed using the transaction timer independently of idle, wait,
stage and statement events.


TABLE performance_schema.setup_instruments
==========================================
There is one new instrument:
  "transaction"


TABLE performance_schema.setup_consumers
========================================
As with statements and stages, there will be three transaction consumers, each
corresponding to a table of the same name:

+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_transactions_current      | YES     |
| events_transactions_history      | NO      |
| events_transactions_history_long | NO      |
+----------------------------------+---------+


TABLE performance_schema.events_statements_current
==================================================
Schema change.
Column NESTING_EVENT_TYPE is changed to
  NESTING_EVENT_TYPE ENUM('TRANSACTION', 'STATEMENT', 'STAGE', 'WAIT')


TABLE performance_schema.events_statements_history
==================================================
Same as performance_schema.events_statements_current.


TABLE performance_schema.events_statements_history_long
=======================================================
Same as performance_schema.events_statements_current.


TABLE performance_schema.events_stages_current
==============================================
Same as performance_schema.events_statements_current.


TABLE performance_schema.events_stages_history
==============================================
Same as performance_schema.events_statements_current.


TABLE performance_schema.events_stages_history_long
===================================================
Same as performance_schema.events_statements_current.


TABLE performance_schema.events_waits_current
=============================================
Same as performance_schema.events_statements_current.


TABLE performance_schema.events_waits_history
=============================================
Same as performance_schema.events_statements_current.


TABLE performance_schema.events_waits_history_long
==================================================
Same as performance_schema.events_statements_current.

===============================================================================
SERVER VARIABLES
===============================================================================

New server variables:

1) performance_schema_events_transactions_history_size

   The number of rows per thread in the events_transactions_history table.
   Global, read only.
   Default: 10

2) performance_schema_events_transactions_history_long_size

   The number of rows per thread in events_transactions_history_long.
   Global, read only.
   Default: 10,000

===============================================================================
FUNCTIONAL REQUIREMENTS
===============================================================================
Install
-------

Func-Req (1): A fresh MySQL installation of CURRENT-VERSION must create the
following tables:
- 1.1, TABLE performance_schema.events_transactions_current
- 1.2, TABLE performance_schema.events_transactions_history
- 1.3, TABLE performance_schema.events_transactions_history_long
- 1.4, TABLE performance_schema.events_transactions_summary_by_account_by_event_name
- 1.5, TABLE performance_schema.events_transactions_summary_by_host_by_event_name
- 1.6, TABLE performance_schema.events_transactions_summary_by_user_by_event_name
- 1.7, TABLE performance_schema.events_transactions_summary_by_thread_by_event_name
- 1.8, TABLE performance_schema.events_transactions_summary_global_by_event_name

Upgrade
-------

Func-Req (2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
the following tables:
- 2.1, TABLE performance_schema.events_transactions_current
- 2.2, TABLE performance_schema.events_transactions_history
- 2.3, TABLE performance_schema.events_transactions_history_long
- 2.4, TABLE performance_schema.events_transactions_summary_by_account_by_event_name
- 2.5, TABLE performance_schema.events_transactions_summary_by_host_by_event_name
- 2.6, TABLE performance_schema.events_transactions_summary_by_user_by_event_name
- 2.7, TABLE performance_schema.events_transactions_summary_by_thread_by_event_name
- 2.8, TABLE performance_schema.events_transactions_summary_global_by_event_name

An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must also change the column
NESTING_EVENT_TYPE to
  NESTING_EVENT_TYPE ENUM('TRANSACTION', 'STATEMENT', 'STAGE', 'WAIT')
for the following tables:
- 2.9, TABLE performance_schema.events_statements_current
- 2.10, TABLE performance_schema.events_statements_history
- 2.11, TABLE performance_schema.events_statements_history_long
- 2.12, TABLE performance_schema.events_stages_current
- 2.13, TABLE performance_schema.events_stages_history
- 2.14, TABLE performance_schema.events_stages_history_long
- 2.15, TABLE performance_schema.events_waits_current
- 2.16, TABLE performance_schema.events_waits_history
- 2.17, TABLE performance_schema.events_waits_history_long


(3) TABLE performance_schema.events_transactions_current.
---------------------------------------------------------
Func-Req (3.1): Security privileges for events_transactions_current are
enforced. Legal operations are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (3.2): Table performance_schema.events_transactions_current is visible
in the information_schema.

Func-Req (3.3): Table performance_schema.events_transactions_current is visible
in SHOW TABLES.


(4) TABLE performance_schema.events_transactions_history.
---------------------------------------------------------
Func-Req (4.1): Security privileges for events_transactions_history are
enforced. Legal operations are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (4.2): Table performance_schema.events_transactions_history is visible
in the information_schema.

Func-Req (4.3): Table performance_schema.events_transactions_history is visible
in SHOW TABLES.


(5) TABLE performance_schema.events_transactions_history_long.
--------------------------------------------------------------
Func-Req (5.1): Security privileges for events_transactions_history_long are
enforced. Legal operations are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (5.2): Table performance_schema.events_transactions_history_long is
visible in the information_schema.

Func-Req (5.3): Table performance_schema.events_transactions_history_long is
visible in SHOW TABLES.


(6) TABLE performance_schema.events_transactions_summary_by_account_by_event_name.
----------------------------------------------------------------------------------
Func-Req (6.1): Security privileges for
events_transactions_summary_by_account_by_event_name are enforced. Legal
operations are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (6.2): Table
performance_schema.events_transactions_summary_by_account_by_event_name is
visible in the information_schema.

Func-Req (6.3): Table
performance_schema.events_transactions_summary_by_account_by_event_name is
visible in SHOW TABLES.


(7) TABLE performance_schema.events_transactions_summary_by_host_by_event_name.
-------------------------------------------------------------------------------
Func-Req (7.1): Security privileges for
events_transactions_summary_by_host_by_event_name are enforced. Legal
operations are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (7.2): Table
performance_schema.events_transactions_summary_by_host_by_event_name is
visible in the information_schema.

Func-Req (7.3): Table
performance_schema.events_transactions_summary_by_host_by_event_name is
visible in SHOW TABLES.


(8) TABLE performance_schema.events_transactions_summary_by_user_by_event_name.
-------------------------------------------------------------------------------
Func-Req (8.1): Security privileges for
events_transactions_summary_by_user_by_event_name are enforced. Legal
operations are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (8.2): Table
performance_schema.events_transactions_summary_by_user_by_event_name is
visible in the information_schema.

Func-Req (8.3): Table
performance_schema.events_transactions_summary_by_user_by_event_name is
visible in SHOW TABLES.


(9) TABLE performance_schema.events_transactions_summary_by_thread_by_event_name.
---------------------------------------------------------------------------------
Func-Req (9.1): Security privileges for
events_transactions_summary_by_thread_by_event_name are enforced. Legal
operations are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (9.2): Table
performance_schema.events_transactions_summary_by_thread_by_event_name is
visible in the information_schema.

Func-Req (9.3): Table
performance_schema.events_transactions_summary_by_thread_by_event_name is
visible in SHOW TABLES.


(10) TABLE performance_schema.events_transactions_summary_global_by_event_name.
-------------------------------------------------------------------------------
Func-Req (10.1): Security privileges for
events_transactions_summary_global_by_event_name are enforced. Legal operations
are SELECT, TRUNCATE.
CREATE TABLE and DROP TABLE are currently also legal, as they are used during
install/upgrade.

Func-Req (10.2): Table
performance_schema.events_transactions_summary_global_by_event_name is visible
in the information_schema.

Func-Req (10.3): Table
performance_schema.events_transactions_summary_global_by_event_name is visible
in SHOW TABLES.


(11) TABLE performance_schema.setup_timers.
------------------------------------------
Func-Req (11.1): Table setup_timers has a new timer named "transaction" with a
timer_name of MICROSECOND.


(12) TABLE performance_schema.setup_instruments.
-----------------------------------------------
Func-Req (12.1): Table setup_instruments displays the new row for the transaction
instrumentation named "transaction".


(13) TABLE performance_schema.setup_consumers.
---------------------------------------------
Func-Req (13.1): Table setup_consumers has a new consumer named
"events_transactions_current", which controls the table of the same name.

Func-Req (13.2): Table setup_consumers has a new consumer named
"events_transactions_history", which controls the table of the same name.

Func-Req (13.3): Table setup_consumers has a new consumer named
"events_transactions_history_long", which controls the table of the same name.


(14) Server start options and variables.
---------------------------------------
Func-Req (14.1): A new server start option is available,
"performance-schema-events-transactions-history-size", to control the size of
the table events_transactions_history.

Func-Req (14.2): A new server start option is available,
"performance-schema-events-transactions-history-long-size", to control the size
of the table events_transactions_history_long.