WL#6616: PERFORMANCE_SCHEMA, INDEXES

Implement indexes in the performance_schema storage engine itself.

This would allow the optimizer to have more choices (beside a full table scan),
and make queries more efficient.

Rationale for this task
=======================

With the performance schema in general, people are mostly concerned with "overhead".

Overhead can come from two sources:
- (a) overhead generated when instrumenting the application workload
- (b) overhead generated by the monitoring application itself when executing a
performance schema query.

For years now, everybody has been strongly focused on (a) alone.
All the performance improvements have been targeted to improve (a) as a result.

However, overhead from (b) is expected to be -- or become -- an issue, as
adoption of the performance schema, and helpers like sys views, increases.

With sys views being part of the mainstream server starting with MySQL 5.7,
overhead generated by sys queries in performance_schema tables is expected to
increase.

Example of query with and without index:

SELECT * from performance_schema.threads where PROCESSLIST_ID= 1000;

Without index:
- the SELECT is a full table scan
- the performance schema returns ALL rows
- the optimizer applies the where clause to every rows, only keeping 1

This consumes a lot of CPU, which indirectly affects the server performances at
peak load.

With an index on PROCESSLIST_ID:
- the optimizer uses the index to read data
- the performance schema is provided the Id to look for (1000)
- the performance schema returns only ONE row, not all

This is more efficient, consuming less CPU, for the same result.

Note that for tables where each row is computed on the fly, like most aggregate
statistics in the performance schema, the benefit of having an index makes an
even bigger difference, as statistics for rows that are not part of the final
result do not need to be -- computed --, which saves even more CPU.

This task is to reduce overhead from (b), to avoid impacting the server in
production simply by monitoring it.

Performance Characteristics
===========================

Performance Overhead

The row retrieval mechanism is the same for indexed and non-indexed queries, so
there should be no direct performance impact when fetching a row via an index.
The trivial cost of key matching is more than offset by the gains achieved with
the reduced size of the result set.

As an example, a primary key on performance_schema.variables_by_thread will
considerably reduce the size of the result set on a simple query, as evidenced
by the status counters of the storage engine API.

First, the query with indexes disabled:

  SELECT * FROM variables_by_thread IGNORE INDEX (primary) WHERE thread_id = 28
AND variable_name = 'time_zone'; (results omitted)

The handler status counters shows 135 reads:
  +-----------+-----------------------+----------------+
  | THREAD_ID | VARIABLE_NAME         | VARIABLE_VALUE |
  +-----------+-----------------------+----------------+
  |        28 | Handler_read_first    | 0              |
  |        28 | Handler_read_key      | 0              |
  |        28 | Handler_read_last     | 0              |
  |        28 | Handler_read_next     | 0              |
  |        28 | Handler_read_prev     | 0              |
  |        28 | Handler_read_rnd      | 0              |
  |        28 | Handler_read_rnd_next | 135            |
  +-----------+-----------------------+----------------+

Now, the same query with indexes enabled:

  SELECT * FROM variables_by_thread WHERE thread_id = 28 AND variable_name =
'time_zone';

The handler status counters shows 1 read:
+-----------+-----------------------+----------------+
| THREAD_ID | VARIABLE_NAME         | VARIABLE_VALUE |
+-----------+-----------------------+----------------+
|        28 | Handler_read_first    | 0              |
|        28 | Handler_read_key      | 1              |
|        28 | Handler_read_last     | 0              |
|        28 | Handler_read_next     | 0              |
|        28 | Handler_read_prev     | 0              |
|        28 | Handler_read_rnd      | 0              |
|        28 | Handler_read_rnd_next | 0              |
+-----------+-----------------------+----------------+

In this example there is only one user connection. With 1000 user connections,
the non-indexed result set would be 136,000 and the indexed result set would
still be 1.

How Performance Schema Indexes Differ from Regular Indexes
==========================================================

1) Indexes are fixed.

The indexes on Performance Schema tables are predefined. They cannot be deleted,
added or altered.

2) Indexes are virtual.

A Performance Schema index is implemented as a filtered scan across the
existing table data, rather than a traversal through a separate data structure.
There are no B-trees or hash tables to be constructed, updated or otherwise managed.

Performance Schema table indexes are also virtual in sense that table data does
not persist beyond server shutdown.

3) Indexes are unordered.

Performance Schema tables indexes behave like hash indexes in that a) they
quickly retrieve the desired rows, and b) do not provide row ordering, leaving
the server to sort the result set if necessary. However, depending on the query,
indexes obviate the need for a full table scan and will return a considerably
smaller result set.

Reported performance issues
===========================

http://mysqlserverteam.com/using-sys-session-as-an-alternative-to-show-processlist/

See Simon Mudd comments:

http://mysqlserverteam.com/using-sys-session-as-an-alternative-to-show-processlist/#comment-11037

"
On a busier server with 1400 connections SHOW PROCESSLIST still takes 0.00
seconds, and the select on sys.session took 1 minute 38 seconds.
"

"
This seems to indicate that some sort of indexing in P_S might be appropriate on
tables with a large number of rows? I have discussed this with a couple of
people and understand it complicates things so agree that avoiding if possible
is better, but people will expect to get good performance out of P_S/sys and
this shows that currently that is not always possible.
"

Functional Requirements
=======================
CURRENT-VERSION = 8.0
PREVIOUS-VERSION = 5.7

F-1: A fresh MySQL installation of CURRENT-VERSION must create all tables with
indexes as specified.

F-2: An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must recreate the
tables with indexes as specified.

F-3: Result sets using indexes are identical to results with indexes disabled.

F-4: Indexes cannot be altered.

F-5: Multi-segment indexes must support partial key lookups.

F-6: Indexes are visible with SHOW INDEXES.

F-7: Tables with primary keys must be listed in
information_schema.table_constraints.

F-8: The primary key columns must be listed in information_schema.key_column_usage.

F-9: Table indexes must be represented in the EXPLAIN output for queries that
reference indexed columns.

F-10: Queries using indexes are reflected in status counts for ha_read_key,
handler_read_next.

Non-functional Requirements
===========================
NF-1: Queries using indexes should generally reduce the number of SE API calls,
as recorded by Handler status counters.

NF-2: Performance Schema table and index definitions are managed by the server
in the same way as other system tables.

This section lists the indexes to implement in performance schema tables.

Table accounts
==============

- PRIMARY KEY (USER, HOST)

Table cond_instances
====================

- PRIMARY KEY (OBJECT_INSTANCE_BEGIN)
- KEY (NAME)

Table events_stages_current
===========================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_stages_history
===========================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_stages_history_long
================================

No index.
Rationale: the underlying data storage is a circular log buffer,
there is no underlying structure to leverage to support an index.

Table events_stages_summary_by_account_by_event_name
====================================================

- PRIMARY KEY(USER, HOST, EVENT_NAME)

Table events_stages_summary_by_host_by_event_name
=================================================

- PRIMARY KEY(HOST, EVENT_NAME)

Table events_stages_summary_by_thread_by_event_name
===================================================

- PRIMARY KEY(THREAD_ID, EVENT_NAME)

Table events_stages_summary_by_user_by_event_name
=================================================

- PRIMARY KEY(USER, EVENT_NAME)

Table events_stages_summary_global_by_event_name
================================================

- PRIMARY KEY(EVENT_NAME)

Table events_statements_current
===============================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_statements_history
===============================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_statements_history_long
====================================

No index.
Rationale: the underlying data storage is a circular log buffer,
there is no underlying structure to leverage to support an index.

Table events_statements_summary_by_account_by_event_name
========================================================

- PRIMARY KEY(USER, HOST, EVENT_NAME)

Table events_statements_summary_by_digest
=========================================

- PRIMARY KEY(SCHEMA_NAME, DIGEST)

Table events_statements_summary_by_host_by_event_name
=====================================================

- PRIMARY KEY(HOST, EVENT_NAME)

Table events_statements_summary_by_program
==========================================

- PRIMARY KEY(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)

Table events_statements_summary_by_thread_by_event_name
=======================================================

- PRIMARY KEY(THREAD_ID, EVENT_NAME)

Table events_statements_summary_by_user_by_event_name
=====================================================

- PRIMARY KEY(USER, EVENT_NAME)

Table events_statements_summary_global_by_event_name
====================================================

- PRIMARY KEY(EVENT_NAME)

Table events_transactions_current
=================================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_transactions_history
=================================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_transactions_history_long
======================================

No index.
Rationale: the underlying data storage is a circular log buffer,
there is no underlying structure to leverage to support an index.

Table events_transactions_summary_by_account_by_event_name
==========================================================

- PRIMARY KEY(USER, HOST, EVENT_NAME)

Table events_transactions_summary_by_host_by_event_name
=======================================================

- PRIMARY KEY(HOST, EVENT_NAME)

Table events_transactions_summary_by_thread_by_event_name
=========================================================

- PRIMARY KEY(THREAD_ID, EVENT_NAME)

Table events_transactions_summary_by_user_by_event_name
=======================================================

- PRIMARY KEY(USER, EVENT_NAME)

Table events_transactions_summary_global_by_event_name
======================================================

- PRIMARY KEY(EVENT_NAME)

Table events_waits_current
==========================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_waits_history
==========================

- PRIMARY KEY(THREAD_ID, EVENT_ID)

Table events_waits_history_long
===============================

No index.
Rationale: the underlying data storage is a circular log buffer,
there is no underlying structure to leverage to support an index.

Table events_waits_summary_by_account_by_event_name
===================================================

- PRIMARY KEY(USER, HOST, EVENT_NAME)

Table events_waits_summary_by_host_by_event_name
================================================

- PRIMARY KEY(HOST, EVENT_NAME)

Table events_waits_summary_by_instance
======================================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY (EVENT_NAME)

Table events_waits_summary_by_thread_by_event_name
==================================================

- PRIMARY KEY(THREAD_ID, EVENT_NAME)

Table events_waits_summary_by_user_by_event_name
================================================

- PRIMARY KEY(USER, EVENT_NAME)

Table events_waits_summary_global_by_event_name
===============================================

- PRIMARY KEY(EVENT_NAME)

Table file_instances
====================

- PRIMARY KEY(FILE_NAME)
- KEY(EVENT_NAME)

Table file_summary_by_event_name
================================

- PRIMARY KEY(EVENT_NAME)

Table file_summary_by_instance
==============================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY (FILE_NAME)
- KEY (EVENT_NAME)

Table global_status
===================

- PRIMARY KEY(VARIABLE_NAME)

Table global_variables
======================

- PRIMARY KEY(VARIABLE_NAME)

Table host_cache
================

- PRIMARY KEY(IP)
- KEY (HOST)

Table hosts
===========

- PRIMARY KEY(HOST)

Table memory_summary_by_account_by_event_name
=============================================

- PRIMARY KEY(USER, HOST, EVENT_NAME)

Table memory_summary_by_host_by_event_name
==========================================

- PRIMARY KEY(HOST, EVENT_NAME)

Table memory_summary_by_thread_by_event_name
============================================

- PRIMARY KEY(THREAD_ID, EVENT_NAME)

Table memory_summary_by_user_by_event_name
==========================================

- PRIMARY KEY(USER, EVENT_NAME)

Table memory_summary_global_by_event_name
=========================================

- PRIMARY KEY(EVENT_NAME)

Table metadata_locks
====================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)
- KEY (OWNER_THREAD_ID, OWNER_EVENT_ID)

Table mutex_instances
=====================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY (NAME)
- KEY (LOCKED_BY_THREAD_ID)

Table objects_summary_global_by_type
====================================

- PRIMARY KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)

Table performance_timers
========================

None

Table prepared_statements_instances
===================================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY (STATEMENT_ID)
- KEY (STATEMENT_NAME)
- KEY (OWNER_THREAD_ID, OWNER_EVENT_ID)
- KEY (OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME)

Table replication_applier_configuration
=======================================

- PRIMARY KEY(CHANNEL_NAME)

Table replication_applier_status
================================

- PRIMARY KEY(CHANNEL_NAME)

Table replication_applier_status_by_coordinator
===============================================

- PRIMARY KEY(CHANNEL_NAME)
- KEY (THREAD_ID)

Table replication_applier_status_by_worker
==========================================

- PRIMARY KEY(CHANNEL_NAME, WORKER_ID)
- KEY (THREAD_ID)

Table replication_connection_configuration
==========================================

- PRIMARY KEY(CHANNEL_NAME)

Table replication_connection_status
===================================

- PRIMARY KEY(CHANNEL_NAME)
- KEY(THREAD_ID)

Table replication_group_member_stats
====================================

None.

Table replication_group_members
===============================

None.

Table rwlock_instances
======================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY (NAME)
- KEY (WRITE_LOCKED_BY_THREAD_ID)

Table session_account_connect_attrs
===================================

- PRIMARY KEY(PROCESSLIST_ID, ATTR_NAME)

Table session_connect_attrs
===========================

- PRIMARY KEY(PROCESSLIST_ID, ATTR_NAME)

Table session_status
====================

- PRIMARY KEY(VARIABLE_NAME)

Table session_variables
=======================

- PRIMARY KEY(VARIABLE_NAME)

Table setup_actors
==================

- PRIMARY KEY(HOST, USER, ROLE)

Table setup_consumers
=====================

- PRIMARY KEY(NAME)

Table setup_instruments
=======================

- PRIMARY KEY(NAME)

Table setup_objects
===================

- KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)

Table setup_timers
==================

- PRIMARY KEY(NAME)

Table socket_instances
======================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY(THREAD_ID)
- KEY(SOCKET_ID)
- KEY(IP, PORT)

Table socket_summary_by_event_name
==================================

- PRIMARY KEY(EVENT_NAME)

Table socket_summary_by_instance
================================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY(EVENT_NAME)

Table status_by_account
=======================

- PRIMARY KEY(USER, HOST, VARIABLE_NAME)

Table status_by_host
====================

- PRIMARY KEY(HOST, VARIABLE_NAME)

Table status_by_user
====================

- PRIMARY KEY(USER, VARIABLE_NAME)

Table status_by_thread
=======================

- PRIMARY KEY(THREAD_ID, VARIABLE_NAME)

Table table_handles
===================

- PRIMARY KEY(OBJECT_INSTANCE_BEGIN)
- KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)
- KEY (OWNER_THREAD_ID, OWNER_EVENT_ID)

Table table_io_waits_summary_by_index_usage
===========================================

- UNIQUE KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME)

Table table_io_waits_summary_by_table
=====================================

- UNIQUE KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)

Table table_lock_waits_summary_by_table
=======================================

- UNIQUE KEY (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)

Table threads
=============

- PRIMARY KEY (THREAD_ID)
- KEY(NAME)
- KEY(PROCESSLIST_ID)
- KEY(PROCESSLIST_USER, PROCESSLIST_HOST)
- KEY(PROCESSLIST_HOST)
- KEY(THREAD_OS_ID)

Table user_variables_by_thread
==============================

- PRIMARY KEY (THREAD_ID, VARIABLE_NAME)

Table users
===========

- PRIMARY KEY (USER)

Table variables_by_thread
=========================

- PRIMARY KEY (THREAD_ID, VARIABLE_NAME)

Table events_errors_summary_by_account_by_error
===============================================

- PRIMARY KEY(USER, HOST, ERROR_NUMBER)

Table events_errors_summary_by_host_by_error
============================================

- PRIMARY KEY(HOST, ERROR_NUMBER)

Table events_errors_summary_by_thread_by_error
==============================================

- PRIMARY KEY(THREAD_ID, ERROR_NUMBER)

Table events_errors_summary_by_user_by_error
============================================

- PRIMARY KEY(USER, ERROR_NUMBER)

Table events_errors_summary_global_by_error
===========================================

- PRIMARY KEY(ERROR_NUMBER)