WL#5420: PERFORMANCE SCHEMA TABLE LOCK WAIT SUMMARY

This aggregation summarizes data obtained with the TABLE LOCK instrumentation,
see WL#5371 PERFORMANCE_SCHEMA Instrumenting Table Locks

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 locks which user table,
so the data provided by this instrumentation can not be used to tune directly
the server.

When implementing changes to the server table locking code however,
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,
and uses locks against tables, to minimize the application footprint on the server,
and improve the application performances / scalability.

* 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.
For example, an application doing little table io but locking tables for a long
time could have a negative impact on existing applications already deployed,
and this impact will be visible with the instrumentation provided.
Without table lock instrumentation, this impact will be un accounted for,
and performance drops due to locking will be harder to identify.

TABLE performance_schema.TABLE_LOCK_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_READ_NORMAL` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ_NORMAL` bigint(20) unsigned NOT NULL,
  `COUNT_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ_WITH_SHARED_LOCKS` bigint(20) unsigned NOT NULL,
  `COUNT_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ_HIGH_PRIORITY` bigint(20) unsigned NOT NULL,
  `COUNT_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ_NO_INSERT` bigint(20) unsigned NOT NULL,
  `COUNT_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ_EXTERNAL` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE_ALLOW_WRITE` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE_CONCURRENT_INSERT` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE_DELAYED` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE_DELAYED` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE_DELAYED` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE_DELAYED` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE_DELAYED` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE_LOW_PRIORITY` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE_NORMAL` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE_EXTERNAL` bigint(20) unsigned NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |

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 lock operations,
as generated by the "wait/lock/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 operations.

The columns COUNT/SUM/MIN/AVG/MAX TIMER_WRITE aggregates all WRITE operations.

The other columns are per specific operation, as detailed below:

Internal read locks:
  COUNT/SUM/MIN/MAX READ_NORMAL
  COUNT/SUM/MIN/MAX READ_WITH_SHARED_LOCKS
  COUNT/SUM/MIN/MAX READ_HIGH_PRIORITY
  COUNT/SUM/MIN/MAX READ_NO_INSERT

External read locks:
  COUNT/SUM/MIN/MAX READ_EXTERNAL

Internal write locks:
  COUNT/SUM/MIN/MAX WRITE_ALLOW_WRITE
  COUNT/SUM/MIN/MAX WRITE_CONCURRENT_INSERT
  COUNT/SUM/MIN/MAX WRITE_DELAYED
  COUNT/SUM/MIN/MAX WRITE_LOW_PRIORITY
  COUNT/SUM/MIN/MAX WRITE_NORMAL

External write locks:
  COUNT/SUM/MIN/MAX WRITE_EXTERNAL

The grouping is by table.

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.

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

(1) Table performance_schema.table_lock_waits_summary_by_table.
---------------------------------------------------------------

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

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

Func-Req (1.3): Security privileges for table_lock_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_lock_waits_summary_by_table
is visible in the information_schema.

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

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

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

Resolved
========

Removed WRITE_ALLOW_READ on 2010-06-21,
since TL_WRITE_ALLOW_READ was dead code,
and got removed from mysql-next-mr.

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