A brief introduction to MySQL's show profile and the replacement of this feature in MySQL 5.7 performance_schema

The source of this article: http://www.cnblogs.com/wy123/p/6979499.html

The show Profile command is used to track resource consumption information for executed SQL statements, which can help you see how SQL statements are executed and can be used as a reference for performance analysis or problem diagnosis.
In MySQL5.7, the show Profile command is deprecated and MySQL uses the information from the system tables in Performance_schema to replace the show command
This article provides a brief introduction to MySQL profile usage, as well as improvements after MySQL5.7, while making a simple comparison with the DMV in SQL Server and the profiles and extension events.
Finally, the metrics reflected in MySQL and SQL Server are similar, as well as reference functions.

Show profile supported by MySQL5.7

Show profile tracking SQL execution needs to be open for configuration to be used

　　

The test executes several times "select COUNT (1) from Test_table1;" This SQL statement to see the query_id of the executed SQL

　　

Then look at the execution of a specific query_id

　　

You can then view resource consumption information for 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 on show profile, please refer to the various reference materials and official documentation.

　　

The information recorded in show profile is actually stored in INFORMATION_SCHEMA. PROFILING in this system table,
The various show profiles are just equivalent to a waistcoat, in a way that shows information_schema. Information in the PROFILING.
Frankly speaking, the individual is not very fond of the system similar to the encapsulation command, rather than directly to define the query conditions to query the system table itself to more real.

MySQL's show profile is pretty much the same feature.
The details are either very, very frequent, and will be familiar to you, or you can look it up when you use them.

MySQL5.7 After the Performance_schema replaces show profile

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

Personal understanding is to store the original in INFORMATION_SCHEMA. The information in the Profiling system table is stored in a different way.
This process is also supported for configurable, first look at performance_schema.setup_actors this system table, by default, the profile tracking record is turned on.

　　

You can turn off the profile tracking feature at the global level and only limit the execution record of an account to be tracked

　　

The account is re-opened and the tracking of all accounts is recorded by default.
Then according to the official website prompts, you need to open a configuration option to properly record profile information.
Execute the following SQL.

UPDATEPerformance_schema.setup_instrumentsSETENABLED= 'YES', TIMED= 'YES'WHERENAME like '%statement/%';UPDATEPerformance_schema.setup_instrumentsSETENABLED= 'YES', TIMED= 'YES'WHERENAME like '%stage/%';UPDATEPerformance_schema.setup_consumersSETENABLED= 'YES'WHERENAME like '%events_statements_%';UPDATEPerformance_schema.setup_consumersSETENABLED= 'YES'WHERENAME like '%events_stages_%';

Continue to test with the SQL query statement above (select COUNT (1) from Test_table1).
The system table Performance_schema.events_statements_history_long can then blur the information of the previously executed SQL statements based on the text information.

Based on the event_id of the SQL statements above, we can query the resource consumption information of this SQL statement during the execution.

　　

The top two SQL, the official when copied.

SELECTEVENT_ID,TRUNCATE(timer_wait/1000000000000,6) asDuration, Sql_text fromPerformance_schema.events_statements_history_longWHERESql_text like '%select count (1) from test_table1%'; SELECTEvent_Name asStage,TRUNCATE(timer_wait/1000000000000,6) asDuration fromPerformance_schema.events_stages_history_longWHEREnesting_event_id=544102;

However, the information recorded in the Performance_schema system table does not query the consumption of a certain class of resources, as shown in the CPU for query of the show profile.
itself has not yet been searched for related equivalent to the show Profile CPU for Query * * * system table, have to know also hope to inform, thank you.
By the time some of the information on this is said, in the Performance_schema system table recorded in the information: "Does not cover all metrics compared to the native profiling i.e. CONTEXT SWIT CHES, BLOCK IO, SWAPS "
Also said that compared to the previous version of the show profile, the new record profile mode is still to be perfected, do not know so far has not perfected this function.
Reference: https://www.percona.com/blog/2015/04/16/profiling-mysql-queries-from-performance-schema/

This is probably the message in the show profile in MySQL,
Refer to the "in-depth MySQL" found that the show profile mentioned in the execution of the execution gave a warning that the subsequent version may remove this feature, and therefore search for the replacement of show profile.

MySQL profile is a simple contrast to the profiles in SQL Server

Finally, there are some similarities to the comparison of similar functions in SQL Server system table DMV.
SQL Server can use the DMV to query some information about the executed SQL, such as the time of execution, the CPU time consumed, the logical reading and writing of the execution, the physical reading and writing, etc.
But there are some different results, let's say it.

The above MySQL statistic is a result that emphasizes the dimension of step and time, that is, how much time each step takes,
Here, SQL Server counts a whole consumption of information.
If SQL Server thinks it is possible to achieve a similar, the simplest is the results of the profile tracking in SQL Servers, also known as profile, it seems that the routines are the same,
The other is the improved extended event in SQL Server, referring to the previous blog post: http://www.cnblogs.com/wy123/p/6835939.html
Can get the session level of waiting resources and wait time, this is basically equivalent to the MySQL Performance_schema recorded information.
However, this information captured by the SQL Server extension event is information_schema than in MySQL's original profile. The PROFILING is more specific and detailed.
There is a clearer and more intuitive result in resource consumption and time dimensions.
such as the following, or that sentence, the routines are the same, changed a vest just.

　　

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

　　

Summarize:

The profile trace results can be fed back to the resource consumption information during SQL execution to provide a reference for performance optimization or problem diagnosis, as a tool for DBAs in managing and optimizing data
Functionality is similar, both in MySQL and in SQL Server.
Of course, at the time of the problem diagnosis, only this information, or not fully enough, need some other information to do a comprehensive consideration.

A brief introduction to MySQL's show profile and the replacement of this feature in MySQL 5.7 performance_schema