WL#5379: PERFORMANCE SCHEMA TABLE / INDEX IO SUMMARY

Provide TABLE_IO_WAITS_SUMMARY_ tables in the performance schema,
to aggregate all the table io wait events (wait/io/table/sql/handler).

Table IO waits are aggregated:
- by table (object type + schema name + object name)
- by table index (object type + schema name + object name + index)
and grouped by instruments (EVENT_NAME).

Target audience

* Server core developers

Implementers of the server itself, or implementers of storage engines,
do not have direct control on which MySQL user application performs io on which
user table,
so the data provided by this instrumentation can not be used to tune directly
the server.

When implementing changes to the code driving table io however,
and in particular to the optimizer,
server developers will then be able to use the results on this instrumentation
to evaluate the overall impact of a server change on an end user application,
when doing benchmarks.

* Application developers

Application developers writing queries against the database server to implement
an application are the primary target audience for this instrumentation.

Application developers are expected to 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 by application developers
to see which table is used, which index is used (or not), and the overall
impact on table io of an application.
Without this instrumentation, the performance schema will only report a
single statistic aggregated under "wait/io/table/...",
which is not detailed enough to tune an application design.

* Production engineers

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

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.
Aggregation of table io by table and by index allows to identify bottlenecks
happening on a SQL table, when multiple applications access the same table.

TABLE performance_schema.TABLE_IO_WAITS_SUMMARY_BY_TABLE
========================================================

Columns
-------

  `OBJECT_TYPE` varchar(64) DEFAULT NULL,
  `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OBJECT_NAME` varchar(64) DEFAULT NULL,
  `COUNT_STAR` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `COUNT_READ` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `COUNT_FETCH` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `COUNT_INSERT` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `COUNT_UPDATE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `COUNT_DELETE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_DELETE` bigint(20) 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 all the table io operations,
as generated by the "wait/io/table/sql/handler" instrument only.

The columns COUNT_STAR, SUM/MIN/AVG/MAX TIMER_WAIT aggregates all operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_READ aggregates all READ (FETCH) operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_WRITE aggregates all WRITE (INSERT,
UPDATE, DELETE) operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_FETCH aggregates all FETCH operations.
These columns are identical to TIMER_READ, this is expected.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_INSERT aggregates all INSERT operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_UPDATE aggregates all UPDATE operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_DELETE aggregates all DELETE operations.

The grouping is by table.

TRUNCATE resets all statistics, and may reset statistics for more detailed
aggregates.
In particular, TRUNCATE TABLE_IO_WAITS_SUMMARY_BY_TABLE
will also perform automatically TRUNCATE TABLE_IO_WAITS_SUMMARY_BY_INDEX.

TABLE performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE
==============================================================

Columns
-------

  `OBJECT_TYPE` varchar(64) DEFAULT NULL,
  `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OBJECT_NAME` varchar(64) DEFAULT NULL,
  `INDEX_NAME` varchar(64) DEFAULT NULL,
  `COUNT_STAR` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `COUNT_READ` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `COUNT_FETCH` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `COUNT_INSERT` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `COUNT_UPDATE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `COUNT_DELETE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_DELETE` bigint(20) 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 all the table io operations,
as generated by the "wait/io/table/sql/handler" instrument.

The columns COUNT_STAR, SUM/MIN/AVG/MAX TIMER_WAIT aggregates all operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_READ aggregates all READ (FETCH) operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_WRITE aggregates all WRITE (INSERT,
UPDATE, DELETE) operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_FETCH aggregates all FETCH operations.
These columns are identical to TIMER_READ, this is expected.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_INSERT aggregates all INSERT operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_UPDATE aggregates all UPDATE operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_DELETE aggregates all DELETE operations.

The grouping is by INDEX_NAME, where INDEX_NAME corresponds to the name of the
index that was used when the table io operation was recorded.

For example:

- a row for INDEX_NAME='PRIMARY' shows all the table io done while using the
index named 'PRIMARY' (in short, the primary key), 
- a row where INDEX_NAME is NULL shows all the table io done while using no index.

Aggregation "BY_INDEX_USAGE" means that:

A FETCH operation that uses an index will be counted against the index name:
- the index was *used* during the fetch.

An INSERT operation will be counted against INDEX_NAME = NULL

Restrictions
------------

A DDL operation that changes the index structure of a table may cause the per
index statistics to be reset.

Convenience columns
===================

Columns such as SUM/MIN/AVG/MAX TIMER_WAIT/TIMER_READ/TIMER_WRITE
are derived from lower level columns that count statistics per operations.
These columns are redundant.
Given that we anticipate that many users will want to have these statistics,
the columns are provided as convenience, instead of forcing every user
to define their own view to compute the same.

Naming considerations
=====================

Note that the name used, TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE,
allows the possibility to implement later a different aggregation,
"TABLE_IO_WAITS_SUMMARY_BY_INDEX_MAINTAINED" that shows the cost
of maintaining an index, should the need for this arise later.

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

New columns
-----------

  `INDEX_NAME` varchar(64) DEFAULT NULL

A new column, INDEX_NAME, displays the name of the index used
for table io operations.
It is null for other type of events.

Tables events_waits_history and events_waits_history_long are
changed the same way.


Requirements
============

(1) Table performance_schema.table_io_waits_summary_by_table.
-------------------------------------------------------------

Func-Req (1.1): A fresh MySQL installation of CURRENT-VERSION must create the table
performance_schema.table_io_waits_summary_by_table.

Func-Req (1.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
the table
performance_schema.table_io_waits_summary_by_table.

Func-Req (1.3): Security privileges for table_io_waits_summary_by_table
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 (1.4): Table performance_schema.table_io_waits_summary_by_table
is visible in the information_schema.

Func-Req (1.5): Table performance_schema.table_io_waits_summary_by_table
is visible in SHOW TABLES.

Func-Req (1.6): Rows in performance_schema.table_io_waits_summary_by_table
displays the aggregation of all table io waits, by table.

Func-Req (1.7): truncate table performance_schema.table_io_waits_summary_by_table
resets the aggregation statistics in table_io_waits_summary_by_table.

Func-Req (1.8): truncate table performance_schema.table_io_waits_summary_by_table
also resets the aggregation statistics in table_io_waits_summary_by_index_usage.

(2) Table performance_schema.table_io_waits_summary_by_index_usage.
-------------------------------------------------------------------

Func-Req (2.1): A fresh MySQL installation of CURRENT-VERSION must create the table
performance_schema.table_io_waits_summary_by_index_usage.

Func-Req (2.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create
the table
performance_schema.table_io_waits_summary_by_index_usage.

Func-Req (2.3): Security privileges for table_io_waits_summary_by_index_usage
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 (2.4): Table performance_schema.table_io_waits_summary_by_index_usage
is visible in the information_schema.

Func-Req (2.5): Table performance_schema.table_io_waits_summary_by_index_usage
is visible in SHOW TABLES.

Func-Req (2.6): Rows in performance_schema.table_io_waits_summary_by_index_usage
displays the aggregation of all table io waits, by index used.

Func-Req (2.7): truncate table
performance_schema.table_io_waits_summary_by_index_usage
resets the aggregation statistics in table_io_waits_summary_by_index_usage.

(3) Table performance_schema.events_waits_current.
--------------------------------------------------

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

Func-Req (3.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade
table events_waits_current to add the new index_name column.

(4) Table performance_schema.events_waits_history.
--------------------------------------------------

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

Func-Req (4.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade
table events_waits_history to add the new index_name column.

(5) Table performance_schema.events_waits_history_long.
-------------------------------------------------------

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

Func-Req (5.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade
table events_waits_history_long to add the new index_name column.

Status update
=============

Note from 2010-11-12 (Marc)
Table EVENTS_WAITS_SUMMARY_BY_OBJECT has been introduced as
OBJECTS_SUMMARY_GLOBAL_BY_TYPE in WL#4816 PERFORMANCE_SCHEMA SUMMARIES.

Removed EVENTS_WAITS_SUMMARY_BY_OBJECT from the spec.

Note from 2010-12-06 (Marc)
"BY_INDEX" renamed to "BY_INDEX_USAGE".

Note from 2010-12-09 (Marc)
Added INDEX_NAME in events_waits_current/history/history_long

The detailed design is documented in doxygen format.
See instructions in the file Doxyfile-perfschema, in the project branch.