Introduction to the show profile (outdated) of MySQL and its substitution in performance_schema in MySQL 5.7, performanceschema

Introduction to the show profile (outdated) of MySQL and its substitution in performance_schema in MySQL 5.7, performanceschema

 

Source: http://www.cnblogs.com/wy123/p/6979499.html

 

The show profile command is used to track the resource consumption information of executed SQL statements. It can help you view the execution status of SQL statements and serve as a reference for performance analysis or problem diagnosis.
In MySQL5.7, the show profile command is not recommended. MySQL uses the system table information in performance_schema to replace the show profile command.
This article briefly introduces the use of MySQL profile and the improvement after MySQL5.7. It also makes a simple comparison with DMV in SQL Server and profile and extended events,
At last, we will find that the indicators reflected by this information in MySQL and SQL Server are similar to those for reference.

 

Show profile, which is still supported by MySQL5.7

Show profile is an outdated option after MySQ L5.7, but MySQL 5.7 is still supported, but it is not recommended. You must enable the configuration to use show profile to track SQL Execution.

　　

Run the "select count (1) from test_table1;" SQL statement several times to view the QUERY_ID of the executed SQL statement.

　　

Then, view the execution process of a specific query_id.

　　

Then, you can view the resource consumption information of one aspect of a query (executed SQL statement.
For example, show profile cpufor query 82 or show profile all for query 82;

　　

Or show profile all for query 82. For more information about show profile parameters, refer to various references and official documents.

　　

The information recorded in show profile is actually stored in the INFORMATION_SCHEMA.PROFILING system table,
The various show profiles are just equivalent to a vest. In another way, the information in INFORMATION_SCHEMA.PROFILING is displayed.
To be honest, I do not like system-like command encapsulation. It is better to define the query conditions directly to query the system table itself.

The show profile function of MySQL is similar to this function.
The detailed functions are either used very frequently, and will naturally become familiar with them, or you can check them when using them.

 

After MySQL5.7Performance_schema replaces show profile

　　First refer to the official document https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html

 

In my personal understanding, the information originally stored in the INFORMATION_SCHEMA.PROFILING system table is replaced by a storage method.
This process also supports configuration. First, check the cece_schema.setup_actors system table. By default, profile tracking is enabled.

　　

You can disable the profile record tracking function at the local level, but only limit the execution records of one account to be tracked.

　　

Here, the account is re-created, and the trace of all accounts is recorded by default.
Then, according to the prompts on the official website, you need to open a configuration option to record the profile information normally.
Run the following SQL statement.

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME LIKE '%statement/%';UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'WHERE NAME LIKE '%stage/%';UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'WHERE NAME LIKE '%events_statements_%';UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'WHERE NAME LIKE '%events_stages_%';

Continue to use the preceding SQL query statement (select count (1) from test_table1) for testing,
Then, in the system table performance_schema.events_statements_history_long, You Can fuzzy match the information of the previously executed SQL statement based on the text information.

Based on the Event_id of the matching SQL statement, you can query the resource consumption information of this SQL statement during execution.

　　

The preceding two SQL statements are copied.

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXTFROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%select count(1) from test_table1%';  SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS DurationFROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=544102;

 

However, the information recorded in the performance_schema system table cannot be used to query the consumption of a certain type of resources like show profile cpu for query.
No system table is found, which is equivalent to show profile cpu for query ***. If you have any information, please kindly advise. Thank you.
In some documents, the information recorded in the performance_schema system table is: "Does not cover all metrics compared to the native profiling I. e. context switches, block io, SWAPS"
That is to say, compared with the show profile of the previous version, the new method of recording the profile still needs to be improved. I do not know whether this function has been improved so far.
Reference: https://www.percona.com/blog/2015/04/16/profiling-mysql-queries-from-performance-schema/

 

 

The show profile information in MySQL is probably like this,
Referring to the show profile statement in MySQL, a warning is given for executing the show profile statement, indicating that this function may be removed in later versions. Therefore, you can search for the show profile substitution.

 

 

Simple comparison between profile information of MySQL and profile in SQL Server

Finally, we can simply compare it with the similar functions in the sqlserver system table DMV, but there are some similarities.
SQL Server can use DMV to query information about executed SQL statements, such as execution time, CPU consumed time, logical read/write, and physical read/write.
However, the results are different.

The above MySQL statistics show that a result emphasizes the step and time dimensions, that is, how much time is spent in each step,
SQL Server calculates the overall consumption information.
If SQL Server can achieve similar results, the simplest is the profile tracking result in SQL Server, also called profile. It seems that the routines are the same,
In addition, it is the expansion event improved in sqlserver, refer to the previous blog: http://www.cnblogs.com/wy123/p/6835939.html
You can get the Session-level waiting resources and waiting time, which is basically equivalent to the information recorded in performance_schema in MySQL.
However, the information captured by the SQL Server extended event is more specific and detailed than the INFORMATION_SCHEMA.PROFILING in the original MySQL Profile.
There is a clearer and more intuitive result in the resource consumption and time dimension.
For example, in the following example, the routines are the same. I just changed my vest.

　　

If the above results are captured by the extended Event, the statistics are more like the profile information in MySQL.

　　

 

 

Summary:

Profile tracking results can feedback the resource consumption information during SQL Execution to provide reference for performance optimization or problem diagnosis. It serves as a DBA tool for data management and optimization.
Functions are similar in MySQL or SQL Server.
Of course, when diagnosing a problem, it is still not enough to have such information alone, and some other information needs to be considered comprehensively.