WL#5768: PERFORMANCE SCHEMA, prepared statements instrumentation

Follow up on statement instrumentation.

Instrument prepared statements:
- Provide aggregated statistics by prepared statements.
- Expose prepared statements in use by the server, for inspection.

This is implemented with table performance_schema.prepared_statements_instances.

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

CURRENT-VERSION := 5.7
PREVIOUS-VERSION := 5.6

(1) Table performance_schema.prepared_statements_instances.
-----------------------------------------------------------

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

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

(2) Server options and variables
---------------------------------

Func-Req (2.1): The server system variable
performance_schema_max_prepared_statements_instances is defined.
This variable is global, and read only.

Func-Req (2.2): The server status variable
performance_schema_prepared_statements_lost is defined.
This variable is global, and can be flushed with FLUSH STATUS.

(3) Functional behavior
-----------------------

Func-Req (3.1): When a SQL "PREPARE" statement, in the TEXT protocol, is
executed, and when the instrumentation is enabled, a new row representing the
prepared statement is created in table prepared_statements_instances.

Func-Req (3.2): When a SQL "EXECUTE" statement, in the TEXT protocol, is
executed, and when the instrumentation is enabled, the matching existing row
representing the prepared statement is updated in table
prepared_statements_instances.

Func-Req (3.3): When a SQL "DEALLOCATE PREPARE" statement, in the TEXT protocol,
is executed, the matching existing row representing the prepared statement is
deleted in table prepared_statements_instances.

Func-Req (3.4): When a COM_STMT_PREPARE command, in the BINARY protocol, is
executed, and when the instrumentation is enabled, a new row representing the
prepared statement is created in table prepared_statements_instances.

Func-Req (3.5): When a COM_STMT_EXECUTE command, in the BINARY protocol, is
executed, and when the instrumentation is enabled, the matching existing row
representing the prepared statement is updated in table
prepared_statements_instances.

Func-Req (3.6): When a COM_STMT_CLOSE command, in the BINARY protocol, is
executed, the matching existing row representing the prepared statement is
deleted in table prepared_statements_instances.

Overview
========

For historical reasons, MySQL has two separate ways to use prepared statements.

The 'binary' protocol, implemented with:
- COM_STMT_PREPARE
- COM_STMT_EXECUTE
- COM_STMT_CLOSE

The 'text' protocol, implemented with:
- SQLCOM_PREPARE
- SQLCOM_EXECUTE
- SQLCOM_DEALLOCATE_PREPARE

This instrumentation covers both.

New table
=========

CREATE TABLE performance_schema.`prepared_statements_instances` (
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `STATEMENT_ID` integer unsigned NOT NULL,
  `STATEMENT_NAME` varchar(64) default NULL,
  `SQL_TEXT` longtext NOT NULL,
  `OWNER_THREAD_ID` bigint(20) unsigned NOT NULL,
  `OWNER_EVENT_ID` bigint(20) unsigned NOT NULL,
  `OWNER_OBJECT_TYPE` enum('EVENT','FUNCTION','PROCEDURE','TABLE','TRIGGER')
DEFAULT NULL,
  `OWNER_OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OWNER_OBJECT_NAME` varchar(64) DEFAULT NULL,
  `TIMER_PREPARE` bigint(20) unsigned NOT NULL,
  `COUNT_REPREPARE` bigint(20) unsigned NOT NULL,
  `COUNT_EXECUTE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_EXECUTE` bigint(20) unsigned NOT NULL,
  `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL,
  `SUM_ERRORS` bigint(20) unsigned NOT NULL,
  `SUM_WARNINGS` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL,
  `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL,
  `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL,
  `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL,
  `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL,
  `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL,
  `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL,
  `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Operations:
- SELECT is allowed
- TRUNCATE is allowed, to reset statistics

Columns semantic:

OBJECT_INSTANCE_BEGIN is a unique internal identifier, the address in memory of
the prepared statement

STATEMENT_ID is the internal statement ID assigned by the server.
Both TEXT and BINARY protocols uses a statement id.

STATEMENT_NAME is the external statement name assigned by the user,
in the TEXT protocol. 
For example, for the following SQL statement, using the TEXT protocol:
  PREPARE foo FROM "...",
the name of the prepare statement is "foo".
For the BINARY protocol, this column is NULL.

SQL_TEXT is the prepared statement text, with '?' placeholder markers.

OWNER_THREAD_ID, OWNER_EVENT_ID is the event
that created the prepared statement.

OWNER_OBJECT_TYPE, OWNER_OBJECT_SCHEMA, OWNER_OBJECT_NAME:
When a prepared statement is created by a client session, all NULL.
When a prepared statement is created by a stored program, these columns point to
the stored program.
A typical user error is to forget to de allocate prepared statements.
These columns are useful to find which stored program are leaking prepared
statements, if any.

TIMER_PREPARE: the time spent executing the statement preparation itself

COUNT_REPREPARE: Number of times the statement was re-prepared internally.
Note that timing statistics of REPREPARE are not available, as REPREPARE is not
a separate operation, it is counted as part of EXECUTE.

COUNT_EXECUTE, SUM_TIMER_EXECUTE, MIN_TIMER_EXECUTE, AVG_TIMER_EXECUTE,
MAX_TIMER_EXECUTE: aggregated statistics for every EXECUTE statement related to
this prepared statement

SUM_LOCK_TIME, ..., SUM_NO_GOOD_INDEX_USED: aggregated statement execution
statistics

New server variables
====================

performance_schema_max_prepared_statements_instances,
the number of instrumented prepared statements.
This parameter is autotuned (-1) by default.
The auto tuned value depends on max_prepared_stmt_count.

performance_schema_prepared_statements_lost,
indicates how many prepared statements could not be instrumented.

Behavior:
=========

PREPARE
-------

A prepared statement in the server is created by one of the following commands:
- COM_STMT_PREPARE
- SQLCOM_PREPARE

If the associated statement is instrumented, a new row is added in table
prepared_statements_instances (or the lost counter is incremented).

Note: there is no way to decouple the statement instrumentation
(statement/com/prepare, statement/sql/prepare) from the instrumentation of
prepared statement instances. Unclear if this has value or not.

EXECUTE
-------

Whenever the statements:
- COM_STMT_EXECUTE
- SQLCOM_PREPARE
are executed, if the statement is instrumented, and if a prepared statement
instance exists, then the instrumented prepared statement is updated.

DEALLOCATE_PREPARE
------------------

Whenever the statements:
- COM_STMT_CLOSE
- SQLCOM_DEALLOCATE_PREPARE

are executed, if a prepared statement instance exists, then the instrumented
prepared statement is removed from table prepared_statements_instances.

Note: removal always happen, even if "statement/com/prepare,
statement/sql/prepare" instrumentation is disabled. This is to avoid resource leaks.

Enabling instrumentation
========================

Collection of statistics depends on existing statement instruments:
- "statement/sql/prepare_sql" for SQLCOM_PREPARE
- "statement/com/Prepare" for COM_STMT_PREPARE
- "statement/sql/execute_sql" for SQLCOM_EXECUTE
- "statement/com/Execute" for COM_STMT_EXECUTE
Given that these instruments are enabled by default,
data in table performance_schema.prepared_statement_instances
is collected by default.