WL#7804: REPORT FOR SUPPORT

The aim is to implement a procedure in the sys schema that will allow the DBA
to easily collect diagnostics data from a MySQL instance to investigate.

The implementation will consist of:

   * sys.metrics - A view that includes the following data:

      - performance_schema.global_status
      - information_schema.INNODB_METRICS
      - Current memory allocation (as per the Performance Schema memory instruments)
      - Total memory allocated (as per the Performance Schema memory instruments)
      - The current time (in human readable format and as unix timestamp)

     The view should remove duplicate variables between the global status and
the InnoDB metrics.
     The view should add columns to show where the variable comes from and
whether it is enabled. For the Performance Schema memory instruments, these can
be partially enabled.

   * sys.statement_performance_analyzer(...) - A procedure that can be used to
use snapshots of performance_schema.events_statements_summary_by_digest
     together with the sys views based on
performance_schema.events_statements_summary_by_digest to generate
     the view based on a single snapshot or the difference between two snapshots.

   * sys.diagnostics(...) - The main procedure which collects:
      * A configuration overview
      * Perform an iteration every N seconds for a total of maximum M seconds
collecting:
         - sys.metrics view (or equivalent if performance_schema = OFF)
         - Relevant sys schema views
         - Replication status
         - Relevant ndbinfo tables (if mysqld is part of Cluster)
      * At the end overall (since server restart) statistics and the delta for
each iteration for the sys.metrics view
      * The delta from the start of the execution to the end should be generated
for several sys schema views including queries in the 95th percentile
      * For each iteration the output of:
         - SHOW ENGINE INNODB STATUS
         - information_schema.INNODB_TRX
         - sys.processlist
         - Memory information from the Performance Schema (using sys views)
      * Schema information
      * The output of SHOW ENGINE PERFORMANCE_SCHEMA STATUS.
   
The procedure is able to perform a number of iterations based on the arguments
provided, and it will be able to adjust the
settings for the Performance Schema consumers and instruments.

Given that the view / procedures are part of the product,
customers do not need to be trained again and again to execute a different
script/instructions, as the content of the procedure can change instead.

Function requirements:

F-1: The sys.metrics view
F-2: The sys.statement_performance_analyzer(...) stored procedure
F-3: Provide the sys.diagnostics(...) stored procedure
F-4: Add a row with each of the following options to the sys.sys_config table:
   * @sys.statement_performance_analyzer.limit ( variable =
statement_performance_analyzer.limit, value = 100 )
   * @sys.statement_performance_analyzer.view ( variable =
sys.statement_performance_analyzer.view, value = NULL) 
   * @diagnostics.allow_i_s_tables ( variable = diagnostics.allow_i_s_tables,
value = OFF )
   * @diagnostics.include_raw ( variable = diagnostics.include_raw, vaule = OFF )

   
Non-Functional requirements:

NF-1: The impact on the rest of the system should be minimal (beyond what is
expected from using the Performance Schema and other features in general).
NF-2: No events should be logged to the binary log (accomplished through SET
SESSION sql_log_bin = 0, so requires SUPER to execute the procedures)

sys.metrics
-----------

The view will have the following columns:

   * Variable_name: The name of the variable, e.g. VARIABLE_NAME from
performance_schema.global_status or the NAME column from
information_schema.INNODB_METRICS. All variable names are in lower case.
   * Variable_value: The value, e.g. VARIABLE_VALUE from
performance_schema.global_status or the COUNT column from
information_schema.INNODB_METRICS.
   * Type: The variable type: one of Global Status, Performance Schema, System
Time, or InnoDB Metrics concatenated with the value of the SUBSYSTEM column in
information_schema.INNODB_METRICS.
   * Enabled: Whether the metrics is enabled. For global_status and system time
this is always YES, for INNODB_METRICS it depends on the value of STATUS, for
Performance Schema it can be YES, NO, or PARTIAL depending on whether the memory
instruments are enabled.
   
The following InnoDB metrics are explicitly filtered out as they have duplicate
variables in global_status:

   'lock_row_lock_time', 'lock_row_lock_time_avg', 'lock_row_lock_time_max',
'lock_row_lock_waits',
   'buffer_pool_reads', 'buffer_pool_read_requests',
'buffer_pool_write_requests', 'buffer_pool_wait_free',
   'buffer_pool_read_ahead', 'buffer_pool_read_ahead_evicted',
'buffer_pool_pages_total', 'buffer_pool_pages_misc',
   'buffer_pool_pages_data', 'buffer_pool_bytes_data',
'buffer_pool_pages_dirty', 'buffer_pool_bytes_dirty',
   'buffer_pool_pages_free', 'buffer_pages_created', 'buffer_pages_written',
'buffer_pages_read',
   'buffer_data_reads', 'buffer_data_written', 'file_num_open_files',
   'os_log_bytes_written', 'os_log_fsyncs', 'os_log_pending_fsyncs',
'os_log_pending_writes',
   'log_waits', 'log_write_requests', 'log_writes', 'innodb_dblwr_writes',
'innodb_dblwr_pages_written', 'innodb_page_size'


sys.statement_performance_analyzer(...)
---------------------------------------

The procedure will take three arguments:

   * The action to complete. Supported actions are:
      - snapshot        Store a snapshot. The default is to make a snapshot of
the current content of
                        performance_schema.events_statements_summary_by_digest,
but by setting in_table
                        this can be overwritten to copy the content of the
specified table.
                        The snapshot is stored in the sys.tmp_digests temporary
table.
      - overall         Generate analysis based on the content specified by
in_table. For the overall analysis,
                        the table argument (see below) can be NOW() to use a
fresh snapshot. This will overwrite an existing snapshot.
                        Use NULL for the table argument to use the existing
snapshot. If in_table IS NULL and no snapshot
                        exists, a new will be created.
                        See also the view argument and
@sys.statement_performance_analyzer.limit.
      - delta           Generate a delta analysis. The delta will be calculated
between the reference table in
                        the table argument and the snapshot. An existing
snapshot must exist.
                        The action uses the sys.tmp_digests_delta temporary 
table.
                        See also the view argument and
@sys.statement_performance_analyzer.limit.
      - create_table    Create a regular table suitable for storing the snapshot
for later use, e.g. for
                        calculating deltas.
      - create_tmp      Create a temporary table suitable for storing the
snapshot for later use, e.g. for
                        calculating deltas.
      - save            Save the snapshot in the table specified by the table
arguement. The table must exists and have
                        the correct structure. If no snapshot exists, a new is
created.
      - cleanup         Remove the temporary tables used for the snapshot and 
delta.
   * The table argument used for some actions. Use the format 'db1.t1' or 't1'
without using any backticks (`)
     for quoting. Periods (.) are not supported in the database and table names.
               
     The meaning of the table for each action supporting the argument is:

      - snapshot        The snapshot is created based on the specified table.
Set to NULL or NOW() to use
                        the current content of
performance_schema.events_statements_summary_by_digest.
      - overall         The table with the content to create the overall
analyzis for. The following values
                        can be used:
                           o A table name - use the content of that table.
                           o NOW()        - create a fresh snapshot and
overwrite the existing snapshot.
                           o NULL         - use the last stored snapshot.
      - delta           The table name is mandatory and specified the reference
view to compare the currently
                        stored snapshot against. If no snapshot exists, a new
will be created.
      - create_table    The name of the regular table to create.
      - create_tmp      The name of the temporary table to create.
      - save            The name of the table to save the currently stored
snapshot into.
   * The view argument. Which views to include. The supported views are:
      - with_runtimes_in_95th_percentile  Based on the
sys.statements_with_runtimes_in_95th_percentile view
      - analysis                          Based on the sys.statement_analysis 
view
      - with_errors_or_warnings           Based on the
sys.statements_with_errors_or_warnings view
      - with_full_table_scans             Based on the
sys.statements_with_full_table_scans view
      - with_sorting                      Based on the
sys.statements_with_sorting view
      - with_temp_tables                  Based on the
sys.statements_with_temp_tables view
      - custom                            Use a custom view. This view must be
specified in @sys.statement_performance_analyzer.view to an existing view or a 
query

     Default is to include all except 'custom'.
     
The procedure supports the following configuration options:

   * @sys.statement_performance_analyzer.limit
     The maximum number of rows to include for the views that does not have a
built-in limit (e.g. the 95th percentile view has a built-in limit).
     If not set the limit is 100.

   * @sys.statement_performance_analyzer.view
     Used together with the 'custom' view. If the value contains a space, it is
considered a query, otherwise it must be
     an existing view querying the
performance_schema.events_statements_summary_by_digest table. There cannot be
any limit
     clause including in the query or view definition if
@sys.statement_performance_analyzer.limit > 0.
     If specifying a view, use the same format as for in_table.

   * @sys.debug
     Whether to provide debugging output.
     Default is 'OFF'. Set to 'ON' to include.

For example to create a report with the queries in the 95th percentile since
last truncate of performance_schema.events_statements_summary_by_digest and the
delta for a 1 minute period:

   1. Create a temporary table to store the initial snapshot.
   2. Create the initial snapshot.
   3. Save the initial snapshot in the temporary table.
   4. Wait one minute.
   5. Create a new snapshot.
   6. Perform analyzis based on the new snapshot.
   7. Perform analyzis based on the delta between the initial and new snapshots.

   mysql> CALL sys.statement_performance_analyzer('create_tmp',
'mydb.tmp_digests_ini', NULL);
   Query OK, 0 rows affected (0.08 sec)

   mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
   Query OK, 0 rows affected (0.02 sec)

   mysql> CALL sys.statement_performance_analyzer('save',
'mydb.tmp_digests_ini', NULL);
   Query OK, 0 rows affected (0.00 sec)

   mysql> DO SLEEP(60);
   Query OK, 0 rows affected (1 min 0.00 sec)

   mysql> CALL sys.statement_performance_analyzer('snapshot', NULL, NULL);
   Query OK, 0 rows affected (0.02 sec)

   mysql> CALL sys.statement_performance_analyzer('overall', NULL,
'with_runtimes_in_95th_percentile');
   +-----------------------------------------+
   | Next Output                             |
   +-----------------------------------------+
   | Queries with Runtime in 95th Percentile |
   +-----------------------------------------+
   1 row in set (0.05 sec)

   ...

   mysql> CALL sys.statement_performance_analyzer('delta',
'mydb.tmp_digests_ini', 'with_runtimes_in_95th_percentile');
   +-----------------------------------------+
   | Next Output                             |
   +-----------------------------------------+
   | Queries with Runtime in 95th Percentile |
   +-----------------------------------------+
   1 row in set (0.03 sec)

   ...


sys.diagnostics(...)
--------------------

The procedure will take three arguments:

   * 'in_max_runtime': The maximum runtime in seconds.
      - Range: 1 > INT UNSIGNED
      - Pass NULL to get the default of 60 seconds
      - A value > 0 is required
   * 'in_interval': The time between starting each iteration
      - Range: 1 > INT UNSIGNED
      - Pass NULL to get the default of 30 seconds
      - A sleep to wait for the next iteration will not start if the current run
time plus the time between iterations exceeds the maximum runtime.
   * 'in_auto_config': The choice of the Performance Schema configuration to 
use:
      - Range: ENUM ('current', 'medium', 'full')
      - current: do not make any changes to the configuration
      - medium: enable all consumers except %_history and %_history_long and all
instruments
                except wait/synch/% and all thread except the one monitoring
      - full: enable all consumers and all instrument + all threads except the
one monitoring.
     
     Enabling additional instruments and consumers will in general have an
impact on the performance of MySQL,
     so great care should be taken when using 'medium' and in particularly 
'all'.
     
     The connection running the report will always be disabled for the duration
of the procedure.
     
The procedure supports the following configuration options:

   * @sys.diagnostics.allow_i_s_tables
     Set to 'ON' to include additional queries involving the
information_schema.TABLES table. By default only a SELECT COUNT(*) is performed.

   * @sys.diagnostics.include_raw
     Set to 'ON' to include the output of the sys schema views at the start and
end, and the sys.metrics (or equivalent) for each iteration.

   * @sys.debug
     Whether to provide debugging output.
     Default is 'OFF'. Set to 'ON' to include.

   * @sys.statement_truncate_len
     At which length to truncate statements in the sys.processlist output.

For example to create a diagnostics report that starts an iteration every 30
seconds and runs for at most 60 seconds and using the current Performance Schema
settings:

   CALL sys.diagnostics(60, 30, 'current');

I.e. this will execute a total of 2 iterations.