In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article will explain in detail how MySQL uses profiling. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.
Mysql SQL optimization tool We often use explain to parse the implementation of sql, according to the implementation plan to evaluate the performance consumption bottleneck of sql, and MYSQL Profiling provides us with detailed SQL implementation process such as cpu/io/swap/memory usage and each process execution time consumption.
The main uses are 1: to view the consumption bottleneck location of SQL execution 2, to view the execution process of sql, and where each operation is in which source file.
Here is a brief introduction to how to use it:
Profiling is supported after mysql version 5.0.37. After mysql5.7, it can be replaced by performance_schema (25.18.1 Query Profiling Using Performance Schema), but it is more convenient to track it at session level.
1. Related variables
(root:localhost:Wed Nov 15 16:32:50 2017) [performance_schema] > show variables like'% profil%'
+-+ +
| | Variable_name | Value |
+-+ +
| | have_profiling | YES | # # whether profile is supported |
| | profiling | ON | # # whether to enable profile. 0 | off: disable; 1 | on: enable |
| | profiling_history_size | 15 | number of historical sql displayed in # #. Default is the last 15, and the maximum is 100. |
+-+ +
2. Check the syntax
You can view the help documentation through help show profiles
Show profiles can view the history of the last 15 sql
Click (here) to collapse or open
SHOW PROFILE [type [, type]...]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
Type:
ALL
| | BLOCK IO |
| | CONTEXT SWITCHES |
| | CPU |
| | IPC |
| | MEMORY |
| | PAGE FAULTS |
| | SOURCE |
| | SWAPS |
3. Use examples
1) enable profling
(root:localhost:Wed Nov 15 16:37:00 2017) [dbtest] > set profiling=1
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root:localhost:Wed Nov 15 16:37:14 2017) [dbtest] > show variables like'% profil%'
+-+ +
| | Variable_name | Value |
+-+ +
| | have_profiling | YES |
| | profiling | ON |
| | profiling_history_size | 15 |
+-+ +
3 rows in set (0.00 sec)
2) View the sql of all profiling records
(root:localhost:Wed Nov 15 16:37:16 2017) [dbtest] > show profiles
+-+
| | Query_ID | Duration | Query | |
+-+
| | 1 | 0.00089900 | show variables like'% profil%' |
+-+
3) View the sql of the specified profiling record
(root:localhost:Wed Nov 15 16:39:14 2017) [dbtest] > show profile for query 2
+-+ +
| | Status | Duration |
+-+ +
| | starting | 0.000103 | |
| | checking permissions | 0.000008 | |
| | Opening tables | 0.000060 | |
| | init | 0.000023 | |
| | System lock | 0.000011 | |
| | optimizing | 0.000007 | |
| | statistics | 0.000016 | |
| | preparing | 0.000015 | |
| | executing | 0.000007 | |
| | Sending data | 0.000063 | |
| | end | 0.000004 | |
| | query end | 0.000010 | |
| | closing tables | 0.000012 | |
| | freeing items | 0.000016 | |
| | logging slow query | 0.000003 | |
| | logging slow query | 0.000070 | |
| | cleaning up | 0.000014 | |
+-+ +
4) View the sql of the specified profiling record, and display the step consumption information of cpu/block io/
(root:localhost:Wed Nov 15 16:43:47 2017) [dbtest] > show profile cpu, block io for query 2
+-+ +
| | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | |
+-+ +
| | starting | 0.000103 | NULL | NULL | NULL | NULL | |
| | checking permissions | 0.000008 | NULL | NULL | NULL | NULL | |
| | Opening tables | 0.000060 | NULL | NULL | NULL | NULL | |
| | init | 0.000023 | NULL | NULL | NULL | NULL | |
| | System lock | 0.000011 | NULL | NULL | NULL | NULL | |
| | optimizing | 0.000007 | NULL | NULL | NULL | NULL | |
| | statistics | 0.000016 | NULL | NULL | NULL | NULL | |
| | preparing | 0.000015 | NULL | NULL | NULL | NULL | |
| | executing | 0.000007 | NULL | NULL | NULL | NULL | |
| | Sending data | 0.000063 | NULL | NULL | NULL | NULL | |
| | end | 0.000004 | NULL | NULL | NULL | NULL | |
| | query end | 0.000010 | NULL | NULL | NULL | NULL | |
| | closing tables | 0.000012 | NULL | NULL | NULL | NULL | |
| | freeing items | 0.000016 | NULL | NULL | NULL | NULL | |
| | logging slow query | 0.000003 | NULL | NULL | NULL | NULL | |
| | logging slow query | 0.000070 | NULL | NULL | NULL | NULL | |
| | cleaning up | 0.000014 | NULL | NULL | NULL | NULL | |
+-+ +
5) View the sql of the specified profiling record, and display the source file information of each step
(root:localhost:Wed Nov 15 16:44:09 2017) [dbtest] > show profile source for query 2
+-+
| | Status | Duration | Source_function | Source_file | Source_line | |
+-+
| | starting | 0.000103 | NULL | NULL | NULL | |
| | checking permissions | 0.000008 | check_access | sql_parse.cc | 5635 | |
| | Opening tables | 0.000060 | open_tables | sql_base.cc | 5029 | |
| | init | 0.000023 | mysql_prepare_select | sql_select.cc | 1051 | |
| | System lock | 0.000011 | mysql_lock_tables | lock.cc | 304 | |
| | optimizing | 0.000007 | optimize | sql_optimizer.cc | 138|
| | statistics | 0.000016 | optimize | sql_optimizer.cc | 381 |
| | preparing | 0.000015 | optimize | sql_optimizer.cc | 504 |
| | executing | 0.000007 | exec | sql_executor.cc | 110 | |
| | Sending data | 0.000063 | exec | sql_executor.cc | 187|
| | end | 0.000004 | mysql_execute_select | sql_select.cc | 1106 | |
| | query end | 0.000010 | mysql_execute_command | sql_parse.cc | 5307 | |
| | closing tables | 0.000012 | mysql_execute_command | sql_parse.cc | 5383 | |
| | freeing items | 0.000016 | mysql_parse | sql_parse.cc | 6676 | |
| | logging slow query | 0.000003 | log_slow_do | sql_parse.cc | 2077 | |
| | logging slow query | 0.000070 | log_slow_do | sql_parse.cc | 2078 | |
| | cleaning up | 0.000014 | dispatch_command | sql_parse.cc | 1878 | |
+-+
17 rows in set, 1 warning (0.00 sec)
6) View the sql of the specified profiling record and display all the step consumption information
(root:localhost:Wed Nov 15 16:40:34 2017) [dbtest] > show profile all for query 2\ G
* * 1. Row *
Status: starting
Duration: 0.000103
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: NULL
Source_file: NULL
Source_line: NULL
* 2. Row * *
Status: checking permissions
Duration: 0.000008
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: check_access
Source_file: sql_parse.cc
Source_line: 5635
* 3. Row * *
Status: Opening tables
Duration: 0.000060
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: open_tables
Source_file: sql_base.cc
Source_line: 5029
* * 4. Row *
Status: init
Duration: 0.000023
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_prepare_select
Source_file: sql_select.cc
Source_line: 1051
* * 5. Row * *
Status: System lock
Duration: 0.000011
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_lock_tables
Source_file: lock.cc
Source_line: 304
* 6. Row * *
Status: optimizing
Duration: 0.000007
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 138
* 7. Row * *
Status: statistics
Duration: 0.000016
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 381
* * 8. Row *
Status: preparing
Duration: 0.000015
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 504
* 9. Row * *
Status: executing
Duration: 0.000007
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: exec
Source_file: sql_executor.cc
Source_line: 110
* 10. Row * *
Status: Sending data
Duration: 0.000063
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: exec
Source_file: sql_executor.cc
Source_line: 187
* 11. Row * *
Status: end
Duration: 0.000004
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_execute_select
Source_file: sql_select.cc
Source_line: 1106
* 12. Row * *
Status: query end
Duration: 0.000010
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 5307
* 13. Row * *
Status: closing tables
Duration: 0.000012
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 5383
* 14. Row * *
Status: freeing items
Duration: 0.000016
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: mysql_parse
Source_file: sql_parse.cc
Source_line: 6676
* * 15. Row * *
Status: logging slow query
Duration: 0.000003
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: log_slow_do
Source_file: sql_parse.cc
Source_line: 2077
* 16. Row * *
Status: logging slow query
Duration: 0.000070
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: log_slow_do
Source_file: sql_parse.cc
Source_line: 2078
* 17. Row * *
Status: cleaning up
Duration: 0.000014
CPU_user: NULL
CPU_system: NULL
Context_voluntary: NULL
Context_involuntary: NULL
Block_ops_in: NULL
Block_ops_out: NULL
Messages_sent: NULL
Messages_received: NULL
Page_faults_major: NULL
Page_faults_minor: NULL
Swaps: NULL
Source_function: dispatch_command
Source_file: sql_parse.cc
Source_line: 1878
17 rows in set, 1 warning (0.00 sec)
This is the end of the article on "how MySQL uses profiling". I hope the above content can be helpful to you so that you can learn more knowledge. if you think the article is good, please share it for more people to see.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
