Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use SHOW PROFILE and SHOW PROFILES sentences in MySQL 5.5. 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.

The SHOW PROFILE and SHOW PROFILES statements show the resource consumption of MySQL database statements.

It is important to note that both commands show SQL resource consumption for the current session, but not for other sessions.

-- you need to enable the Profiling parameter.

Mysql > show variables like 'profil%'

+-+ +

| | Variable_name | Value |

+-+ +

| | profiling | OFF |

| | profiling_history_size | 15 |

+-+ +

2 rows in set (0.10 sec)

Mysql > set profiling = 1

Query OK, 0 rows affected (0.00 sec)

Mysql > show session variables like 'profil%'

+-+ +

| | Variable_name | Value |

+-+ +

| | profiling | ON |

| | profiling_history_size | 15 |

+-+ +

2 rows in set (0.00 sec)

SHOW PROFILES displays the most recent statements sent to the MySQL server. The number of display statements is set by the profiling_history_size parameter, with a default value of 15 and a maximum value of 100. setting the parameter to 0 does not turn off the profiling function. Except for SHOW PROFILE and SHOW PROFILES, other statements are displayed in the list.

SHOW PROFIL displays the specific information of a statement. If no FOR QUERY statement is specified, the output shows the last statement executed. If FOR QUERY and statement number are specified, the information for the specified statement is displayed. The statement number corresponds to the Query_ID field in the SHOW PROFILES display result. The LIMIT statement limits the number of rows output. By default, the SHOW PROFILE statement displays only the status and run time fields, and the status field is similar to the status field in SHOW PROCESSLIST.

-- example

Mysql > select * from T9

Mysql > select * from t_arc1 limit 10

Mysql > show profiles

+-+

| | Query_ID | Duration | Query | |

+-+

| | 1 | 0.00019375 | select * from T9 |

| | 2 | 0.00023150 | select * from t_arc1 limit 10 |

+-+

2 rows in set (0.00 sec)

Mysql > show profile for query 2

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000058 | |

| | checking permissions | 0.000005 | |

| | Opening tables | 0.000011 | |

| | System lock | 0.000006 | |

| | init | 0.000015 | |

| | optimizing | 0.000003 | |

| | statistics | 0.000011 | |

| | preparing | 0.000010 | |

| | executing | 0.000002 | |

| | Sending data | 0.000094 | |

| | end | 0.000003 | |

| | query end | 0.000003 | |

| | closing tables | 0.000003 | |

| | freeing items | 0.000007 | |

| | logging slow query | 0.000001 | |

| | cleaning up | 0.000002 | |

+-+ +

16 rows in set (0.00 sec)

Mysql > show profile cpu for query 2

+-+

| | Status | Duration | CPU_user | CPU_system | |

+-+

| | starting | 0.000058 | 0.000000 | 0.000000 | |

| | checking permissions | 0.000005 | 0.000000 | 0.000000 | |

| | Opening tables | 0.000011 | 0.000000 | 0.000000 | |

| | System lock | 0.000006 | 0.000000 | 0.000000 | |

| | init | 0.000015 | 0.000000 | 0.000000 | |

| | optimizing | 0.000003 | 0.000000 | 0.000000 | |

| | statistics | 0.000011 | 0.000000 | 0.000000 | |

| | preparing | 0.000010 | 0.000000 | 0.000000 | |

| | executing | 0.000002 | 0.000000 | 0.000000 | |

| | Sending data | 0.000094 | 0.000000 | 0.000000 | |

| | end | 0.000003 | 0.000000 | 0.000000 | |

| | query end | 0.000003 | 0.000000 | 0.000000 | |

| | closing tables | 0.000003 | 0.000000 | 0.000000 | |

| | freeing items | 0.000007 | 0.000000 | 0.000000 | |

| | logging slow query | 0.000001 | 0.000000 | 0.000000 | |

| | cleaning up | 0.000002 | 0.000000 | 0.000000 | |

+-+

16 rows in set (0.00 sec)

-- View the file, function name and specific number of lines of the source code corresponding to each step in the execution of SQL parsing

MariaDB [test] > show profile source for query 31\ G

* * 1. Row *

Status: starting

Duration: 0.000034

Source_function: NULL

Source_file: NULL

Source_line: NULL

* 2. Row * *

Status: checking permissions

Duration: 0.000005

Source_function: check_access

Source_file: sql_parse.cc

Source_line: 6043

This is the end of the article on "how to use SHOW PROFILE and SHOW PROFILES sentences in MySQL 5.5". I hope the above content can be of some help 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.