WL#5378: PERFORMANCE SCHEMA SUMMARY BY USER / HOST

Provide connection statistics, and aggregate performance schema statistics:
- by user account (USER + HOST)
- by user name (USER)
- by client machine (HOST).

Target audience

* Application developers

A good security practice is to define dedicated users for applications, so that
an application is given privileges to only what it needs to operate.

In this context, performance schema statistics per users allows application
developers to see load statistics per applications, when deploying several 
applications against the same database server.

* Production engineers

Production engineers monitoring a server can use statistics to assess the server
load caused by a specific host, or user, or user+host.

This is useful to compare load originating from several identical
machines (multiple servers in a web server farm), or to analyze the load of a
given hardware / software / application configuration, for capacity planning.

For example, given:
- application server app-1,
- application server app-2,
...
- application server app-N.
which are identical (same applications, connectors, middleware, os, configuration),

a DBA can:
- perform a selective upgrade on app-1 only (of any component),
- monitor app-1 in comparison to app-x, to assess the overall impact of the upgrade.
- then if all is well, upgrade app-2, ..., app-N to complete the roll out.

Introduction
============

New tables
- ACCOUNTS,
- USERS,
- HOSTS
provide statistics on connections.

New summary tables provide events statistics aggregated by:
- by user account (USER + HOST)
- by user name (USER)
- by client machine (HOST),
and grouped by instruments (EVENT_NAME).

These summaries are an intermediate aggregation level between:
- XXX_SUMMARY_BY_THREAD_BY_EVENT_NAME, which is more detailed
- XXX_SUMMARY_GLOBAL_BY_EVENT_NAME, which is less detailed

For waits, the new tables are:
- EVENTS_WAITS_SUMMARY_BY_ACCOUNT_BY_EVENT_NAME,
- EVENTS_WAITS_SUMMARY_BY_USER_BY_EVENT_NAME,
- EVENTS_WAITS_SUMMARY_BY_HOST_BY_EVENT_NAME.

See related WL#2360, which introduced the waits tables.

Likewise, for stages, the new tables are:
- EVENTS_STAGES_SUMMARY_BY_ACCOUNT_BY_EVENT_NAME,
- EVENTS_STAGES_SUMMARY_BY_USER_BY_EVENT_NAME,
- EVENTS_STAGES_SUMMARY_BY_HOST_BY_EVENT_NAME.

See related WL#4813, which introduced the stages tables.

Likewise, for statements, the new tables are:
- EVENTS_STATEMENTS_SUMMARY_BY_ACCOUNT_BY_EVENT_NAME,
- EVENTS_STATEMENTS_SUMMARY_BY_USER_BY_EVENT_NAME,
- EVENTS_STATEMENTS_SUMMARY_BY_HOST_BY_EVENT_NAME.

See related WL#2515, which introduced the statements tables.

TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_ACCOUNT_BY_EVENT_NAME
======================================================================

Columns
-------

  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `EVENT_NAME` varchar(128) NOT 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

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.

Note that "user1@hostA", "user2@hostA" and "user1@hostB" are 3 (three) distinct
user accounts, so this table will show separate rows for each account.

Alternate names (rejected)
- EVENTS_WAITS_SUMMARY_BY_USER_AND_HOST_BY_EVENT_NAME
- EVENTS_WAITS_SUMMARY_BY_USER_AT_HOST_BY_EVENT_NAME
- EVENTS_WAITS_SUMMARY_BY_USER_HOST_BY_EVENT_NAME
- EVENTS_WAITS_SUMMARY_BY_USER_BY_EVENT_NAME
(reserved to aggregate BY_USER only)

TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_USER_BY_EVENT_NAME
===================================================================

Columns
-------

  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `EVENT_NAME` varchar(128) NOT 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

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.

Note that "user1@hostA", "user2@hostA" and "user1@hostB" uses 2 (two) distinct
user names, so this table will show separate rows for each user name ("user1"
and "user2"), not account (user@host).

TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_HOST_BY_EVENT_NAME
===================================================================

Columns
-------

  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `EVENT_NAME` varchar(128) NOT 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

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.

Note that "user1@hostA", "user2@hostA" and "user1@hostB" are connections from 2
(two) distinct client hosts, so this table will show separate rows for each host
("hostA" and "hostB").


TABLE performance_schema.EVENTS_STAGES_SUMMARY_BY_ACCOUNT_BY_EVENT_NAME
=======================================================================

Table similar to EVENTS_STAGES_SUMMARY_BY_THREAD_BY_EVENT_NAME,
grouped by USER and HOST instead of THREAD.

TABLE performance_schema.EVENTS_STAGES_SUMMARY_BY_USER_BY_EVENT_NAME
====================================================================

Table similar to EVENTS_STAGES_SUMMARY_BY_THREAD_BY_EVENT_NAME,
grouped by USER instead of THREAD.

TABLE performance_schema.EVENTS_STAGES_SUMMARY_BY_HOST_BY_EVENT_NAME
====================================================================

Table similar to EVENTS_STAGES_SUMMARY_BY_THREAD_BY_EVENT_NAME,
grouped by HOST instead of THREAD.

TABLE performance_schema.EVENTS_STATEMENTS_SUMMARY_BY_ACCOUNT_BY_EVENT_NAME
===========================================================================

Table similar to EVENTS_STATEMENTS_SUMMARY_BY_THREAD_BY_EVENT_NAME,
grouped by USER and HOST instead of THREAD.

TABLE performance_schema.EVENTS_STATEMENTS_SUMMARY_BY_USER_BY_EVENT_NAME
========================================================================

Table similar to EVENTS_STATEMENTS_SUMMARY_BY_THREAD_BY_EVENT_NAME,
grouped by USER instead of THREAD.

TABLE performance_schema.EVENTS_STATEMENTS_SUMMARY_BY_HOST_BY_EVENT_NAME
========================================================================

Table similar to EVENTS_STATEMENTS_SUMMARY_BY_THREAD_BY_EVENT_NAME,
grouped by HOST instead of THREAD.

TABLE performance_schema.ACCOUNTS
=================================

  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `CURRENT_CONNECTIONS` bigint(20) NOT NULL,
  `TOTAL_CONNECTIONS` bigint(20) NOT NULL

Operations
----------

SELECT is supported.
TRUNCATE is supported, see below.

Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------

When a user+host connects to the server:
- a new row is added in table ACCOUNTS, if none was present.
- column current_connection is incremented by one.
- column total_connection is incremented by one.

When a user+host disconnects from the server:
- column current_connection is decremented by one.
- column total_connection is un changed.

When TRUNCATE TABLE USER_HOST is executed:
- rows for disconnected users (current_connections = 0) are deleted.
- for users still connected (current_connections != 0),
the column total_connection is reset to current_connections.

Also, when TRUNCATE TABLE ACCOUNTS is executed,
an implicit truncate is also executed on the following tables:
- every *_summary_by_account_* tables.

TABLE performance_schema.USERS
==============================

  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `CURRENT_CONNECTIONS` bigint(20) NOT NULL,
  `TOTAL_CONNECTIONS` bigint(20) NOT NULL

Operations
----------

SELECT is supported.
TRUNCATE is supported, see below.

Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------

When a user connects to the server:
- a new row is added in table USERS, if none was present.
- column current_connection is incremented by one.
- column total_connection is incremented by one.

When a user disconnects from the server:
- column current_connection is decremented by one.
- column total_connection is un changed.

When TRUNCATE TABLE USERS is executed:
- rows for disconnected users (current_connections = 0) are deleted.
- for users still connected (current_connections != 0),
the column total_connection is reset to current_connections.

Also, when TRUNCATE TABLE USERS is executed,
an implicit truncate is also executed on the following tables:
- ACCOUNTS.
- every *_summary_by_user_* tables.

TABLE performance_schema.HOSTS
==============================

  `HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
  `CURRENT_CONNECTIONS` bigint(20) NOT NULL,
  `TOTAL_CONNECTIONS` bigint(20) NOT NULL

Operations
----------

SELECT is supported.
TRUNCATE is supported, see below.

Normal privilege checks do apply: a user must be granted the proper table
privileges to perform an operation.

Semantic
--------

When a user connects to the server:
- a new host is added in table HOSTS, if none was present.
- column current_connection is incremented by one.
- column total_connection is incremented by one.

When a host disconnects from the server:
- column current_connection is decremented by one.
- column total_connection is un changed.

When TRUNCATE TABLE HOSTS is executed:
- rows for disconnected hosts (current_connections = 0) are deleted.
- for hosts still connected (current_connections != 0),
the column total_connection is reset to current_connections.

Also, when TRUNCATE TABLE HOSTS is executed,
an implicit truncate is also executed on the following tables:
- ACCOUNTS.
- every *_summary_by_host_* tables.

System variables
================

performance_schema_users_size:
maximum number of users the performance schema can keep track of.
This variable can be set at server start up only.

performance_schema_accounts_size
maximum number of user+host accounts the performance schema can keep track of.
This variable can be set at server start up only.

performance_schema_hosts_size
maximum number of hosts the performance schema can keep track of.
This variable can be set at server start up only.

Status variables
================

Performance_schema_users_lost,
Performance_schema_accounts_lost,
Performance_schema_hosts_lost:

Incremented when the respective system variables are found to be too small,
when aggregation per user / account / host can not be computed.

Requirements
------------

(1) Table performance_schema.accounts.
--------------------------------------

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

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

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

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

Func-Req (1.6): Truncate table accounts
causes an implicit truncate on the following tables:
- (1.6.1) events_waits_summary_by_account_by_event_name
- (1.6.2) events_stages_summary_by_account_by_event_name
- (1.6.3) events_statements_summary_by_account_by_event_name
- (1.6.4) events_waits_summary_by_thread_by_event_name
- (1.6.5) events_stages_summary_by_thread_by_event_name
- (1.6.6) events_statements_summary_by_thread_by_event_name

(2) Table performance_schema.users.
-----------------------------------

Func-Req (2.1 -- 2.5): Same as 1.1 -- 1.5,
for table users.

Func-Req (2.6): Truncate table users
causes an implicit truncate on the following tables:
- (2.6.1) events_waits_summary_by_user_by_event_name
- (2.6.2) events_stages_summary_by_user_by_event_name
- (2.6.3) events_statements_summary_by_user_by_event_name
- (2.6.4) events_waits_summary_by_account_by_event_name
- (2.6.5) events_stages_summary_by_account_by_event_name
- (2.6.6) events_statements_summary_by_account_by_event_name
- (2.6.7) events_waits_summary_by_thread_by_event_name
- (2.6.8) events_stages_summary_by_thread_by_event_name
- (2.6.9) events_statements_summary_by_thread_by_event_name

(3) Table performance_schema.hosts.
-----------------------------------

Func-Req (3.1 -- 3.5): Same as 1.1 -- 1.5,
for table hosts.

Func-Req (3.6): Truncate table hosts
causes an implicit truncate on the following tables:
- (3.6.1) events_waits_summary_by_host_by_event_name
- (3.6.2) events_stages_summary_by_host_by_event_name
- (3.6.3) events_statements_summary_by_host_by_event_name
- (3.6.4) events_waits_summary_by_account_by_event_name
- (3.6.5) events_stages_summary_by_account_by_event_name
- (3.6.6) events_statements_summary_by_account_by_event_name
- (3.6.7) events_waits_summary_by_thread_by_event_name
- (3.6.8) events_stages_summary_by_thread_by_event_name
- (3.6.9) events_statements_summary_by_thread_by_event_name

(4) Table performance_schema.events_waits_summary_by_account_by_event_name.
---------------------------------------------------------------------------

Func-Req (4.1 -- 4.5): Same as 1.1 -- 1.5,
for table events_waits_summary_by_account_by_event_name.

Func-Req (4.6): Truncate table events_waits_summary_by_account_by_event_name
causes an implicit truncate on the following tables:
- (4.6.1) events_waits_summary_by_thread_by_event_name

(5) Table performance_schema.events_waits_summary_by_user_by_event_name.
------------------------------------------------------------------------

Func-Req (5.1 -- 5.5): Same as 1.1 -- 1.5,
for table events_waits_summary_by_user_by_event_name.

Func-Req (5.6): Truncate table events_waits_summary_by_user_by_event_name
causes an implicit truncate on the following tables:
- (5.6.1) events_waits_summary_by_account_by_event_name
- (5.6.2) events_waits_summary_by_thread_by_event_name

(6) Table performance_schema.events_waits_summary_by_host_by_event_name.
------------------------------------------------------------------------

Func-Req (6.1 -- 6.5): Same as 1.1 -- 1.5,
for table events_waits_summary_by_host_by_event_name.

Func-Req (6.6): Truncate table events_waits_summary_by_host_by_event_name
causes an implicit truncate on the following tables:
- (6.6.1) events_waits_summary_by_account_by_event_name
- (6.6.2) events_waits_summary_by_thread_by_event_name

(7) Table performance_schema.events_stages_summary_by_account_by_event_name.
----------------------------------------------------------------------------

Func-Req (7.1 -- 7.5): Same as 1.1 -- 1.5,
for table events_stages_summary_by_account_by_event_name.

Func-Req (7.6): Truncate table events_stages_summary_by_account_by_event_name
causes an implicit truncate on the following tables:
- (7.6.1) events_stages_summary_by_thread_by_event_name

(8) Table performance_schema.events_stages_summary_by_user_by_event_name.
------------------------------------------------------------------------

Func-Req (8.1 -- 8.5): Same as 1.1 -- 1.5,
for table events_stages_summary_by_user_by_event_name.

Func-Req (8.6): Truncate table events_stages_summary_by_user_by_event_name
causes an implicit truncate on the following tables:
- (8.6.1) events_stages_summary_by_account_by_event_name
- (8.6.2) events_stages_summary_by_thread_by_event_name

(9) Table performance_schema.events_stages_summary_by_host_by_event_name.
-------------------------------------------------------------------------

Func-Req (9.1 -- 9.5): Same as 1.1 -- 1.5,
for table events_stages_summary_by_host_by_event_name.

Func-Req (9.6): Truncate table events_stages_summary_by_host_by_event_name
causes an implicit truncate on the following tables:
- (9.6.1) events_stages_summary_by_account_by_event_name
- (9.6.2) events_stages_summary_by_thread_by_event_name

(10) Table performance_schema.events_statements_summary_by_account_by_event_name.
----------------------------------------------------------------------

Func-Req (10.1 -- 10.5): Same as 1.1 -- 1.5,
for table events_statements_summary_by_account_by_event_name.

Func-Req (10.6): Truncate table events_statement_summary_by_account_by_event_name
causes an implicit truncate on the following tables:
- (10.6.1) events_statements_summary_by_thread_by_event_name

(11) Table performance_schema.events_statements_summary_by_user_by_event_name.
------------------------------------------------------------------------------

Func-Req (11.1 -- 11.5): Same as 1.1 -- 1.5,
for table events_statements_summary_by_user_by_event_name.

Func-Req (11.6): Truncate table events_statements_summary_by_user_by_event_name
causes an implicit truncate on the following tables:
- (11.6.1) events_statements_summary_by_account_by_event_name
- (11.6.2) events_statements_summary_by_thread_by_event_name

(12) Table performance_schema.events_statements_summary_by_host_by_event_name.
------------------------------------------------------------------------------

Func-Req (12.1 -- 12.5): Same as 1.1 -- 1.5,
for table events_statements_summary_by_host_by_event_name.

Func-Req (12.6): Truncate table events_statements_summary_by_host_by_event_name
causes an implicit truncate on the following tables:
- (12.6.1) events_statements_summary_by_account_by_event_name
- (12.6.2) events_statements_summary_by_thread_by_event_name

(13) Server start options and variables.
---------------------------------------

Func-Req (13.1): A new server start option is available,
"performance-schema-max-accounts-size",
to control the size of the table accounts.

Func-Req (13.2): A new server start option is available,
"performance-schema-max-users-size",
to control the size of the table users.

Func-Req (13.3): A new server start option is available,
"performance-schema-max-hosts-size",
to control the size of the table hosts.

(14) Server status.
-------------------

Func-Req (14.1): A new server status variable is available,
"Performance_schema_accounts_lost".
This variable indicates how many rows could not be added in table accounts,
due to insufficient space.

Func-Req (14.2): A new server status variable is available,
"Performance_schema_users_lost".
This variable indicates how many rows could not be added in table users,
due to insufficient space.

Func-Req (14.3): A new server status variable is available,
"Performance_schema_hosts_lost".
This variable indicates how many rows could not be added in table hosts,
due to insufficient space.

Change history
==============

2011-04-27, Marc:
- renamed USER_HOST to ACCOUNT, per discussion with various stake holders, and
architecture.
- fixed many singular / plural naming typos: tables are ACCOUNTS (plural),
summaries are BY_ACCOUNT (singular),
- renamed server variables to be xxx-size, not xxx

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