WL#7804: REPORT FOR SUPPORT
Affects: Server-5.7 — Status: Complete
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.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.