Technology Sharing | MySQL's AWR Report? — MySQL status diagnostic report

Author: Qin Fulang

A member of the Aikesheng DBA team, responsible for the daily problem handling of the project and the troubleshooting of the company's platform. Love IT, like to swim on the Internet, good at photography and cooking, DBA who doesn’t know how to cook is not a good driver, didi~

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

introduction

Students who have used Oracle database know that there is a function in Oracle: AWR (full name Automatic Workload Repository), automatic load information database. It collects operational statistics and other statistical information about a specific database. Oracle executes a snapshot of all its important statistics and load information at a fixed time interval (1 hour by default), and stores the snapshot in AWR. DBAs provide very good facilities for analyzing the database. Although MySQL does not have such strong functions, MySQL has a similar stored procedure called diagnostics that provides similar functions.

The diagnostics() stored procedure uses MySQL's own information_schema, performance_schema, sys and other metadata information and performance data information library table functions to diagnose the current server status and provide the DBA with a report that can be used to analyze the database status.

Basic information

The data collected by diagnostics() mainly contains the following information:

• Information from the metrics view;
• Information from other related sys schema views, such as query views that detect the 95th percentile;
• If it is the MySQL server of NDB Cluster, there will also be ndbinfo schema information;
• Master-slave replication status information

Some sys schema views are calculated as initial (optional), overall, and delta:

• The initial view is the content of the view at the beginning of the diagnostics() process. This output is the same as the starting value used for the delta view. If the diagnostics.include_raw configuration option is ON, the initial view will be included in the report.
• The overall view is the content of the view at the end of the diagnostics() process. This output is the same as the end value used for the delta view. The overall view is always included in the report.
• The delta view is the difference from the beginning to the end of the procedure execution. The minimum and maximum values are the minimum and maximum values of the ending view, respectively. They do not necessarily reflect the minimum and maximum values during the monitoring period. Except for the metrics view, Delta only calculates the difference between the first and last output.

Note:

This stored procedure disables the binary log during execution by manipulating the session value of the sql_log_bin system variable. This is a restricted operation, so this stored procedure needs to have sufficient permissions to set restricted session variables.

Command example

Way 1:

Create a diagnostic report, use the current Performance Schema setting, start an iteration every 30 seconds, and run for a maximum of 120 seconds:

mysql> CALL sys.diagnostics(120, 30, 'current')；

To capture the output of the diagnostics() process into a file while it is running, you can use the MySQL client tee file name and the notee command:

mysql> tee diag.out;
mysql> CALL sys.diagnostics(120, 30, 'current');
mysql> notee;

The table style generated by this method is as follows:

Way 2:

shell> mysql -uroot -p -S /opt/mysql/data/6666/mysqld.sock -H -e "CALL sys.diagnostics(120, 30, 'current');" >report.html

This method generates web page styles as follows:

parameter

CALL sys.diagnostics( in_max_runtime, in_interval, 'current')；

• in_max_runtime (INT UNSIGNED): Maximum data collection time, in seconds. The default value is 60 seconds, and a value greater than 0 is required for customization.
• in_interval (INT UNSIGNED): Sleep time between data collection, in seconds. The default value is 30 seconds, and a value greater than 0 is required for customization.

• in_auto_config ENUM('current','medium','full'): The Performance Schema configuration to be used. The allowed values are:

  • current: Use the current instrumen and consumers settings.
  • medium: enable some instrumen and consumers.
  • full: Enable all instrumen and consumers.

Note:

For the concepts and basic principles of use of instrumen and consumer in Performance Schema mode, you can consult the information by yourself, so I won't go into details in this article.

The more instrumen and consumers are enabled, the greater the impact on the performance of the MySQL server. Be careful to use the medium setting, especially the full setting, which has a greater impact on performance. There is no special requirement to use current.

The use of medium or full settings requires super permission. If a setting other than current is selected, the current setting will be restored at the end of the program.

Configuration options

The diagnostics() operation can use the following configuration options or their corresponding user-defined variables:

The following parameters need to be added or modified in sys.sys_config. For specific usage, please refer to the sys_config section of the official MySQL document.

• debug, @sys.debug

If this option is ON, debug output is generated. The default value is OFF.

UPDATE `sys`.`sys_config` SET `value` = 'ON' WHERE `variable` = 'debug';

• diagnostics.allow_i_s_tables, @sys.diagnostics.allow_i_s_tables

If this option is ON, then the diagnostics() procedure is allowed to perform a table scan on the INFORMATION_SCHEMA.TABLES table. If there are many tables, this may come at a higher price. The default value is OFF.

UPDATE `sys`.`sys_config` SET `value` = 'ON' WHERE `variable` = 'diagnostics.allow_i_s_tables';

• diagnostics.include_raw, @sys.diagnostics.include_raw

If this option is ON, the output of the diagnostics() process includes the raw output of querying the metrics view. The default value is OFF.

UPDATE `sys`.`sys_config` SET `value` = 'ON' WHERE `variable` = 'diagnostics.include_raw';

• statement_truncate_len, @sys.statement_truncate_len

The maximum length of the statement returned by the format_statement() function. Longer sentences will be truncated to this length. The default value is 64.

UPDATE `sys`.`sys_config` SET `value` = '32' WHERE `variable` = 'statement_truncate_len';

Report information

(1) Basic information of MySQL server

(2) Basic configuration information

• Parameter configuration

• Configuration of instrumen and consumer under Performance Schema

(3) sys schema initial state

(4) Information for each iteration

It is related to the iteration cycle and the number of times given by the command

(5) Schema information

(6) Overall status information

(7) Delta status information

Due to the length of the space, the above is only part of the information listed. The specific information can be understood by yourself. A link to the report is attached at the end of the document, which can be downloaded for review.

Conclusion

diagnostics() contains a lot of information, which can realize "one-click" database performance information status diagnosis and output reports, helping DBA analyze database status conveniently in actual work.

attached link:

sys_config:https://dev.mysql.com/doc/refman/8.0/en/sys-sys-config.html

diagnostic report:

Link: https://pan.baidu.com/s/1XEV2Jf9qe9-dpi-8IN_6UQ extraction code: 3306