WL#6629: PERFORMANCE_SCHEMA, STATUS VARIABLES

Overview
========
Expose the following Information Schema data in the Performance Schema:
  INFORMATION_SCHEMA.GLOBAL_STATUS
  INFORMATION_SCHEMA.GLOBAL_VARIABLES
  INFORMATION_SCHEMA.SESSION_STATUS
  INFORMATION_SCHEMA.SESSION_VARIABLES

The following SHOW commands will be preserved for backward compatibility:
  SHOW GLOBAL STATUS
  SHOW GLOBAL VARIABLES
  SHOW SESSION STATUS
  SHOW SESSION VARIABLES

The Information Schema tables will be deprecated.

General Design Goals
====================
1. Return consistent, unambiguous results

   The SHOW STATUS and SHOW VARIABLES commands return a mix of global and 
   local values that can be confusing. 

2. Better statistics

   There is currently no way to view the session status and system variables for
   selected threads.
  
Specific Design Goals
=====================
Issues with the current implementation are noted here with respect to the design
goals. Status and system variables are implemented very differently, and are
therefore discussed separately.

For clarity, SHOW STATUS and SHOW VARIABLES are synonyms for the equivalent
SELECTs on corresponding tables in the Information Schema.

Status Variables
----------------
Status variables provide information about server operation. They are
informational only (most are counters), and cannot be changed except when reset
by the FLUSH command.

1. Result Consistency

   Problem: Ambiguous scope

   Status variables do not have an intrinsic scope. Scope is determined by the 
   SHOW STATUS command and reflected in the value of each status variable.
   SHOW SESSION STATUS returns the status of the current thread plus all global
   status values. SHOW GLOBAL STATUS aggregates the status of all connections 
   and combines the results with all global status values.

   Solution: Define scope for each status variable
   
   Each status variable will be assigned a scope of GLOBAL, SESSION or both.
   Global-only status variables will not appear in session tables, and 
   session-only status variables will not appear in global tables.

   Problem: Indiscriminate output

   The server maintains a master list comprised of all global, session and
   plugin-defined status variables. This list drives the SHOW STATUS output, 
   and is the reason that SHOW STATUS displays all status variables regardless 
   of scope.

   Solution: Filter output by scope
   
   Assigning scope to all status variables allows for straightforward filtering
   of results according to scope.
   
   Problem: Inconsistent results

   The SHOW STATUS command backs out any changes made to status variables that 
   were caused by the command itself. However, the same is not done for SELECTs 
   on the status variable tables in the INFORMATION_SCHEMA, so SHOW STATUS and 
   SELECT I_S.SESSION_STATUS return different results for some status variables.
   
   Solution: Do not back out status changes caused by SHOW STATUS commands 
   
   Apart from the lack of a compelling use case, there is also a potential 
   exploit where a connection repeatedly issuing "SHOW STATUS" could go 
   unnoticed since it backs out its own status variable updates.

2. Better statistics
   
   Problem: Session data for specific connections is not accessible.
   
   SHOW SESSION STATUS returns session data for the current connection. SHOW 
   GLOBAL STATUS returns session data for all connections.
   
   Solution: Associate status variables with thread id
   
   Session status can be viewed for any thread. Results can be aggregated by 
   thread, host, user and account.

System Variables
----------------
System variables indicate how the server is configured. They are set at server 
startup using options on the command line or in an option file. Most system
variables are dynamic and can be changed during server operation.

1. Result Consistency

   Problem: Indiscriminate output
   
   System variables are defined with a GLOBAL, SESSION or SESSION_ONLY scope. 
   Some global system variables serve as defaults for session variables, such as
   autocommit and sort_buffer_size. However, SHOW SESSION VARIABLES lists 
   variables with global-only scope, such as max_connections and 
   binlog_cache_size.
   
   Solution: Honor predefined scope
   
   Performance Schema system variable tables will strictly adhere to the global,
   session and session-only scope assignments for system variables.

2. Better statistics

   Problem: Session data is limited to the current session.
   
   Solution: Associate system variables with thread id
   
   This allows the system configuration for other connections to be accessible 
   by administrators. SELECTs can be done by thread or globally.


Related Bugs
============

BUG#27508 - Semantic of SHOW [SESSION|GLOBAL] STATUS
- Fixed by this worklog.

BUG#39790 - No way to determine the source of many configuration options values
- Not done.

BUG#68969 - Add "show all variables" for distinguish global-only variables
- Fixed. SHOW SESSION will not show global-only variables. The SHOW ALL syntax
is not implemented.

BUG#71057 - Expose session variables for all threads to enhance config 
                management
- Fixed. Addressed by STATUS_BY_THREAD and VARIABLES_BY_THREAD tables.

BUG#65189 - Don't pollute status settings in SHOW [GLOBAL] VARIABLES
- Not done.

===============================================================================
FUNCTIONAL REQUIREMENTS
===============================================================================
CURRENT-VERSION = 5.7
PREVIOUS-VERSION = 5.6
 
Install
=======
F-1 A fresh MySQL installation of CURRENT-VERSION must create the following
tables
- 1.1 TABLE performance_schema.variables_by_thread
- 1.2 TABLE performance_schema.variables_global
- 1.3 TABLE performance_schema.status_by_thread
- 1.4 TABLE performance_schema.status_by_account
- 1.5 TABLE performance_schema.status_by_user
- 1.6 TABLE performance_schema.status_by_host
- 1.7 TABLE performance_schema.status_global
- 1.8 TABLE performance_schema.session_status
- 1.9 TABLE performance_schema.session_variables
 
Upgrade
=======
F-2 An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create the
following tables
- 2.1 TABLE performance_schema.variables_by_thread
- 2.2 TABLE performance_schema.variables_global
- 2.3 TABLE performance_schema.status_by_thread
- 2.4 TABLE performance_schema.status_by_account
- 2.5 TABLE performance_schema.status_by_user
- 2.6 TABLE performance_schema.status_by_host
- 2.7 TABLE performance_schema.status_global
- 2.8 TABLE performance_schema.session_status
- 2.9 TABLE performance_schema.session_variables
 
NEW TABLES
==========

F3 For each new tables:
- 3.1 TABLE performance_schema.variables_by_thread
- 3.2 TABLE performance_schema.variables_global
- 3.3 TABLE performance_schema.status_by_thread
- 3.4 TABLE performance_schema.status_by_account
- 3.5 TABLE performance_schema.status_by_user
- 3.6 TABLE performance_schema.status_by_host
- 3.7 TABLE performance_schema.status_global
- 3.8 TABLE performance_schema.session_status
- 3.9 TABLE performance_schema.session_variables

F-3.x.1 Security privileges are enforced. Legal operations are SELECT.

CREATE TABLE and DROP TABLE are currently also legal as they are used during
install/upgrade.

F-3.x.2 Table is visible in the information_schema.

F-3.x.3 Table is visible in SHOW TABLES.


SERVER OPTIONS
==============

F-4 Server start options and variables
---------------------------------------
F-4.1 A new server start option is available, in non embedded build,
"show_compatibility_56".
F-4.2 "show_compatibility_56" control the behavior of the SHOW VARIABLE and
SHOW STATUS commands.


EXISTING INFORMATION_SCHEMA TABLES
==================================

F-5 For the following tables:
-5.1 TABLE information_schema.session_status
-5.2 TABLE information_schema.global_status
-5.3 TABLE information_schema.session_variables
-5.4 TABLE information_schema.global_variables

F-5.x.1 Embedded build
Same behavior as in 5.6.

F-5.x.2 With show_compatibility_56 = ON,
SELECT returns the same data as in 5.6, with an additional deprecation
warning.

F-5.x.3 With show_compatibility_56 = OFF,
SELECT returns no data.


SHOW COMMANDS
=============

F-6 SHOW STATUS
----------------
F-6.1 Alias for SHOW SESSION STATUS. Same behavior.

F-7 SHOW GLOBAL STATUS
-----------------------
F-7.1 Embedded build
Same behavior as in 5.6.

F-7.2 show_compatibility_56 = ON
Same behavior as in 5.6, with an additional deprecation warning when using a
WHERE clause.

F-7.3 show_compatibility_56 = OFF
Displays the content of
- performance_schema.status_global

Displays GLOBAL status variables only.


F-8 SHOW SESSION STATUS
------------------------
F-8.1 Embedded build
Same behavior as in 5.6.

F-8.2 show_compatibility_56 = ON
Same behavior as in 5.6, with an additional deprecation warning when using a
WHERE clause.

NOTE Despite the name, SHOW SESSION STATUS in 5.6 return both GLOBAL and
SESSION status, which makes no sense.

F-8.3 show_compatibility_56 = OFF
Displays the content of
- performance_schema.status_by_thread where thread_id = << current thread id >>

In 5.7, SHOW SESSION STATUS should only return SESSION status, as expected.


F-9 SHOW LOCAL STATUS
----------------------
F-9.1 Alias for SHOW SESSION STATUS. Same behavior.


F-10 SHOW VARIABLES
-------------------
F-10.1 Alias for SHOW SESSION VARIABLES. Same behavior.


F-11 SHOW GLOBAL VARIABLES
--------------------------
F-11.1 Embedded build
Same behavior as in 5.6.

F-11.2 show_compatibility_56 = ON
Same behavior as in 5.6, with an additional deprecation warning when using a
WHERE clause.

F-11.3 show_compatibility_56 = OFF
Displays the content of
- performance_schema.variables_global

Displays GLOBAL status variables only.


F-12 SHOW SESSION VARIABLES
---------------------------
F-12.1 Embedded build
Same behavior as in 5.6.

F-12.1 show_compatibility_56 = ON
Same behavior as in 5.6, with an additional deprecation warning when using a
WHERE clause.

NOTE Despite the name, SHOW SESSION VARIABLES in 5.6 return both GLOBAL and
SESSION variables, which makes no sense.

F-12.2 show_compatibility_56 = OFF
Displays the content of
- performance_schema.variables_by_thread where thread_id = << current thread id >>

In 5.7, SHOW SESSION VARIABLES returns only SESSION variables, as expected.


F-13 SHOW LOCAL VARIABLES
-------------------------
F-13.1 Alias for SHOW SESSION VARIABLES. Same behavior.

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

TABLE performance_schema.session_variables
==========================================
CREATE TABLE `session_variables` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------

This table lists all system variables with a scope of SESSION or a combined
scope of GLOBAL and SESSION for all connection threads.

System variables with a purely GLOBAL scope are ignored.

This table displays variables only for the current session.

TABLE performance_schema.variables_by_thread
============================================
CREATE TABLE `variables_by_thread` (
  `THREAD_ID` bigint(20) unsigned NOT NULL,
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------

This table lists all system variables with a scope of SESSION or a combined
scope of GLOBAL and SESSION for all connection threads.

System variables with a purely GLOBAL scope are ignored.


TABLE performance_schema.variables_global
=========================================
CREATE TABLE `variables_global` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------

This table lists all system variables having a scope of GLOBAL or a combined
scope of GLOBAL and SESSION.

System variables with a purely SESSION scope are ignored.


TABLE performance_schema.session_status
=======================================

CREATE TABLE `session_status` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------
When show_compatibility_56 = ON
This table is empty.

When show_compatibility_56 = OFF
This table lists the value of all status variables with a scope of SESSION or a
combined scope of GLOBAL and SESSION for all connection threads.

Status variables with a purely GLOBAL scope are ignored.

Note that per thread status variables are always displayed,
regardless of column INSTRUMENTED in table performance_schema.threads.

This table displays status only for the current session.

TABLE performance_schema.status_by_thread
=========================================

CREATE TABLE `status_by_thread` (
  `THREAD_ID` bigint(20) unsigned NOT NULL,
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------
When show_compatibility_56 = ON
This table is empty.

When show_compatibility_56 = OFF
This table lists the value of all status variables with a scope of SESSION or a
combined scope of GLOBAL and SESSION for all connection threads.

Status variables with a purely GLOBAL scope are ignored.

Note that per thread status variables are always displayed,
regardless of column INSTRUMENTED in table performance_schema.threads.

Existing status counters named "COM_" are excluded from the performance schema
status tables.

TABLE performance_schema.status_by_account
==========================================
CREATE TABLE `status_by_account` (
  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------
When show_compatibility_56 = ON
This table is empty.

When show_compatibility_56 = OFF
This table aggregates status variables by account.

Status variables with a purely GLOBAL scope are ignored.

Note status statistics are aggregated per account only for instrumented threads,
that is, threads in table performance_schema.threads with column INSTRUMENTED =
'YES'.

Existing status counters named "COM_" are excluded from the performance schema
status tables.

TABLE performance_schema.status_by_user
=======================================
CREATE TABLE `status_by_user` (
  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------
When show_compatibility_56 = ON
This table is empty.

When show_compatibility_56 = OFF
This table aggregates status variables by user.

Status variables with a purely GLOBAL scope are ignored.

Note status statistics are aggregated per user only for instrumented threads,
that is, threads in table performance_schema.threads with column INSTRUMENTED =
'YES'.

Existing status counters named "COM_" are excluded from the performance schema
status tables.

TABLE performance_schema.status_by_host
=======================================
CREATE TABLE `status_by_host` (
  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------
When show_compatibility_56 = ON
This table is empty.

When show_compatibility_56 = OFF
This table aggregates status variables by host.

Status variables with a purely GLOBAL scope are ignored.

Note status statistics are aggregated per host only for instrumented threads,
that is, threads in table performance_schema.threads with column INSTRUMENTED =
'YES'.

Existing status counters named "COM_" are excluded from the performance schema
status tables.


TABLE performance_schema.status_global
======================================
CREATE TABLE `status_global` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 

Operations
----------
SELECT is supported.
Normal privilege checks: a user must be granted the proper table privileges to
perform an operation.

Semantic
--------
When show_compatibility_56 = ON
This table is empty.

When show_compatibility_56 = OFF
This table lists all status variables having a scope of GLOBAL or a combined
scope of GLOBAL and SESSION.

Status variables with a purely SESSION scope are ignored.

Note status statistics are aggregated globally only for instrumented threads,
that is, threads in table performance_schema.threads with column INSTRUMENTED =
'YES'.

Existing status counters named "COM_" are excluded from the performance schema
status tables.


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

A new system variable is defined, only for the regular build.
Name: show_compatibility_56
Type: boolean flag
Default value: OFF
Command line: yes
cnf file: yes
Scope: global (*)
dynamic: yes (**)

(*) rationale for global scope: implementing per session scope was considered
earlier, but unfortunately, a per session scope also imply to compute statistics
in the server in *both* old (5.6) and new (5.7) mode at the same time, which
double the performance overhead.
(**) rationale for dynamic: trivial to implement, and will facilitate user
adoption to try and test applications in 5.7 mode

For the embedded build, the variable show_compatibility_56 does not exist,
and the server always behave as in MySQL 5.6

SHOW STATUS
===========
Alias for SHOW SESSION STATUS.


SHOW GLOBAL STATUS
==================
Displays GLOBAL status variables only.

When EMBEDDED,
No changes from MySQL 5.6

When show_compatibility_56 = ON

The syntax SHOW GLOBAL STATUS is supported, same behavior as in 5.6.
The syntax SHOW GLOBAL STATUS LIKE  is supported, same behavior as in 5.6.

The syntax SHOW GLOBAL STATUS WHERE  is supported, same behavior as
in 5.6, with an additional deprecation warning.

When show_compatibility_56 = OFF

The syntax SHOW GLOBAL STATUS is supported.
The syntax SHOW GLOBAL STATUS LIKE  is supported.

The syntax SHOW GLOBAL STATUS WHERE  is removed, this statement
fails with an error.

Displays the content of:
- performance_schema.status_global
- performance_schema.events_statements_summary_global_by_event_name,
for the COM_ counters.

Existing "COM_" status counters are also displayed by SHOW GLOBAL STATUS,
to be compatible with the previous 5.6 behavior.

SHOW SESSION STATUS
===================

When EMBEDDED,
No changes from MySQL 5.6

When show_compatibility_56 = ON

The syntax SHOW SESSION STATUS is supported, same behavior as in 5.6.
The syntax SHOW SESSION STATUS LIKE  is supported, same behavior as in 5.6.

The syntax SHOW SESSION STATUS WHERE  is supported, same behavior as
in 5.6, with an additional deprecation warning.

Note that despite the name, SHOW SESSION STATUS in 5.6 return both GLOBAL and
SESSION status, which makes no sense.

When show_compatibility_56 = OFF

The syntax SHOW SESSION STATUS is supported.
The syntax SHOW SESSION STATUS LIKE  is supported.

The syntax SHOW SESSION STATUS WHERE  is removed, this statement
fails with an error.

Displays the content of:
- performance_schema.status_by_thread where thread_id = << current thread id >>
- performance_schema.events_statements_summary_by_thread_by_event_name,
for the COM_ counters.

Existing "COM_" status counters are also displayed by SHOW SESSION STATUS,
to be compatible with the previous 5.6 behavior.

In 5.7 mode, SHOW SESSION STATUS returns only SESSION status, as expected.


SHOW LOCAL STATUS
=================
Alias for SHOW SESSION STATUS.


SHOW VARIABLES
==============
Alias for SHOW SESSION VARIABLES.


SHOW GLOBAL VARIABLES
=====================
Displays GLOBAL status variables only.

When EMBEDDED,
No changes from MySQL 5.6

When show_compatibility_56 = ON

The syntax SHOW GLOBAL VARIABLES is supported, same behavior as in 5.6
The syntax SHOW GLOBAL VARIABLES LIKE  is supported, same behavior as in 5.6

The syntax SHOW GLOBAL VARIABLES WHERE  is supported, same behavior
as in 5.6, with an additional deprecation warning.

When show_compatibility_56 = OFF

The syntax SHOW GLOBAL VARIABLES is supported.
The syntax SHOW GLOBAL VARIABLES LIKE  is supported.

The syntax SHOW GLOBAL VARIABLES WHERE  is removed, this statement
fails with an error.

Displays the content of:
- performance_schema.variables_global


SHOW SESSION VARIABLES
======================

When EMBEDDED,
No changes from MySQL 5.6

When show_compatibility_56 = ON

The syntax SHOW SESSION VARIABLES is supported, same behavior as in 5.6.
The syntax SHOW SESSION VARIABLES LIKE  is supported, same behavior as in 5.6.

The syntax SHOW SESSION VARIABLES WHERE  is supported, same behavior
as in 5.6, with an additional deprecation warning.

Note that despite the name, SHOW SESSION VARIABLES in 5.6 return both GLOBAL and
SESSION variables, which makes no sense.

When show_compatibility_56 = OFF

The syntax SHOW SESSION VARIABLES is supported.
The syntax SHOW SESSION VARIABLES LIKE  is supported.

The syntax SHOW SESSION VARIABLES WHERE  is removed, this statement
fails with an error.

Displays the content of:
- performance_schema.variables_by_thread where thread_id = << current thread id >>

In 5.7 mode, SHOW SESSION VARIABLES returns only SESSION variables, as expected.


SHOW LOCAL VARIABLES
====================
Alias for SHOW SESSION VARIABLES.


================================================================================
INFORMATION_SCHEMA TABLES
================================================================================

TABLE information_schema.session_status
=======================================

When EMBEDDED,
No changes from MySQL 5.6

When show_compatibility_56 = ON

Same behavior as in 5.6, with an additional deprecation warning on SELECT.

When show_compatibility_56 = OFF

A SELECT on this table returns an empty result set.
A deprecation warning is raised.

TABLE information_schema.global_status
======================================

Likewise.

TABLE information_schema.session_variables
==========================================

Likewise.

TABLE information_schema.global_variables
=========================================

Likewise.

================================================================================
REPLICATION TABLES
================================================================================

Due to integration with task WL#7817, now merged in 5.7.5:
  RPL Monitoring: Move status variables to replication P_S tables,
some status variables are moved to dedicated performance_schema replication tables.

As a result, the following status variables:

Show status like 'Slave_running';
Show status like 'Slave_retried_transactions';
Show status like 'Slave_last_heartbeat';
Show status like 'Slave_received_heartbeats';
show status like 'Slave_heartbeat_period';

are:

- exposed by SHOW GLOBAL STATUS with show_compatibility_56=ON
  (compatibility with existing behavior)
- exposed by SHOW SESSION STATUS with show_compatibility_56=ON
  (compatibility with existing behavior)

- not exposed by SHOW GLOBAL STATUS with show_compatibility_56=OFF
  (variables are not global but per slave)
- not exposed by SHOW SESSION STATUS with show_compatibility_56=OFF
  (variables are not per session)
- not exposed by the performance_schema.status_* tables
  (then are exposed in replication tables instead, with dedicated columns)

Note that there are no special rules for:

Show status like 'Slave_open_temp_tables';
Show status like 'Slave_rows_last_search_algorithm_used';

as these two status variables are not affected by WL#7817.

================================================================================
ADDITIONAL NOTES
================================================================================

Transaction Isolation

Performance Schema tables are non-transactional. Global and 
session status changes caused by active transactions will be visible regardless
of transaction isolation level. Status updates will not be rolled back when a 
transaction is rolled back.


================================================================================
PROPOSED MIGRATION PATH
================================================================================

To clarify the overall intent,
below is an overview of the general principle proposed.

MySQL 5.6
=========

INFORMATION_SCHEMA.SESSION_STATUS is available
INFORMATION_SCHEMA.GLOBAL_STATUS is available
INFORMATION_SCHEMA.SESSION_VARIABLES is available
INFORMATION_SCHEMA.GLOBAL_VARIABLES is available
SHOW STATUS is available
SHOW VARIABLES is available

There are existing applications using these features.

MySQL 5.7, with SHOW_COMPATIBILITY_56 = ON
==========================================

INFORMATION_SCHEMA.SESSION_STATUS is still available
INFORMATION_SCHEMA.GLOBAL_STATUS is still available
INFORMATION_SCHEMA.SESSION_VARIABLES is still available
INFORMATION_SCHEMA.GLOBAL_VARIABLES is still available
SHOW STATUS is still available
SHOW VARIABLES is still available

The metadata returned by INFORMATION_SCHEMA tables is unchanged.
The data returned by INFORMATION_SCHEMA tables is unchanged.

The metadata returned by SHOW commands is unchanged.
The data returned by SHOW commands is unchanged.

There existing applications using these features are still functional,
and see the same variable names and values as in 5.6.

The only visible effect is that an extra deprecation warning raised at runtime
when selecting from the information schema.

Applications are expected to run as-is, with no code change.

In addition, new tables for status are available:
- performance_schema.status_by_thread
- performance_schema.status_by_account
- performance_schema.status_by_user
- performance_schema.status_by_host
- performance_schema.status_global
- performance_schema.session_status

However, all the performance schema status tables appear empty.

Rationale: the code can not compute statistics both in 5.6 and 5.7 mode at the
same time, because aggregation is done with different grouping criteria.
Supporting both aggregations would double the runtime overhead.

Also, new tables for variables are available:
- performance_schema.variables_by_thread
- performance_schema.variables_global

These tables are functional (there is no aggregation to perform, so no extra
overhead is expected).

MySQL 5.7, with SHOW_COMPATIBILITY_56 = OFF
===========================================

INFORMATION_SCHEMA.SESSION_STATUS is available but empty
INFORMATION_SCHEMA.GLOBAL_STATUS is available but empty
INFORMATION_SCHEMA.SESSION_VARIABLES is available but empty
INFORMATION_SCHEMA.GLOBAL_VARIABLES is available but empty
SHOW STATUS is still available, except for the WHERE  syntax.
SHOW VARIABLES is still available, except for the WHERE  syntax.

The metadata returned by INFORMATION_SCHEMA tables is unchanged.
There is no data returned by INFORMATION_SCHEMA tables, these tables are empty.

The syntax SHOW ... STATUS LIKE  is still available.
The syntax SHOW ... STATUS WHERE  is removed,
this statement fails with an error.

The metadata returned by SHOW commands is unchanged.
The data returned by SHOW commands is changed in some cases,
specific changes are:
- SHOW SESSION ... only displays session related items

Existing applications using the INFORMATION_SCHEMA
are expected to change, and use the new performance_schema tables instead.

Existing applications using the SHOW STATUS / SHOW VARIABLES commands
can potentially be impacted, because the content exposed by SHOW commands is
affected.

================
STATUS VARIABLES
================
This section describes how the existing status variable implementation will
be used, plus any changes that need to be made.


1. Add SCOPE to status variables.

A scope field will be added to the SHOW_VAR structure:

  enum_mysql_show_scope
  {
    SHOW_SCOPE_UNDEF = 0,
    SHOW_SCOPE_GLOBAL = 1,
    SHOW_SCOPE_SESSION = 2,
    SHOW_SCOPE_ALL = 3
  };

  struct st_ mysql_show_var
  {
    const char *name;
    char *value;
    enum enum_mysql_show_type type;
    enum enum_mysql_show_scope scope;
  };

The scope designation will be used to filter status variable output as follows:

  SHOW_SCOPE_UNDEF - Scope undefined, status variable will be treated as
  SHOW_ALL.

  SHOW_SCOPE_GLOBAL - Status variable applies only to the server, and has a 
  single, global value. It only appears with SHOW GLOBAL STATUS and the global 
  status tables in the Performance Schema.

  Examples: Aborted_connects, Binlog_cache_use

  SHOW_SCOPE_SESSION - Status variable applies individual connections, but is 
  not aggregated across connections in global queries. It appears only in
  SHOW SESSION STATUS and the session status tables in the Performance Schema.

  Examples: Compression, Last_query_cost

  SHOW_SCOPE_ALL - Status variable applies to individual connections, but will 
  be aggregated across all connections for global queries. Appears in both
  SHOW GLOBAL STATUS and SHOW SESSION STATUS, and the global and session status
  tables in the Performance Schema.

  Examples: Bytes_sent, Open_tables, Com variables

2. all_status_vars: A global dynamic array of status variables

Status variables are defined in various SHOW_VAR arrays throughout the server 
and plugins. During server initialization, status variable definitions are 
assembled into a single global dynamic array, "all_status_vars". New status
variables are added to the array as plugins are loaded.

all_status_vars contains a sorted list of every status variable defined in the
server and plugins. This list drives the output of the SHOW STATUS commands and
SELECTs on the status variable tables in the Information Schema.

all_status_vars will be preserved to maintain backward compatibility in the
Information Schema. The Performance Schema will use all_status_vars to access
global and session status values, however, it will do so selectively so as to 
avoid impacting server performance.


3. Global locks

LOCK_status is a global mutex used to protect all_status_vars during
initialization and during SHOW STATUS execution. Locks on LOCK_status can
interfere with other server operations such as thread disconnects, replication
slave i/o, and authentication.

SHOW STATUS currently locks LOCK_status for the entire duration of command
execution, including cross-thread aggregation. The Performance Schema
will only hold LOCK_status after completing thread aggregation. Variables having
both global and session visibility, such "Bytes_received" and the "Com"
counters, are aggregated using thread-local values, thus obviating the need to
hold LOCK_status.


4. Thread locks

Two locks will be held in the global thread manager to guard against threads
being removed during aggregation:

  Global_THD_manager::LOCK_thd_remove
  Global_THD_manager::LOCK_thd_count
  
LOCK_thd_remove is held during the entire aggregation. LOCK_thd_count held
briefly while a snapshot of the current thread list is copied.
  
TODO: Global server vars won't go away (what about plugins?)
  
  
================
SYSTEM VARIABLES
================
This section describes how the existing system variable implementation will be
used, plus any changes that need to be made.

System variables are declared throughout the server and plugins. For each system
variable, a static sys_var object is constructed that defines attributes of the
variable such as scope, access restrictions, command-line options, charset, etc.
It also contains a pointer to the variable itself.


1. system_variable_hash: A hash table of all system variables

During server initialization, all system variable objects are loaded into the
global hash table, "system_variable_hash". Additional global and session system
variables are added to the hash table as plugins are loaded.

All system variables are accessed and updated via system_variable_hash, which
also drives the output of the SHOW VARIABLES command.

** The Performance Schema will use system_variable_hash to materialize the
system variable tables.


2. Global locks

system_variable_hash is protected by the "LOCK_system_variable_hash" read/write
lock. Write locks are taken during plugin load and unload. Read locks are taken
for system variable reads and updates, SHOW VARIABLES, new connection threads,
and when syncing session system variables with the global variables.

** This locking scheme will not be changed. The Performance Schema will hold
a read lock on LOCK_system_variable_hash during while global and session
system variable tables are materialized.


3. Per-thread locks

Session-level variables are stored in the structure "global_system_variables",
which is dynamically extended to accommodate plugin-defined system variables
having a session scope.

All connection threads have a local copy of this structure. Should the global
copy change, such as when a plugin is loaded, then thread-local copies are
synced on the next access. In rare cases, the SHOW VARIABLES command can trigger
a resync of all connection threads after a new plugin is loaded.

The global mutex LOCK_global_system_variables is required for all access to
the global_system_variables structure. However, the values of session-level
variables are read from thread-local storage, thus no additional global locks
are required.