WL#6629: PERFORMANCE_SCHEMA, STATUS VARIABLES

Affects: Server-5.7   —   Status: Complete 

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.


Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.